死磕数据库系列（八）：MySQL 主从同步详解

2023-03-16 00:00:00 数据 线程 服务器 日志 复制


前面我们学习 MySQL 的数据类型与存储引擎、索引、性能优化等内容，接下来我们学习一下比较重要的知识：MySQL 主从同步复制。

MySQL 主从复制概述

Mysql 内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将 Mysql 的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。

主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

MySQL 主从复制解决的问题

  • 数据分布：通过复制将数据分布到不同地理位置
  • 负载均衡：读写分离以及将读负载到多台从库
  • 备份：可作为实时备份
  • 高可用性：利用主主复制实现高可用

MySQL 主从复制原理

复制的原理其实很简单，仅分为以下三步：

  • 在主库上把数据更改记录到二进制日志binary log中，具体是在每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中去，Mysql会按照事务提交的顺序来记录二进制日志的。日志记录好之后，主库通知存储引擎提交事务。
  • 从库会启动一个IO线程，该线程会连接到主库。而主库上的binlog dump线程会去读取主库本地的binlog日志文件中的更新事件。发往从库，从库接收到日志之后会将其记录到本地的中继日志relay-log当中。
  • 从库中的SQL线程读取中继日志relay-log中的事件，将其重放到从库中。（在5.6版本之前SQL线程是单线程的，使得主从之间延迟更大）

MySQL 主从复制工作流程

整体上来说，复制有3个步骤：

  • master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
  • slave将master的binary log events拷贝到它的中继日志(relay log)；
  • slave重做中继日志中的事件，将改变反映它自己的数据。

下图描述了复制的过程：

  • 该过程的部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
  • 下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
  • SQL slave thread（SQL从线程）处理该过程的后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。
  • 此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

MySQL 主从复制的方式

日志文件中记录的到底是什么呢？mysql支持了两种日志格式，这两种日志格式也体现了各自的复制方式

基于语句复制

基于语句的复制相当于逻辑复制，即二进制日志记录了操作的语句，通过这些语句在从库进行重放来实现复制。

这种方式简单，二进制日志占用空间少，使得带宽小传输效率较高。但是基于语句的更新依赖于其他因素，比如插入数据时利用时间戳函数调用当前时间作为时间值也会出现问题，因为由于主从之间的延迟导致时间值不一致。存储过程和触发器也可能出现问题。

所以在开发当中我们应该将逻辑尽量放在代码层，而不应放到mysql中，不易扩展。

基于行复制

基于行的复制相当于物理复制，即二进制日志记录了实际更新数据的每一行。这样导致行复制的压力比较大，因为日志占用空间较大，传输占用带宽也较高。但是比基于语句复制更加，可以屏蔽一些由于主库从库之间的差异导致的不一致。如刚才提到的时间戳函数。

二者对比：
  • 语句复制
    • 传输效率高，减少延迟。
    • 在从库更新不存在的记录时，语句赋值不会失败。而行复制会导致失败，从而更早发现主从之间的不一致。
    • 设表里有一百万条数据，一条sql更新了所有表，基于语句的复制仅需要发送一条sql，而基于行的复制需要发送一百万条更新记录
  • 行复制
    • 不需要执行查询计划。
    • 不知道执行的到底是什么语句。

例如一条更新用户总积分的语句，需要统计用户的所有积分再写入用户表。如果是基于语句复制的话，从库需要再一次统计用户的积分，而基于行复制就直接更新记录，无需再统计用户积分。

因为两种方式各有优缺点，所以 mysql 在这两种复制模式进行动态的切换。默认是语句。

MySQL主从复制配置过程

有两台MySQL数据库服务器Master和slave，Master为主服务器，slave为从服务器，初始状态时，Master和slave中的数据信息相同，当Master中的数据发生变化时，slave也跟着发生相应的变化，使得master和slave的数据信息同步，达到备份的目的。

要点：负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输给从服务器的各种修改动作。因此，主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。

MySQL 的安装请参阅：CentOS 下 MySQL 8.0 安装部署，超详细！，你可能已经知道 MySQL 从版本 5.7 开始提供了 NoSQL 存储的功能，在 8.0 中这部分功能也得到了一些改进（MySQL 5.7 vs 8.0，哪个性能更牛？），MySQL 8.0 的 5 个新特性，太实用了！

创建复制帐号

1、在Master的数据库中建立一个备份帐户：每个slave使用标准的 MySQL 用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。用户名的密码都会存储在文本文件master.info中

命令如下：

mysql > GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO backup@’10.100..200’ IDENTIFIED BY ‘1234’;

建立一个帐户backup，并且只能允许从10.100.0.200这个地址上来登陆，密码是1234。

如果因为mysql版本新旧密码算法不同，可以设置：

set password for 'backup'@'10.100.0.200'=old_password('1234')

拷贝数据:（假如是你完全新安装mysql主从服务器，这个一步就不需要。因为新安装的master和slave有相同的数据）

