一、开始构建一主一从复制结构
1.1、开启MySQL的二进制日志功能
在MySQL配置文件my.cnf中,增加以下配置,用于开启MySQL的二进制日志功能:
server_id = 1
log_bin = mysql-bin
binlog_format = row
- 在主从复制结构中,每个MySQL实例的
“server_id”都不能相同
; log_bin
配置用于开启主从复制,并设定二进制日志的文件名前缀,后缀如“.000001”;binlog_format
配置二进制日志记录格式。有“row
”和“statement
”两种格式,前者以事件形式记录,相对严谨;后者以完整SQL语句记录,可读性强。
添加配置后,需要重启MySQL服务。
1.2、创建用于主从复制的用户
在用作主库的MySQL实例中,创建用于主从复制的用户:
mysql> grant replication slave on *.* to repl@'192.168.18.%' identified by '123123';
1.3、在从库测试到主库的连接
在从库上用mysql
命令测试上一步创建的用于Replication的“repl”用户能否正常连接到主库:
# mysql -h 192.168.18.130 -u repl -p'123123'
1.4、备份主库数据
使用mysqldump
命令对主库中的数据进行备份,并将备份文件拷贝到从库:
# mysqldump -h 127.0.0.1 -u root -A -R --triggers --master-data=2 --single-transaction > /tmp/full.sql
--master-data=2
:以注释的形式记录binlog的状态信息,包含备份时刻的二进制日志文件名和position号,从备份文件中的“CHANGE MASTER TO”行可以看到;--single-transaction
:对支持事务的表,会对该表创建一致性快照,从快照备份数据而不必锁表。
1.5、将主库数据恢复到从库
将从主库拷贝的备份文件导入到从库中:
mysql> set sql_log_bin = 0;
mysql> source /tmp/full.sql;
mysql> set sql_log_bin = 1;
将sql_log_bin
设置为0,代表在本次会话中,不记录产生的二进制日志。
1.6、从库开启复制功能
首先要从主库的备份文件,确定从库该从哪个位置号开始复制。在主库的备份文件中,记录了备份时的二进制日志文件,和备份之前的Position号。由此得出从库应该从“mysql-bin.000003”文件的“519”位置开始复制:
# head -30 /tmp/full.sql | grep 'CHANGE MASTER TO'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=519;
在从库上通过help change master to
语句查看用法,将从库连接到主库上:
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.18.130',
MASTER_USER='repl',
MASTER_PASSWORD='123123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=519;
在从库上开启复制:
mysql> start slave;
查看主从复制状态是否正确:
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果IO和SQL线程的状态都是“Yes”,表明主从复制状态构建成功。
二、理解主从复制原理
2.1、涉及到的文件
主库:
- 二进制日志文件:记录主库的数据变化;
从库:
- relay log二进制日志文件:中继日志。存储从主库请求的二进制日志;
- master.info:存储连接主库的用户名、密码等信息,及上次请求过的主库二进制日志文件名和Position号;
- relay-log.info:记录从库的SQL线程上次执行过的relay log二进制日志文件名和Position。
2.2、主从复制过程
- 从库的IO线程从master.info中获取主库的连接信息,和主库的二进制日志的文件名和Position号,并用这些信息向主库发送请求;
- 主库根据从库IO线程发送的二进制日志文件名和Position号,通过DUMP IO线程截取对应的二进制日志,并将截取的二进制日志发送给从库;
- 从库接收主库发送的数据,将其暂存在TCP/IP缓存中,向主库发送已确认收到数据,并更新master.info文件中记录的主库二进制日志文件名和Position号;
- 从库的IO线程将TCP/IP缓存中的数据写入relay log文件中;
- 从库的SQL线程根据relay-log.info记录的relay log文件名和Position号,读取relay log中新的记录并执行。同时更新relay-log.info记录的信息。
三、主从复制常见问题
3.1、与主库的连接问题
通常在构建主从复制过程中出现,通过查看从库的状态,“Slave_IO_Running”状态显示为“Connecting”:
mysql> show slave status \G
Slave_IO_Running: Connecting
Last_IO_Error: error connecting to master 'repl@192.168.18.130:3306' - retry-time: 60 retries: 1
这种情况,可以通过在从库上执行mysql
命令,以主从复制用户连接主库,根据报错信息来解决问题。
3.2、IO线程问题
这种情况通常是由于跟主库的binlog不一致导致,比如在主库上执行了reset master
命令,导致主从复制异常,出现IO线程故障:
mysql> show slave status\G
Slave_IO_Running: No
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
这时候只能通过重新构建主从来解决故障。在重新构建之前,清空从库的状态:
mysql> stop slave;
mysql> reset slave all;
3.3、SQL线程问题
出现这种问题,通常是在从库上执行了写入操作。
mysql> show slave status\G
Slave_SQL_Running: No
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'db2'; database exists' on query. Default database: 'db2'. Query: 'create database db2
一种有风险的解决办法:
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1; #将同步指针向下移动一个,如果还有问题,重复操作此步骤。
mysql> start slave;
但最安全的方式还是重新构建主从。
四、主从复制监控要点
对于主从复制的监控,通常是通过show slave status\G
获取相关信息:
首先,IO和SQL线程的状态:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
线程故障详细信息:
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
主从复制延时:
Seconds_Behind_Master: 0
过滤复制相关状态:
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
延时复制状态:
SQL_Delay: 0
SQL_Remaining_Delay
五、主从复制延时过高原因分析
5.1、主库写binlog不及时
因为每次主从复制都会从主库的binlog文件中截取日志,对于没有及时写入到binlog文件中的记录,将无法及时同步到从库。通常在主库系统繁忙时出现。
解决思路是将“sync_binlog
”参数设置为1,这样在每次事务提交时都会立即刷新binlog到磁盘。但是在数据量太大时,将会耗费更多系统资源。
“sync_binlog”参数的默认值是0,由操作系统判断什么时候将binlog写入磁盘。
5.2、主库的DUMP IO线程压力大
通常在从库过多时出现,过多的从库在主从复制时,将会产生更多的DUMP IO线程。
5.3、从库IO线程阻塞
这种情况通常出现在事务数量较大,或单个事务的数据量大。
对于单个事务数据量大的,可以将事务拆分成多个小的事务;
对于事务数量较大的,可以将多个事务分组提交(group commit)。
5.4、从库SQL线程阻塞
MySQL5.6默认采用“classic replication
”,只有一个SQL线程,从库中的事务都是一个一个执行的。在单个事务的数据量太大时,势必会导致其他事务阻塞。可以将大的事务拆分成多个小的事务改善这个问题。
六、延时从库
延时从库,指让从库的数据复制落后于主库一段时间。这样可以在主库出现误操作时,不会立即将误操作同步到从库,从而利用复制的时间差做一些补救工作。
延迟从库设定方法:在从库上执行以下操作:
mysql> stop slave;
mysql> change master to master_delay=300; #设定延时同步时间为300秒;
mysql> start slave;
在主库上出现误操作时,利用延时从库做补救工作:
1)停止业务,避免更多的数据写入,同时关闭从库的SQL线程:
mysql> stop slave sql_thread;
2)从relay log文件中截取正常的数据记录:
首先通过show slave status\G
查看从库使用的“Relay_Log_File”和“Relay_Log_Pos”,将“Relay_Log_Pos”作为截取日志的起始位置。
再在从库上通过show relaylog events in "Relay_Log_File"
查找正常日志的结束位置,将误操作语句的“at”位置作为截取日志的结束位置
3)截取日志:
通过mysqlbinlog --start-position=*** --stop-position=***
截取日志。
4)恢复relaylog:
将截取的日志导入从库。
5)将从库切换为主库:
mysql> stop slave;
mysql> reset slave all;
七、半同步复制
在传统的主从复制过程中,当主库的DUMP IO线程将二进制日志传送给从库后,从库会先将数据放在TCP/IP缓存中,然后会发送ACK标志位为1的TCP数据包,告知主库已经收到数据,同时会更新master.info中记录的主库二进制文件名和Position号。再将TCP/IP缓存中的数据写入relay log文件中。
如果TCP/IP缓存中的数据没有写入relay log之前,从库宕机,那TCP/IP缓存中的数据将会丢失,这样就造成主从数据不一致的问题。
而半同步复制,是在TCP/IP缓存中的数据都写入到relay log文件中后,才给主库发送ACK标志位为1的数据包。否则超过10秒钟主库没有收到ACK,将会切换为异步复制。半同步复制虽然解决了主从数据不一致的问题,但是如果从库将TCP/IP缓存中的数据写入到relay log文件时发生IO阻塞,将会影响主库性能。在MySQL5.7.17版本之后,出现了增强半同步复制的新特性MGR(MySQL Group Replication)。
半同步复制需要主库和从库都加载插件来实现:
主库:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
从库:
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
查看插件是否加载成功:
mysql> show plugins;
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
启用该插件:
主库:
mysql> set global rpl_semi_sync_master_enabled=1;
从库:
mysql> set global rpl_semi_sync_slave_enabled=1;
重启从库的IO线程:
mysql> stop slave io_thread;
mysql> start slave io_thread;
查看状态:
mysql> show status like "rpl_semi_sync_master_status";
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
八、过滤复制
有一种需求是,只希望将主库的指定库复制到从库,而不是将所有库都复制,这时就可以使用“过滤复制”。
通过查看从库的状态,可以看到以下参数:
mysql> show slave status\G
Replicate_Do_DB: #只复制指定库;
Replicate_Ignore_DB: #忽略指定库的复制;
Replicate_Do_Table: #只复制指定表;
Replicate_Ignore_Table: #忽略指定表的复制;
Replicate_Wild_Do_Table: #模糊匹配;
Replicate_Wild_Ignore_Table:
通过在从库的配置文件中,增加以下配置,并重启从库,让从库只同步db01,db02库:
replicate_do_db=db01
replicate_do_db=db02
九、GTID复制
GTID:将一个已经提交的事务编号,并且是一个全局唯一的编号。GTID由“server_uuid:transaction_id”组成。“server_uuid”保存在MySQL的数据目录“auto.cnf”文件中,每次重新初始化MySQL,或者删除“auto.cnf”后重启MySQL都会自动生成。
9.1、关于GTID的三个重要参数
gtid_mode=on #开启GTID;
enforce_gtid_consistency=true #强制GTID的一致性;
log_slave_updates=1 #slave数据更新是否记入二进制日志binlog,主要用于双主和MHA环境;
9.2、基于GTID构建主从
首先清理data目录和binlog文件。然后在所有节点的my.cnf文件中增加以下配置:
server_id=1
log_bin=mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
配置主从。在从库执行以下命令:
mysql> change master to
master_host='192.168.18.130',
master_user='repl',
master_password='123123',
master_auto_position=1;
mysql> start slave;
9.3、从库误写入操作处理
处理的主要思路是,对于报错的GTID,通过手工执行空事务跳过该事务:
mysql> stop slave;
mysql> set gtid_next='*****:2'; #slave sql thread报错的GTID,或者想要跳过的GTID;
mysql> begin;commit; #或者执行一条查询语句;
mysql> set gtid_next='automatic';
mysql> start slave;
9.4、GTID复制和普通复制的区别
- 在主从复制环境中,主库发生的事务,都是由唯一的GTID记录的,方便Failover和数据补偿;
- GTID需要3个额外的参数;
- GTID复制时,执行
CHANGE MASTER TO
命令不在需要二进制文件的名称和Position号,使用master_auto_position=1即可; - 在复制过程中,从库不再依赖master.info文件记录的主库二进制日志文件名和Position号,而是直接读取最后一个relay log的GTID号;
- 在mysqldump备份时,会告诉从库备份中已经有哪些事务,直接从下一个GTID开始请求binlog:
# mysqldump -h 127.0.0.1 -A -E -R --triggers --master-data=2 --single-transaction > full.sql
# cat full.sql | head -30
SET @@SESSION.SQL_LOG_BIN= 0; #自动关闭当前会话的二进制日志记录;
SET @@GLOBAL.GTID_PURGED='7d99cecc-4040-11eb-9080-000c29a94c0c:1-2'; #记录备份文件已有的事务,主从复制直接从下一个GTID开始