MySQL主从复制的一些心得体会

187次阅读

共计 4084 个字符，预计需要花费 11 分钟才能阅读完成。

MySQL 复制
MySQL 复制支持单向，异步复制。通过一台主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。MySQL 主从复制是异步进行的。同步需要版本为 5.5, 使用 google 提供的插件来实现。

MySQL 主从复制操作很灵活既可以实现整个服务的级别的复制，也可以实现对某个库，甚至某个数据库中的指定的某个对象进行复制。

MySQL 主从复制应用场景
提高性能。通过一 (多) 主多从的部署方案，将涉及数据写的操作放在 Master 端操作，而将数据读的操作分散到众多的 Slave 当中。降低了 Master 的负载，提高数据写入的响应效率；多台从服务器提供读，分担了请求，提高了读的效率。

数据安全。数据复制到 Slave 节点，即使 Master 宕机，Slave 节点还保存着完整数据。

数据分析。将数据分析，放在 slave 上。

主从复制的原理
MySQL 的 Replication 是一个异步的复制过程，从一个 MySQL 实例(Master) 复制到另一台 MySQL 实例上。在 Master 和 Slave 之间复制的整个过程主要由 3 个线程完成，其中两个线程（SQL 线程和 IO 线程）在 Slave 端，另外一个线程 (IO 线程) 在 Master 端。

要实现主从复制，首先要在 Master 端打开 Binary Log 功能。因为整个复制过程实际上就是 Slave 从 Master 上获取二进制日志，然后在自己身上完全按照产生的顺序一次执行日志中记录的各种操作的过程。

复制的具体过程如下：

MySQL 主从复制的一些心得体会

MySQL 主从复制原理图

Slave 的 IO 线程连上 Master，并请求日志文件指定位置 (或从开始的日志) 之后的日志的内容。

Master 接收到来自 Slave 的 IO 线程请求后，负责复制 IO 线程根据请求的信息读取指定日志之后的日志信息，返回给 Slave 端的 IO 线程。返回信息中除了日志所包含的信息，还包含了包括本次返回的信息在 Master 端的 Binary Log 文件的名称和位置。

Slave 的 IO 线程接受到信息后，将日志内容一次写入 Slave 端的 Relay Log 文件 (mysql-relay-bin.xxxx) 的末端，并将读取到的 Master 端的 bin-log 的文件和位置记录到 master-info 文件中，以便在下一次读取时能够清楚地告诉 Master，下次从 bin-log 哪个位置开始往后的日志内容。

Slave 的 SQL 线程检测检测到 Relay Log 中更新内容后，会马上解析该 Log 文件中的内容，还原成在 Master 端真实执行时的可执行的 SQL 语句，并执行这些 SQK 语句。实际上 Master 和 Slave 执行同样的语句。

Binary Log 记录方式
Row Level
Binary Log 会记录成每一行数据被修改的形式，然后在 Slave 端再对相同的数据进行修改。

优点：在 Row Level 模式下，Binnary Log 可以不记录执行的 Query 语句的上下文相关信息，只要记录哪一行修改了，修改成什么样子。Row Level 会详细的记录下每一行数据的修改细节，而且不会出现某个特定情况下的存储过程，或 Function，以及 Trigger 的调用和触发无法被正确复制问题。

缺点：产生大量的日志内容。

Statment Level
每一条会修改的 SQL 语句都会记录到 Master 的 Binnary 中。Slave 端在复制的时候，SQL 线程会解析成和原来 Master 端执行过相同的 SQL 语句，并再次执行。

优点：首先，解决了 Row Level 下的缺点，不须要记录每一行的数据变化，减少了 Binnary Log 日志量，节约了 IO 成本，提高了性能。

缺点：由于它是记录的执行语句，为了让这些语句在 Slave 端也能正确执行。那么它还必须记录每条语句在执行时的一些相关信息，即上下文信息，以保证所有语句在 Slave 端被执行的时候能够得到和在 Master 端执行时相同的结果。另外，由于 MySQL 发展比较快，很多新功能不断加入，使得 MySQL 复制遇到了不小的挑战，复制时设计的内容岳父在，越容易出 bug。在 Statement Level 下，目前已发现不少的情况下会造成 MySQL 的复制问题。主要是在修改数据使用了某些特定的函数货功能后，出现，比如：Sleep()函数在有些版本中就不能正确的复制，在存储过程中使用了 last_insert_id()函数，可能会使 Slave 和 Master 的到不一致的 ID，等等。

Mixed Level
在 Mixed 模式下，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 Statement 和 Row 之间选择一种。除了 MySQL 认为通过 Statement 方式可能造成复制过程中 Master 和 Slave 之间产生不一致数据。(如特殊 Procedure 和 Funtion 的使用，UUID()函数的使用等特殊情况)时，它会选择 ROW 的模式来记录变更之外，都会使用 Statement 方式。

mysql 主从延时

有很多种原因会造成 mysql 的主从延时，通过查看 Seconds_Behind_Master: 的值来确定是否存在延迟问题，该值越大，延迟现象越明显。众所周知备库 relay-log 和主库的 bin-log 里的内容一样，真正和主库有关两的是 io_thread，当主库 I / O 负载很大或网络阻塞时，io_thread 不能及时复制 binlog，而 sql_thread 直能跟上 io_thread 的脚步，这时 seconds_behind_master 的值是 0，实际上却不是，这时用该值作为延迟参考则不准。我们可以使用 pt-heartbeat 来监控延时现象。该工具定期在主库上不断写入时间戳，通过主从复制同步到从库中去，此时从库服务器的时间和主库保持一致，通过比较从服务器上同步过来的主库时间戳和从库系统时间戳的差值的大小来确定是否存在延迟现象。

主从延迟的主要原因是：主库多线程并发更新，从库单线程串行更新。解决方法：将从库变为多线程更新，可以使用 mysql-transfer：是一个基于 mysql 的 patch，用来加速主从同步速度。

一般来说，我们遇到了主从延时的问题，如果不是高并发原因的话，可以手动临时解决一下：

方法一：忽略错误后，继续同步
该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况
解决：
stop slave;
# 表示跳过一步错误，后面的数字可变
set global sql_slave_skip_counter =1;
start slave;
之后再用 mysql> show slave status\G 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
ok，现在主从同步状态正常了。。。

方式二：重新做主从，完全同步
该方法适用于主从库数据相差较大，或者要求数据完全统一的情况
解决步骤如下：
1. 先进入主库，进行锁表，防止数据写入
使用命令：
mysql> flush tables with read lock;
注意：该处是锁定为只读状态，语句不区分大小写
2. 进行数据备份
# 把数据备份到 mysql.bak.sql 文件
[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql
这里注意一点：数据库备份一定要定期进行，可以用 shell 脚本或者 Python 脚本，都比较方便，确保数据万无一失
3. 把 mysql 备份文件传到从库机器，进行数据恢复
# 使用 scp 命令
[root@server01 mysql]# scp mysql.bak.sql root@192.168.128.101:/tmp/
4. 停止从库的状态
mysql> stop slave;
5. 然后到从库执行 mysql 命令，导入数据备份
mysql> source /tmp/mysql.bak.sql
6. 设置从库同步，注意该处的同步点，就是主库 show master status 信息里的 | File| Position 两项
change master to master_host = ‘192.168.128.100’, master_user = ‘rsync’, master_port=3306, master_password=”, master_log_file = ‘mysqld-bin.000001’, master_log_pos=3260;
7. 重新开启从同步
mysql> stop slave;
8. 查看同步状态
mysql> show slave status\G 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
好了，同步完成啦。