MySQL 主从复制

文章目录

• 一、 主从复制介绍
• • 1.1 什么是主从复制
  • 1.2 为何要做主从
  • 1.3 主从复制架构
  • 1.4 主从复制原理
• 二、主从复制实现
• • 2.1 主节点配置
  • 2.2 从节点配置
  • 2.3 当主节点业务运行一段时间后，后期加载从节点的实现思路
  • 2.3 同步时出现错误解决方案
  • 2.4 如果在从节点写错了与主服务器的同步信息，需要先清除后重新写
  • 2.5 主从复制时候可以指定 START SLAVE 语句，指定执到特定的点
• 三、级联复制实现
• 四、主主复制
• 五、复制过滤器
• 六、复制过程的监控和维护
• 七、主从复制不一致原因及解决方案
• • 7.1 主从复制不一致原因
  • 7.2 主从不一致修复方法
  • 7.3 如何避免主从不一致

一、 主从复制介绍 1.1 什么是主从复制

将主服务器的binlog日志复制到从服务器上执行一遍，达到主从数据的一致状态，称之为主从复制。一句话表示就是，主数据库做什么，从数据库就跟着做什么。

1.2 为何要做主从

• 为实现服务器负载均衡/读写分离做铺垫，提升访问速度

  #1、什么是读写分离
  有了主从保持数据一致作为大前提，我们便可以分离读写操作，其中Master负责写操作的负载，
  也就是说一切写的操作都在Master上进行，而读的操作则分摊到Slave上进行。
  
  #2、读写分离的作用
  先说答案：读写分离可以大大提高读取的效率。
  
  在一般的互联网应用中，经过一些数据调查得出结论，读/写的比例大概在 10：1左右 ，
  也就是说写操作非常少，大量的数据操作是集中在读的操作（如某些应用，像基金净值预测的网站。
  其数据的更新都是有管理员更新的，即更新的用户比较少。而查询的用户数 量会非常的多。）
  此时我们可以制作一主多从，因为写操作很少，所以由一个主库负责即可，而大量的读操作则分配给
  多个从库，这样占据比例最大的读操作的压力就被负载均衡了，因此读效率会得到了极大的提升，
  另外，还有一个原因就是：写操作涉及到锁的问题，不管是行锁还是表锁还是块锁，
  都是比较降低系统执行效率的事情。我们这样的分离是把写操作集中在一个节点上，
  而读操作其其他的N个节点上进行，这从另一个方面有效的提高了读的效率，保证了系统的性能及高可用性。
  
  #3、具体做法
  方案一：
  就是主库写，从库读
  
  方案二：
  主库负责写，还有部分读，从库只负责读，而且是读操作的主力
  即当主服务器比较忙时，部分查询请求会自动发送到从服务器中，以降低主服务器的工作负荷。
  

• 通过复制实现数据的异地备份，保障数据安全

• 提高数据库系统的可用性

1.3 主从复制架构

一主一从复制架构

一主多从复制架构

级联复制、一主一从、一主多从、双主模式。

1.4 主从复制原理

主从复制相关线程

• 主节点：
  • dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
• 从节点：
  • I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
  • SQL Thread：从中继日志中读取日志事件，在本地完成重放

跟复制功能相关的文件：

• master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
• relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系
• mysql-relay-bin.00000#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志

MySQL8.0 取消 master.info 和 relay-log.info文件

终极日志如下：

[root@rocky8 ~]# file /var/lib/mysql/rocky8-relay-bin.000001
/var/lib/mysql/rocky8-relay-bin.000001: MySQL replication log, server id 1 MySQL V5+, server version 8.0.26

二、主从复制实现 2.1 主节点配置

1.在主节点开启二进制日志

vim /etc/my.cnf
[mysqld]
log_bin=1

2.为当前节点设置一个全局惟一的ID号

vim /etc/my.cnf
[mysqld]
log_bin=1
server-id=1
log-basename=master  #可选项，设置datadir中日志名称，确保不依赖主机名

server-id的取值范围
1 to 4294967295 (>= MariaDB 10.2.2)，默认值为1
0 to 4294967295 (<= MariaDB 10.2.1)，默认值为0，如果从节点为0，所有master都将拒绝此
slave的连接

3.查看主节点二进制日志的文件和位置开始进行复制

SHOW MASTER STATUS;

4.创建有复制权限的用户账号

GRANT REPLICATION SLAVE  ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';

