MySQL主从复制（概念和作用、实战、常见问题和解决办法、扩展、GTID同步集群、集群扩容、半同步复制）_mysql集群主从复制

1. 主从复制

1.1 概念和作用

MySQL主从复制是一种数据复制技术，用于将一个MySQL数据库实例（称为主数据库）的更改同步到其他MySQL数据库实例（称为从数据库）。默认采用的是异步复制的机制。主从复制的作用包括数据备份、负载均衡（读写分离）和实现高可用性等。

• 数据备份：给主服务增加一个数据备份。
• 负载均衡/读写分离：对于大部分Java业务系统来说，都是读多写少，当主服务的访问压力过大时，可以将数据请求转为由从服务来分担，主服务只负责数据写入的请求，这样能大大缓解数据库的访问压力。
• 故障转移-高可用：当MySQL主服务宕机后，可以由一台从服务切换为主服务，继续提供读写功能。

对于高可用架构，主从数据的同步只是实现故障转移的前提条件，要实现MySQL主从切换，还需要依靠一些其他的中间件来实现，比如MMM、MHA、MGR。

在一般的项目中，如果数据库的访问压力没那么大，那读写分离不一定是必须要做的，但是，主从架构和高可用架构都是必须要搭建的。

1.2 主从复制的步骤

主数据库和从数据库之间的通信是通过以下步骤进行的：

1. 主数据库将每个更新操作记录在二进制日志（Binary Log）中，这是一个二进制格式的日志文件。
2. 从数据库连接到主数据库，并请求复制主数据库的二进制日志。
3. 主数据库将二进制日志传输给从数据库，从数据库将其复制到本地的中继日志（Relay Log）文件中。
4. 从数据库读取中继日志文件中的更新操作，并在本地执行这些操作，从而使从数据库保持与主数据库的同步。

1.3 搭建主从同步（配置步骤）

主从复制的配置步骤：

1. 在主数据库上启用二进制日志，并配置一个唯一的标识符server-id。
2. 在主服务器上创建一个用于复制的用户，并赋予适当的权限。在实际生产环境中，通常不会直接使用root用户，而会创建一个拥有全部权限的用户来负责主从同步。
3. 在从服务器上设置主服务器的连接信息，包括主数据库的地址、用户名和密码。并启动复制进程。
4. 从服务器连接到主服务器，请求复制数据，并进行初始数据同步。

搭建主从集群时，有两个必要的要求：

1. 双方MySQL必须版本一致，至少主服务的版本低于从服务。
2. 两节点之间的时间需要同步。

1.3.1 配置master主库

1. 首先，配置主库的mysql配置文件：/etc/my.cnf这一步需要对master进行配置，主要是需要打开主库的binlog日志，以及指定server-id。

[mysqld]
server-id=47
# 开启binlog
log_bin=master-bin
log_bin-index=master-bin.index
# 关闭域名解析
skip-name-resolve
1
2
3
4
5
6
7

配置说明：主要修改以下几个属性

• server-id：服务节点的唯一标识。需要给集群中的每个服务分配一个单独的ID。
• log_bin：打开binlog日志记录，并指定文件名。
• log_bin-index：binlog日志文件

2. 重启MySQL主库：service mysqld restart。
3. 创建一个拥有全部权限的用户来负责主从同步。在实际生产环境中，通常不会直接使用root用户，而会创建一个拥有全部权限的用户来负责主从同步。

# 登录主数据库
mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'root'@'%';
flush privileges;
#查看主节点同步状态：
show master status;
1
2
3
4
5
6

这个指令结果中的File和Position记录的是当前日志的binlog文件以及文件中的索引。而后面的Binlog_Do_DB和Binlog_Ignore_DB这两个字段是表示需要记录binlog文件的库以及不需要记录binlog文件的库。目前我们没有进行配置，就表示是针对全库记录日志。这两个字段如何进行配置，会在后面进行介绍。

开启binlog后，数据库中的所有操作都会被记录到datadir当中，以一组轮询文件的方式循环记录。而指令查到的File和Position就是当前日志的文件和位置。而在后面配置从服务时，就需要通过这个File和Position通知从服务从哪个地方开始记录binLog。

