MySQL的主从架构搭建

一、MySQL高可用集群介绍

1-1、数据库主从架构与分库分表

随着现在互联网的应用越来越大，数据库会频繁的成为整个应用的性能瓶颈。而我们经常使用的MySQL数据库，也会不断面临数据量太大、数据访问太频繁、数据读写速度太快等一系列的问题。所以，我们需要设计复杂的应用架构来保护孱弱的数据库，例如添加Redis缓存，增加MQ进行流量削峰等等。但是，数据库本身如果不能得到提升，这就相当于是水桶理论中的最短板。

而要提升数据库的性能，一种思路，当然是对数据库本身进行优化，例如对MySQL进行优化配置，或者干脆换成ClickHouse这一类的针对大数据的产品。另一方面就是跟微服务架构的思路一样，从单体架构升级到集群架构，这样才能真正全方位解放数据库的性能瓶颈。而我们后续要学习的分库分表就是一种非常常见的数据库集群架构管理方案。

但是就像微服务架构并不是简单的将服务从单机升级为就能一样，分库分表也并不只是字面意义上的将数据分到多个库或者多个表这么简单，他也是基于数据库产品的一系列分布式解决方案。在不同的应用场景下，针对不同的数据库产品，分库分表也有不同的落地方式。而我们后续，会以最为常见的MySQL数据库以及ShardingSphere框架来了解分库分表要如何进行。

1-2、MySQL主从同步原理

既然要解决MySQL数据库的分布式集群化问题，那就不能不先了解MySQL自身提供的主从同步原理。这是构建MySQL集群的基础，也是后续进行分库分表的基础，更是MySQL进行生产环境部署的基础。

其实数据库的主从同步，就是为了要保证多个数据库之间的数据保持一致。最简单的方式就是使用数据库的导入导出工具，定时将主库的数据导出，再导入到从库当中。这是一种很常见，也很简单易行的数据库集群方式。也有很多的工具帮助我们来做这些事情。但是这种方式进行数据同步的实时性比较差。

而如果要保证数据能够实时同步，对于MySQL，通常就要用到他自身提供的一套通过Binlog日志在多个MySQL服务之间进行同步的集群方案。基于这种集群方案，一方面可以提高数据的安全性，另外也可以以此为基础，提供读写分离、故障转移等其他高级的功能。

即在主库上打开Binlog日志，记录对数据的每一步操作。然后在从库上打开RelayLog日志，用来记录跟主库一样的Binlog日志，并将RelayLog中的操作日志在自己数据库中进行重演。这样就能够更加实时的保证主库与从库的数据一致。

MySQL的Binlog默认是不打开的。

他的实现过程是在从库上启动一系列IO线程，负责与主库建立TCP连接，请求主库在写入Binlog日志时，也往从库传输一份。这时，主库上会有一个IO Dump线程，负责将Binlog日志通过这些TCP连接传输给从库的IO线程。而从库为了保证日志接收的稳定性，并不会立即重演Binlog数据操作，而是先将接收到的Binlog日志写入到自己的RelayLog日志当中。然后再异步的重演RelayLog中的数据操作。

MySQL的BinLog日志能够比较实时的记录主库上的所有日志操作，因此他也被很多其他工具用来实时监控MySQL的数据变化。例如Canal框架，可以模拟一个slave节点，同步MySQL的Binlog，然后将具体的数据操作按照定制的逻辑进行转发。例如转发到Redis实现缓存一致，转发到Kafka实现数据实时流转等。而ClickHouse也支持将自己模拟成一个MySQL的从节点，接收MySQL的Binlog日志，实时同步MySQL的数据。

二、搭建MySQL服务

2-1、基础环境搭建

以下准备两台服务器，来搭建一个MySQL的主从集群。均安装CentOS7操作系统。 192.168.253.132将作为MySQL主节点，192.168.253.133将作为MySQL的从节点。

然后在两台服务器上均安装MySQL服务，MySQL版本采用mysql-8.0.31版本。

2-1、安装MySQL

2-1-1、下载MySQL

下载地址如下： /archives/co…

2-1-2、将下载的文件上传到服务器解压重命名

我将文件放到 /usr/local/soft 下面

然后进行解压




tar -xvf mysql-8.0.31-linux-glibc2.17-x86_64-



