MySQL 复制备忘

一、环境说明

MySQL 支持两种复制方式:基于行的复制和基于语句的复制,两种方式都是通过在主库上记录二进制日志,在被库重复日志的方式来实现异步的数据复制。

二、复制的用途

三、复制工作过程

第一步是主库记录二进制日志。在每次准备提交事务完成数据更新前,主库将数据更新的事件记录到二进制日志中,MySQL 会按照事务提交的顺序而不是语句执行顺序来记录二进制日志。在记录二进制日志后,主库会告诉存储引擎可以提交事务了。然后备库复制二进制日志到其本地中继日志中,首先备库会启动一个工作线程,称为 I/O 线程,I/O 和主库建立一个普通的客户端连接,然后在主库上启动一个特殊的二进制转储(binlog dump)线程,该线程会读取主库上二进制日志的事件,如果该线程追赶上主库将睡眠状态,直到主库发送信号量通知其有新事件产生才会被唤醒,备库 I/O 线程会将收到的事件记录到中继日记。备库复制过程有两个线程,一个 SQL 线程和一个 IO 线程。

四、复制配置

要求:主备拥有同样的数据

过程:

4.1 创建复制账号

主从推荐都加,其实主只需要 REPLICATION SLAVE 权限就可以了,主要目的是:

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
    -> TO repl@'192.168.0.%' IDENTIFIED BY 'pass';

4.2 配置主库和备库

主库:打开二进制日志选项,指定唯一的 server id,在配置文件 my.cnf 修改如下

[mysqld]
log-bin=bin
server-id=1     # server id 值应该唯一,防止相同

如果之前没有开启二进制日志,则需要重启 MySQL 服务,为确保二进制是否已经创建,使用 SHOW MASTER STATUS 命令检查

mysql> SHOW MASTER STATUS\G
+----------------+-----------+--------------+------------------+-------------------+
| File           | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+-----------+--------------+------------------+-------------------+
| bin-log.000001 | 400       |              |                  |                   |
+----------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

备库修改配置如下:

[mysqld]
log-bin=bin
server-id=2
relay-log=mysqld-relay-bin
log-slave-updates=1
# 如果是 A -> B -> C 架构需要启用 log-slave-updates 该选项,允许备库将其重放的事件也记录到自身的二进制日志中
read_only=1     # 防止从库被修改

log_slave_updates 选项可以让备库变成其它服务器的主库,下图为发送复制事件到其它备库

4.3 启动复制

从库启动复制配置

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.10',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='pass',
    -> MASTER_LOG_FILE='bin-log.000001',
    -> MASTER_LOG_POS=400,
    -> MASTER_CONNECT_RETRY=10;

查看当前状态

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

START SLAVE

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

五、线上服务器配置主从

很多情况下,一些主库已经运行很长时间了,需要做主从同步,这时从库还没有数据,那么就要把主的数据导入到从。

最基本的方法是关闭主库,把数据复制到备库。然后重启主库后,会使用一个新的二进制文件,在备库执行 CHANGE MASTER TO 指向这个文件的起始处即可。(缺点很明显,关闭主库)

如果只包含 InnoDB 表,那么使用 mysqldump 如下命令备份数据:

mysqldump -hxx.xx.xx.xx --single-transaction --all-databases \
    --master-data=1 > all-databases.sql

或者边导出边写入

mysqldump -hmaster_ip --single-transaction --all-databases \
    --master-data=1 | mysql -hslave_ip

master-data 默认值是 1,加上之后会记录 Position 点并执行 CHANGE MASTER TO MASTER_LOG_FILE='bin-log.xxxx', MASTER_LOG_POS=xxxx; 语句,为 2 则记录但会注释 CHANGE MASTER 语句,不会被执行。

六、一些重要的选项

关于主从复制的一些问题(如延迟问题等),这里暂不讨论。

七、参考

–EOF–

Written by kumu on 17 February 2014