MySQL主从复制简述

本文对MySQL主从复制的配置以及实现原理进行简述。

概述

MySQL主从复制技术是指将主库中的数据复制到从数据库中,基本实现流程是将主库的操作写入二进制日志文件,将二进制日志文件传递到从库,在从库中重放日志中的操作过程,从而达到对主库的操作(增删改)同步更新至从库的目的。

类型

MySQL主从复制的方案主要有:主从复制、主主复制、多源复制等多种类型。

优势

  • 减轻负载
    读写分离,主库写,从库读,降低主库压力;
  • 确保安全
    在从库进行数据备份,不影响主库服务和性能;
  • 提升性能
    主库出现异常时,切换到从库提供服务,缩短停机窗口;

多源复制技术(Multi-Source Replication)是从MySQL 5.7版本之后支持的复制技术,如上图中的类型⑤所示,即:将多个Master的数据汇聚到一台Slave上,这种复制类型除了上述优点外,还具有[1]

  • 数据汇集
    将多台主库的数据汇集到一台从库服务器,尤其对于分库分表的应用场景中,可以在从库中对数据做统一处理;
  • 成本节约
    在MySQL 5.7版本之前,对于1主1从或1主多从的复制方式,需要为每台主机配置相应1台备机,多源复制技术出现后,可以将多个从库进行数据合并,用一台备机进行备份;
  • 集中备份
    方便在一台服务器上对接收到的所有主库数据进行统一备份;
  • 异地灾备
    将从库放在距离远的地方,可用于异地备份;

主从复制

实现过程

MySQL主从复制的基本流程是:
Master服务器开启binlog日志功能,当Master数据发生变更时,将变更信息记录到binlog二进制日志文件中;
Slave服务器会开启I/O thread线程,连接到Master服务器,Master服务器开启Binlog Dump线程,检测Master服务器的二进制文件变化,若有新的变更记录产生,Binlog Dump线程将读取的二进制内容发送至Slave服务器,Slave服务器的I/O thread线程读取Binlog Dump线程发送过来的内容,并将其写入Slave服务器的中继日志文件relaylog中;
Slave服务器中的SQL thread线程会检测relaylog的变化,将Master上的变更事件在Slave服务器中进行重放操作,从而使Master服务器上的数据与Slave服务器一致。

注意事项:

  • 主从同步前主从服务器做好时间同步校对;
  • Master要开启binlog二进制日志功能,通常为了数据安全性考虑,Slave服务器最好也同时开启;
  • 主从服务器的MySQL版本最好相同(若不相同,要保证Master 的版本低于Slave的版本)
  • 主服务器要创建账号且拥有SLAVE权限
  • 复制过程需要3个线程参与,其中Master服务器1个(Binlog Dump),Slave服务器2个(I/O thread、SQL thread)
  • 同步前主从库中的数据要保持一致(可以在同步前将主库的数据导入从库)

配置流程

假定Master和Slave服务器的IP分别为:
Master:192.168.1.100
Slave:192.168.1.101

Master服务器:

  • 创建用户

    1
    2
    GRANT REPLICATION SLAVE  ON *.* TO 'slave'@'192.168.1.101' IDENTIFIED BY 'password' ;
    FLUSH PRIVILEGES;

    创建账号slave,添加SLAVE的权限,且只能从192.168.1.101登录,密码为password;如果要撤销同步账号的权限,执行:

    1
    REVOKE REPLICATION SLAVE ON *.* FROM 'slave'@'192.168.1.101';
  • 修改MySQL配置
    在[mysqld]配置项下添加:

    1
    2
    3
    log-bin           = mysql-bin   #[必须]启用二进制日志
    server-id = 1 #[必须]服务器唯一ID,默认是1
    expire-logs-days = 3 #只保留3天的二进制日志,以防磁盘被日志占满

    重启MySQL服务

  • 查看主库状态
    在主库执行show master status;

    1
    2
    3
    4
    5
    6
    7
    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000001 | 128 | | | |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

    记录File和Position的值,在后续配置从库时会用到。

