20220405-MySQL面试宝典-主从复制篇

一：请简述MySQL主从复制原理。
二：请简述MySQL异步、半同步、全同步复制的区别。
三：主从复制过程中，二进制日志是主库主动推送？还是从库主动请求？
四：请简述MySQL主从复制几个相关参数。
五：请简述传统复制和基于GTID复制的差异。
六：请简述MySQL GTID复制模式有哪些限制？
七：请简述binlog有哪些种类，有哪些区别。
八：请简述MySQL过滤复制。
九：MySQL主从复制过程中，从库某一张表数据异常导致复制中断，如何恢复。
十：请简述MySQL主从复制主键冲突问题解决思路。
十一：请简述reset master,reset slave作用。
十二：请简述master.info和relay-log.info作用。

一：请简述MySQL主从复制原理。

MySQL的复制功能用三个线程来实现：
主库:Binlog Dump线程
从库:I/O线程和SQL线程

1.用户提交数据的修改，然后Master主库把所有数据库变更写进Binary Log二进制日志。
主库通过Binlog Dump线程把二进制日志内容推送给Slave从库，从库被动接收数据，不是主动获取，除非是新建连接。

2.在从库上执行START SLAVE语句时，已经使用CHANGE MASTER TO语句配置好复制信息，从库会创建一个I/O线程，该线程连接到主库并请求主库为其发送所需的二进制日志。
从库I/O线程与主库Binlog Dump线程成功建立连接后，从库I/O线程接收主库Binlog Dump线程发送的二进制日志，并将它们写入从库本地的Relay Log中继日志文件。

3.从库SQL线程读取并解析中继日志中的内容，按照读取的顺序进行回放，二进制日志中存放的事务顺序就是主库中事务的提交顺序，并将数据变更写入本地数据库文件中，这样就实现了数据在主从数据库实例之间的同步。

二：请简述MySQL异步、半同步、全同步复制的区别。

mysql异步复制

mysql异步复制是指，mysql主库将事务信息写入binlog文件中的时候，此时主库会通过binlog dump线程给从库发送这些新的binlog变化，然后并不等待从库的响应继续提交事务并写入binlog，所以主库并不保证这些事务变化的binlog数据会传输并应用到任何从库。

MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从库上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。

主库将事务 Binlog 事件写入到 Binlog 文件中，此时主库只会通知一下 Dump 线程发送这些新的 Binlog，然后主库就会继续处理提交操作，而此时不会保证这些 Binlog 传到任何一个从库节点上。

mysql全同步复制

mysql全同步复制是指，当主库提交事务的binlog后，所有的从库节点必须全部收到事务并且apply并且提交这些内容之后，即io_thread和sql_thread完成所有binlog变化的接受的应用执行，主库的线程才可以继续进行后续操作，但是缺点是，主库完成一个事务的时间会被拉长，性能急剧降低。

mysql半同步复制

mysql半同步复制是介于异步和全同步之间，主库只需要等待至少一个从节点，收到并且flush binlog到relay log文件即可，主库不需要等待所有从库给主库反馈，这里只是一个收到的反馈，而并不是从库已经完成并提交的反馈，即从库只应用完成io_thread内容即可无需等到sql_thread的执行完成。
主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

主库可以使用系统变量rpl_semi_sync_master_wait_for_slave_count(默认值为1)来设置需要收到多少个从库发回的ACK消息，才能执行存储引擎层的事务提交。

三：主从复制过程中，二进制日志是主库主动推送？还是从库主动请求？

既有从库主动请求，也有主库主动推送的情况。
对于复制线程在主从之间新建立连接或重新建立连接的情况，不是主库主动推送二进制日志，因为这是主库并不知道需要发送哪些二进制日志给新建立连接的从库，而是从库主动向主库请求所需的二进制日志，
从库向主库注册连接时，携带了从库自身所需二进制日志的位置信息。主从之间的复制连接始终是从库先发起请求的，就算主库主动断开从库的连接，重新建立连接时也是从库重试的，而不是主库。
如果复制线程已经在主从之间建立连接，而且从库已经完全接收建立连接时请求的二进制日志内容，后续的增量二进制日志是由主库主动推送给从库的，而不是从库主动向主库请求的，
因为这是主库随时都可以写入新的内容，从库难以即时感知，也没必要即时感知。

