MySQL主从复制

原文发表于cu：2017-06-09

参考文档：

主从复制原理：https://segmentfault.com/a/1190000008942618

本文涉及MySQL主从复制原理及1个简单是主从复制验证。

一．MySQL主从复制原理

1. 主从复制架构图

主从复制中的三个线程:

Binlog Dump线程：此线程运行在主库，当主从都配置好后，从库运行START SLAVE启动复制后，会在主库上生成一个BinlogDump线程，该线程的主要作用就是读取主库Binlog事件，然后发送到从库（从库的I/O线程）。
I/O线程：此线程运行在从库，作用是向主数据库要数据，并且将主库发送过来的变更事件写入到从库的中继日志中。
SQL线程：此线程运行在从库，主要作用是读取中继日志中的变更事件并更新从库。

2. 主从复制流程

大致流程：主库在事务提交时会把变更作为事件记录（Events）到二进制文件（binlog）当中，从库的IO线程从主库获取二进制日志(binlog)，并在本地保存为中继日志(relay-log)，然后通过SQL线程来在从库上执行中继日志中的内容，使从库和主库保持一致。

详细流程如下：

主库验证从库发起的连接；
主库为从库开启一个线程；
从库将主库日志的偏移位告诉主库；
主库检查该值是否小于当前二进制日志偏移位。
如果小于，则通知从库可以取数据。
从库持续从主库取数据，直至取完，这时，从库线程进入睡眠，主库线程同时进入睡眠。
当主库有更新时，主库线程被激活，并将二进制日志推送给从库，并通知从库线程进入工作状态。
从库SQL线程执行二进制日志，随后进入睡眠状态。

二．验证环境

1. 操作系统

CentOS-6.7-x86_64

2. MySQL版本

MySQL版本是5.6.36: https://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.36.tar.gz

3. 拓扑图

采用VMware ESXi虚拟出的2台服务器master/slave，地址10.11.4.196/198；
MySQL已安装并配置完成，可参考http://www.cnblogs.com/netonline/p/7327409.html中的MySQL部分；

三．主库配置

1. my.cnf配置

[root@master ~]# vim /etc/my.cnf
[mysqld]
# ID值唯一的标识了群集中的主从服务器,master_id必须为1到232–1之间的一个正整数值,slave_id值必须为2到232–1之间的一个正整数值.
server_id = 196

# 启用binlog,启用后才可通过I/O写到Slave的relay-log,是进行replication的前提.
log_bin = /mysql/mysql-bin

# 设置binlog文件的最大值，当达到这个值会自动生成一个新的binlog文件.
max_binlog_size = 1G

# binlog会占据大量磁盘空间,可以设置binlog文件过期时间,以天为单位.
# expire-logs-days =

# 配置每次事务提交时是否都需要刷新binlog到磁盘,默认配置0,是由文件系统自己控制;如果设置为1，默认每次事务提交都会刷新binlog到磁盘.
# 因为binlog也有缓存，事务提交时先写缓存,如果系统突然宕机,可能会丢失缓存中的记录;但每次事务提交都写磁盘会对性能造成影响.
# 通过半同步复制可以解决因系统突然宕机而导致binlog缓存数据丢失的问题.
sync_binlog = 0

# 二进制日志记录有三种方式：row, STATEMENT and mixed
# row,基于行的复制,记录每一行的变更操作,优点:对复制的兼容性高,缺点:日志记录量大，对IO的影响也很大,不容易用来做分析;
# STATEMENT,基于语句的复制,记录操作的sql语句,是默认的格式,优点:日志量小,便于用来做分析,IO影响小,缺点:时间上可能不完全同步造成偏差，执行语句的用户也可能是不同一个用户;
# mixed, 混合模式,默认采用STATEMENT记录,当出现不确定函数时就采取row记录.
binlog-format = mixed

# 只将指定数据库的变动写入二进制文件binlog,如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项;
# 在databases中无相应数据库时,开启binlog-do-db会导致mysql无法启动.
# binglog-do-db = 
# 对指定数据库的变动不写入二进制文件binlog,如果有多个数据库可用逗号分隔,或者使用多个binlog-ignore-db选项.
binlog-ignore-db = information_schema,mysql,performance_schema,test
# 指定需要复制同步的数据库，如果有多个数据库可用逗号分隔，或者使用多个replicate-do-db选项
# replicate-do-db = 
# 指定不需要复制同步的数据库,如果有多个数据库可用逗号分隔,或者使用多个replicate-ignore-db选项.
# replicate-ignore-db = 

#配置文件在重启后生效
[root@master ~]# service mysqld restart

2. 创建复制用户

#在主库上为10.11.4.0网段的主机授权，从库用户repl获得REPLICATION SLAVE权限
[root@master ~]#mysql -uroot -p
Enter password:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.11.4.%' IDENTIFIED  BY 'repl';
mysql> flush privileges;

3. 刷新表并设置只读

#锁表，禁止新数据写入；
#（视情况执行），设置只读是为了防止在获取binlog文件名与偏移量时，或从主库备份数据库到从库时，主库发生变化；初次同步完成后勿忘解锁
[root@master ~]#mysql -uroot -p
Enter password:

