MySQL · 复制

发表于 | 更新于 | 分类于 | 阅读次数：

简介

MySQL 的复制功能是构建基于大规模、高性能应用的基础，同时也是高可用性、可扩展性、灾难恢复、备份以及数据仓库等工作的基础。

复制解决的基本问题：是让一台服务器与其他服务器保持数据同步。

其他扩展功能：

  • 数据分布：复制的开销很小，通常不会对带宽造成很大的压力，可以通过远程复制来构建不同的数据中心。
  • 负载均衡：通过复制可以将读操作分布到多个服务器上，读写分离。
  • 备份：对备份来说，复制是一项很有意义的技术补充，但复制既不是备份也不能够取代备份。
  • 高可用性和故障切换：复制能避免 MySQL 单点失败，设计良好的故障切换系统能显著地缩短宕机时间。

复制原理

  • 主库把数据更改记录到二进制日志（Binary Log）。
  • 备库将主库的日志复制到自己的中继日志（Relay Log）。
  • 备库读取中继日志中的事件，将其重放到备库数据之上。

复制的原理并不复杂，其实就是一个完全备份加上二进制日志备份的还原。需要特别注意的是，复制不是完全实时地进行同步，而是异步实时。

这种架构有一个缺陷：在主库上并发运行的查询在备库只能串行化执行，因为只有一个 SQL 线程来重放中继日志中的事件，这是很多工作负载的性能瓶颈所在。

复制方式

MySQL支持两种复制方式：基于行的复制（MySQL 5.1 版本支持）和基于语句的复制（MySQL 3.23 版本支持），这两种复制方式都是通过在主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制。

复制文件

mysql-bin.index：二进制日志的索引文件，开启二进制日志时就会产生，该文件记录磁盘上的二进制日志文件，每一行包含了二进制文件的文件名。MySQL 依赖这个文件，除非在这个文件里有记录，否则识别不了二进制日志文件。

1
2
3
4
[root@ptdsmapp04 mysql]# cat mysql-bin.index
/data/mysql/mysql-bin.000014
/data/mysql/mysql-bin.000015
/data/mysql/mysql-bin.000016

relay-log.index：中继日志的索引文件，和 mysql-bin.index 作用类似。

1
2
3
[root@ptdsmapp04 mysql]# cat relay-log.index
./relay-log.000016
./relay-log.000017

master.info：这个文件用于保存备库连接到主库所需要的信息，格式为纯文本（每行一个值）。此文件不能删除，否则备库在重启后无法连接到主库。这个文件以文本的方式记录了复制用户的密码，所以要注意此文件的权限控制。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[root@ptdsmapp04 mysql]# cat master.info 
25
mysql-binlog.000234
933317366
10.133.0.51
repl
Changeme_123
3306
60
0

0
30.000

0
e1db90f2-fce1-11e8-86d2-005056b1d631
86400

0

relay-log.info：这个文件包含了当前备库复制的二进制日志和中继日志坐标，同样也不能删除这个文件，否则在备库重启后将无法从哪个位置开始复制，可能会导致重放已经执行过的语句。

1
2
3
4
5
6
7
8
9
[root@ptdsmapp04 mysql]# cat relay-log.info 
7
./relay-log.000017
933317585
mysql-binlog.000234
933317366
0
0
1

参数详解

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.133.0.51
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-binlog.000234
          Read_Master_Log_Pos: 744390811
               Relay_Log_File: relay-log.000017
                Relay_Log_Pos: 744391030
        Relay_Master_Log_File: mysql-binlog.000234
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                            ...
          Exec_Master_Log_Pos: 744390811
                            ...
        Seconds_Behind_Master: 0
1
2
3
4
5
6
7
8
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-binlog.000234
         Position: 844390811
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.01 sec)
变量 说明
Slave_IO_State 显示当前 IO 线程的状态，上述状态是等待主发送二进制日志
Master_Log_File 显示当前同步的主上的二进制日志
Read_Master_Log_Pos 显示当前同步的主上二进制日志的偏移量位置，单位是字节
Relay_Log_File 显示当前写入的中继日志
Relay_Log_Pos 显示当前执行到中继日志的偏移量位置
Relay_Master_Log_File 当前中继日志同步的二进制日志
Slave_IO_Running 从中 IO 线程的运行状态， YES 表示运行正常
Slave_SQL_Running 从中 SQL 线程的运行状态， YES 表示运行正常
Exec_Master_Log_Pos 表示同步的主上二进制日志偏移量的位置，Read_Master_Log_Pos - Exec_Master_Log_Pos 可以表示当前 SQL 线程运行的延时，单位是字节
Seconds_Behind_Master 主从复制延迟，单位是秒

配置主从复制操作实践

  • 每台服务器上创建复制用户
  • 配置主库和备库
  • 通知从库连接到主库并从主库复制数据

创建复制用户

主库和备库都创建该用户：

1
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'x.x.x.%' IDENTIFIED BY 'Changeme_123';

配置主库和从库

主库 my.cnf 文件

1
2
server_id         = 10
log_bin           = mysql-bin

备库 my.cnf 文件

1
2
3
4
5
6
7
server_id         = 20
log_bin           = mysql-bin
relay_log         = relay-log
read_only         = 1
log_slave_updates = 1
sync_binlog       = 0
innodb_flush_log_at_trx_commit = 0

Tips：修改配置文件，需要重启 mysqld 服务生效

同步主从数据并启动复制

（1）克隆数据到从库

1
2
3
4
5
6
7
8
9
10
11
主库全局只读锁：
flush tables with read lock;

备份主库：
mysqldump -uroot -pHaier@123 -A --triggers --routines --events --single-transaction --master-data=2 > master.sql

传到从库：
scp master.sql haieradmin@x.x.x.x:/tmp

从库导入数据：
mysql -uroot -p'xx' < master.sql

（2）配置复制关系

1
2
3
4
5
6
7
CHANGE MASTER TO
MASTER_HOST='10.133.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='Changeme_123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-binlog.000232',
MASTER_LOG_POS=3108253;

（3）启动复制

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.133.0.51
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-binlog.000234
          Read_Master_Log_Pos: 744390811
               Relay_Log_File: relay-log.000017
                Relay_Log_Pos: 744391030
        Relay_Master_Log_File: mysql-binlog.000234
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                            ...
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 744390811
                            ...
        Seconds_Behind_Master: 0

（4）检查复制线程

主库上可以看到由备库 I/O 线程向主库发起的连接：

1
2
3
4
5
6
7
8
9
10
mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1257405
   User: repl
   Host: 10.133.0.52:46102
     db: NULL
Command: Binlog Dump
   Time: 79824
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL

从库上也可以看到两个线程，一个是 I/O 线程，一个是 SQL 线程：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> show processlist\G
*************************** 1. row ***************************
     Id: 55
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 79927
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 56
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 3681
  State: Slave has read all relay log; waiting for more updates
   Info: NULL

（5）主库解锁

1
unlock tables;