四：请简述MySQL主从复制几个相关参数。

(1)log-slave-updates

log-slave-updates这个参数用来配置从服务器的更新是否写入二进制日志，这个选项默认是不打开的。

(2)master-connect-retry

master-connect-retry这个参数是用来设置在和主服务器连接丢失的时候，重试的时间间隔，默认是60秒

(3)relay_log_recovery = 1

当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。

默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。

(4)slave-skip-errors

在复制过程中，由于各种的原因，从服务器可能会遇到执行BINLOG中的SQL出错的情况，在默认情况下，服务器会停止复制进程，不再进行同步，等到用户自行来处理。

Slave-skip-errors的作用就是用来定义复制过程中从服务器可以自动跳过的错误号，当复制过程中遇到定义的错误号，就可以自动跳过，直接执行后面的SQL语句。

--slave-skip-errors=[err1,err2,…….|ALL]

但必须注意的是，启动这个参数，如果处理不当，很可能造成主从数据库的数据不同步，在应用中需要根据实际情况，如果对数据完整性要求不是很严格，那么这个选项确实可以减轻维护的成本。

往下跳一个指针,1可以更换其他数字

set global sql_slave_skip_counter=1;

(5)sync_binlog

MySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。
默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。

这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。

对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

五：请简述传统复制和基于GTID复制的差异。

MySQL 5.6之前只支持传统复制，即 基于二进制日志文件和位置的复制，在5.6及之后的版本中，出现了基于GTID的复制。

传统复制，也可以称为基于二进制日志文件和位置的复制，在从库中配置复制时，要求指定从库中获取的二进制日志文件(binlog file)和位置(binlog position),

以便从库中的复制线程启动时，能够以指定的二进制日志和位置为起点，持续读取主库中的二进制日志，并在从库中应用，从而达到数据库同步的目的。

基于GTID的复制，是新的事务复制方法，利用GTID可以自动在主库寻找需要复制的二进制日志记录，因此不需要关心日志文件或位置，极大地简化了许多常见的复制任务。

GTID（ Global Transaction Identifier）全局事务标识，由主库上生成的与事务绑定的唯一标识，这个标识不仅在主库上是唯一的，在MySQL集群内也是唯一的。

GTID是 MySQL 5.6 版本引入的一个有关于主从复制的重大改进，相对于之前版本基于Binlog文件+Position的主从复制，基于GTID的主从复制，数据一致性更高，主从数据复制更健壮，主从切换、故障切换不易出错，很少需要人为介入处理。

GTID之前的主从复制是基于文件+偏移的方式，建立主从复制，必须先知道主库的binlog文件和偏移位置（ MASTER_LOG_FILE 和 MASTER_LOG_POS）。

而使用基于GTID的主从复制，设置 MASTER_AUTO_POSITION =1，从库发送自身已经接收到的gtid给主库，主库将从库缺失的gtid及其对应的binlog文件发送给从库，也就是主库只发送从库没有接收到的事务。

所有的信息由MySQL集群自动获取完成，不需要人为干预，大大简化了复制搭建过程。

MySQL 5.7.x 及之后的版本新增了一张InnoDB存储引擎的mysql.gtid_executed表来持久化GTID信息。

MySQL GTID特点
1.事务提交产生GTID，GTID与事务及事务提交所在的节点绑定，GTID与事务一起写入Binlog，但是从库应用Binlog并不会生成新的GTID。

2.集群中的任何一个节点，根据其GTID值就可以知道哪些事务已经执行，哪些事务没有执行，如果发现某个GTID已执行，重复执行该GTID，将会被忽略，即同一个GTID只能被应用一次。

