主从复制(Replication)
一、主从介绍
1.介绍
两台或以上数据库实例,通过二进制日志,实现数据的“同步”关系
1. 基于二进制日志复制的
2.主库的修改操作会记录二进制日志
3.从库会请求新的二进制日志并回放,最终达到主从数据同步
4. 主从复制核心功能:
辅助备份,处理物理损坏
扩展新型的架构:高可用,高性能,分布式架构等
2.主从复制前提(搭建过程)
时间同步
至少2台以上实例,要有不同角色换分,server_id
主库开binlog
网络畅通
开启专用复制线程
“补课”
确认复制起点
#总结
1.需要2台以上数据库实例,时间同步,网络畅通,Server_id不同,区分不同角色(主库,从库)
2.主库开启binlog,建立专用版复制用户
3.从库需要提前“补课”
4.从库:主库的链接信息,确认复制起点
5.从库:开启专用的复制线程
二、主从搭建(一台机器)
1.搭建
1.1实例准备
[root@db02 ~]# mysqld --defaults-file=/data/mysql/conf/my.3307.cnf --user=root &
[root@db02 ~]# mysqld --defaults-file=/data/mysql/conf/my.3308.cnf --user=root &
[root@db02 ~]# mysqld --defaults-file=/data/mysql/conf/my.3309.cnf --user=root &
1.2检查关键信息
[root@db02 ~]# mysql -uroot -p123 -S /data/mysql/3307/data/mysql.sock -e "show variables like 'server_id';"
[root@db02 ~]# mysql -uroot -p123 -S /data/mysql/3308/data/mysql.sock -e "show variables like 'server_id';"
[root@db02 ~]# mysql -uroot -p123 -S /data/mysql/3309/data/mysql.sock -e "show variables like 'server_id';"
1.3主库binlog
[root@db02 ~]# mysql -uroot -p -S /data/mysql/3307/data/mysql.sock -e "select @@log_bin";
Enter password:
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
vim /data/mysql/conf/my.3307.conf
sync_binlog=1
1.4主库建立复制用户
[root@db02 ~]# mysql -uroot -p123 -S /data/mysql/3307/data/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123'";
[root@db02 ~]# mysql -uroot -p123 -S /data/mysql/3307/data/mysql.sock -e "select user,host from mysql.user"
+---------------+-----------+
| user | host |
+---------------+-----------+
| repl | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
1.5主库备份恢复到从库
--主库备份
[root@db02 ~]# mysqldump -uroot -p -S /data/mysql/3307/data/mysql.sock -A --master-data=2 --single-transaction >/tmp/all.sql
--恢复到从库:
[root@db02 ~]# mysql -uroot -p123 -S /data/mysql/3308/data/mysql.sock </tmp/all.sql
[root@db02 ~]# mysql -uroot -p123 -S /data/mysql/3309/data/mysql.sock </tmp/all.sql
1.6告知从库复制信息
--查看主库binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000010 | 154 | | | |
mysql> help change master to
mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
--和上面参数对应
[root@db02 ~]# grep "\-- CHANGE MASTER TO" /tmp/all.sql;
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=444;
1.7 主库的链接信息,确认复制起点
[root@db02 ~]# mysql -uroot -p123 -S /data/mysql/3308/data/mysql.sock
CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
[root@db02 ~]# mysql -uroot -p -S /data/mysql/3309/data/mysql.sock
CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
1.8 从库:开启专用的复制线程(3308,3309)
mysql> start slave;
1.9 验证主从状态
--命令行查看线程状态(两个yes说明成功):
[root@db02 ~]# mysql -u root -p123 -S /data/mysql/3308/data/mysql.sock -e "show slave status\G" | grep Running
[root@db02 ~]# mysql -u root -p123 -S /data/mysql/3309/data/mysql.sock -e "show slave status\G" | grep Running
1.10 如果搭建不成功,可以执行以下命令
从第一步开始重新操作(没有问题就不要执行)
清空主从环境
mysql -uroot -p123 -S /data/mysql/3308/data/mysql.sock -e "stop slave;reset slave all;"
mysql -uroot -p123 -S /data/mysql/3309/data/mysql.sock -e "stop slave;reset slave all;"
2.主从复制的原理
2.1 主从中涉及到的资源
1)文件
主库:
binlog文件
从库:
--relay-log文件:
作用:存储接受的binlog,默认是在从库的数据目录下的
cd /data/3308/data
db02-relay-bin.000001
db02-relay-bin.000002
---手工定义的方法
mysql> show variables like '%relay%';
relay_log_basename | /service/mysql/data/db02-relay-bin
--master-info文件:
作用:连接主库的信息,已经接收binlog位置点信息,默认存储在从库的数据路径下
---手工定义的方法
master_info_repository=FILE/TABLE
relay.info :记录从库会放到relay-log的位置点
2)线程
主库:
Binlog_dump_Thread:
--作用:用来接收从库请求,并且投递binlog
mysql> show processlist;
从库:
IO线程:请求日志,接收binlog日志
SQL线程:回放日志
2.2 主从复制原理画图说明
2.3 主从复制文字说明
1. S:Change master to IP,Port,USER,PASSWORD,binlog位置信息写入到M.info中
2. S:连接主库
3. M:分配Dump_T线程,专门和S_IO通信
show processlist;
4. S:IO线程:IO线程请求新日志
5. M:Dump_T 接收请求,截取日志,返回给S_IO
6. S:IO线程接收到binlog,日志存放在TCP/IP,此时,网络层面会返回一个ACK给主库Dump,主库工作完成
7. S:IO将binlog最终写入到replay中,并更新M.info
8. S:SQL线程读R.info,获取上次执行到的位置点
9. S:SQL线程向后执行新的relay-log,再次更新R.info
--小细节
1. S:realy-log 参数:realy_log_purge=ON,定期删除应用过的relay_log
2. M:Dump线程实时监控中主库的binlog变化,如果有新变化,发信号给从库
三、主从监控
1.主库方面
mysql> show processlist;
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 9 | | 3309 | 7 | e72eed9b-9926-11eb-8dcc-000c296d3dea |
| 8 | | 3308 | 7 | e278859b-9926-11eb-8c10-000c296d3dea |
+-----------+------+------+-----------+--------------------------------------+
2.从库方面
mysql> show slave status \G
1. 主库相关信息:来自于M.info
Master_Host:10.0.0.52
Master_User:repl
Master_Port:3307
Connect_Retry:10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1025
2. 从库的relay-log的执行情况,来自于R.info,一般用来判断主从延时
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 901
Relay_Master_Log_File: mysql-bin.000001
Seconds_Behind_Master:0(延时主库多少秒)
3. 从库的线程状态及具体报错信息
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
Last_IO_Errno:0
Last_IO_Error:
Last_SQL_Errno:0
Last_SQL_Error:
4. 过滤复制相关信息
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
5. 延时从库(人为配置)的配置信息:
SQl_Delay:0
SQL_Remaining_Delay:NULL
6. GTID相关复制信息
Retrieved_Gtid_set:
Executed_Gtid_Set:
四、主从故障分析及处理
1.监控方法
mysql> show slave status \G
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
Last_IO_Errno:0
Last_IO_Error:
Last_SQL_Errno:0
Last_SQL_Error:
2.IO线程
正常状态:
Slave_IO_Running:Yes
非正常状态:
Slave_IO_Running:NO
Slave_IO_Running:Connecting
1)IO线程故障原因
连接主库:
(1)网络,防火墙
(2)用户,密码,授权:replication slave
(3)主库连接数上限:(最大连接数是151)
mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
| 151 |
+-------------------+
(4)版本不统一 5.7 native 8.0 sha2
2)故障模拟
主从的线程管理:
mysql> start slave; #启动所有线程
mysql> stop slave; #关闭所有线程
mysql> start slave sql_thread; #单独启动sql线程
mysql> start slave io_thread; #单独启动IO线程
解除从库身份:
mysql> reset slave all;
mysql> show slave status \G
模拟故障
CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
mysql> start slave;
mysql> show slave status \G
线程信息会显示NO
3)通用的故障处理思路
1.手动通过复制用户连接主库
CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
mysql -urepl -p123 -h 10.0.0.52 -P 3307
报错:
1045 ---> 用户或者密码错误
2003(113) ---> ip地址错误
2003(111) ---> 端口错误
1040 ---> 连接数上限
2. 故障修复
mysql> stop slave;
mysql> slave all;
mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
4)请求日志,接收日志
1. 故障原因
主库二进制日志不完整:损坏,不连续...
从库请求的起点问题...
主从的server id (server_uuid)相同
relaylog 问题
2. 模拟故障
在主库中运行
mysql> reset master;
如果业务繁忙期间做,有可能会导致数据库夯住
如果要恢复主从,需要重新搭建主从
===============================
生产中如果要 reset master;
1. 找业务不繁忙的时间,停业务5分钟
2. 等待从库重放所有主库日志
3. 主库 reset master;
4. 从库重新同步主库日志
mysql> stop slave;
mysql> slave all;
mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
mysql> start slave;
===============================
第三方工具:
pt工具
3.SQL线程故障
1)主要做什么工作?
回放relay-log中的日志,可以理解为执行relay-log SQL
2)SQL线程故障本质
为什么SQL线程执行不了SQL语句
原因整理:
创建的对象已经存在了
需要操作的对象不存在
约束冲突
以上问题:大几率出现在从库写入或者双主结构中容易出现
SQL_MODE,参数,版本
3)故障模拟
1. 先在从库建库
create database hhh;
2. 再去主库再建一个库
create database hhh;
3. 检查从库SQL线程状态
Slqve_SQL_Running:NO
会报错:
4)处理故障
(1)思路1:一切以主库为准,将从库进行反操作一下,重启线程
mysql> drop database hhh;
mysql> start slave;
(2)思路2:以从库为准,跳过此次复制错误
方法一:
mysql> stop slave;
mysql> set global sql_slave_skip_counter = 1;
注意:如果要用此种方法,一定要保证此时故障数据是完全一致时,才能采用
第三方工具:
pt工具:帮助我们检查主从数据一致,并可以修复主从不一致情况
#将同步指针向下移动一个,如果多次不同步,可以重复操作。
mysql> start slave;
方法二:暴力方法,遇到自动跳过
vim /etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突
(3)思路3:重新搭建主从:备份恢复+重新构建
扩展
1. 从库只读
mysql> select @@read_only; #普通用户只读
mysql> select @@super_read_only; #普通管理员只读
2. 中间件
4.什么是主从延时?
主库发生了操作,从库很久才跟上来
5.关于主从延时怎么监控?
--粗略评估
mysql> show slave status;
Seconds_Behind_Master:0 #从库落后于主库的时间
--准确评估
日志量:主从binlog位置点:从relay执行的位置点
---主库查看
mysql> show master status;
+------------------+----------+--------------+-------------
| File | Position | Binlog_Do_DB | Binlog_Ignor
+------------------+----------+--------------+-------------
| mysql-bin.000001 | 1329 | |
+------------------+----------+--------------+------------
--从库查看
ll /data/3308/data/relay.log.info
7 #来自于哪个主库
./db02-relay-bin.000002
1205
mysql-bin.000001
1329
0
0
1
可以得出:有或者没有延时的情况,等于0不代表没有延时
评估主从延时更加精确的指标是,延时了多少日志量
主库执行的日志量,从库执行的日志的对比
6.如何计算延时的日志量
show master status;
cat /data/3308/data/relay.log.info
7. 主从复制延时的原因
1)主库
1. 外部:网络,硬件配置,主库业务繁忙,从库太多
--主库业务繁忙
1)拆分业务(分布式):组件分离,垂直,水平
2)大事务的拆分:比如,1000W业务,拆分为20次执行
2. 内部
1)二进制日志更新
sync_binlog=1
2)5.7之前的版本,没有开GTID之前,主库可以并发事务,但是dump传输时是串行的,所以会导致,事务量,大事务时会出现比较严重延时
--解决方案:
5.6+版本,出现了gtid,事务在主从的全局范围内就有了唯一性标志
5.7+版本,就可以实现并发传输binlog
但是,即使有这么多的优秀特性,我们依然需要尽可能的减少大事务,以及锁影响
ps:怎么判断是主库传输不及时
1.Seconds_behind_master
2.主库:show master status; show slave status \G
2)从库
1. 外部
网络,从库配置低,参数设定
2. 内部
IO线程:写relay-log ---> IO性能
SQL线程:SQL默认在非GTID模式下是串行的
解决方案:
1)开启GTID
2)串行改并行
5.6+GTID:database级别,基于库级别SQL线程并发
5.7+GTID:Logic_clock 逻辑时钟。保证了同库级别下的事务顺序问题,所以可以理解为基于事务级别的并发回放,MTS
即使有以上的自带的优化机制,我们还是要注意对于大事务的处理的问题,锁的问题
优化数据库的根本就是优化人,大事务和锁都是需要我们额外关注的
五、主从复制搭建实战(多台机器)
1.主库操作:
给主库个密码 mysqladmin -uroot password '123'
2.创建主从复制用户
#登录数据库
[root@db01 ~]# mysql -uroot -p123
#创建rep用户作为从库连接用的用户
mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by 'oldboy123';
这里必须 *.* 不能指定库授权,因为 replication slave 是全局的
3)查看主库binlog信息
mysql> show master status;
2. 从库操作:
1)修改配置文件
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
#主库server-id为1,从库不等于1
server_id=2
#重启数据库
[root@db02 ~]# /etc/init.d/mysqld restart
2)配置主从同步
#登陆数据库
[root@db02 ~]# mysql -uroot -poldboy123
#执行change master to 语句
mysql> change master to
-> master_host='10.0.0.51',
-> master_user='rep',
-> master_password='123',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=1357;
#开启IO和SQL线程
start slave;
3.添加数据库主从(数据库运行中做主从)
1.准备纯净的从库环境
2.主库全备数据
#如果不打点备份
mysqldump -uroot -p123 -A -R --triggers > /backup/full.sql
#打点备份
mysqldump -uroot -p123 -A -R --triggers --master-data=2 –single-transaction > /backup/full.sql
3.将热备数据传达从库
scp /backup/full.sql 172.16.1.52:/tmp
4.模拟主库运行,有数据写入
insert into .....
5.导入全备数据
#导入不打点的数据的话,接下来主从,位置点只能去主库查看 show master status; 这个是不准确的
#如果导入的是打点的数据,那么接下来主从的同步位置点可以从全被sql里面找到,是准确的
6.查看sql文件中的位置点(如果是打点备份的话)
[root@db02 scripts]# head -50 /tmp/dadian.sql|grep 'MASTER_LOG_POS'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1544;
7.从库配置主从
mysql> change master to
master_host='10.0.0.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000001',
master_log_pos=1357;
8.开启SQL线程和IO线程
start slaves;
9.查看主从状态
show slave status;
4.验证多个从库server_id相同,主从是否有问题
1.配置第二个从库
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
2.启动数据库
systemctl start mysql
3.将主库的全备发送到从库
scp /tmp/full.sql root@172.16.1.51:/tmp
4.在从库执行sql文件
mysql < /tmp/full.sql
5.查看sql文件中的位置点
[root@db02 scripts]# head -50 /tmp/dadian.sql|grep 'MASTER_LOG_POS'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1544;
6.在从库上执行change master
change master to
master_host='172.16.1.50',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000001',
master_log_pos=2100;
7.开启SQL线程和IO线程
start slave;
8.查看主从复制的状态
show slave status\G
问题
1.主库出了问题怎么办?
物理方面:
1.看主库是否能ssh上
2.检查一下binlog是否完整
3.手工追加日志到最新位置
4.从库替代主库工作
逻辑方面:drop
只能通过备份去恢复
2.从库怎么称当主库
1.修复最新状态
2.取消从库身份
3.清空binlog日志信息