#MySQL8.0 分成两步实现
mysql> create user tom@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to tom@'10.0.0.%';

具体步骤

1.修改配置文件
vim /etc/my.cnf

[mysqld]
log_bin=/data/mysql/logs/mysql-bin # 二进制日志存放目录及前缀
server-id=1 # 指定唯一id号

2.创建目录并且授权     
[root@rocky8 ~]# mkdir -p /data/mysql/logs
[root@rocky8 ~]# chown -R mysql.mysql /data/mysql 

3.重启mysql

4.登录数据库并查看当前二进制日志位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      156 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
5.创建账号
mysql> create user tom@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.*  to tom@'10.0.0.%';

2.2 从节点配置

1.启动中继日志

[mysqld]
server_id=# #为当前节点设置一个全局惟的ID号
log-bin # 建议从节点也开启二进制日志
read_only=ON #设置数据库只读，针对supper user无效
relay_log=relay-log #relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index  #默认值hostname-relay-bin.index

2.使用有复制权限的用户账号连接至主服务器，并启动复制线程

CHANGE MASTER TO MASTER_HOST='10.0.0.201', 
MASTER_USER='tom', 
MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='mysql-bin.000003', 
MASTER_LOG_POS= 156;


START SLAVE [IO_THREAD|SQL_THREAD];
SHOW SLAVE STATUS;

具体操作
1.安装和主节点一样版本的数据库
2.修改从节点配置
3.启动从节点
4.执行change master to 命令

mysql> show slave status;
Empty set, 1 warning (0.01 sec)

mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.201', 
    -> MASTER_USER='tom', 
    -> MASTER_PASSWORD='123456', 
    -> MASTER_LOG_FILE='mysql-bin.000003', 
    -> MASTER_LOG_POS= 156;
Query OK, 0 rows affected, 8 warnings (0.01 sec)

5.再次查看状态

mysql> show slave status;
               Slave_IO_State: 
                  Master_Host: 10.0.0.201
                  Master_User: tom
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 156
               Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: No
            Slave_SQL_Running: No

可以看出 Slave_IO_Running: No ， Slave_SQL_Running: No 都未开启
Seconds_Behind_Master: NULL
5.开启线程

mysql> start slave;


mysql> show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.0.0.201
                  Master_User: tom
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 156
               Relay_Log_File: relay-log.000003
                Relay_Log_Pos: 371
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes # 已经开启
            Slave_SQL_Running: Yes # 已经开启
			。。。
			  Seconds_Behind_Master: 0 # 已经显示出数据

6。查看状态

