MySQL主从复制

原文刊载于cu:2017-06-09

参考文档:

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

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

一.MySQL主从复制原理

1. 主从复制架构图

图片 1

主从复制中的多个线程:

  1. Binlog
    Dump线程:此线程运行在主库,当主从都配备好后,从库运行START
    SLAVE启动复制后,会在主库上生成一个BinlogDump线程,该线程的重中之重成效就是读取主库Binlog事件,然后发送到从库(从库的I/O线程)。
  2. I/O线程:此线程运行在从库,效用是向主数据库要多少,并且将主库发送过来的变动事件写入到从库的连接日志中。
  3. SQL线程:此线程运行在从库,紧要作用是读取中继日志中的变更事件并更新从库。

  4. 主从复制流程


大体流程:主库在业务提交时会把改变作为事件记录(伊芙nts)到二进制文件(binlog)当中,从库的IO线程从主库获取二进制日志(binlog),并在本土保存为过渡日志(relay-log),然后通过SQL线程来在从库上执行中继日志中的内容,使从库和主库保持一致。

详细流程如下:

  1. 主库验证从库发起的连接;
  2. 主库为从库开启一个线程;
  3. 从库将主库日志的偏移位告诉主库;
  4. 主库检查该值是不是低于当前二进制日志偏移位。
  5. 倘使低于,则通告从库可以取多少。
  6. 从库持续从主库取多少,直至取完,那时,从库线程进入睡眠,主库线程同时跻身睡眠。
  7. 当主库有更新时,主库线程被激活,并将二进制日志推送给从库,并通报从库线程进入工作状态。
  8. 从库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. 拓扑图

图片 2

  1. 应用VMware
    ESXi虚拟出的2台服务器master/slave,地址10.11.4.196/198;

  2. 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;

图片 3

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%';

图片 4

  1. read-only
    = OFF/ON,全局变量,唯有管理员具有修改权限;
  2. read-only
    = ON,此功能只对非管理员组用户有效;
  3. 经过命令设置的read-only在劳动重启后消退,可以在my.cnf文件中装置永久生效。

五.验证

1. 查看线程

1)主库

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

mysql> show processlist\G;

图片 5

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

2)从库

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

mysql> show processlist\G;

图片 6

  1. 从库的I/0线程与SQL线程已由系统用户启动;
  2. SQL线程常见还有1个周边意况”
    Reading event from the relay log”。

  3. 查看从库状态


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

mysql> show slave status\G;

图片 7

  1. 主要关切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

  2. 从库中的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);

图片 8

2)从库查看数据库与表

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

Enter password:

(1)查看数据库

mysql> show databases;

图片 9

(2)查询表

mysql> select * from dbtest.tabtest;

图片 10

(3)slave状态

mysql> show slave status\G;

图片 11

  1. 联合复制的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. 注意事项

  1. 半一块复制须求安装相应插件帮忙,主从的插件不一样;
  2. 半同台复制一定水平上保障交到的事情已经传给了足足一个备库(可通过环境变量设置);
  3. 半一并复制仅保障工作已经传递到备库上,并不保障已经在备库上进行到位了,即从库的进行同步过来的binlog是异步的;
  4. 半联名复制并不是从严意义上的半同台复制。若是半联机复制发生超时(由rpl_semi_sync_master_timeout参数控制,单位是阿秒,暗中同意为10000,即10s),会临时关张半一头复制,转而利用异步复制;当master
    dump线程发送完一个事务的有着事件之后,纵然在rpl_semi_sync_master_timeout内,收到了从库的响应,则基本又重新恢复生机为半一块复制;
  5. 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版本。
网站地图xml地图