1.3.2 配置slave从库

1、修改配置文件my.cnf：设置server-id，打开从库的中继日志、二进制日志。

[mysqld]
#主库和从库需要不一致
server-id=48
#打开MySQL中继日志
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
#打开从服务二进制日志
log-bin=mysql-bin
#使得更新的数据写进二进制日志中
log-slave-updates=1
1
2
3
4
5
6
7
8
9
10

配置说明：主要需要关注的几个属性

• server-id：服务节点的唯一标识
• relay-log：打开从服务的relay-log日志
• log-bin：打开从服务的bin-log日志记录

2、启动MySQL从服务，并设置主节点同步状态

#登录从库
mysql -u root -p;
#设置同步主库：(以下是一条语句) 设置主库IP、端口、用户名、密码、log日志文件
CHANGE MASTER TO
MASTER_HOST='192.168.232.128',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='master-bin.000004',
MASTER_LOG_POS=156,
GET_MASTER_PUBLIC_KEY=1;
#开启slave
start slave;
#查看主从同步状态
show slave status;
或者用 show slave status \G; 这样查看比较简洁
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

注：CHANGE MASTER指令中需要指定的MASTER_LOG_FILE和MASTER_LOG_POS必须与主服务中查到的保持一致。并且后续如果要检查主从架构是否成功，也可以通过检查主服务与从服务之间的File和Position这两个属性是否一致来确定。

重点关注红色方框的两个属性Slave_IO_Running: Yes和Slave_SQL_Running: Yes，与主节点保持一致，就表示这个主从同步搭建是成功的。

从这个指令的结果能够看到，有很多Replicate_开头的属性，这些属性指定了两个服务之间要同步哪些数据库、哪些表的配置。只是在我们这个示例中全都没有进行配置，就标识是全库进行同步。后面我们会补充如何配置需要同步的库和表。

1.3.3 主从复制的问题和解决方法

1. 主从延迟：从服务器上的数据复制相对于主服务器有延迟。解决办法：
   • 并行复制：MySQL自5.7版本后就已经支持并行复制了，可以让从库也用多线程并行复制binlog数据。可以在从库上设置slave_parallel_workers为一个大于0的数，然后把slave_parallel_type参数设置为LOGICAL_CLOCK，这就可以了。
   • 半同步复制：半同步复制机制是一种介于异步复制和全同步复制之前的机制。主库在执行完客户端提交的事务后，并不是立即返回客户端响应，而是等待至少一个从库接收并写到relay log中，才会返回给客户端。MySQL在等待确认时，默认会等10秒，如果超过10秒没有收到ack，就会降级成为异步复制。半同步复制需要基于特定的扩展模块来实现。而mysql从5.5版本开始，往上的版本都默认自带了这个模块。这个模块包含在mysql安装目录下的lib/plugin目录下的semisync_master.so和semisync_slave.so两个文件中。需要在主库上安装semisync_master模块，在从库上安装semisync_slave模块。
2. 复制错误：复制进程出现错误，导致复制中断或失败。解决方案：
   • 检查复制进程的错误日志，查找详细的错误信息。
   • 确保主从服务器之间的网络连接正常，没有丢包或连接中断。
   • 检查复制账户的权限，确保复制账户具有正确的权限。
   • 重启复制进程或重新连接复制进程。
3. 主从切换：需要进行主从切换，将一个从服务器提升为新的主服务器。解决办法：
   • 可以使用监控工具来检测主数据库的故障，并执行自动化脚本来切换到从数据库。也可以使用GTID或binlog文件来确定主从同步的位置
4. 主从数据不一致：主从服务器之间的数据不一致。解决办法：
   • 可以使用工具如pt-table-checksum和pt-table-sync来检测和修复数据不一致性。

这些问题和解决方法可以帮助确保主从复制的可靠性和一致性，并确保故障时能够进行及时的切换和恢复。

在我们搭建的这个主从集群中，有一个比较隐藏的问题，就是这样的主从复制之间会有延迟。这在做了读写分离后，会更容易体现出来。即数据往主库写，而读数据在从库读。这时候这个主从复制延迟就有可能造成刚插入了数据但是查不到。当然，这在我们目前的这个集群中是很难出现的，但是在大型集群中会很容易出现。