mysql>flush tables with read lock;

4. 获取master binlog文件名与偏移量

#获取到binlog文件名与偏移量，可为从库设定同步复制点
[root@master ~]# mysql -uroot -p
Enter password:

mysql>show master status;

5. 解除只读锁定

#解除只读锁定
[root@master ~]# mysql -uroot -p
Enter password:

mysql>unlock tables;

6. iptables

[root@master ~]# vim /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

[root@master ~]# service iptables restart

四．从库配置

1. my.cnf配置

#将主库服务器上的my.cnf文件拷贝到从库服务器
[root@master ~]# scp /etc/my.cnf slave:/etc/
Enter password:

#修改server id值与主库服务器不同，其余配置保持不变即可；
#从库可以配置slave_skip_errors=[err_code1,err_code2,… | all]参数；
#在复制过程，由于各种原因导致binlog中的sql出错，默认情况下从库会停止复制，要用户介入；
#可以通过设置slave-skip-errors来定义错误号，如果复制过程中遇到已定义的错误号，便可以跳过；如果从库是用来做备份，设置这个参数会存在数据不一致，建议不使用；如果是从库是分担主库的查询压力，可以考虑采用。
[root@slave ~]# vim /etc/my.cnf
[mysqld]
# ID值唯一的标识了群集中的主从服务器,master_id必须为1到232–1之间的一个正整数值,slave_id值必须为2到232–1之间的一个正整数值.
server_id = 198


#使用--skip-slave-start启动，可以不立即启动从库的复制线程，方便后续配置操作
[root@slave ~]# service mysqld stop
[root@slave ~]# mysqld_safe --skip-slave-start &

2. 配置同步

#配置从库向主库提交的参数，如果参数有错误，可以重新配置；
#master-host、master-user、master-password、master-port等也可在my.cnf文件中指定；
#”start slave”启动复制。
[root@slave ~]# mysql -uroot -p
Enter password:

mysql> change master to 
       master_host = '10.11.4.196', 
       master_user = 'repl', 
       master_password = 'repl', 
       master_log_file = 'mysql-bin.000001',
       master_log_pos = 120;
mysql> start slave;

3. 设置从库只读

#主从复制框架基本完成，但从库还可以进行数据的写入操作；
#如果有用户向从库中写数据，然后从库在从主库同步数据库时，会造成数据错乱，从而造成数据损坏，所以需要把从库设置成只读
[root@slave ~]# mysql -uroot -p
Enter password:

mysql> set global read_only=1;
mysql> show global variables like 'read%';

read-only = OFF/ON，全局变量，只有管理员具有修改权限；
read-only = ON，此功能只对非管理员组用户有效；
通过命令设置的read-only在服务重启后消失，可以在my.cnf文件中设置永久生效。

五．验证

1. 查看线程

1）主库

[root@master ~]# mysql -uroot -p
Enter password:

mysql> show processlist\G;

主库的binlog dump线程已由从库的repl用户启动。

2）从库

[root@slave ~]# mysql -uroot -p
Enter password:

mysql> show processlist\G;

从库的I/0线程与SQL线程已由系统用户启动；
SQL线程常见还有1个常见状态" Reading event from the relay log"。

2. 查看从库状态

[root@slave ~]# mysql -uroot -p
Enter password:

mysql> show slave status\G;

重点关注Slave_IO_Running与Slave_SQL_Running，状态均为YES时正常。

#slave status各指标意义如下（红色粗体指标比较重要）：
Slave_IO_State: I/O线程已连接master,正等待二进制日志事件到达
Master_Host: master ip
Master_User: 连接master的用户
Master_Port: master端口
Connect_Retry: 当重新建立主从连接时,如果连接建立失败,重试间隔时间，默认60s。
Master_Log_File: I/O线程当前正在读取的master二进制日志文件
Read_Master_Log_Pos: 在当前的master二进制日志中，I/O线程已读取到的位置
Relay_Log_File: SQL线程当前正在读取和执行的中继日志文件的名称
Relay_Log_Pos: SQL线程在当前的中继日志中已读取和执行的位置
Relay_Master_Log_File: SQL线程执行的master二进制文件
Slave_IO_Running: I/O线程是否运行并成功地连接到master
Slave_SQL_Running: SQL线程是否运行
Replicate_Do_DB:需要复制的数据库
Replicate_Ignore_DB:不需要复制的数据库
Replicate_Do_Table:需要复制的表
Replicate_Ignore_Table:不需要复制的表
Replicate_Wild_Do_Table: 限制复制更新的表，匹配指定的数据库和表名模式的语句
Replicate_Wild_Ignore_Table: 不需要要复制表，匹配给出的通配符模式的语句
Last_Errno:错误代码
Last_Error:错误信息
Skip_Counter: SQL_SLAVE_SKIP_COUNTER的值
Exec_Master_Log_Pos: master上一个被执行的位置
Relay_Log_Space: 中继日志文件大小
Until_Condition: 在START SLAVE语句的UNTIL子句中指定的值
Until_Log_File: 用于指示日志文件名
Until_Log_Pos: 位置值
Master_SSL_Allowed: 如果允许对主服务器进行SSL连接,则值为Yes,否则NO
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: slave落后master多少的一个指标.此状态是一个很重要的性能指标,正常为0,如果slave的I/O线程无法连接master显示null
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 最近的IO线程错误代码，其中2003代表I/o线程无法连接主服务器
Last_IO_Error: 最近的IO线程错误信息（例如：error reconnecting to master 'repl@192.168.1.6:3306' - retry-time: 60  retries: 3）
Last_SQL_Errno: 最近的SQL线程错误代码
Last_SQL_Error: 最近的SQL线程错误信息
Replicate_Ignore_Server_Ids:
Master_Server_Id: master的服务器ID
Master_UUID: master的UUID值
Master_Info_File: slave的master.info文件路径
SQL_Delay: 正数表明slave有延迟了
SQL_Remaining_Delay: 整数表明延迟时间
Slave_SQL_Running_State: SQL线程运行状态,SQL线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:最近的I/O线程错误时间
Last_SQL_Error_Timestamp:最近的SQL线程报错时间
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0