注意事项:

  • 在主从同步前,需要保证主库和从库的数据一致,可以将主库数据导入到从库,在导出主库数据之前,先锁定主库:

    1
    flush tables with read lock;    #数据库只读锁定命令,防止导出数据库的时候有数据写入。unlock tables命令解除锁定

    导出数据库命令:

    1
    mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql

    导入数据库命令:

    1
    2
    3
    4
    # 首先创建数据库
    CREATE DATABASE `数据库名`;
    # 导入数据
    mysql -u用户名 -p密码 数据库名 < 数据库名.sql
  • 建议不要在主库配置要同步的数据库和要忽略的数据库,只在从库上做限制,具体原因可参考:《为什么mysql的binlog-do-db选项是危险的》,同时关于binlog-do-db、 binlog-ignore-db、replicate-do-db、replicate-ignore-db的配置说明可参考:《MySQL主从复制过滤规则应用》

Slave服务器:

  • 修改MySQL配置
    在[mysqld]配置项下添加:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    log-bin               = mysql-bin           #[建议]启用二进制日志
    server-id = 2 #[必须]服务器唯一ID,不能与主库相同
    replicate-ignore-db = mysql #[建议]不进行备份的数据库
    replicate-ignore-db = information_schema #[建议]不进行备份的数据库
    replicate-ignore-db = performation_schema #[建议]不进行备份的数据库
    replicate-ignore-db = sys #[建议]不进行备份的数据库

    slave-skip-errors = all #跳过复制过程中的所有错误
    slave-net-timeout = 60 #一定时间后从库没有收到主库发送的binlog会主动重连
  • 配置主从通信
    在从库执行:

    1
    CHANGE MASTER TO MASTER_HOST='192.168.1.100', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=128;
  • 开启同步
    在从库执行:

    1
    start slave;

验证

  • 线程验证
    在主库执行:SHOW FULL PROCESSLIST;

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> SHOW FULL PROCESSLIST;
    +-------+-------+-----------------+------+-------------+------+---------------------------------------------------------------+-----------------------+
    | Id | User | Host | db | Command | Time | State | Info |
    +-------+-------+-----------------+------+-------------+------+---------------------------------------------------------------+-----------------------+
    | 18046 | slave | 192.168.1.100:60066 | NULL | Binlog Dump | 14 | Master has sent all binlog to slave; waiting for more updates | NULL |
    | 18047 | root | localhost | NULL | Query | 0 | starting | SHOW FULL PROCESSLIST |
    +-------+-------+-----------------+------+-------------+------+---------------------------------------------------------------+-----------------------+
    2 rows in set (0.00 sec)

    可以看到Id为18046这条记录,用户为slave,状态:Master has sent all binlog to slave; waiting for more updates,表示主库已经开启了Binlog Dump线程;
    在从库执行:SHOW FULL PROCESSLIST;

    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql> SHOW FULL PROCESSLIST;
    +----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+
    | Id | User | Host | db | Command | Time | State | Info |
    +----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+
    | 1 | system user | | NULL | Connect | 853 | Slave has read all relay log; waiting for more updates | NULL |
    | 2 | system user | | NULL | Connect | 2270 | Waiting for master to send event | NULL |
    | 7 | root | localhost | NULL | Query | 0 | starting | SHOW FULL PROCESSLIST |
    +----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+
    3 rows in set (0.00 sec)

    可以看到从库开启了2个用户为system user的线程,其中状态为Slave has read all relay log; waiting for more updates的线程为SQL thread,状态为Waiting for master to send event的线程为I/O thread;

  • 状态验证
    在从库执行:SHOW SLAVE STATUS \G

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    mysql> SHOW SLAVE STATUS \G
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 192.168.1.100
    Master_User: slave
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000003
    Read_Master_Log_Pos: 4151663
    Relay_Log_File: yurixu-relay-bin.000007
    Relay_Log_Pos: 322375
    Relay_Master_Log_File: mysql-bin.000003
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB:
    Replicate_Ignore_DB: mysql,information_schema,performation_schema,sys
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    Last_Errno: 0
    Last_Error:
    Skip_Counter: 0
    Exec_Master_Log_Pos: 4151663
    Relay_Log_Space: 322583
    Until_Condition: None
    Until_Log_File:
    Until_Log_Pos: 0
    Master_SSL_Allowed: No
    Master_SSL_CA_File:
    Master_SSL_CA_Path:
    Master_SSL_Cert:
    Master_SSL_Cipher:
    Master_SSL_Key:
    Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
    Replicate_Ignore_Server_Ids:
    Master_Server_Id: 1
    Master_UUID: e1767343-c887-11e7-9562-560001432728
    Master_Info_File: /var/lib/mysql/master.info
    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
    Master_Bind:
    Last_IO_Error_Timestamp:
    Last_SQL_Error_Timestamp:
    Master_SSL_Crl:
    Master_SSL_Crlpath:
    Retrieved_Gtid_Set:
    Executed_Gtid_Set:
    Auto_Position: 0
    Replicate_Rewrite_DB:
    Channel_Name:
    Master_TLS_Version:
    1 row in set (0.00 sec)

    Slave_IO_State: Waiting for master to send event: #当前从库的状态
    Slave_IO_Running: Yes #I/O线程是否运行
    Slave_SQL_Running: Yes #SQL线程是否运行
    Seconds_Behind_Master: 0 #0表示主从库已同步

    Slave_IO_Running和Slave_SQL_Running必须均为YES,否则可以从以下几个方面排查 [2]