关停Master服务器，将Master中的数据拷贝到B服务器中，使得Master和slave中的数据同步，并且确保在全部设置操作结束前，禁止在Master和slave服务器中进行写操作，使得两数据库中的数据一定要相同！

配置master

接下来对master进行配置，包括打开二进制日志，指定的servr ID。例如，在配置文件加入如下值：

server-id=1
log-bin=mysql-bin

server-id：#为主服务器A的ID值
log-bin：#二进制变更日值

重启master，运行SHOW MASTER STATUS，输出如下：

配置slave

Slave的配置与master类似，你同样需要重启slave的MySQL。如下：

log_bin           = mysql-bin
server_id         = 2
relay_log         = mysql-relay-bin
log_slave_updates = 1
read_only         = 1
#server_id：是必须的，而且。

log_bin：slave没有必要开启二进制日志bin_log，但是在一些情况下，必须设置，例如，如果slave为其它slave的master，必须设置bin_log。在这里，我们开启了二进制日志，而且显示的命名(默认名称为hostname，但是，如果hostname改变则会出现问题)。

relay_log：配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志(后面会看到它的用处)。有些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。

read_only：尽量使用read_only，它防止改变数据(除了特殊的线程)。但是，read_only并是很实用，特别是那些需要在slave上创建表的应用。

启动slave

接下来就是让slave连接master，并开始重做master二进制日志中的事件。你不应该用配置文件进行该操作，而应该使用CHANGE MASTER TO语句，该语句可以完全取代对配置文件的修改，而且它可以为slave指定不同的master，而不需要停止服务器。如下：

mysql> CHANGE MASTER TO MASTER_HOST='server1',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='p4ssword',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=;

MASTER_LOG_POS的值为0，因为它是日志的开始位置。

你可以用SHOW SLAVE STATUS语句查看slave的设置是否正确：

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: server1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
...omitted...
Seconds_Behind_Master: NULL

Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running是No，表明slave还没有开始复制过程。日志的位置为4而不是0，这是因为0只是日志文件的开始位置，并不是日志位置。实际上，MySQL知道的个事件的位置是4。

为了开始复制，你可以运行：

mysql> START SLAVE;

运行SHOW SLAVE STATUS查看输出结果：

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: server1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 164
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 164
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...omitted...
Seconds_Behind_Master: 0

在这里主要是看:

  • Slave_IO_Running=Yes
  • Slave_SQL_Running=Yes

slave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master不再是NULL。日志的位置增加了，意味着一些事件被获取并执行了。如果你在master上进行修改，你可以在slave上看到各种日志文件的位置的变化，同样，你也可以看到数据库中数据的变化。

你可查看master和slave上线程的状态。在master上，你可以看到slave的I/O线程创建的连接：

在master上输入show processlist \G;

mysql> show processlist \G
*************************** 1. row ***************************
Id: 1
User: root
Host: localhost:2096
db: test
Command: Query
Time: 
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 2
User: repl
Host: localhost:2144
db: NULL
Command: Binlog Dump
Time: 1838
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL

2 rows in set (0.00 sec)

行2为处理slave的I/O线程的连接。

在slave服务器上运行该语句：

mysql> show processlist \G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 2291
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 1852
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 5
User: root
Host: localhost:2152
db: test
Command: Query
Time: 
State: NULL
Info: show processlist

3 rows in set (0.00 sec)

行1为I/O线程状态，行2为SQL线程状态。

以上就是 MySQL 主从同步复制的配置过程，其实，MySQL5.6 开始新增一种方式的主从同步复制策略：基于 Gtid 的主从同步。由于篇幅原因，这里就不再赘述了，有需要的读者可以参阅往期推文：基于 Gtid 的 MySQL 主从同步实践

添加新 slave 服务器

假如master已经运行很久了，想对新安装的slave进行数据同步，甚至它没有master的数据。此时，有几种方法可以使slave从另一个服务开始，例如，从master拷贝数据，从另一个slave克隆，从近的备份开始一个slave。Slave与master同步时，需要三样东西：

  • (1)master的某个时刻的数据快照；
  • (2)master当前的日志文件、以及生成快照时的字节偏移。这两个值可以叫做日志文件坐标(log file coordinate)，因为它们确定了一个二进制日志的位置，你可以用SHOW MASTER STATUS命令找到日志文件的坐标；
  • (3)master的二进制日志文件。

可以通过以下几中方法来克隆一个slave：

  • (1)冷拷贝(cold copy)
    • 停止master，将master的文件拷贝到slave；然后重启master。缺点很明显。
  • (2)热拷贝(warm copy)
    • 如果你仅使用MyISAM表，你可以使用mysqlhotcopy拷贝，即使服务器正在运行。
  • (3)使用mysqldump
    • <1>锁表：如果你还没有锁表，你应该对表加锁，防止其它连接修改数据库，否则，你得到的数据可以是不一致的。如下：
    • 使用mysqldump来得到一个数据快照可分为以下几步：
