MySQL 基于日志点复制

MySQL 的复制功能不仅可以构建高性能的应用，也是高可用性、可扩展性、灾难恢复等工作的基础。

复制的主要功能

1、数据分布。
MySQL 提供的复制功能并不需要很大的带宽的要求，因此可以在不同的物理位置来分布数据。

2、读操作的负载均衡
通过复制与负载均衡方案，可以将读操作分布到多个从服务器上，实现对读密集型应用的性能优化。

3、备份
复制能起到一定的备份作用，但是并不能完全取代备份。

4、高可用性和故障转移
复制能够帮助避免 MySQL 单点失败的问题，当主库宕机时，迅速的故障切换能减少系统的停机时间。

基于日志点复制的原理

大体来说，复制分为以下几步：

1、主库将数据的更新事件记录到二进制日志。

二进制日志由各种事件组成，一个事件通常为一个更新加一些其它信息，比如时间戳。在每次准备提交事务前，主库将数据更新的事件记录到二进制日志中。记录完毕后，主库将告诉存储引擎可以提交事件了。

2、从库将主库上的日志复制到自己的中继日志中。

首先，从库启动一个工作线程，称为 I/O 线程，该线程跟主库建立一个普通的连接，然后在主库上启动一个二进制转储（binlog dump）线程，这个二进制存储线程会读取并向从库发送主库上二进制日志中的事件，从库的 I/O 线程接收到事件并将其记录到从库的中继日志中。

注意，二进制存储线程的工作方式不是轮询，当该线程追赶上了主库，它就进入休眠状态，直到主库发送信号通知来将其唤醒。

3、从库读取中继日志中的事件，将其重放到从库中。

当主库的时间发送过来时，从库的 I/O线程将事件写入中继日志（relay log）中，然后从库上 SQL 线程就去读取并且重放中继日志中的事件，达到与主库数据的一致。可以看到， I/O 线程和 SQL 线程的协作，使得获取事件和重放事件的解耦，两者异步执行。在 5.6之前，只能有一个 SQL 线程工作，因此重放是串行化进行的。在5.6之后，增加了多线程重放，但是注意，多线程是基于库的，一个库只能使用一个 SQL 线程重放事件。

复制的步骤

1、创建复制账号

从库中运行的 I/O 线程会建立一个到主库的连接，因此需要在主库上创建个用户，并且拥有适当的权限，从库将以该用户的身份连接到主库上读取其二进制日志。

建立一个只有使用使用内网 IP 才能登陆的账号：

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO repl@’192.168.xxx.%’ IDENTIFIED BY ‘password’;

该命令创建 repl账号，同时为授予 REPLICATION SLAVE 与 REPLICATION CLIENT 权限。注意，实际上只需要 REPLICATION SLAVE 权限就可以进行复制了。而 REPLICATION CLIENT 权限主要是允许该账号对复制进行监控和管理，比如 SHOW MASTER STATUS、SHOW SLAVE STATUS、SHOW BINARY LOGS 语句的使用。

账户建立好后，应该在从库主机上尝试着使用该账号远程登录主库，如果顺利登陆，说明两台主机的 MySQL 能正常通信，并且账号没问题。

2、对主、从库进行配置

对主、从库进行必要的配置：
主：

# 该命令启动二进制日志，并且指定二进制日志位置与名称
log_bin   = mysql-bin
# 这里必须为主库设置一个唯一的 ID 
server_id = 1

备：

log_bin           = mysql-bin
# 确保唯一性
server_id         = 2
# 指定中继日志的位置与名称
relay_log         = /var/lib/mysql/mysql-relay-bin
# 命令从库将其重放的事件记录到自己的二进制日志中
log_slave_updates = 1
# 阻止没有特权权限的线程修改数据
read_only         = 1

实际上，只有 server_id 是必须设置的，其他的配置项根据实际情况进行配置。例如，如果从库也是其他库的主库，那么就要将 log_slave_updates 设置为1。如果从库只用来读数据，则要将 read_only 设置为1。

3、初始化数据

使用 mysqldump 或者其他方法对主库数据进行全量备份，然后在从库上恢复该备份。注意，主库备份的时候，要记录当前的二进制日志的名字及坐标（即偏移量）。可以通过 SHOW MASTER STATUS 命令来查看。

4、开始复制

数据初始化好后，就可以开始复制了。

mysql-> CHANGE MASTER TO MASTER_HOST = ‘主机IP’，
-> MASTER_USER = ‘repl’,
-> MASTER_PASSWORD = ‘password’,
-> MASTER_LOG_FILE = ‘主库上当前二进制文件’,
-> MASTER_LOG_POS = ‘日志坐标’;

开始复制后，可以随时使用 CHANGE MASTER TO 来动态地更改复制的设置，而不需要重启从库。

注意，一定要获得正确的二进制日志坐标，I/O线程会去从日志坐标开始一直读到日志最后，然后进入休眠状态，直到主库发送信号唤醒。

设置好后，使用
mysql-> START SLAVE;

可以使用 SHOW SLAVE STATUS 命令来查看情况，如果 SLAVE_IO_STATUS 是 Waiting for master to send event，那么就说明复制已经正常开始了。

查看从库状态

通过在从库上执行命令

mysql-> show slave status;

Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.136.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 3266
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 877
Relay_Master_Log_File: mysql.000010
Slave_IO_Running: Yes　
Slave_SQL_Running: Yes

　　　　　……
Exec_Master_Log_Pos: 3266
Relay_Log_Space: 1084

　　　　　……
Seconds_Behind_Master: 0

　　　　　……
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400

主要变量解释：

Slave_IO_State 为 I/O线程的状态，Waiting for master to send event 说明该线程正在等待主库发送事件。

Read_Master_Log_Pos 为 I/O线程读取主库上的二进制日志到了哪个位置，单位为字节。与主库上二进制日志记录坐标进行对比，可以得到当前 I/O 线程的延时。

Exec_Master_Log_Pos 为 SQL线程重复主库上的二进制日志到了哪个位置，单位为字节，（Read_Master_Log_Pos - Exec_Master_Log_Pos ）可以表示当前 SQL 线程运行的延时。 Read_Master_Log_Pos 总是大于等于 Exec_Master_Log_Pos 。

Seconds_Behind_Master 是通过比较 SQL线程执行的最后一个事件里记录的时间戳和 I/O线程获取到的最后一个事件里记录的时间戳进行比较，而得到的这么一个差值。该值并不能完全反应主从延迟，考虑这种情况：当主库I/O负载很大或是网络阻塞， I/O线程不能及时获取到二进制日志里的事件，而 SQL线程一直都能跟上 I/O线程的脚步，这时Seconds_Behind_Master 的值为0，但此时主从之间是有延迟的。

SQL_Delay 5.6之后新增的延迟复制，表示获取到主库的事件之后， SQL线程还要等待N秒才重放。

SQL_Remaining_Delay 如果 SQL线程在等待过程中，则表示剩余等待时间，其他时候为0。

Slave_SQL_Running_State SQL线程的状态

基于 GTID 复制

基于日志点的复制需要对从库指定二进制日志的偏移量，如果偏移量有差错，会导致同步后数据不一致的问题，并且可能会报错而终止复制。为了解决这个问题，MySQL 在5.6之后推出了基于 GTID 的复制。

GTID 简单地说就是 MySQL 为事务生成的 ID，它是全局唯一的，即在整个集群中都是唯一的。

从库会告诉主库已执行的事务的 GTID 值，主库会告诉从库哪些GTID 事务没有被执行。这一切都是由 MySQL 维护进行的，因此能更好地保证主从数据的一致性。