关于MySQL主从复制的一些理解

最新推荐文章于 2024-01-19 11:47:04 发布

「已注销」

最新推荐文章于 2024-01-19 11:47:04 发布

阅读量154

点赞数 1

分类专栏： MySQL之路文章标签： mysql

本文链接：https://blog.csdn.net/zhen1819/article/details/111304053

版权

MySQL之路专栏收录该内容

11 篇文章 0 订阅

订阅专栏

一、开始构建一主一从复制结构

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、主从复制过程

Replication过程

从库的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开始