1
2
3
4
5
6
7
1. Master服务器和Slave是否可以ping通;
2. 配置主从通信时,用户名、密码、MASTER_LOG_POS等项是否正确;
3. MySQL的server-uuid是否相同;(如果是复制的MySQL,server-uuid是相同的)
$ find / -name 'auto.cnf'
$ cat /var/lib/mysql/auto.cnf
[auto]
server-uuid=6b831bf3-8ae7-11e7-a178-000c29cb5cbc # 按照这个16进制格式,修改server-uuid,重启mysql即可
  • 同步验证
    在主库添加数据,登录从库,查看是否会同步过来。

主主复制

主主复制的实现过程与主从复制类似,主从复制一般只在主库执行写操作,在从库执行读操作;主主复制对应的两台服务器均可执行写操作,因此需要保证向数据库里插入数据的自增长ID不能相同,可以通过在两台服务器上添加参数,实现自增长ID互为奇数和偶数。
auto-increment-increment:自增起始值;
auto_increment_offset:自增偏移量;
因此主主复制时,两台服务器的auto_increment_offset均设置为2,auto-increment-increment分别设置为1和2。

配置流程

Master1服务器:

  • 创建用户
    参考主从复制流程

  • 修改MySQL配置
    参考主从复制流程,且两台服务器均需要开启binlog日志,同时需要在[mysqld]配置项中增加:

    1
    2
    auto-increment-increment = 1    
    auto-increment-offset = 2

    重启MySQL服务

  • 查看Master1状态
    参考主从同步流程,记录File和Position的值。

Master2服务器:

  • 创建用户
    参考主从复制流程

  • 修改MySQL配置
    参考主从复制流程,且两台服务器均需要开启binlog日志,同时需要在[mysqld]配置项中增加:

    1
    2
    auto-increment-increment = 2    
    auto-increment-offset = 2

    重启MySQL服务

  • 查看Master2状态
    参考主从同步流程,记录File和Position的值。

  • 配置通信
    先在Master2数据库上做同步master的设置:(确保Master2上要同步的数据,提前在Master1上存在,最好双方数据保持一致)

    1
    2
    3
    CHANGE MASTER TO MASTER_HOST...
    START SLAVE;

    show slave status \G

    这样就实现了Master2->Master1的同步;
    再在Master1上做同步master的设置:

    1
    2
    3
    CHANGE MASTER TO MASTER_HOST...
    START SLAVE;

    show slave status \G