[root@rocky8 my.cnf.d]# netstat -ntp
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 10.0.0.201:22           10.0.0.1:3435           ESTABLISHED 4329/sshd: root [pr 
tcp        0     52 10.0.0.201:22           10.0.0.1:7359           ESTABLISHED 4464/sshd: root [pr 
tcp6       0      0 10.0.0.201:3306         10.0.0.211:49566        ESTABLISHED 5261/mysqld  

可以看到从节点与主节点保持持续的连接。

2.3 当主节点业务运行一段时间后，后期加载从节点的实现思路

主节点：需要先把主节点的数据做个完全备份，然后开启新的二进制日志
从节点：需要先恢复完全备份的数据，然后同步主节点上完全备份后新的二进制日志

1.主节点全量备份

[root@rocky8 ~]# mysqldump -uroot -p123 -A -F --single-transaction --master-data=1 > /opt/full_backup.sql

2.将全量备份的sql文件拷贝到从节点上去然后change master to 字段下添加如下：

vim /root/full_backup.sql

CHANGE MASTER TO 
MASTER_HOST='10.0.0.201',
MASTER_USER='tom',
MASTER_PASSWORD='123456', # 手动添加的

MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=156; #自动生成的

3.配置从节点的配置文件

[mysqld]
server-id=2
log-bin

read_only=ON

relay_log=relay-log
relay_log_index=relay-log.index

4.重启从节点数据库，执行sql语句

mysql> source /root/full_backup.sql

6.开启IO和sql线程

mysql> start slave;

2.3 同步时出现错误解决方案

可以在从服务器忽略几个主服务器的复制事件，此为global变量，或指定跳过事件的ID
注意: Centos 8.1以上版本上的MariaDB10.3主从节点同时建同名的库和表不会冲突，建主键记录会产生冲突
方式1.

#系统变量，在从节点上指定跳过复制事件的个数
mysql> SET GLOBAL sql_slave_skip_counter = N;
mysql> stop slave; # 先停止
mysql> start slave; # 在启动

方式2.

#服务器选项，只读系统变量，指定跳过事件的ID，需要在配置文件中指定
[mysqld]
slave_skip_errors=1007|ALL （all表示忽略全部错误ID）

2.4 如果在从节点写错了与主服务器的同步信息，需要先清除后重新写

在从节点上执行

mysql> reset slave all #清除所有从服务器上设置的主服务器同步信息，如HOST，PORT, USER和 PASSWORD 
等

2.5 主从复制时候可以指定 START SLAVE 语句，指定执到特定的点

START SLAVE [thread_types]
START SLAVE [SQL_THREAD] UNTIL   MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS =
log_pos
START SLAVE [SQL_THREAD] UNTIL   RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS =
log_pos
thread_types:
    [thread_type [, thread_type] ... ]
thread_type: IO_THREAD | SQL_THREAD

三、级联复制实现

需要在中间的从服务器启用以下配置 ，实现中间slave节点能将master的二进制日志在本机进行数据库更新，并且也同时更新本机的二进制，从而实现级联复制

[mysqld]
server-id=18
log_bin
log_slave_updates      #级联复制中间节点的必选项,MySQL8.0此为默认值,可以不要人为添加,其它
版本默认不开启
read-onl

步骤：1.在现有的主从模式下，只需要在中间节点开启log_slave_updates
2.开始事务完全备份从节点的数据到第三节点
3.第三节点配置同步第二节点即可

四、主主复制

主主复制：两个节点，都可以更新数据，并且互为主从
容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id
配置一个节点使用奇数id

auto_increment_offset=1   #开始点
auto_increment_increment=2 #增长幅度

另一个节点使用偶数id

auto_increment_offset=2
auto_increment_increment=2

主主复制的配置步骤：
(1) 各节点使用一个惟一server_id
(2) 都启动binary log和relay log
(3) 创建拥有复制权限的用户账号
(4) 定义自动增长id字段的数值范围各为奇偶
(5) 均把对方指定为主节点，并启动复制线程

五、半同步

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失
而采取完全同步的消息通知机制，有可能会等待很久，所以采取半同步，只要有一个从节点同步完成立刻返回成功的消息。
CentOS8 在MySQL8.0 实现半同步复制
是由插件支撑的

#查看插件文件
[root@centos8 ~]#rpm -ql mysql-server |grep semisync
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so

1.安装插件

#主服务器配置:
mysql>INSTALL PLUGIN rpl_semi_sync_master SonAME 'semisync_master.so'; #永久安装插
件

2.修改主配置文件

#master服务器配置
[root@master ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=8
log-bin
rpl_semi_sync_master_enabled=ON #修改此行,需要先安装semisync_master.so插件后,再重启,否
则无法启动
rpl_semi_sync_master_timeout=3000   #设置3s内无法同步，也将返回成功信息给客户端

3.修改从节点主配置文件

先安装插件
mysql>INSTALL PLUGIN rpl_semi_sync_slave SonAME 'semisync_slave.so';

#slave服务器配置
[root@slave1 ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=18
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则
无法启动

[root@slave2 ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=28
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则
无法启动


#注意:如果已经实现主从复制,需要stop slave;start slave;
mysql> stop slave;
mysql> start slave;

CentOS 8 在Mariadb-10.3.11上实现 实现半同步复制
不需要安装插件，只需要指定如下

#在master实现，启用半同步功能
[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin
plugin-load-add = semisync_master # 添加插件
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000   #设置3s内无法同步，也将返回成功信息给客户端

在所有slave节点也不需要安装插件，如下配置：

[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
server-id=18
plugin_load_add = semisync_slave
rpl_semi_sync_slave_enabled=ON  

五、复制过滤器

让从节点仅复制指定的数据库，或指定数据库的指定表
复制过滤器两种实现方式：
(1) 服务器选项：主服务器仅向二进制日志中记录与特定数据库相关的事件
缺点：基于二进制还原将无法实现；不建议使用
优点: 只需要在主节点配置一次即可

vim /etc/my.cnf
binlog-do-db=db1 #数据库白名单列表，不支持同时指定多个值，如果想实现多个数据库需多行实现
binlog-do-db=db2 
binlog-ignore-db= #数据库黑名单列表

(2) 从服务器SQL_THREAD在读取relay log中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本地
缺点：会造成网络及磁盘IO浪费,在所有从节点都要配置
优点: 不影响二进制备份还原
从服务器上的复制过滤器相关变量

replicate_do_db="db1,db2,db3" #指定复制库的白名单，变量可以指定逗号分隔的多个值，选
项不支持多值,只能分别写多行实现
replicate_ignore_db= #指定复制库黑名单
replicate_do_table= #指定复制表的白名单
replicate_ignore_table= #指定复制表的黑名单
replicate_wild_do_table= foo%.bar%    #支持通配符
replicate_wild_ignore_table=

配置示例，在所有从节点配置，值复制 db1,db2和hellodb数据库

[mysqld]
server-id=8
log-bin
binlog-do-db=db1
binlog-do-db=db2
binlog-do-db=hellodb 

六、复制过程的监控和维护

1.清理日志

PURGE { BINARY | MASTER } LOGS   { TO 'log_name' | BEFORE datetime_expr }
RESET MASTER TO # #mysql 不支持
RESET SLAVE [ALL]

2.复制监控

SHOW MASTER STATUS
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW SLAVE STATUS
SHOW PROCESSLIST

3,从服务器是否落后于主服务器

Seconds_Behind_Master：0

4.如何确定主从节点数据是否一致
第三方工具

percona-toolkit

5.数据库不一致如何修复
删除从库，主库做完整性备份，重新复制和同步

6.数据延迟解决方案
利用GTID
减少大事务
减少锁
升级到MySQL5.7以上版本
需要额外的监控工具的辅助
一从多主：mariadb10 版后支持
多线程复制：对多个数据库复制

七、主从复制不一致原因及解决方案 7.1 主从复制不一致原因

• 主库binlog格式为Statement，而非ROW 格式，同步到从库执行后可能造成主从不一致。
• 主库执行更改前有执行set sql_log_bin=0，会使主库不记录binlog，从库也无法变更这部分数据。
• 从节点未设置只读，误操作写入数据
• 主库或从库意外宕机，宕机可能会造成binlog或者relaylog文件出现损坏，导致主从不一致
• 主从实例版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面可能不支持该功能
• 主从sql_mode 不一致
• MySQL自身bug导致

7.2 主从不一致修复方法

• 将从库重新实现
  虽然这也是一种解决方法，但是这个方案恢复时间比较慢，而且有时候从库也是承担一部分的查询操作的，不能贸然重建。

• 使用percona-toolkit工具辅助
  PT工具包中包含pt-table-checksum和pt-table-sync两个工具，主要用于检测主从是否一致以及修复数据不一致情况。这种方案优点是修复速度快，不需要停止主从辅助，缺点是需要知识积累，需要时间去学习，去测试，特别是在生产环境，还是要小心使用
  使用方式参考

• 手动重建不一致的表
  在从库发现某几张表与主库数据不一致，而这几张表数据量也比较大，手工比对数据不现实，并且重做整个库也比较慢，这个时候可以只重做这几张表来修复主从不一致
  这种方案缺点是在执行导入期间需要暂时停止从库复制，不过也是可以接受的

例：A,B,C这三张表主从数据不一致

1、从库停止Slave复制
mysql>stop slave;
2、在主库上dump这三张表，并记录下同步的binlog和POS点
mysqldump -uroot -pmagedu -q --single-transaction --master-data=2 testdb A B 
C >/backup/A_B_C.sql
3、查看A_B_C.sql文件，找出记录的binlog和POS点
head A_B_C.sql
例如:MASTERLOGFILE='mysql-bin.888888', MASTERLOGPOS=666666;
#以下指令是为了保障其他表的数据不丢失，一直同步直到那个点结束，A,B,C表的数据在之前的备份已
经生成了一份快照，只需要导入进入，然后开启同步即可
4、把A_B_C.sql拷贝到Slave机器上，并做指向新位置
mysql>start slave until MASTERLOGFILE='mysql-bin.888888', 
MASTERLOGPOS=666666;
5、在Slave机器上导入A_B_C.sql
mysql -uroot -pmagedu testdb 
mysql>set sql_log_bin=0;
mysql>source /backup/A_B_C.sql
mysql>set sql_log_bin=1;
6、导入完毕后，从库开启同步即可。
mysql>start slave;

7.3 如何避免主从不一致

• 主库binlog采用ROW格式
• 主从实例数据库版本保持一致
• 主库做好账号权限把控，不可以执行set sql_log_bin=0
• 从库开启只读，不允许人为写入
• 定期进行主从一致性检验