1.3.4 MySQL主从复制监控和管理、测试

为了监控和管理MySQL主从复制，可以采取以下措施：

1. 使用MySQL自带的工具如SHOW SLAVE STATUS命令或者SHOW MASTER STATUS命令来查看主从复制状态。
2. 监控复制进程的延迟，确保从数据库与主数据库的同步保持在可接受的范围内。
3. 定期备份主数据库和从数据库的数据，以防止数据丢失。
4. 在主数据库和从数据库上设置合适的日志记录级别，以便跟踪和调试复制过程中的问题。

在MySQL主从架构中，是需要严格限制从服务的数据写入的，一旦从库有数据写入，就会造成数据不一致。

主库创建一个数据库，查看从库是否创建成功
注： 如果在slave从服务上查看slave状态，发现Slave_SQL_Running=no，就表示主从同步失败了。这有可能是因为在从数据库上进行了写操作，与同步过来的SQL操作冲突了，也有可能是slave从服务重启后有事务回滚了。

如果是因为slave从服务事务回滚的原因，可以按照以下方式重启主从同步：

mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave 
1
2
3

而另一种解决方式就是重新记录主节点的binlog文件消息：

mysql> stop slave ;
mysql> change master to .....
mysql> start slave ;
1
2
3

但是这种方式要注意binlog的文件和位置，如果修改后和之前的同步接不上，那就会丢失部分数据。所以不太常用。

1.4 主从同步扩展

1.4.1 主库同步与部分同步（同步范围限制）

实际环境中，一般并不需要针对全库做备份，而只需要对一些特别重要的库或者表来进行同步。配置方式如下
1、在Master端：在my.cnf中，可以通过以下这些属性指定需要针对哪些库或者哪些表记录binlog。

#需要同步的二进制数据库名（多个配置多条）
binlog-do-db=masterdemo0
binlog-do-db=masterdemo1
#只保留7天的二进制日志，以防磁盘被日志占满(可选)
expire-logs-days = 7
#不备份的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
1
2
3
4
5
6
7
8
9

2、在Slave端：在my.cnf中，需要配置备份库与主服务的库的对应关系。

#如果salve库名称与master库名相同，使用本配置
replicate-do-db = masterdemo0
replicate-do-db = masterdemo1
#如果master库名[mastdemo]与salve库名[mastdemo01]不同，使用以下配置[需要做映射]
replicate-rewrite-db = masterdemo -> masterdemo01
#如果不是要全部同步[默认全部同步]，则指定需要同步的表
replicate-wild-do-table=masterdemo01.t_dict
replicate-wild-do-table=masterdemo01.t_num
1
2
3
4
5
6
7
8

配置完成了之后，在show master status指令中，就可以看到Binlog_Do_DB和Binlog_Ignore_DB两个参数的作用了。

1.4.2 读写分离配置

MySQL主从集群是单向的，也就是只能从主库同步到从库，而从库的数据表更是无法同步到主库的。所以，在这种架构下，为了保证数据一致，通常会需要保证数据只在主库上写，而从库只进行数据读取。这个功能，就是大名鼎鼎的读写分离。但是这里要注意下，mysql主从本身是无法提供读写分离的服务的，需要由业务自己来实现。这也是ShardingSphere的一个重要功能

• 到这里可以看到，在MySQL主从架构中，是需要严格限制从服务的数据写入的，一旦从库有数据写入，就会造成数据不一致。并且从库在执行事务期间还很容易造成数据同步失败。
• 如果需要限制用户写数据，我们可以在从服务中将read_only参数的值设为1( set global read_only=1; )。这样就可以限制用户写入数据。但是这个属性有两个需要注意的地方：
  1、read_only=1设置的只读模式，不会影响slave同步复制的功能。 所以在MySQL slave库中设定了read_only=1后，通过 “show slave status\G” 命令查看salve状态，可以看到salve仍然会读取master上的日志，并且在slave库中应用日志，保证主从数据库同步一致；
  2、read_only=1设置的只读模式， 限定的是普通用户进行数据修改的操作，但不会限定具有super权限的用户的数据修改操作 。 在MySQL中设置read_only=1后，普通的应用用户进行insert、update、delete等会产生数据变化的DML操作时，都会报出数据库处于只读模式不能发生数据变化的错误，但具有super权限的用户，例如在本地或远程通过root用户登录到数据库，还是可以进行数据变化的DML操作； 如果需要限定super权限的用户写数据，可以设置super_read_only=0。另外 如果要想连super权限用户的写操作也禁止，就使用"flush tables with readlock;"，这样设置也会阻止主从同步复制！

