MySQL好好学五（主从架构）

大纲

主从复制
解决主从同步数据不一致
解决数据丢失
实现读写分离、数据备份、高可用

主从复制

1、主从复制简介

MySQL单机版部署可能会有性能问题，MySQL提供了一套主从复制的架构，以提高其可用性。MySQL主从复制架构，就是部署两台机器，一台机器上部署的MySQL是master节点，另一台机器上部署的MySQL是slave节点。

我们可以向master节点读写数据，master节点会把写入的数据自动复制到slave节点去，让slave节点与master节点有一份一样的数据，即读写分离。

读写分离指将数据库中的读操作与写操作进行区分并分配到不同的服务器上。这样可以提高系统性能、降低单台服务器的负载，并保证数据的安全性，常见的实现方式：主从复制（Master-Slave Replication）或者使用中间件如ProxySQL等来管理连接池

2、主从复制的原理

主节点

当主节点上进行 insert、update、delete 操作时，会按照时间先后顺序写入到 binlog 中；当从节点连接到主节点时，主节点会创建一个叫做 binlog dump 的线程；一个主节点有多少个从节点，就会创建多少个 binlog dump 线程；当主节点的 binlog 发生变化的时候，也就是进行了更改操作，binlog dump 线程就会通知从节点 (Push模式)，并将相应的 binlog 内容发送给从节点；

从节点当开启主从同步的时候，从节点会创建两个线程用来完成数据同步的工作。
I/O线程：

此线程连接到主节点，主节点上的 binlog dump 线程会将 binlog 的内容发送给此线程。此线程接收到 binlog 内容后，再将内容写入到本地的 relay log。

主从配置一般都是和读写分离相结合，主服务器负责写数据，从服务器负责读数据，并保证主服务器的数据及时同步到从服务器。

简单来说，你只要给master挂上一个slave节点，slave节点的IO线程就会跟master节点建立网络连接，然后请求master节点传输binlog日志，master节点的IO dump线程就负责传输binlog日志给从节点，从节点收到日志后就可以回放增删改操作恢复数据了。

3、主从复制的方式

异步复制