3.当一个连接执行一个特定GTID的事务，但是还没有提交，此时有另外一个连接也要执行相同GTID的事务，那么第二个连接的执行将会被阻塞，直到第一个事务提交或者回滚。如果第一个事务成功提交，第二个事务将会被忽略。如果第一个事务回滚，第二个事务正常执行。

如何开启GTID

gtid_mode=ON
enforce_gtid_consistency=ON

GTID长啥样

GTID = server_uuid:transaction_id

示例：3E11FA47-71CA-11E1-9E33-C80AA9429562:1
server_uuid标识了该事务执行的源节点，存储在数据目录中的auto.cnf文件中，transaction_id 是在该主库上生成的事务序列号，从1开始，示例中 3E11FA47-71CA-11E1-9E33-C80AA9429562 是这个节点的server_uuid，1为这个节点上提交的第1个事务的事务号，如果提交了10个事务，GTID会是这样： 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-10

GTID存储在什么地方？
GTID与事务绑定在一起，随着事务的提交，GTID随事务信息一起写入Binlog，通过主从复制，传递到从库。对于已经执行了的事务，其GTID通常会记录在MySQL的系统变量@@GLOBAL.gtid_executed 以及系统表mysql.gtid_executed中，系统变量@@GLOBAL.gtid_executed 在内存中，属于非持久化存储，而系统表mysql.gtid_executed属于持久化存储

基于GTID的复制搭建：

CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3306,MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;

基于Binlog+Position的复制搭建：

CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=524;

六：请简述MySQL GTID复制模式有哪些限制？

1.更新操作涉及非事务引擎

在同一个事务中，不能同时操作支持事务(InnoDB)和不支持事务(MyISAM)的引擎。

这是由于同时操作这两类引擎时可能导致将多个GTID被分配给同一个事务。

主从数据库Server中相同的表使用不同的存储引擎时(其中，一个Server使用事务表，另一个Server使用非事务表)，如果在非事务表上定义了触发器，可能导致事务与GTID之间一一对应关系被破坏。

2.CREATE TABLE … SELECT 语句

使用GRID复制时，CREATE TABLE …语句不允许同时使用SELECT语句。

当binlog_format设置statement时，CREATE TABLE … SELECT语句是作为一个整体且只分配一个GTID的事务形式被记录在二进制日志中。

如果主库使用statement格式二进制日志，而从库使用row格式的二进制日志，则从库将无法正确处理事务。

3.临时表

使用GTID时(这里指的是系统变量enforce_gtid_consistency设置为ON时)，事务、存储过程、存储函数和触发器内不支持CREATE TEMPORARY TABLE和DROP TEMPORARY TABLE语句，

不过可以在这些对象之外执行CREATE TEMPORARY TABLE和DROP TEMPORARY TABLE语句，当需要使用autocommit = 1自动提交。

4.防止执行不受支持的语句

要防止执行会导致GTID复制失败的语句，则需要在启动GTID时，在整个复制拓扑的所有实例中启用系统变量enforce_gtid_consistency。

当启用此系统变量之后，上述可能会导致复制出现问题的语句将直接报错，不予执行。

5.关于跳过事务

使用GTID时不支持使用系统变量sql_slave_skip_counter来跳过事务。

6.忽略Server实例

使用GTID时，不推荐在CHANGE MASTER TO语句中使用IGNORE_SERVER_IDS选项来忽略某个Server实例的二进制日志变更，因为在GTID复制模式下，已经应用的事务会自动被忽略。
在启动GTID复制之前，请检查并清除该选项的设置。

7.GTID复制模式和mysql_upgrade

当Server启用了GTID复制模式时(gtid_mode = ON),如果需要对Server使用mysql_update进行升级，则不能启用二进制日志记录(–write-binlog选项)。

七：请简述binlog有哪些种类，有哪些区别。

mysql复制主要有三种方式：

基于SQL语句的复制(statement-based replication, SBR)
基于行的复制(row-based replication, RBR)
混合模式复制(mixed-based replication, MBR)。

对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