1.4.3 其他集群方式

为了进一步提高整个集群的读能力，可以扩展出一主多从。而为了减轻主节点进行数据同步的压力，可以继续扩展出多级从的主从集群。为了提高整个集群的高可用能力，可以扩展出多主的集群。

为了提高整个集群的高可用能力，可以扩展出多主的集群。

也可以扩展出互为主从的互主集群甚至是环形的主从集群，实现MySQL多活部署。

搭建互主集群只需要按照上面的方式，在主服务上打开一个slave进程，并且指向slave节点的binlog当前文件地址和位置。

这里是使用的最为传统的Binlog方式搭建集群，是基于日志记录点的方式来进行主从同步的。在这个实验中，Executed_Grid_Set一列，实际上就是另外一种搭建主从同步的方式，即GTID搭建方式。GTID的本质也是基于Binlog来实现的主从同步，只是他会基于一个全局的事务ID来标识同步进度。这个GTID全局事务ID是一个全局唯一、并且趋势递增的分布式ID策略。

1.5 GTID（全局事务ID）

1.5.1 GTID

上面我们搭建的集群方式，是基于Binlog日志记录点的方式来搭建的，这也是最为传统的MySQL集群搭建方式。而在这个实验中，可以看到有一个Executed_Grid_Set列，暂时还没有用上。实际上，这就是另外一种搭建主从同步的方式，即GTID搭建方式。这种模式是从MySQL5.6版本引入的。

MySQL GTID复制是一种复制技术，通过使用全局事务标识符（GTID）来追踪和复制事务，确保在主从服务器之间的数据同步和一致性。每个事务都被分配一个唯一的GTID，用于标识事务在主服务器上的提交。

在基于GTID的复制中，首先从服务器会告诉主服务器已经在从服务器执行完了哪些事务的GTID值，然后主库会有把所有没有在从库上执行的事务，发送到从库上进行执行，并且使用GTID的复制可以保证同一个事务只在指定的从库上执行一次，这样可以避免由于偏移量的问题造成数据不一致。

1.5.2 配置GTID的步骤

要配置MySQL GTID复制，需要在my.cnf中进行以下步骤：

1. 在主服务器和从服务器的配置文件中启用GTID模式，并重启MySQL服务。

gtid_mode=on
enforce_gtid_consistency=on
log_bin=on
server_id=单独设置一个
binlog_format=row
1
2
3
4
5

2. 在主服务器上创建一个复制账户，并赋予适当的权限。
3. 在从服务器上设置主服务器的连接信息，并启动复制进程。

gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=1
server_id=单独设置一个
1
2
3
4

然后分别重启主服务和从服务，就可以开启GTID同步复制方式。

1.5.3 GTID复制的优点、注意事项和限制

GTID复制具有以下优点：

• 数据一致性和可靠性：GTID复制使用全局事务标识符，确保主从服务器之间的数据同步和一致性，无需关注binlog文件的编号和轮换。
• 简化主从配置：GTID复制简化了主从服务器的配置过程，无需手动配置和管理binlog文件和位置信息。
• 自动故障转移和重连：GTID复制支持自动故障转移和自动重连，使主从切换和恢复更加简单和可靠。

注意事项和限制：

• 版本要求： GTID复制要求MySQL版本在5.6及以上。
• 兼容性： GTID复制与传统的基于binlog的复制不兼容，无法在同一个复制拓扑中同时使用。
• 主从切换的复杂性：GTID复制的主从切换需要更多的操作和配置，相对于传统的基于binlog的复制更为复杂。

1.6 集群扩容

要扩展到一主多从的集群架构，其实就比较简单了，只需要增加一个binlog复制就行了。