异步复制(Asynchronous replication）是MySQL的默认复制。异步复制是指主库在执行完客户端提交的事务后会立刻将执行结果返回给客户端，并不关心从库是否已经接收处理，这样带来的问题就是当主死掉了，此时主上提交的事务可能还没有传到从上。而强行将从提升为主就会导致新主上的数据不完整。

异步复制是一种基于偏移量的主从复制，实现的原理是：主库开启binlog功能并授权从库连接主库，从库通过change master得到主库的相关同步信息，然后连接主库进行验证，主库的IO线程根据从库slave的线程请求，从master.info开始记录的位置点向下开始取信息，同时把提取到的位置点和最新的位置与binlog信息一同发给从库IO线程，从库将相关的sql语句放在relay_log中，最终从库的sql线程会将relay_log里的sql语句应用到从库上，至此同步过程完成，以后一直循环此过程。对于异步复制而言，主库将事务的Binlog事件写入到binlog文件中，此时主库会通知下dump线程发送这些新的binlog然后主库会继续处理提交操作，而此时并不保证这些日志会传输到任何一个从库的节点上。

半同步复制

半同步复制（Semi synchronous replication）。介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时也会造成一定程度的延迟，这个延迟为一个TCP/IP往返的时间。所以半同步复制需要在低延时的网络中使用。对于半同步复制而言，是介于同步复制和异步复制之间的一种，主库需要等待至少一个从库节点收到并且刷新binlog到relay日志中，主库不需要等待所有从库给主库反馈，同时这里只是收到反馈而不死和完全执行并且提交事务的反馈，这样会节省很多的时间。

全同步复制

全同步复制（Fully synchronous replication）。当主库执行完一个事务，所有的从库都执行了该事务才会将结果返回给客户端。这样保证了数据的安全性，但是因为需要等待所有从库执行完该事务才能返回客户端结果，所以全同步复制的性能必然会受到很大的影响。对于全同步复制而言，当主库提交一个事务后，要求所有从库节点必须收到，执行并提交这些事务，然后主库线程才能继续做后续操作，而因此带来的问题就是主库完成一个事务的时间被大幅度拉长，性能降低。

4、搭建示例

一主一从

-- 步骤一：建立时间服务器、同步时间
-- 主节点的数据库服务器上安装NTP
[root@mysqlmaster ~]# yum -y install ntp
-- 主节点配置NTP
[root@mysqlmaster ~]# vim /etc/ntp.conf //添加如下两行
 server 127.127.1.0  
 fudge 127.127.1.0 stratum 8  
参数说明：
server 127.127.1.0 //local clock,就和本地系统时间同步。127.127.1.0在这里是一个IP地址，不是网段。 
fudge 127.127.1.0 stratum 8 设置时区为东八区，在安装系统时，选择的时上海时间 。

-- 主节点重启NTP
[root@mysqlmaster ~]# systemctl restart ntpd  //重启ntp服
务
[root@mysqlmaster ~]# netstat -anpt | grep ntpd    //查看ntp服务
[root@mysqlmaster ~]# systemctl enable netpd    //设置开机自动启动 


-- 主节点关闭防火墙
[root@mysqlmaster ~]# systemctl stop firewalld  //关闭防火墙
[root@mysqlmaster ~]# systemctl disable firewalld  //关闭开机自动启动 
-- 在从节点上同步时间
[root@mysqlslave1 ~]# yum -y install nptdate  //安装npt
[root@mysqlslave1 ~]# /usr/bin/ntpdate 192.168.116.20  //此处的ip为自己的主库ip


-- 步骤二：配置主数据库服务器
-- 主节点：创建主从复制的授权用户
mysql> grant replication slave on *.* to slave@192.168.116.3 identified by '123'
说明：当你的从数据库来复制的时候，使用的是slave用户， 
slave用户的权限是replication slave  作用到所有库下的所有表。 
192.168.116.3是从数据库的ip

-- 配置主库的主配置文件my.cnf

[root@mysqlmaster ~]# vim /etc/my.cnf //添加一下内容

 [mysqld]
 ......   //省略部分内容
 log-bin=/data/mysql/log/mysql-bin-master  #启用二进制日志
 server-id=1   #数据库服务器ID标识
 binlog-do-db=HA #可以被从服务器复制的库, 即二进制需要同步的数据库名 


-- 主节点重启mysql，并查看状态 
[root@mysqlmaster ~]# /etc/init.d/mysqld restart 
mysql> show master status; 
[root@mysqlmaster ~]# ls /data/mysql/log/  #查看二进制日志


-- 导出数据库
[root@mysqlmaster ~]# mysqldump  -uroot -p123 HA >HA.sql //导出数据库
[root@mysqlmaster ~]# scp HA.sql root@192.168.116.3 //将数据发送给从服务器 



-- 步骤三：配置从数据库配置
-- 两台数据库版本要一直
mysql> show variables like '%version%';   //查看mysql版本

-- 测试连接到主服务器是否正常
[root@mysqlslave1 ~]# mysql -uslave -p123 -h 192.168.116.20  //使用在主库中授权的用户登录
mysql> show databases; //查看库
这里只会看到一个information_schema的库  因为用户授权的权限只是复制的权限（replication slave） 


-- 导入数据库，和主库保持一致
[root@mysqlslave1 ~]# mysql -uroot -p123 -e "create database HA;" //创建HA库
[root@mysqlslave1 ~]# mysql -uroot -p123 HA<HA.sql  //将数据导入HA 


-- 修改从库的my.conf文件
server-id = 2  
relay-log=/data/mysql/log/relay-log-bin  //中继日志文件的路径名称
relay-log-index=/data/mysql/log/slave-relay-bin.index  //中继日志索引文件的路径名称 


注释： server-id ：从服务器ID号，不要和主ID相同 ，如果设置多个从服务器，每个从服务器必须有一个唯一的 server-id值，必须与主服务器的以及其它从服务器的不相同。这些ID值能唯一识别复制服务器 群集中的每个服务器实例
-- 重启从数据库服务 
[root@mysqlslave1 ~]#  /etc/init.d/mysqld restart

-- 在从库中更改其相关配置
[root@mysqlslave1 ~]#  mysql -uroot -p123
 mysql> stop slave; //停止slave
 mysql> change master to master_host='192.168.116.20',
                         master_user='slave',master_password='123',
                          master_log_file='mysql-bin-master.000019',master_log_pos=588; 


注释：

master_host：主库的ip地址
master_user：在主库中做的具有replication权限的授权用户名
master_password：主库的数据库密码
master_log_file：主库的二进制文件
master_log_pos：主库的二进制文件的其实位置
这里 master_log_file，master_log_pos的值 通过上边的命令show master status; 在主库中查看，一定要与主库的查询结果保持一致。

-- 启动slave 并查看slave状态
mysql> start slave;
mysql> show slave status;
说明：
查看其中的关键信息：
1）Master_Host：主库的ip地址是否正确
2）Master_User：是否使用的是在主库中授权的用户
3）Master_Log_File：是否与在主库中进行show master status显示的信息一致
4）Slave_IO_Running：从库中的I/O线程是否开启
5）Slave_SQL_Running：从库中的SQL线程是否开启 

-- 查看主库连接信息
mysql> show processlist \G;
查看其中关键信息：
1）User：用户是否是授予aplication slave权限的用户
2）Host：是否是从库的ip地址
3）Command：Binlog Dump 是否开启

一主一从错误分析

-- 没有关闭防火墙
[root@mysqlmaster ~]# systemctl stop firewalld 
-- 主库在用户授权时，从库的ip写错 
进入主库mysql 进行查看
mysql> select user,host from mysql.user;
查看授权用户对用的ip是否为从库 

-- 在从库中进行更改配置时出错 
进入从库 重新更改配置
mysql> change master to master_host='192.168.116.20',
                        master_user='slave',master_password='123',
                         master_log_file='mysql-bin-master.000019',master_log_pos=588; 
master_log_file master_log_pos 这两个值  进入主库进行查看保持与主库一致
mysql> show master status;
注意：master_log_file、master_log_po 的值一定要与主库对应

联级复制(M-S-S）

-- 主服务器上授权用户 
[root@mysqlmaster ~]# mysql -uroot -p123    //进入数据库
mysql> grant replication slave on *.* to slave@'192.168.116.%' identified by '123' //授权用户
mysql> flush privileges; //刷新授权用户 


-- 修改主库配置文件
[root@mysqlmaster ~]# vim /etc/my.cnf
添加以下内容
[mysqld]
………省略部分内容
server-id=1    
binlog-do-db=HA        
log-bin=/data/mysql/log/mysql-bin-master    
sync-binlog=1
binlog-format=row 

参数说明:
server-id：指定主库实例，每台Mysql服务器中的id不能相同
binlog-do-db：指定要实现复制的库
log-bin：二进制文件的文件位置
sync-binlog：每写缓冲多少次就同步到磁盘
sync_binlog=1：表示同步写缓冲和磁盘二进制日志文件，不使用文件系统缓存，在使用innodb事务引擎时，在复
制环境中，为了保证最大的可用性，都设置为“1”，但会对影响io的性能。
binlog-format：格式化二进制文件日志.


-- 重启服务、查看状态
[root@mysqlmaster ~]# /etc/init.d/mysqld restart
//进入数据库后
mysql> show master status;//显示主服务器的当前binlog文件及事件位置 
--导出主服务器HA库完整备份， 拷贝到 中继服务器 和slave服务器
[root@mysqlmaster ~]# mysqldump -uroot -p123 -B HA>ha.sql
[root@mysqlmaster ~]# scp ha.sql root@192.168.116.2:~
[root@mysqlmaster ~]# scp ha.sql root@192.168.116.3:~ 


-- 部署Slave中继
-- 导入数据库
[root@mysqlslave1 ~]# mysql -uroot -p123 <ha.sql 
-- 配置从库的配置文件
[root@mysqlslave1 ~]# vim /etc/my.cnf
 添加以下内容
  …………   //省略部分内容
 server-id=2
 log-slave-updates=1
 log-bin=/data/mysql/log/mysql-bin-slave1
 relay-log=/data/mysql/log/relay-log-bin
 relay-log-index=/data/mysql/log/slave-relay-bin.index 
参数说明：
server-id：指定主库实例，每台Mysql服务器中的id不能相同
log-slave-updates：在上面讲到的中从复制过程中，从库会通过I/O线程将读取主库的二进制文件并写到自己的中 继日志中，在这里的Slave不仅需要接收，它还需要往下传递二进制文件。所以添加这个参数 后，中继从库在把接收到的二进制文件写入到从库时，同时也会写到自己的二进制文件中。 在级联复制中，这个参数是必要的。
log-bin：指定二进制文件位置
relay-log：指定中继文件位置
relay-log-index：指定中继文件索引位置 

-- 重启mysql
[root@mysqlslave1 ~]# /etc/init.d/mysqld restart 

-- 进行从库的授权
[root@mysqlslave1 ~]# mysql -uroot -p123
 mysql> stop slave;
 msyql> change master to master_host='192.168.116.20',master_user='slave',
         master_password='123',master_log_file='mysql-bin-master.000001',
         master_log_pos=154;
 mysql> start slave; 

-- 查看中继服务的状态
mysql> show slave status \G 
 //通过上面的方法进行检查其中的参数 I/O、SQL 两个线程的状态

-- 授权用户（给mysqlslave2使用）
mysql> grant replication slave on *.* to 'slave'@'192.168.116.%' identified by '123'
mysql> flush privileges;
mysql> show master status; //查看slave中继服务器的当前的binlog文件即事件位置


--步骤三：部署salve
-- 导入数据库
[root@mysqlslave2 ~]# mysql -uroot -p123 <ha.sql 
-- 修改配置文件 
[root@mysqlslave2 ~]# vim /etc/my.cnf
 //添加以下内容
  [mysqld]
 ………省略部分内容
 server-id = 3
 relay-log=/data/mysql/log/relay-log-bin 
 relay-log-index=/data/mysql/log/slave-relay-bin.index 

--重启mysql
[root@mysqlslave2 ~]# /etc/init.d/mysqld restart 
-- 授权
[root@mysqlslave2 ~]# mysql -uroot -p123 //进入数据库
 mysql> stop slave;
 mysql> change master to master_host='192.168.116.3',master_user='slave',
         master_password=123,master_log_file='mysql-bin-slave1.000001',
         master_log_pos='448';
 mysql> start slave; 

-- 查看状态
mysql> show slave status\G

解决主从同步数据不一致

MySQL主从复制是一种常用的数据库架构，它可以提高数据库的可用性和性能。但是，由于网络延迟、主从复制配置不当等原因，可能会导致数据不一致的问题，这是一个需要高度重视的问题。

1、主从同步数不一致原因

1.网络延迟：主从复制需要通过网络进行数据同步，如果网络延迟过高，就会导致数据同步不及时，从而导致数据不一致。

2.主从复制配置不当：如果主从复制配置不当，例如主库和从库的时区不同、二进制日志格式不同等，也会导致数据不一致。

3.主库和从库出现故障：如果主库和从库出现故障，例如宕机、断电等，也会导致数据不一致。

2、主从同步数据不一致解决方案

1.设置正确的时区：如果主从库的时区不同，可以在主库和从库上分别设置正确的时区。可以使用命令“SET GLOBAL time_zone = '+8:00';”，其中“+8:00”表示东八区的时区。

2.选择正确的二进制日志格式：在MySQL 5.6版本以后，推荐使用“ROW”格式的二进制日志格式，因为它可以记录每一行数据的变化情况，从而避免了数据不一致的问题。

3.使用GTID：GTID是一种全局事务标识符，可以在主从库之间进行数据同步。使用GTID可以避免由于网络延迟等原因导致的数据不一致。

4.定期检查主从同步状态：可以使用命令“SHOW SLAVE STATUS\G;”来查看主从同步状态。如果发现主从同步状态异常，可以及时进行处理。

5.使用第三方工具：可以使用第三方工具，例如pt-table-checksum和pt-table-sync，来检查和同步主从库之间的数据。
注意：
在使用GTID的过程中，需要先在主库和从库上启用GTID，然后使用命令“CHANGE MASTER TO MASTER_USE_GTID=slave_pos;”来进行主从同步。使用pt-table-sync工具可以检查并同步主从库之间的数据，最终达到数据一致的目的。