复制代码

名称太长，重命名为mysql




mv mysql-8.0.31-linux-glibc2.17-x86_64-minimal mysql



复制代码

2-1-3、创建数据目录，用户组及用户

创建数据目录





cd mysql 




mkdir data



复制代码

创建用户组和用户




groupadd mysql 



useradd -g mysql mysql



复制代码

修改权限




chown -R  /usr/local/soft/mysql/




复制代码

2-1-4、配置环境变量

打开文件




vim /etc/profile



复制代码

在最后一行添加以下内容




#mysql



export PATH=$PATH:/usr/local/soft/mysql/bin:/usr/local/soft/mysql/lib




复制代码

保存之后，使之生效





source /etc/profile



复制代码

2-1-5、初始化数据库




bin/mysqld --initialize --user=mysql --basedir=/usr/local/soft/mysql --datadir=/usr/local/soft/mysql/data




复制代码

执行完成之后，要记录原始密码

2-1-6、配置MySQL




vim /etc/



复制代码

添加如下配置(先将mysqld_safe中的配置注释掉)




[mysqld]



basedir=/usr/local/soft/mysql



datadir=/usr/local/soft/mysql/data




socket=/usr/local/soft/mysql/



port=3306




 



[client]



socket=/usr/local/soft/mysql/



复制代码

2-1-7、配置服务

将复制到 /etc//mysql中




cp -a ./support-files/ /etc//mysql



复制代码

赋权限




chmod +x /etc//mysql



复制代码

添加到系统服务




chkconfig --add mysql



复制代码

检查服务




chkconfig --list mysql




复制代码

2-1-8、启动服务并修改默认密码

启动服务




service mysql start




复制代码

到此mysql服务已完成搭建

登录MySQL




mysql -uroot -p




复制代码

然后输入上面生成的默认密码，即可登录成功

修改默认密码





ALTER USER "root"@"localhost" IDENTIFIED BY "root";



复制代码

修改可以远程任务ip连接




use mysql;




update user set host='%' where user='root';



复制代码

刷新配置





flush privileges;



复制代码

远程连接这时候会因为密码加密方式不同无法连接，报如下错误




Unable to load authentication plugin 'caching_sha2_password'.




复制代码

修改密码规则





ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';



复制代码

修改配置文件




vim /etc/



复制代码

添加如下配置





default_authentication_plugin=mysql_native_password



复制代码

2-2、搭建主从集群

目前已经将两个服务器（192.168.253.132/133）的mysql安装完毕，下面来进行主从配置

主从原理主要是通过BinLog日志来实现数据同步，因此可知数据库的BinLog是必须要配置的

2-2-1、配置master主服务

首先，配置主节点的mysql配置文件： /etc/(没有的话就手动创建一个) 这一步需要对master进行配置，主要是需要打开binlog日志，以及指定 severId。我们打开MySQL主服务的文件，在文件中一行server-id以及一个关闭域名解析的配置。然后重启服务。

2-2-1-1、配置文件

以192.168.253.132作为master节点，开始配置




[mysqld]



basedir=/usr/local/soft/mysql



datadir=/usr/local/soft/mysql/data




socket=/usr/local/soft/mysql/



log-error=/usr/local/soft/mysql/data/



pid-file=/usr/local/soft/mysql/data/



port=3306




# 默认使用“mysql_native_password”插件认证



#mysql_native_password




default_authentication_plugin=mysql_native_password



 



# 配置servierId



server-id=47




#开启binlog



log_bin=master-bin



log_bin-index=master-bin.index




skip-name-resolve



# 允许最大连接数



max_connections=200




# 允许连接失败的次数。



max_connect_errors=10




# 服务端使用的字符集默认为UTF8




character-set-server=utf8




# 创建新表时将使用的默认存储引擎



default-storage-engine=INNODB



 



# Disabling symbolic-links is recommended to prevent assorted security risks



symbolic-links=0




# Settings user and group are ignored when systemd is used.



# If you need to run mysqld under a different user or group,



# customize your systemd unit file for mariadb according to the



# instructions in http:///wiki/Systemd



 



[mysqld_safe]



#log-error=/usr/local/soft/mysql/mariadb/



#pid-file=//usr/local/soft/mysql/mariadb/



 



#



# include all files from the config directory



