本文涉及MySQL主从复制原理及1个简单是主从复制验证。
主从复制中的三个线程:
大致流程:主库在事务提交时会把变更作为事件记录(Events)到二进制文件(binlog)当中,从库的IO线程从主库获取二进制日志(binlog),并在本地保存为中继日志(relay-log),然后通过SQL线程来在从库上执行中继日志中的内容,使从库和主库保持一致。
详细流程如下:
CentOS-6.7-x86_64
MySQL版本是5.6.36: https://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.36.tar.gz
- [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
- #在主库上为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;
- #锁表,禁止新数据写入;
- #(视情况执行),设置只读是为了防止在获取binlog文件名与偏移量时,或从主库备份数据库到从库时,主库发生变化;初次同步完成后勿忘解锁
- [root@master ~]#mysql -uroot -p
- Enter password:
- mysql>flush tables with read lock;
- #获取到binlog文件名与偏移量,可为从库设定同步复制点
- [root@master ~]# mysql -uroot -p
- Enter password:
- mysql>show master status;
- #解除只读锁定
- [root@master ~]# mysql -uroot -p
- Enter password:
- mysql>unlock tables;
- [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
- #将主库服务器上的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 &
- #配置从库向主库提交的参数,如果参数有错误,可以重新配置;
- #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;
- #主从复制框架基本完成,但从库还可以进行数据的写入操作;
- #如果有用户向从库中写数据,然后从库在从主库同步数据库时,会造成数据错乱,从而造成数据损坏,所以需要把从库设置成只读
- [root@slave ~]# mysql -uroot -p
- Enter password:
- mysql> set global read_only=1;
- mysql> show global variables like 'read%';
- [root@master ~]# mysql -uroot -p
- Enter password:
- mysql> show processlist\G;
- [root@slave ~]# mysql -uroot -p
- Enter password:
- mysql> show processlist\G;
- [root@slave ~]# mysql -uroot -p
- Enter password:
- mysql> show slave status\G;
- #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个文件:master.info,relay-log.info,relay-bin
master.info:记录master的ip,账号,密码,以及从库的I/O线程当前读取到主库的binglog的位置。
relay-log.info:记录从库的SQL线程当前读取到中继日志(relay-bin)的位置。
relay-bin:中继日志,记录的格式与主库的二进制日志一样,但中继日志在SQL线程执行完当前中继日志中的事件之后会删除中继日志中的内容。
- [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);
- [root@slave~]#mysql - uroot - p
- Enter password:
- mysql > show databases;
- mysql > select * from dbtest.tabtest;
- mysql > show slave status\G;
从MySQL5.5开始,MySQL以插件的形式支持半同步复制。
可参考:
https://dev.mysql.com/doc/refman/5.6/en/replication-semisync-interface.html
MySQL默认的复制即是异步的,主库在执行完客户端提交的事务后会立即将结果返给给客户端,并不关心从库是否已经接收并处理。这时主如果crash掉了,主上已经提交的事务可能并没有传到从上,如果此时,强行将从提升为主,可能导致新主上的数据不完整。
当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回,所以全同步复制的性能会受到到严重的影响。
介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟,这个延迟最少是一个TCP/IP往返的时间。所以,推荐半同步复制在小事务,低延时的网络中使用,可以实现在性能很小损失的情况下的零数据丢失。
来源: http://www.linuxidc.com/Linux/2017-10/147550.htm