概念
什么是MySQL的主从复制
MySQL主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL默认采用异步复制方式,这样从节点就不用一直访问主服务器来更新自己的数据。数据的更新可以在远程连接上进行,从节点可以复制主节点中的所有数据库或者特定的数据库,或者特定的表。
为什么需要主从复制
- 在业务复杂的系统中,有这个一个情景,有一句SQL语句需要锁表,导致暂时不能使用读的服务,那么就很影响运行中的业务了。使用主从复制,让主库负责写,从库负责读,这样缓解主节点的压力,让业务正常的运行(这里涉及了读写分离,这里不做主要讲解,以后会专门写一篇博客)
- 做数据的热备
- 架构的扩展。业务量越来越大,一个节点既负责读,又负责写操作,I/O访问频率过高,单机无法满足,就会致使业务卡顿,影响用户体验。此时做多库的存储,降低磁盘I/O访问的频率,提高单个机器的I/O性能。
主从复制
主从复制中涉及的文件
主节点:主要涉及 binlog 文件
从节点:
- relaylog 中继日志
- master.info 保存主库信息文件
- relaylog.info 从库中relaylog应用的信息
这些文件都放在从库的数据目录下。
主从复制中涉及的线程
主库:
- Binlog_Dump Thread :DUMP_T
从库:
- SLAVE_IO_THREAD :IO_T
- SLAVE_SQL_THREAD :SQL_T
主从复制的原理
- 通过在从库中执行"change master to …"语句,来告诉从库关于主库的连接信息、同步数据的起点等信息。(change master to …语句会在下面搭建主从时做详细介绍)
- 从库会将通过"change master to …"语句获取到的信息,记录到 master.info 文件中
- 从库执行 start slave 语句就会开启主从复制,从库中也会立即开启 IO_T 和 SQL_T 两个线程
- 从库中的 IO_T 线程会读取 master.info 文件中的信息,获取到关于主库IP、PORT、User、Pass、binlog等相关信息
- IO_T线程获取到主库的相关信息后,就会按照这些信息去请求连接主库。主库中会专门提供一个 DUMP_T 线程,负责和 IO_T 线程进行交互。IO_T 线程和 DUMP_T 线程通过TCP/IP三次握手建立连接
- 建立连接后,IO_T 线程就会根据 mater.info 文件中读取到的 binlog 和其中的 postion 信息(例如:msyql-bin.000004, 444),通过 DUMP_T 线程请求主库中的 binlog
- DUMP_T 线程会将从 IO_T 线程得到的位置信息到主库最新的 binlog数据信息之间的数据,通过网络传输给从库的 IO_T 线程
- IO_T 线程接收到新的 binlog 数据后,将这些数据存储到TCP/IP缓存中,然后立即返回ACK信息给主库(有的机制是将TCP/IP中的缓存刷新到磁盘后,再返回ACK信息),并更新 master.info 文件中的信息,作为下一次请求新的 binlog 的起始位置
- IO_T 线程将TCP/IP缓存中的数据转储到 reloylog 文件中
- SQL_T 线程读取 relaylog.info 中的信息,获取到上次已经回放到的位置信息
- SQL_T 线程会按照上次的位置点开始回放 relaylog 中最新的数据,回放结束后会更新 relaylog.info 文件中的信息
- 从库会自动对 relaylog 文件中已经回放过的数据进行定期清理
TIP:一旦主从复制构建车成功,主库当中发生了新的变化,就会给 DUMP_T 线程发送信号,然后通过 DUMP_T 线程发送信号给从库的 IO_T 线程,告知 IO_T 线程主库有了新的数据产生,从而增强了主从复制的实时性。(MySQL 5.5版本以前都是IO_T线程定时访问dump_T线程,来获取新的数据信息)
主从搭建
主从复制是基于 binlog 来实现的。主库中发生了新的操作,都会被记录在 binlog 中,然后从库取得主库的 binlog 进行回放,即将主库中的操作在从库中再执行一遍,从而完成数据的复制。主从复制的过程是异步的,即主库的数据得先刷新到主库得磁盘上,然后再把数据传递到从库中。
实验准备
- 2个或以上的数据库实例
- 主库需要开启二进制日志
- server_id要不同,从而来区分不同的节点
- 主库需要建立专用的复制用户(replication slave)
搭建过程
1、准备多实例
具体的实例部署,可参考此篇博客:传送门
我自己使用的是mysqld3307、mysqld3308两个实例来搭建主从复制。其中mysqld3307作为主库,mysqld3308作为从库。
mysqld3307实例的配置文件:
[root@db01 ~]# cat /data/3307/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
**server_id=7**
log_bin=/data/3307/mysql-bin //开启二进制日志
mysqld3308实例的配置文件:
[root@db01 ~]# cat /data/3308/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
**server_id=8**
log_bin=/data/3308/mysql-bin
2、创建主库专用的复制用户
[root@db01 ~]# mysql -uroot -pxxx -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'x.x.x.%' identified by 'xxx'"
#-e参数:非交互式执行SQL语句
#专用的复制用户为repl
3、备份主库中已经存在的数据
[root@db01 ~]# mysqldump -uroot -pxxx -S /data/3307/mysql.sock -A --master-data=2 --single-transaction -R -E --triggers >/tmp/full.sql
tip:备份数据的时候若是出错,可以到MySQL的数据目录下查看所有表的组成文件是否齐全。因为若是对某张表使用"discard tablespace"时,那么此张表的".ibd"文件就会被删除,也就会导致无法备份成功
4、将备份的数据恢复到从库中
[root@db01 ~]# mysql -S /data/3308/mysql.sock
mysql> set sql_log_bin=0;
mysql> source /tmp/full.sql
mysql> set sql_log_bin=1;
5、告诉从库一些关于主库的信息
通过"change master to"语句将主库的一些信息,人为的告诉从库。可通过"help change master to"查看此语句的格式。
[root@db01 ~]# mysql -S /data/3308/mysql.sock
CHANGE MASTER TO
MASTER_HOST='x.x.x.x', //主库的IP地址
MASTER_USER='repl', //主从复制的专用用户
MASTER_PASSWORD='xxx', //专用用户登录主库的密码
MASTER_PORT=3307, //主库的端口
MASTER_LOG_FILE='mysql-bin.000004', //主库正在使用的binlog
MASTER_LOG_POS=444, //主库正在使用binlog中的position已经到了的位置,也就是从库请求主库新的二进制日志的起点
MASTER_CONNECT_RETRY=10; //最多重新连接10次,超过这个次数还连接不上,就会默认主从复制出错了
tip:change master to 语句中的"MASTER_LOG_FILE"和"MASTER_LOG_POS"信息,可通过查看备份文件"/tmp/full.sql"获知。
6、从库开启主从复制
[root@db01 ~]# mysql -S /data/3308/mysql.sock
mysql> start slave;
7、检查主从复制状态
root@db01 ~]# mysql -S /data/3308/mysql.sock
mysql> show slave status\G;
....
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
....
以上两个参数都为YES,则表明主从复制搭建成功
主从复制监控
主从复制中的状态都可以通过参数来监控,从而获知主从复制中哪里出现了问题。通过"show slave status\G;"命令可以查看这些参数:
mysql> show slave status\G;
*****************************************************************************************************************
Master_Host: x.x.x.x
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
#主库有关的信息(master.info文件中的信息):
*****************************************************************************************************************
Master_Log_File: mysql-bin.000004 #记录了IO thread已经读到的master binlog文件
Read_Master_Log_Pos: 609 #已经读取到的binlog文件中的位置
#可将这两个信息和主库中"show master status"显示的信息作对比,若是都一样,说明实时同步的效果比较好,#
*****************************************************************************************************************
Relay_Log_File: db01-relay-bin.000002 #记录了SQL thread执行到relaylog的哪个文件和相应位置,即以表示relaylog文件已经回放到什么位置
Relay_Log_Pos: 320
#从库relay应用信息有关的(relay.info文件中的信息)
*****************************************************************************************************************
Relay_Master_Log_File: mysql-bin.000004 #记录的是SQL thread执行到的数据,所对应的master上binlog的文件和位置
Exec_Master_Log_Pos
*****************************************************************************************************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
#从库线程运行状态(排错)
*****************************************************************************************************************
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
#过滤复制有关的信息: 可通过设置这几个参数的信息选择性的同步主库中指定的数据库,若是不设置,就默认同步主库中所有的数据库。
*****************************************************************************************************************
Seconds_Behind_Master: 0
#从库同步主库数据延时的时间(单位:秒)。此参数多用于主从延时监控
*****************************************************************************************************************
SQL_Delay: 0
SQL_Remaining_Delay: NULL
#延时从库:即指定主库数据产生变化后,多长事件从库才开始同步
*****************************************************************************************************************
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
#GTID复制有关的状态信息
*****************************************************************************************************************
主从复制故障
一、IO_T 故障
因为 IO_T 涉及和主库的 DUMP_T 通信、获取 binlog 、将 binlog 存储到 relaylog 中,所下面从这三个方面说一下可能出现的错误即解决方法。
(1)连接主库失败:connectiing
情况有:
- 网络问题
- 连接信息错误或连接信息变更了
- 防火墙阻止了
- 连接数到达上线
处理方式:
步骤1:在命令行手工登录主库
[root@db01 data]# mysql -urepl -pxxxx -h x.x.x.x -P 3307
#若是可以登录成功,那可能就是自己在配置"change master to"时出现了错误
步骤2:在从库中重新配置"change master to"信息
mysql> stop slave ; //停掉主从复制
mysql> reset slave all; //把master.info中的信息清空,重新配置
msyql> CHANGE MASTER TO
MASTER_HOST='x.x.x.x',
MASTER_USER='repl',
MASTER_PASSWORD='xxx',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
mysql> start slave; //开启主从复制
(2)请求 binlog 失败
请求 binlog 失败有三种情况:
- 主库的 binlog 没有打开(一般可能性不大)
- 主库中的 binlog 被损坏,不存在了
- 主库中执行了 “reset master” 指令,删除了所有 binlog
处理方式:
主库:
mysql> reset master;
从库:
mysql> stop slave ; //停掉主从复制
mysql> reset slave all; //把master.info中的信息清空,重新配置
msyql> CHANGE MASTER TO
MASTER_HOST='x.x.x.x',
MASTER_USER='repl',
MASTER_PASSWORD='xxx',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
mysql> start slave; //开启主从复制
(3)存储 binlog 到 relaylog 失败
此情况下很有可能是relaylog文件的权限被修改了。
二、SQL_T故障
SQL线程主要用于回放 relaylog 中的SQL语句,所以解决SQL线程故障等同于研究一条SQL语句为什么会执行失败。
有以下三种情况:
- 回放insert、delete、update语句时,此张表可能已经被从库管理员删掉了,这样就会致使insert、delete、update失败
- 回放"create table …"语句时,有可能名称相同的表已经被从库管理员创建过了,从而导致回放失败
- 主库和从库中关于表的约束有冲突(主键、唯一键、非空…等冲突),这也是因为有可能从库管理员对某些表做了操作
处理方式
暴力解决办法
方式一:在从库中操作
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;
#sql_slave_skip_counter = N:用于SQL语句执行错误时,跳过此错误语句,继续执行。N:表示可跳过几个错误
#sql_slave_skip_counter是以event为单位skip的,直到skip完第N个event才停止。sql_slave_skip_counter参数只针对于传统position号的复制,对使用gtid号主从复制不起作用。
*****************************************************************************************************************
方式二:在从库配置文件配置遇到哪些错误可以直接跳过
root@db01 ~]# vim /etc/my.cnf
[mysqld]
slave-skip-errors = 1032,1062,1007
#常见错误代码
#1007:对象已经存在
#1032:无法执行DML
#1062:主键冲突、或约束冲突 (从库的表中已经存在一列数据,再在主库中相同的表中插入相同主键的数据,就会造成主键冲突。
#解决方法:手动让主从库数据一致,然后跳过这个错误,重新启动主从服务。 当然对于较为复杂的表可以使用pt工具对主从的表进行数据对比,查找哪里出现不一致)
但是以上操作都是有风险的,最安全的做法就是重新构建主从。把我一个原则,一切以主库为准
从 SQL_T 出现故障的情况,我们可以很容的发现,都是因为在从库中进行了"写"操作,从而操作数据的冲突。所以为了最大程度的避免 SQL_T 的故障,
(1)我们可以将从库设置为"只读"。关于"只读"的设置可参考此篇博客:传送门(2)使用读写分离中间件,让从库只负责提供"读操作"
读写分离的中间件有:
atlas、mycat、ProxySQL、MaxScale
主从延时原因
主库方面:
(1) binlog 刷新到磁盘不及时,系统检查不到新的二进制日志就会给 DUMP_T 发送信号(可通过设置"sync_binlog=1"来让二进制日志快速刷新到磁盘中)
(2) 默认情况下 DUMP_T 是串行传输 binlog 的
- 情况1:在并发事务量大时或着大事务时,由于 DUMP_T 是串行工作的,就会导致 binlog 传送到从库很慢
解决方法:
开启GTID、开启双1参数,然后才可以使用Group commit,从而支持 DUMP_T 并行传输 binlog
"双一参数":sync_binlog=1、innodb_flush_log_at_trx_commit=1
TIP:为何必须开启GTID和开启双一参数才能使用group commit呢?
详细的原理请参考这篇博文:传送门
(3)主库极其繁忙,致使 binlog 延时传送到从库
有以下几种情况:
- 慢语句 (查询、不走索引等语句)
- 锁等待
- 从库个数
- 网络延时
从库方面
(1)传统复制中,如果主库并发事务量很大,或者出现大事务,由于从库是单SQL线程,导致不管主库已经传输了多少日志,只能一次执行一个事务。
- 5.6 版本,有了GTID,可以实现多SQL线程,但是只能基于不同库的事务进行并发回放.(database模式) 相同库的并发事务也只能串行执行。
- 5.7 版本中,有了增强的GTID,增加了seq_no(序列标记),增加了新型的并发SQL线程模式(logical_clock模式),–>MTS技术;所以5.7版本SQL多线程可以基于事务进行回放,即可以并行执行相同库、不同库的事务。可以通过指定"slave_parallel_workers"参数的值来指定SQL工作线程数量
(2)主从硬件差异太大
(3)主从参数配置不一样,例如:分配的内存…
(4)从库和主库的索引不一致。从库多用于查询,可能会建立索引…
(5)主从版本有差异