3. 从库中的3个文件

在从库的数据库路径下会生成3个文件：master.info,relay-log.info,relay-bin

master.info:记录master的ip，账号，密码，以及从库的I/O线程当前读取到主库的binglog的位置。

relay-log.info：记录从库的SQL线程当前读取到中继日志(relay-bin)的位置。

relay-bin:中继日志,记录的格式与主库的二进制日志一样，但中继日志在SQL线程执行完当前中继日志中的事件之后会删除中继日志中的内容。

4. 查看新建数据数据库同步情况

1）主库创建数据库与表

[root@master ~]# mysql -uroot -p
Enter password:

mysql> create database dbtest;
mysql> use dbtest;
mysql> create table tabtest(id int);
mysql> insert into tabtest() values(1),(2);

2）从库查看数据库与表

[root@slave ~]# mysql -uroot -p

Enter password:

(1)查看数据库

mysql> show databases;

(2)查询表

mysql> select * from dbtest.tabtest;

(3)slave状态

mysql> show slave status\G;

同步复制的binlog偏移量已变更，relaylog偏移量也变更。

六．补充(不做验证):半同步复制

从MySQL5.5开始，MySQL以插件的形式支持半同步复制。

可参考：

http://www.cnblogs.com/chenmh/p/5744227.html

https://dev.mysql.com/doc/refman/5.6/en/replication-semisync-interface.html

1. 概念

1）异步复制（Asynchronous replication）

MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理。这时主如果crash掉了，主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。

2）全同步复制（Fully synchronous replication）

当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能会受到到严重的影响。

3）半同步复制（Semisynchronous replication）

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，推荐半同步复制在小事务，低延时的网络中使用，可以实现在性能很小损失的情况下的零数据丢失。

2. 注意事项

半同步复制需要安装相应插件支持，主从的插件不同；
半同步复制一定程度上保证提交的事务已经传给了至少一个备库（可通过环境变量设置）；
半同步复制仅保证事务已经传递到备库上，并不确保已经在备库上执行完成了，即从库的执行同步过来的binlog是异步的；
半同步复制并不是严格意义上的半同步复制。如果半同步复制发生超时（由rpl_semi_sync_master_timeout参数控制，单位是毫秒，默认为10000，即10s），会暂时关闭半同步复制，转而使用异步复制；当master dump线程发送完一个事务的所有事件之后，如果在rpl_semi_sync_master_timeout内，收到了从库的响应，则主从又重新恢复为半同步复制；
5.6版本是在主库flush disk之后发送binlog；5.7版本加入新参数"rpl_semi_sync_master_wait_point=AFTER_SYNC"，控制主库响应从库的binlog请求之后再flush disk，如果将参数修改为参数"rpl_semi_sync_master_wait_point=AFTER_COMMIT"，则效果同5.6版本。

posted @ 2017-09-27 20:04 Netonline 阅读(739) 评论(0) 编辑收藏举报

刷新页面返回顶部

MySQL主从复制

一．MySQL主从复制原理

1. 主从复制架构图

2. 主从复制流程

二．验证环境

1. 操作系统

2. MySQL版本

3. 拓扑图

三．主库配置

1. my.cnf配置

2. 创建复制用户

3. 刷新表并设置只读

4. 获取master binlog文件名与偏移量

5. 解除只读锁定

6. iptables

四．从库配置

1. my.cnf配置

2. 配置同步

3. 设置从库只读

五．验证

1. 查看线程

1）主库

2）从库

2. 查看从库状态

3. 从库中的3个文件

4. 查看新建数据数据库同步情况

1）主库创建数据库与表

2）从库查看数据库与表

(1)查看数据库

(2)查询表

(3)slave状态

六．补充(不做验证):半同步复制

1. 概念

1）异步复制（Asynchronous replication）

2）全同步复制（Fully synchronous replication）

3）半同步复制（Semisynchronous replication）

2. 注意事项

公告