DBA之路 11_MySQL_主从复制基础&延时从库
2019-06-27 本文已影响15人
Linux_淡忘
上节回顾
1.mysqldump
-R 在备份时，同时备份存储过程和函数，如果没有会自动忽略
-E 在备份时，同时备份EVENT，如果没有会自动忽略
--triggers 在备份时，同时备份触发器，如果没有会自动忽略
--master-data=2
记录备份开始时 position号 ，可以作为将来做日志截取的起点。
假设备份时数据损坏了 我们不可能把数据全备份回来 我们有没有一种方法只备份恢复丢失的数据呢--master-data=2 这个参数的大一时在你备份时自动重新生成一个二进制文件这样可以最大限度的减少你数据丢失时与要恢复的数据量
-u
-p
-S
-h
-P
-R
--triggers
-E
--master-data=2
--single-transaction
--set-gtid-purged=OFF构建主从的备份千万不能加OFF
--max-allowed-packet备份的时候加一个允许的最大包的大小
2.xtrabackup&&innobackupex
1.全备
innobackupex --user=root --no-timestamp --password=oldboy123 /data/backup/full.sql
2.增量
innobackupex --user=root --password --no-timestamp --incremental --incremental-basedir=/data/backup/full.sql /data/backup/inc1
3.整理备份
（1）单独整理全备*(如果后面有增量这里加--redo-only)
innobackupex --apply-log /data/backup/full
（2）增量整理
只有最后一次合并的增量不加redo-only
innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1 /data/backup/full
（3）恢复备份
innobackupex --copy-back /data/backup/full
4.备份工具如何配合binlog应用
binlog截取最重要的是找到起点和终点
周三下午两点例子，数据损坏，截取日志的思路
起点：找到周二晚上全备脚本，找到change master to ，看binlog文件名和gtid
终点：通过看events或者文件内容找到故障点的位置
xtrabackup：
起点：找到周二晚上的增量备份。备份路径下的xtrabackup_binlog_info
终点：通过看events或者文件内容找到故障点的位置
4.异构迁移
操作系统版本不同：
逻辑备份，mysqldump
数据库软件不同：
MySQL---CSV-->MongoDB，ES
MySQL---JSON-->MongoDB，ES
主从复制
1.介绍
依赖于binlog的“实时”备份的一个多节点架构
2.主从复制的前提(搭建主从)
2.1至少两个实例
2.2不同的server_id
2.3主库需要开启二进制日志
2.4主库需要授权一个专用复制用户
2.5主库数据备份
2.6开启专用复制线程
3.搭建主从复制
1.搭建多实例（略）
2.检查server_id
mysql -S /data/3307/mysql.sock -e "select @@server_id"
mysql -S /data/3308/mysql.sock -e "select @@server_id"
mysql -S /data/3309/mysql.sock -e "select @@server_id"
3.检查主库二进制日志情况
mysql -S /data/3309/mysql.sock -e "show variables like '%log_bin%'"
4.主库创建复制用户
mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.03 sec)
5.进行主库数据备份
[root@zyl666 /data/3307]# mysqldump -S /data/3307/mysql.sock -A --master-data=2 -R -E --triggers --single-transaction >/tmp/full.sql
6.恢复数据到从库（3308）
mysql -uroot -p -S /data/3308/mysql.sock
set sql_log_bin=0
source /tmp/full.sql
7.告诉从库复制的信息
help change master to;
该命令可以查到如下命令
change master to
CHANGE MASTER TO
MASTER_HOST='10.0.0.68',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000052',
MASTER_LOG_POS=684,
MASTER_CONNECT_RETRY=10;
8.启动从库
start slave
9.如果change master to信息输入错误怎么办
reset slave all;
然后重新change master to
4.主从复制工作过程
4.1名词认识
1.相关文件
主库：binlog
从库：
relay_log 中继日志
master.info 主库信息文件
relay-log.info 中继日志应用信息
2.线程
主库：
binlog_dump_thread 二进制日志投递线程
查看： mysql -S /data/3307/mysql.sock -e "show processlist"
从库：
IO_Thread:从库IO线程
作用：请求和接收binlog
SQL_thread:从库SQL线程
作用：回放日志
4.2工作原理
<1>.从库执行change master to语句，会立即将主机信息记录到从库的Master.info文件。
<2>.从库执行start slave语句，会立刻生成IO_Thread,SQL_Thread
<3>.IO_T读取master.info文件，获取到主库信息。
<4>.IO_T连接主库（TCP/IP），主库会立刻分配一个DUMP_T线程与其进行交互
<5>.IO_T根据Master.info的binlog信息，向主库的DUMP_T请求最新的binlog
<6>.主库DUMP_T，进过查询，如果有新的，截取并返回给从库的IO_T
<7>.从库IO_T收到binlog，存储在TCP/IP缓存中，并且在网络底层返回ACK
<8>.从库IO_T会更新master.info，重置binlog位置点信息
<9>.从库IO_T会将binlog,写入到relay-log中
<10>.SQL_T读取relay-log.info文件，获取上次执行过的位置点.
<11>.SQL_T按照位置点继续往下执行relaylog日志
<12>.执行完成后，重新更新relay-log.info
<13>.relaylog定期自动清理的功能
细节：
主库发生了新的事件操作，信息修改，更新二进制日志完成后，会发送一个“信号”给DUMP_T，dump线程会tong通知给IO_T线程
主从复制原理图.png
5.主从复制
主库信息查看：
从库信息查看：
show slave stauts;
主库信息(master.info)：
Master_Host: 10.0.0.68主库IP
Master_User: repl复制用户名
Master_Port: 3307主库的端口
Connect_Retry: 10断连后重试次数
Master_Log_File: mysql-bin.000052已获取的binlog的文件名
Read_Master_Log_Pos: 684以获取的binlog的位置好
从库的relaylog的信息（relay-log.info）
Relay_Log_Pos: 4从库已经运行过的relaylog的位置点
Relay_Log_File: zyl666-relay-bin.000001从库已经运行过的relaylog的文件名
从库复制线程的工作状态：
Slave_IO_Running: No
Slave_SQL_Running: Yes
过滤复制：
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
从库延时主库的时间：
Seconds_Behind_Master: NULL 从库延时主库的时间，以秒为单位
从库线程报错信息：
Last_IO_Errno: 1593 IO报错的号码
Last_IO_Error: The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF. IO报错的具体信息
Last_SQL_Errno: 0 SQL报错的号码
Last_SQL_Error: SQL报错的具体信息
延时从库：
SQL_Delay: 0 延时从库设定的延时时间
SQL_Remaining_Delay: NULL 延时操作的剩余时间
GTID复制的相关信息
Retrieved_Gtid_Set: 接收到的GTID的个数
Executed_Gtid_Set:执行的GTID的个数
6.IO线程故障
<1>.主库连接不上
connecting状态：
原因：
网络不通
防火墙
IP 不对
port 不对
用户，密码不对
skip_name_resolve
连接数上限（默认151个）
如何解决？
stop slave;
reset slave all;
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
start slave;
<2>.请求新的Binlgo
IO线程no的状态分析：
原因一：日志名不对
从库信息：
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=444,
对比备份的位置号
原因二：日志损坏，日志不连续
演示：
主库：
mysql -S /data/3307/mysql.sock
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
reset master;
mysql -S /data/3307/mysql.sock
mysql> create database dd;
Query OK, 1 row affected (0.01 sec)
mysql> create database dd1;
Query OK, 1 row affected (0.00 sec)
mysql> create database dd2;
Query OK, 1 row affected (0.00 sec)
处理：
mysql -S /data/3308/mysql.sock
stop slave;
reset slave all ;
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
start slave;
<3>.日志损坏
<4>.日志不连续
<5.>server_id重复
5.2.2SQL线程故障
原因一：
读relay-log.info
读relay-log，并执行日志
更新relay-log.info
以上文件损坏，最好是重新构建主从
原因二：
为什么一条SQL语句执行不成功
1.主从数据库版本差异较大
2.主从数据库配置参数不一致（例如sql_mode等）
3.想要创建的对象已经存在
4.想要删除或者修改的对象不存在
5.主键冲突
6.DML语句不符合表定义及约束时
归根结底是从库写入了。
方法1：
stop slave;
set global sql_slave_skip_counter=1
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突
但是，以上操作有时是有风险的，最安全的做法就是重新构建主从。把握一个原则,一切以主库为主.
方法2:
(1) 可以设置从库只读.
db01 [(none)]>show variables like '%read_only%';
注意：
read_only 只能管普通用户
super_read_only 管root用户
使用中间件做成读写分离的架构
6.主从延时原因分析
Seconds_Behind_Master: NULL 从库延时主库的时间，以秒为单位
主库方面：
1.日志写入不及时
解决方法：
select @@sync_binlog
每次事务提交就立即刷写磁盘
2,日志并发业务较高
解决方法：
“分布式”架构
3.从库太多
解决方法：
级联主从
4.对于传统Classic Replication,主库是有 能力并发运行事务。但是在Dump_T在传输日志的时候
是以事件为单元的所以导致事务的传输工作时串行方式的，这时在主库 TPS（每秒数据量级）很高时，
会产生比较大的主从延时。
解决方法：
group commit；
从5.6开始加入了GTID，在复制时，可以将原来的串行的传输模式编程并行。
条件：有GTID支持，且需要双一来保证。
从库方面：
Classic Replication
SQL线程：只有一个，所以只能串行执行relay的事务。
解决方案：
多加几个SQL线程。
在5.6中出现了Database级别的多线程SQL
虽然出现了这个功能，但是只能针对不同库下的事务，才能并发。
在5.7版本，加入了MTS（加入了sql_no），真正实现了事务级别的并发SQL。
7.延时从库
7.1数据损坏
物理损坏：
逻辑损坏：
对于传统的主从复制，比较擅长物理损坏。
7.2设计理念
对SQL线程进行延时设置。
7.3延时多久合适？
3-6小时
7.4如何设置
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;
mysql>start slave;
mysql> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL
7.5如何使用延时从库
7.5.1思路
模拟故障：
mysql -S /data/3307/mysql.sock
create database delay charset utf8mb4;
use delay;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
drop database delay;
发现问题了：
1.停止SQL线程，停止主库业务
2.模拟SQL线程，手工恢复relaylog
3.截取relaylog日志，找到起点（relay-log.info）和终点。
4.恢复截取的日志，验证数据可用性。
开始处理（从库操作）：
1、停从库的SQL线程
stop slave sql_thread;
2、找relaylog起点和终点
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
show relaylog events in 'db01-relay-bin.000002'
3、截取日志
mysqlbinlog --start-position=320 --stop-position=927 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql
4. 恢复
mysql -S /data/3308/mysql.sock
set sql_log_bin=0;
source /tmp/relay.sql；
