day11 主从复制
回顾
1. mysqldump
-u
-p
-S
-h
-P
-A --triggers -E
--master-data=2
--single-transaction
--set-gtid-purged=OFF 构建主从的备份千万不能加OFF,可以是ON或者AUTO
--max-allowed-packet=
2. xtrabackup
innobackupex
全备:
innobackupex --user=xxx --password=xxx --no-timestamp /data/backup/full
增量:
innobackupex --user=xxx --password=xxx --no-timestamp --incremental --incremental-basedir=/data/backup/full /data/backup/inc1
整理全备:
全备
innobackupex --apply-log /data/backup/full
增量:
只有最后一次合并的增量不需要--redo-only,过程中所有的备份整理都要加--redo-only
inobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1/data/backup/full
恢复备份:
innobackup --copy-back /data/backup/full
3. 备份工具如何配合binlog应用
binlog截取最重要的是找到起点和终点
周三鞋屋两点,数据损坏,截取日志的思路
mysqldump:
起点:找到周二晚上全备脚本,找到CHANGE MASTER TO
终点:通过events 或者 文件内容,找到故障点的位置
xtrabackup:
起点:周二晚上增量备份,
终点:通过events 或者 文件内容,找到故障点的位置
4.异构
操作系统版本不一样:逻辑备份 mysqldump
数据库版本不一样:
MySQL---CSV--->MongoDB ,ES
MySQL--JSON--->MongoDB ,ES
主从复制
一、介绍
依赖于二进制日志的“实时”备份的一个多节点结构
二、主从复制的前提(搭建主从复制)
2.1 至少两个实例
2.2 不同的server_id
2.3 主库要开启二进制日志
2.4 主库需要授权一个专用复制用户
2.5 主库数据备份
2.6开启专用复制线程
三、搭建主从复制
3.1 准备多实例
看前面章节
略……
3.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.3
mysql -S /data/3307/mysql.sock -e "show variables like '%log_bin%'"
mysql -S /data/3308/mysql.sock -e "show variables like '%log_bin%'"
mysql -S /data/3309/mysql.sock -e "show variables like '%log_bin%'"
3.4 主库创建复制用户
mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
3.5 进行主库数据备份
mysqldump -S /data/3307/mysql.sock -A --master-data=2 -R -E --triggers --single-transaction >/tmp/full.sql
3.6 恢复数据到从库
mysql> set sql_log_bin=0;
mysql> cource /tmp/full.sql;
3.7 告诉从库复制的信息
mysql -S /data/3308/mysql.sock
mysql> help change master to
CHANGE MASTER TO
MASTER_HOST='master2.example.com',
MASTER_USER='replication',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
vim /tmp/full.sql
22 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=444;
3.8 启动复制线程
mysql> start slave;
3.9 如果change master to 信息输入错误,怎么办?
mysql> stop slave;
mysql> 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=444,
MASTER_CONNECT_RETRY=10;
mysql> start slave;
四、主从复制工作过程
4.1 名词认识
1. 文件:
(1) 主库: binlog
(2)从库: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_T和SQL_T线程
(3) IO_T读取master.info文件,获取到主库信息
(4) IO_T连接主库,主库会立即分配一个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会将二进制日志写到relay-log中
(10) 从库SQL_T读取relay_log.info文件,获取上次执行过的位置点
(11) SQL_T按照上次的执行点往下执行relay_log日志
(12) SQL_T执行完成之后,重新更新relay-log.info
(13)relay-log定期自动清理的功能
细节:
主库发生了信息的修改,更新二进制日志完成后,会发送一个“信号”给Dump_T,Dump_T通知给IO_T
五、主从复制监控及故障分析处理
5.1 主从监控
1. 主库:
mysql> show processlist;
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 2 | repl | 10.0.0.51:31712 | NULL | Binlog Dump | 6384 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 4 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.12 sec)
mysql>
2. 从库:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 154
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 444
Relay_Log_Space: 3294
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 7
Master_UUID: c130db0d-9879-11e9-ad28-000c296913bb
Master_Info_File: /data/3308/data/master.info
SQL_Delay: 300
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
相关信息:
主库的信息:
Master_Host: 10.0.0.51 ----->主库的IP
Master_User: repl ----->复制用户名
Master_Port: 3307 ----->主库的端口
Connect_Retry: 10 ----->断连之后重连的次数
Master_Log_File: mysql-bin.000004 ----->主库以获取的binlog文件名
Read_Master_Log_Pos: 154 ----->已经获取到的binlog位置号
从库的信息:(master.info)
Relay_Log_File: db01-relay-bin.000002 ----->从库已经运行的relay-log的文件名
Relay_Log_Pos: 320 ----->
从库复制线程工作状态:
Slave_IO_Running: Yes
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: 0 ----->从库延时主库的时间(秒为单位)
从库线程报错详细信息:
Last_IO_Errno: 0 ----->IO报错的号码
Last_IO_Error: ----->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的个数
5.2 主从故障的分析及处理
从库复制线程工作状态:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
从库线程报错详细信息:
Last_IO_Errno: 0 ----->IO报错的号码
Last_IO_Error: ----->IO报错的具体信息
Last_SQL_Errno: 0 ----->SQL报错的号码
Last_SQL_Error: ----->SQL线程报错的具体原因
5.2.1 IO线程故障
(1) 连接主库连接不上
connecting
原因:
网络不通
防火墙
IP不对
port不对
用户,密码不对
skip_name_resolve
连接数上限
处理思路:
[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.51 -P 3308
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host 'db01' is not allowed to connect to this MySQL server ----->端口问题
[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.52 -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.52' (110) ----->IP问题
[root@db01 ~]# mysql -urepl -p1234 -h 10.0.0.51 -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl'@'db01' (using password: YES) ----->密码问题
[root@db01 ~]# mysql -urepl1 -p123 -h 10.0.0.51 -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl1'@'db01' (using password: YES) ----->用户名问题
如何处理?
stop slave;
reset slave all;
change master to
start slave;
(2) 请求新的binlog
IO线程NO的状态分析:
原因一:日志名不对
从库信息:
Master_Log_File: mysql-bin.000001
Read_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;
mysql> reset master;
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)
从库:
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000001' at 154, the last event read from '/data/3307/mysql-bin.000006' at 154, the last byte read from '/data/3307/mysql-bin.000006' at 154.'
处理:
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) 写relaylog
(4) 更新master.info
(5) server_id重复
5.2.2 SQL线程故障
原因一:
读relay-log.info
读relay-log,并执行日志
更新relay-log.info
以上文件损坏,最好是重新构建主从
原因二:
为什么一条SQL语句执行不成功?
1. 主从数据库版本差异较大
2. 主从数据库配置参数不一致(例如:sql_mode等)
3. 想要创建的对象已经存在
4. 想要删除或修改的对象不存在
5. 主键冲突
6. DML语句不符合表定义及约束时
归根结底是从库写入了。
方法一:
stop slave;
set global sql_slave_skip_conuter = 1;
或者:
/etc/my.cnf
slave-skip-erroes = 1032,1062,1007
方法二:万全的解决
设置从库只读,防止写入
使用中间件做成读写分离的架构
六、主从延时原因分析
从库延时主库的时间:
Seconds_Behind_Master: 0 从库延时主库的时间(秒为单位)
6.1 主库方面:
日志写入不及时
sync_binlog=1;
主库并发业务较高
“分布式”架构
从库太多
级联主从
对于Classic Replication:
主库是有能力并发运行事务的,但是在Dump_T在传输日志的时候,是以事件为单元传输日志的,
所以导致事务的传输工作是串行方式的,这是在主库TPS很高时,会产生比较大的主从延时
怎么处理:
group commit
从5.6开始加入了GTID,在复制时,可以将原来穿行的传输模式编程并行的。
除了GTID支持,还需要双一保证。
6.2 从库方面:
Classic Replication
SQL线程只有一个,所以说只能串行执行relay的事务。
怎么解决:
多加几个SQL线程
在5.6中出现了database级别的多线程SQL
只能针对不同库下的事务,才能并发
到5.7版本中加入了MTS,真正实现了事务级别的并发SQL
七、延时从库
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 如何使用延时从库
模拟故障:
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到drop之前的位置点
3. 截取relaylog日志,找到起点(relay-log.info)和终点(drop 操作)
4. 恢复截取的日志,验证数据可用性。
开始处理:
1. 停从库的SQL线程
mysql -S /data/3308/mysql.sock
mysql> stop slave sql_thread;
2. 找relaylog的起点和终点
起点:
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 476
终点:
mysql> show relaylog events in 'db01-relay-bin.000002'
| db01-relay-bin.000002 | 1149 | Query | 7 | 2036 | drop database delay
3. 截取日志
[root@db01 ~]# mysqlbinlog --start-position=476 --stop-position=1149 /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
5. 检查是否恢复