#



!includedir /etc/



 



[client]



socket=/usr/local/soft/mysql/



 



 



复制代码

配置说明：主要需要修改的是以下几个属性：

server-id：服务节点的唯一标识。需要给集群中的每个服务分配一个单独的ID。

log_bin：打开Binlog日志记录，并指定文件名。

log_bin-index：Binlog日志文件

重启MySQL服务， service mysqld restart

2-2-1-2、BinLog日志

这样就完成了master节点BinLog日志的配置，下面连接数据库查看一下BinLog日志信息




cd /usr/local/soft/mysql/data




ll



复制代码

如下红框内容即为binlog日志，从000001到000014轮询将日志以二进制的方式记录，然后记录binlog日志记录到了哪里

2-2-1-3、查看binLog记录情况

连接上数据库，然后执行show master status,就可以看到如下数据

其中：

File:当前binlog记录到了那个文件
Postition:记录到binlog文件的哪个位置
Binlog_Do_DB:需要记录binlog文件的库
Binlog_Ignore_DB:不需要记录binlog文件的库

后面两个字段没有进行配置，就表示是针对全库记录日志。这两个字段如何进行配置，会在后面进行介绍。

开启binlog后，数据库中的所有操作都会被记录到datadir当中，以一组轮询文件的方式循环记录。而指令查到的File和Position就是当前日志的文件和位置。而在后面配置从服务时，就需要通过这个File和Position通知从服务从哪个地方开始记录binLog。

2-2-2、配置slave从节点

2-2-2-1、配置

以192.168.253.133作为slave节点，开始配置




[mysqld]



basedir=/usr/local/soft/mysql



datadir=/usr/local/soft/mysql/data




socket=/usr/local/soft/mysql/



port=3306




 



 



#主库和从库需要不一致



server-id=48




#打开MySQL中继日志



relay-log-index=slave-relay-bin.index




relay-log=slave-relay-bin



#打开从服务二进制日志



log-bin=mysql-bin



#使得更新的数据写进二进制日志中



log-slave-updates=1




 



# 允许最大连接数



max_connections=200




# 允许连接失败的次数。



max_connect_errors=10




# 服务端使用的字符集默认为UTF8




character-set-server=utf8




 



# 创建新表时将使用的默认存储引擎



default-storage-engine=INNODB



# 默认使用“mysql_native_password”插件认证



#mysql_native_password




default_authentication_plugin=mysql_native_password



# 设置只读



#read-only=1




 



symbolic-links=0




 



!includedir /etc/



[client]



socket=/usr/local/soft/mysql/



 



复制代码

配置说明：主要需要关注的几个属性：

server-id：服务节点的唯一标识

relay-log：打开从服务的relay-log日志。

log-bin：打开从服务的bin-log日志记录。

重启MySQL服务， service mysqld restart

2-2-2-2、查看从节点信息

下图各参数可以显示出主节点相关信息，以及同步主节点binlog的相关信息，因为目前master和slave还没有建立联系所以下面没有任何信息

其中查看主从同步信息，主要查看如下两个参数：

Master_Log_File:master节点读取的binlog文件
Read_Master_Log_File_Pos:master节点读取的binlog文件位置
Slave_IO_Running/Relay_SQL_Running:当这两个值为yes的时候证明同步正常

2-2-3、建立主从同步

需要登录到slave节点，然后执行下面语句




CHANGE MASTER TO




MASTER_HOST='192.168.253.132',



MASTER_PORT=3306,



MASTER_USER='root',



MASTER_PASSWORD='root',



MASTER_LOG_FILE='master-bin.000001',



MASTER_LOG_POS=157,




GET_MASTER_PUBLIC_KEY=1;




start slave;



复制代码

注意，CHANGE MASTER指令中需要指定的MASTER_LOG_FILE和 MASTER_LOG_POS必须与主服务中查到的保持一致。并且后续如果要检查主从架构是否成功，也可以通过检查主服务与从服务之间的File和Position这两个属性是否一致来确定。

执行完之后再次执行 show slave status,就可以看到主节点的相关信息，以及读取的binlog信息

当slave节点的MASTER_LOG_FILE及MASTER_LOG_POS和主节点保持一致则证明搭建成功。

2-2-4、主从同步测试