STATEMENT模式（SBR）

每一条修改数据的sql语句会记录到binlog中。

优点：
并不需要记录每一行的数据变化，减少了binlog日志量，节约IO，提高性能。

缺点：
在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

ROW模式（RBR）

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。
优点：
不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。

缺点：
会产生大量的日志，尤其是alter table的时候会让日志暴涨。

MIXED模式（MBR）

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
在MySQL 5.7.7之前，默认的二进制日志采用statement格式。
在MySQL 5.7.7及更高的版本中，默认的二进制变更为row格式。

八：请简述MySQL过滤复制。

过滤复制可以从两方面下手:

1.配置主库的dump线程, 让其只发送需要同步的db二进制。
2.配置从库的sql线程, 让其只回放我们需要同步的db二进制文件。

主库:

show master status;
Binlog_Do_DB           # 该参数用来指定需要同步的db
Binlog_Ignore_DB      # 该参数用来指定不需要同步的db

从库：

show slave status\G
Replicate_Do_DB:          # 指定的回放db
Replicate_Ignore_DB:     # 指定不回放的db
Replicate_Do_Table:        # 指定回放的表
Replicate_Ignore_Table:   # 指定不回放的表
Replicate_Wild_Do_Table:       # 模糊指定回放的表
Replicate_Wild_Ignore_Table:  # 模糊指定不回放的表

设置过滤指定数据库

mysql> STOP SLAVE SQL_THREAD;
mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1, db2);

设置过滤指定表

mysql> CHANGE REPLICATION FILTER
    -> REPLICATE_WILD_DO_TABLE = ('db1.t1%'),
    -> REPLICATE_WILD_IGNORE_TABLE =  ('db1.t2%');

取消取消过滤

mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = ();
Query OK, 0 rows affected (0.00 sec)

mysql> start SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

九：MySQL主从复制过程中，从库某一张表数据异常导致复制中断，如何恢复。

1.在主库上备份表test1

mysqldump -uroot -p'xxx'  --single-transaction cjcdbtest test1 --master-data=2 |gzip >$(date +%F).test1.sql.gz

2.恢复此表到slave库上

mysql cjcdbtest < 2022-04-05.test1.sql 

获取出单独备份表的快照gtid值：

gzip -d 2022-04-05.test1.sql.gz
grep -A6 'GLOBAL.GTID_PURGED' 2022-04-05.test1.sql 

由于GTID_EXECUTED不是空值，导致导入表test1到slave库失败,具体报错如下:

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

解决方法：
方法一：reset master
登陆slave库：

mysql> reset master;

这个操作可以将当前库的GTID_EXECUTED值置空

方法二：–set-gtid-purged=off

在dump导出时，添加–set-gtid-purged=off参数，避免将gtid信息导出

mysqldump -uroot -p --set-gtid-purged=off ...

3.在线开启复制过滤

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('cjcdbtest.test1');
Query OK, 0 rows affected (0.00 sec)

[root@mysql02 ~]# mysql -e "show slave status\G"|egrep 'cjcdbtest.test1'
  Replicate_Wild_Ignore_Table: cjcdbtest.test1

4.启动复制，回放到8a9fb9a3-f579-11ea-830d-90b11c12779c:42262时停止复制（此时从库上所有表的数据都在同一状态，是一致的）

mysql> START SLAVE UNTIL SQL_AFTER_GTIDS ='8a9fb9a3-f579-11ea-830d-90b11c12779c:42262';
Query OK, 0 rows affected, 1 warning (0.03 sec)

5.在线关闭复制过滤:

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ();

6.开启slave复制SQL线程：

 start slave sql_thread;

主从复制恢复

mysql -e "show slave status\G"|egrep 'IO_Running|SQL_Running'

十：请简述MySQL主从复制主键冲突问题解决思路。

Last_SQL_Error: Error 'Duplicate entry '1001-164761-0' for key 'PRIMARY'' on query. Default database: 'bug'. Query: 'insert into misdata (uid,mid,pid,state,mtime) values (164761,1001,0,-1,1262623560)'