验证

参考主从复制流程


多源复制

多源复制是将多个Master的数据同步至1台Slave中,配置流程与主从复制相似。
假定目前需要将Master1和Master2的数据同步至Slave中。
Master1:192.168.1.100
Master2:192.168.1.101
Slave:192.168.1.102

配置流程

Master1服务器:

  • 创建用户
    参考主从复制流程
  • 修改MySQL配置
    参考主从复制流程
  • 查看Master1状态
    参考主从同步流程,记录File和Position的值。

Master2服务器:

  • 创建用户
    参考主从复制流程
  • 修改MySQL配置
    参考主从复制流程
  • 查看Master1状态
    参考主从同步流程,记录File和Position的值。

数据导出:
分别将Master1和Master2中的数据库导出,并上传至Slave服务器;

数据导入:
在Slave上导入Master数据之前,需要先修改master_info_repository和relay_log_info_repository:

1
2
master_info_repository=TABLE
relay_log_info_repository=TABLE

也可以在MySQL中通过命令行修改:

1
2
SET GLOBAL master_info_repository = 'TABLE';
SET GLOBAL relay_log_info_repository = 'TABLE';

具体原因可参官方说明:16.2.4 Replication Relay and Status Logs

配置通信
此处与主从复制流程中的配置通信稍有不同,在Slave中分别CHANGE MASTER到两台Master服务器,结尾以FOR CHANNEL ‘CHANNEL_NAME’区分:

1
2
3
CHANGE MASTER TO MASTER_HOST='192.168.1.100', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=128 FOR CHANNEL 'Master_1';

CHANGE MASTER TO MASTER_HOST='192.168.1.101', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=5638 FOR CHANNEL 'Master_2';

开启同步
可以通过start slave 的方式启动所有复制,也可以单独指定启动某个数据源:

1
2
start slave for CHANNEL  'Master_1';
start slave for CHANNEL 'Master_2';

关闭同步也是如此。

验证

参考主从复制流程,其中在从库进行状态验证时,结尾以FOR CHANNEL ‘CHANNEL_NAME’区分:

1
2
SHOW SLAVE STATUS FOR CHANNEL 'Master_1'\G
SHOW SLAVE STATUS FOR CHANNEL 'Master_2'\G

也可以查看数据库performance_schema的相关表查看同步状态:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT * FROM performance_schema.replication_connection_status \G
*************************** 1. row ***************************
CHANNEL_NAME:
GROUP_NAME:
SOURCE_UUID: e1767343-c887-11e7-9562-560001432728
THREAD_ID: 26
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 240
LAST_HEARTBEAT_TIMESTAMP: 2018-05-29 18:57:16
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)


总结

常用命令:
启动从库复制线程:start slave;
停止从库复制线程:stop slave;
查看主库运行状态:show master status;
查看从库运行状态:show slave status \G
查看从库主机列表:show slave hosts;
查看binlog文件列表:show binary logs;
查看第一个binlog文件内容:show binlog events;
查看指定binlog文件的内容:show binlog events in ‘mysql-bin.000001’;
查看指定binlog文件指定位置的内容:show binlog events in ‘mysql-bin.000001’ from 1625222 limit 10 \G


参考

[1] 性能提升利器:MySQL 5.7多源主从复制的独特性
[2] 搭建 MySQL 5.7.19 主从复制,以及复制实现细节分析
[3] Mysql主从同步(1)-主从/主主环境部署梳理
[4] MySQL 5.7的多源复制
[5] MySql 5.7.18 数据库主从(Master/Slave)同步安装与配置详解

转载请注明出处:http://yurixu.com/blog/2018/05/28/MySQL主从复制简述

分享 本文总阅读量
< !-- add by yurixu 替换Google的jquery并且添加判断逻辑 -->