2-2-4-1、分别查看master和slave节点的库

用Navicat连接之后可以，看到目前两个节点的库是一样的

2-2-4-2、在主节点创建新库

先执行 show master status

然后创建数据库create database syncdemo;

最后再执行show master status,可以看到binlog文件没变化，但是Position读取位置已经发生变化，从157-->509

2-2-4-3、在从节点查询所有库

可以发现刚刚在主节点创建的数据库已经同步到从库上面

再次查看从库的状态，可以发现读取master的binlog位置已经变成和master一样

2-2-5、全库同步与部分同步

在完成这个基本的MySQL主从集群后，我们还可以进行后续的实验：

之前提到，我们目前配置的主从同步是针对全库配置的，而实际环境中，一般并不需要针对全库做备份，而只需要对一些特别重要的库或者表来进行同步。那如何针对库和表做同步配置呢？

首先在Master端：在中，可以通过以下这些属性指定需要针对哪些库或者哪些表记录binlog




#需要同步的二进制数据库名,如果有多条则另起一行



binlog-do-db=masterdemo



#只保留7天的二进制日志，以防磁盘被日志占满(可选)



expire-logs-days = 7




#不备份的数据库



binlog-ignore-db=information_schema



binlog-ignore-db=performation_schema



binlog-ignore-db=sys



复制代码

然后在Slave端：在中，需要配置备份库与主服务的库的对应关系。




#如果salve库名称与master库名相同，使用本配置



replicate-do-db = masterdemo



#如果master库名[mastdemo]与salve库名[mastdemo01]不同，使用以下配置[需要做映射]



replicate-rewrite-db = masterdemo -> masterdemo01




#如果不是要全部同步[默认全部同步]，则指定需要同步的表



replicate-wild-do-table=masterdemo01.t_dict



replicate-wild-do-table=masterdemo01.t_num



复制代码

配置完成了之后，在show master status指令中，就可以看到Binlog_Do_DB和 Binlog_Ignore_DB两个参数的作用了。

2-2-6、GTID同步集群

上面我们搭建的集群方式，是基于Binlog日志记录点的方式来搭建的，这也是最为传统的MySQL集群搭建方式。而在这个实验中，可以看到有一个Executed_Grid_Set列，暂时还没有用上。实际上，这就是另外一种搭建主从同步的方式，即GTID搭建方式。这种模式是从MySQL5.6版本引入的。

GTID的本质也是基于Binlog来实现主从同步，只是他会基于一个全局的事务ID来标识同步进度。GTID即全局事务ID，全局唯一并且趋势递增，他可以保证为每一个在主节点上提交的事务在复制集群中可以生成一个唯一的ID 。

在基于GTID的复制中，首先从服务器会告诉主服务器已经在从服务器执行完了哪些事务的GTID值，然后主库会有把所有没有在从库上执行的事务，发送到从库上进行执行，并且使用GTID的复制可以保证同一个事务只在指定的从库上执行一次，这样可以避免由于偏移量的问题造成数据不一致。

他的搭建方式跟我们上面的主从架构整体搭建方式差不多。只是需要在中修改一些配置。

在主节点上：




gtid_mode=on




enforce_gtid_consistency=on




log_bin=on




server_id=单独设置一个



binlog_format=row



复制代码

在从节点上：




gtid_mode=on




enforce_gtid_consistency=on




log_slave_updates=1




server_id=单独设置一个



复制代码

然后分别重启主服务和从服务，就可以开启GTID同步复制方式。

2-2-7、主从同步失败处理

另外，这个主从架构是有可能失败的，如果在slave从服务上查看slave状态，发现Slave_SQL_Running=no，就表示主从同步失败了。这有可能是因为在从数据库上进行了写操作，与同步过来的SQL操作冲突了，也有可能是slave从服务重启后有事务回滚了。

如果是因为slave从服务事务回滚的原因，可以按照以下方式重启主从同步：




mysql> stop slave ;



mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;



mysql> start slave ;



复制代码

而另一种解决方式就是重新记录主节点的binlog文件消息




mysql> stop slave ;



mysql> change master to .....



mysql> start slave ;



复制代码

但是这种方式要注意binlog的文件和位置，如果修改后和之前的同步接不上，那就会丢失部分数据。所以不太常用。

秒客网