检查主键冲突原因：

show global variables like 'binlog_format';

statement 格式不安全
修改为 mixed/row 格式

针对双主架构，合理配置auto_increment_offset和auto_increment_increment

A：my.cnf上加入参数 
auto_increment_offset = 1 
auto_increment_increment = 2 
这样A的auto_increment字段产生的数值是：1, 3, 5, 7, …等奇数ID了 

B：my.cnf上加入参数 
auto_increment_offset = 2 
auto_increment_increment = 2 
这样B的auto_increment字段产生的数值是：2, 4, 6, 8, …等偶数ID了 

考虑是否临时跳过
在丛库上，执行以下命令，跳过它：

先停止slave：stop slave;
set global sql_slave_skip_counter = 1 

再启动slave：start slave;

说明：执行一次后查看是否正常，不正常再继续执行该命令

或者在从库的my.cnf中加上这条：

slave-skip-errors = 1062

十一：请简述reset master,reset slave作用。

RESET MASTER

删除所有index file 中记录的所有binlog 文件，将日志索引文件清空，创建一个新的日志文件，这个命令通常仅仅用于第一次用于搭建主从关系的时的主库，

注意:
reset master 不同于purge binary log的两处地方

1 reset master 将删除日志索引文件中记录的所有binlog文件，创建一个新的日志文件 起始值从000001 开始，然而purge binary log 命令并不会修改记录binlog的顺序的数值。

2 reset master 不能用于有任何slave 正在运行的主从关系的主库。因为在slave 运行时刻 reset master 命令不被支持，reset master 将master 的binlog从000001 开始记录,slave 记录的master log 则是reset master 时主库的最新的binlog,从库会报错无法找的指定的binlog文件。

RESET SLAVE

reset slave 将使slave 忘记主从复制关系的位置信息。该语句将被用于干净的启动, 它删除master.info文件和relay-log.info 文件以及所有的relay log 文件并重新启用一个新的relaylog文件。

使用reset slave之前必须使用stop slave 命令将复制进程停止。

注：
所有的relay log将被删除不管他们是否被SQL thread进程完全应用(这种情况发生于备库延迟以及在备库执行了stop slave 命令),存储复制链接信息的master.info文件将被立即清除,如果SQL thread 正在复制临时表的过程中，执行了stop slave ，并且执行了reset slave，这些被复制的临时表将被删除。

RESET SLAVE ALL

在 5.6 版本中 reset slave 并不会清理存储于内存中的复制信息比如 master host, master port, master user, or master password,也就是说如果没有使用change master 命令做重新定向，执行start slave 还是会指向旧的master 上面。
当从库执行reset slave之后,将mysqld shutdown 复制参数将被重置。
在5.6.3 版本以及以后 使用使用 RESET SLAVE ALL 来完全的清理复制连接参数信息。(Bug #11809016)

RESET SLAVE ALL does not clear the IGNORE_SERVER_IDS list set by CHANGE MASTER TO. This issue is fixed in MySQL 5.7. (Bug #18816897)

In MySQL 5.6.7 and later, RESET SLAVE causes an implicit commit of an ongoing transaction. See Section 13.3.3, “Statements That Cause an Implicit Commit

十二：请简述master.info和relay-log.info作用。

在MySQL 5.6.2之前，slave记录的master信息以及slave应用binlog的信息存放在文件中，即master.info与relay-log.info。

在5.6.2版本之后，允许记录到table中，参数设置如下：

master-info-repository  = TABLE    ---FILE表示以文件方式
relay-log-info-repository = TABLE  ---FILE表示以文件方式

对应的表分别为mysql.slave_master_info与mysql.slave_relay_log_info，且这两个表均为innodb引擎表。

由I/O线程更新master.info文件。文件中的行和SHOW SLAVE STATUS显示的列的对应关系为：
由SQL线程更新relay-log.info文件。

###chenjuchao 20220405###
欢迎关注我的公众号《IT小Chen》