回顾
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. 检查是否恢复
网友评论