mysql> FLUSH TABLES WITH READ LOCK;
  • <2>在另一个连接用mysqldump创建一个你想进行复制的数据库的转储：
shell> mysqldump --all-databases --lock-all-tables >dbdump.db
  • <3>对表释放锁。
mysql> UNLOCK TABLES;

MySQL 主从复制的常见问题

错误一：change master导致的：

Last_IO_Error: error connecting to master 'repl1@IP:3306' - retry-time: 60  retries

错误二：在没有解锁的情况下停止slave进程：

mysql> stop slave;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

错误三：在没有停止slave进程的情况下change master

mysql> change master to master_host=‘IP', master_user='USER', master_password='PASSWD', master_log_file='mysql-bin.000001',master_log_pos=106;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first

错误四：A B的server-id相同：

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; 
these ids must be different for replication to work (or the --replicate-same-server-id option must be used on
slave but this does not always make sense; please check the manual before using it). 

#查看server-id
mysql> show variables like 'server_id'; 

#手动修改server-id
mysql> set global server_id=2; #此处的数值和my.cnf里设置的一样就行 
mysql> slave start;

错误五：change master之后，查看slave的状态，发现slave_IO_running 仍为NO

需要注意的是，上述几个错误做完操作之后要重启mysql进程，slave_IO_running 变为Yes

错误六：MySQL主从同步异常Client requested master to start replication from position > file size

字面理解：从库的读取binlog的位置大于主库当前binglog的值

这一般是主库重启导致的问题，主库从参数sync_binlog默认为1000，即主库的数据是先缓存到1000条后统一fsync到磁盘的binlog文件中。当主库重启的时候，从库直接读取主库接着之前的位点重新拉binlog，但是主库由于没有fsync后的binlog，所以会返回1236的错误。

正常建议配置sync_binlog=1 也就是每个事务都立即写入到binlog文件中。

  • 1、在从库检查slave状态:

偏移量为4063315

  • 2、在主库检查mysql-bin.001574的偏移量位置
mysqlbinlog mysql-bin.001574 >  ./mysql-bin.001574.bak
tail -10 ./mysql-bin.001574.bak

mysql-bin.001574文件后几行 发现后偏移量是4059237，从库偏移量的4063315远大主库的偏移量4059237，也就是参数sync_binlog=1000导致的。

  • 3、重新设置salve
mysql> stop slave;
mysql> change master to master_log_file='mysql-bin.001574' ,master_log_pos=4059237;
mysql> start slave;

错误8：数据同步异常情况

种：在master上删除一条记录，而slave上找不到。

Last_Error: Could not execute Delete_rows event on table market_edu.tl_player_task; Can't find record in 'tl_player_task', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.002094, end_log_pos 286434186

解决方法：由于master要删除一条记录，而slave上找不到故报错，这种情况主上都将其删除了，那么从机可以直接跳过。可用命令：

stop slave;   
set global sql_slave_skip_counter=1;    
start slave;

第二种：主键重复。在slave已经有该记录，又在master上插入了同一条记录。

Last_SQL_Error: Could not execute Write_rows event on table hcy.t1; 
Duplicate entry '2' for key 'PRIMARY', 
Error_code: 1062; 
handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 924

解决方法：在slave删除重复的主键.

第三种：在master上更新一条记录，而slave上找不到，丢失了数据。

Last_SQL_Error: Could not execute Update_rows event on table hcy.t1;
Can't find record in 't1', 
Error_code: 1032; 
handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000010, end_log_pos 263

解决方法：把丢失的数据在slave上填补，然后跳过报错即可。

insert into t1 values (2,'BTV');
stop slave ;set global sql_slave_skip_counter=1;start slave;

延迟问题

延迟的产生

当主库的TPS并发较高时，由于主库上面是多线程写入的，而从库的SQL线程是单线程的，导致从库SQL可能会跟不上主库的处理速度（生产者比消费者快，导致商品堆积）。

延迟的解决
  • 网络方面：将从库分布在相同局域网内或网络延迟较小的环境中。
  • 硬件方面：从库配置更好的硬件，提升随机写的性能。
  • 配置方面：
    • 从库配置
sync_binlog=0
innodb_flush_log_at_trx_commit=2
logs-slave-updates=0
增大 innodb_buffer_pool_size

让更多操作在Mysql内存中完成，减少磁盘操作。或者升级Mysql5.7版本使用并行复制。

架构方面：比如在事务当中尽量对主库读写，其他非事务中的读在从库。消除一部分延迟带来的数据库不一致。增加缓存降低一些从库的负载。

笔者个人心得，如有错误恳请网友评论指正。

来源：guisu.blog.csdn.net/article/details/7325124 来源：juejin.cn/post/6844903968259178504



相关文章