但是如果我们的集群是已经运行过一段时间，这时候如果要扩展新的从节点就有一个问题，之前的数据没办法从binlog来恢复了。这时候在扩展新的slave节点时，就需要增加一个数据复制的操作，扩容期间业务暂停写入。

MySQL的数据备份恢复操作相对比较简单，可以通过SQL语句直接来完成。具体操作可以使用mysql的bin目录下的mysqldump工具。

mysqldump -u root -p --all-databases > backup.sql
#输入密码
1
2

通过这个指令，就可以将整个数据库的所有数据导出成backup.sql，然后把这个backup.sql分发到新的MySQL服务器上，并执行下面的指令将数据全部导入到新的MySQL服务中。

mysql -u root -p < backup.sql
#输入密码
1
2

这样新的MySQL服务就已经有了所有的历史数据，然后就可以再按照上面的步骤，配置Slave从服务的数据同步了。

1.7 半同步复制（保证不丢失数据）

MySQL主从集群默认采用的是一种异步复制的机制。主库在执行用户提交的事务后，写入binlog日志，然后就给客户端返回一个成功的响应了。而binlog会由一个dump线程异步发送给Slave从库。

由于这个发送binlog的过程是异步的。主库在向客户端反馈执行结果时，是不知道binlog是否同步成功了的。这时候如果主库宕机了，而从库还没有备份到新执行的binlog，那就有可能会丢数据。

那怎么解决这个问题呢，这就要靠MySQL的半同步复制机制来保证数据安全。

半同步复制机制是一种介于异步复制和全同步复制之前的机制。主库在执行完客户端提交的事务后，并不是立即返回客户端响应，而是等待至少一个从库接收并写到relay log中，才会返回给客户端。MySQL在等待确认时，默认会等10秒，如果超过10秒没有收到ack，就会降级成为异步复制。

这种半同步复制相比异步复制，能够有效的提高数据的安全性。但是这种安全性也不是绝对的，他只保证事务提交后的binlog至少传输到了一个从库，并且并不保证从库应用这个事务的binlog是成功的。另一方面，半同步复制机制也会造成一定程度的延迟，这个延迟时间最少是一个TCP/IP请求往返的时间。整个服务的性能是会有所下降的。而当从库出现问题时，主库需要等待的时间就会更长，要等到从库的服务恢复或者请求超时才能给用户响应。

1.7.1 搭建半同步复制集群（高并发必备）

半同步复制需要基于特定的扩展模块来实现。而mysql从5.5版本开始，往上的版本都默认自带了这个模块。这个模块包含在mysql安装目录下的lib/plugin目录下的semisync_master.so和semisync_slave.so两个文件中。需要在主库上安装semisync_master模块，在从库上安装semisync_slave模块。

1、登陆主库，安装semisync_master模块：

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like 'rpl_semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set, 1 warning (0.02 sec)

mysql> set global rpl_semi_sync_master_enabled=ON;
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

第一行是通过扩展库来安装半同步复制模块，需要指定扩展库的文件名。
第二行查看系统全局参数，rpl_semi_sync_master_timeout就是半同步复制时等待应答的最长等待时间，默认是10秒，可以根据情况自行调整。
第三行则是打开半同步复制的开关。

在第二行查看系统参数时，最后的一个参数rpl_semi_sync_master_wait_point其实表示一种半同步复制的方式。半同步复制有两种方式，一种是我们现在看到的这种默认的AFTER_SYNC方式。这种方式下，主库把日志写入binlog，并且复制给从库，然后开始等待从库的响应。从库返回成功后，主库再提交事务，接着给客户端返回一个成功响应。而另一种方式是叫做AFTER_COMMIT方式。他不是默认的。这种方式，在主库写入binlog后，等待binlog复制到从库，主库就提交自己的本地事务，再等待从库返回给自己一个成功响应，然后主库再给客户端返回响应。

2、登陆从库，安装smeisync_slave模块

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like 'rpl_semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set, 1 warning (0.01 sec)

mysql> set global rpl_semi_sync_slave_enabled = on;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'rpl_semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

注意：安装完slave端的半同步插件后，需要重启下slave服务