Day011-MySQL主从复制
2019-10-23 本文已影响0人
驮着集装箱的鲸鱼
1. 主从复制介绍
依赖于二进制日志，“实时”备份的一个多节点架构。
2. 主从复制的前提（搭建主从复制）
（1）至少2个实例
（2）不同的server_id
（3）主库需要开启二进制日志
（4）主库需要授权一个专用的复制用户
（5）主库数据备份
（6）开启专用复制线程
3. 搭建主从复制步骤
3.1 准备多实例
过程略
3.2 检查多实例server_id
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e 'select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e 'select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 8 |
+-------------+
[root@db01 ~]# mysql -S /data/3309/mysql.sock -e 'select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 9 |
+-------------+
3.3 检查3307（主库）二进制日志状态
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e 'show variables like "%log_bin%";'
+---------------------------------+----------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------+
| log_bin | ON |
| log_bin_basename | /data/3307/mysql-bin |
| log_bin_index | /data/3307/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------+
3.4 主库创建专用的复制用户
[root@db01 ~]# mysql -uroot -P3307 -S /data/3307/mysql.sock
mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
3.5 进行主库数据备份
生产中可以用历史备份
[root@db01 ~]# mysqldump -S /data/3307/mysql.sock -A --master-data=2 -R -E --triggers --single-transaction > /tmp/full_3307.sql
[root@db01 ~]# ls /tmp/
full_3307.sql
3.6 恢复数据到从库
[root@db01 ~]# mysql -S /data/3308/mysql.sock -P 3308
mysql> set sql_log_bin=0;
mysql> source /tmp/full_3307.sql
另一台步骤省略……
3.7 change master to 告诉从库复制的信息（如IP、端口、用户名、密码、binlog等）
从库操作
vim /tmp/full_3307.sql
大约22行左右 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=443; 用这个当做起点
[root@db01 ~]# mysql -S /data/3308/mysql.sock -P3308
mysql> help change master to;
mysql> CHANGE MASTER TO 注意 不能用小写
-> MASTER_HOST='10.0.0.51', (主库IP)
-> MASTER_USER='repl', (专用复制用户)
-> MASTER_PASSWORD='123', （授权的密码）
-> MASTER_PORT=3307, （主库端口）
-> MASTER_LOG_FILE='mysql-bin.000002', （这是从/tmp/full_3307中截取的）
-> MASTER_LOG_POS=443, （这是从/tmp/full_3307中截取的）
-> MASTER_CONNECT_RETRY=10;（与主库连接失败后重试连接的次数）
3.8 启动从库复制线程
从库操作
mysql> start slave;
3.9 如果change master to 信息输入错误，可以用以下方法解决，然后重新输入
mysql> stop slave;
mysql> reset slave all; 清理之前输入的CHANGE MASTER TO信息
mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=443,
MASTER_CONNECT_RETRY=10;
mysql> start slave;
查看主从搭建是否成功
3308> show slave status\G
……省略若干行
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……省略若干行
4. 主从复制工作过程
4.1 名词认识
在主从复制工作过程中，会用到很多的文件及涉及到的线程
（1）文件
主库：
涉及到的文件为“binlog”
从库：存在于数据目录下
relay-log：中继日志文件
master.info：主库信息文件
relay-log.info：中继日志应用信息文件
（2）线程
主库：
binlog_dump_thread：二进制日志投递线程。可以使用show processlist查看，名称为Binlog Dump。
从库：默认为传统复制模式
IO_Thread：从库的IO线程（请求和接收binlog）
SQL_Thread：从库的SQL线程（回放（重新运行）从主库拿过来的日志）
4.2 主从复制工作原理（过程）
（1）从库执行change master to语句，然后立即将主库的信息（ip、端口等）记录到master.info中，这个文件就在从库的数据目录下。
（2）从库执行start slave语句的瞬间，会立即生成IO_Thread和SQL_Thread。
（3）IO_Thread读取master.info文件，获取主库的相关信息（IP、端口号等）。
（4）IO_Thread连接主库，连接层开始验证用户名、密码、端口号、IP等是否合法。
一旦合法，主库会立即分配一个dump_thread线程，来与IO_Thread进行交互。
（5）IO_Thread根据master.info中的二进制日志信息，向主库的DUMP_Thread线程请求最新的二进制日志。
（6）DUMP_Thread经过show master status查询，如果发现有新的二进制日志，就截取新的日志并返回给从库的IO_Thread。
（7）从库IO_Thread收到主库发来的binlog，存储在到TCP_IP缓存中，在网络底层返回ACK给主库。
（8）从库IO_Thread会将二进制日志信息写入到relay-log中。
（9）从库IO_Thread更新master.info信息，重置二进制日志位置点信息。
（10）从库SQL_Thread读取relay-log.info文件，获取上次执行过的relay-log.info位置点。
（11）根据获取到的位置点，SQL_Thread按照位置点往下执行relaylog日志。
（12）SQL_Thread执行完后，更新relay-log.info文件。
（13）pwrge线程（非主从线程）把应用过的relay_log定期自动清理
主从复制原理图.png
思考：主库创建一个库后，从库怎么知道的？
源码解释：
主库发生的信息修改，更新二进制日志后，会发送一个信号给dump线程，然后dump线程通知io线程,io线程到主库请求并获取到最新的二进制日志。
5. 主从复制监控及故障分析与处理
5.1 主从监控
主库：
mysql> show processlist; 通过该命令监控主库的线程
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 8 | repl | 10.0.0.51:38432 | NULL | Binlog Dump | 5704 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 9 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
从库：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
主库信息：（master.info）
Master_Host: 10.0.0.51 （主库IP地址，记录与master.info中）
Master_User: repl （主库中创建的复制用户）
Master_Port: 3307 （主库端口号）
Connect_Retry: 10 （与主库连接失败后重试连接的次数）
Master_Log_File: mysql-bin.000002 （主库已经获取到的日志名）
Read_Master_Log_Pos: 443 （已经获取到的binglog日志位置号）
从库信息（relaylog）：
Relay_Log_File: db01-relay-bin.000002 （从库已经运行过的relay log的文件名）
Relay_Log_Pos: 320 （从库已经运行过的relay log的位置点）
Relay_Master_Log_File: mysql-bin.000002
从库复制线程工作状态:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
过滤复制相关状态：默认情况下，主从复制比例为1:1
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: 443
Relay_Log_Space: 526
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: 0
Master_SSL_Verify_Server_Cert: No
从库线程报错详细信息----->IO报错的号码
Last_IO_Errno: 0
从库线程报错详细信息----->IO报错的具体信息
Last_IO_Error:
从库线程报错详细信息----->SQL报错的号码
Last_SQL_Errno: 0
从库线程报错详细信息----->SQL报错的具体原因
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 7
Master_UUID: 92257cfc-8e8e-11e9-a0c5-000c29577287
Master_Info_File: /data/3308/data/master.info
延时从库操作设定时间----->防止主库误操作立即同步到从库（生产需要，主动设置）
SQL_Delay: 0
延时从库操作剩余时间：
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
GTID复制相关信息----->从主库接收到的GTID个数
Retrieved_Gtid_Set:
GTID复制相关信息----->主库与从库一共执行了的GTID个数
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
5.2 主从故障的分析及处理
（1）观察从库复制线程状态
3308> show slave status\G
……省略若干行
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……省略若干行
（2）从库线程报错详细信息
Last_IO_Error: 0 （IO报错号码）
Last_IO_Error: （IO报错具体信息）
Last_SQL_Error: 0 （SQL报错号码）
Last_SQL_Error: （SQL报错具体信息）
5.2.1 IO线程故障
IO线程工作流程
（1）连接主库
（2）请求新的binlogo
（3）写入realy-log
（4）更新master.ingo
IO故障分析
（1）连接主库连接不上
报错：connecting
原因：
网络不通
防火墙阻挡
IP错误
port错误
用户，密码不对
skip_name_resolve（5.7以前的版本，禁止域名和主机名解析）
链接数上限，默认151个链接数
解决办法：
mysql -urepl -p123 -h 10.0.0.51 -P 3307
如果能够连接上，证明没有问题
如果连接不上，就有可能是用户名、密码、端口、IP地址错误。
模拟错误：
用户名错误：
[root@db01 ~]# mysql -urep -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 'rep'@'10.0.0.51' (using password: YES)
密码错误：
[root@db01 ~]# mysql -urepl -p1231 -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'@'10.0.0.51' (using password: YES)
IP错误：
[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.5 -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.5' (110)
端口错误：
[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.51 -P 3306
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.51' (111)
上述错误解决办法：
stop slave;
reset slace all;
change master to;
重新指定IP、端口、等
start slave;
（2）请求新的binlog
报错显示：IO线程显示为NO
原因：
（1）日志名不对
（2）日志损坏
（3）日志不连续
解决办法：
1. 日志名不对
从库查看：从库请求的日志名是否与主库的日志相同
mysql> show slave status\G
省略部分输出……
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 443
与备份文件full_3307中的-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=443;是否相同。
也可以在主库中使用show master status;确定
2. 日志损坏，日志不连续
显示：
Slave_IO_Running: No
经过过排查原因，没有发现日志名不对的情况。那么就有可能是日志损坏了。
模拟场景：
主库：
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> show slave status\G
……省略部分内容
Slave_IO_Running: No
Slave_SQL_Running: Yes
……省略部分内容
主库：
mysql> create database dd;
mysql> create database dd1;
mysql> create database dd2;
此时主从状态已经异常了，所以新建的库不会同步
解决方法：
让日志从头开始记录
主库：
show master status\G
记住binlgogID和POSID
从库：
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线程故障
SQL线程的作用：
读relay-log.info
读relay-log，并执行日志，relay-log里面存放的是主库执行过的SQL语句。
更新relay-log.info
故障原因：
（1）以上文件损坏，最好是重新构建主从，恢复主库数据到从库，然后chage master to等一系列操作，这也是最靠谱的方法。
（2）从库不能执行主库执行过的SQL语句。
为什么从主库拿过来的SQL语句，在从库不能执行？
答：
1. 有可能是数据库版本差异较大，不过这种情况可能性较小。
2. 主从数据库参数配置不一致（例如SQL_mode、内存参数等）
3. 想要创建的对象已经存在。
4. 想要删除或修改的对象不存在。
5. 主键冲突。
6. DML语句不符合表的定义及约束。
总结以上问题：除了前两个问题外，其他都是从库写入造成的故障。
注意：生产环境中，从库要杜绝一切写入！！！
从库写入故障模拟演示1
从库操作：
mysql> create database test; 从库创建库
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| t1 |
| test |
+--------------------+
6 rows in set (0.01 sec)
主库操作：
mysql> show databases; 主库中不存在从库中的test库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| t1 |
+--------------------+
5 rows in set (0.01 sec)
从库操作：
mysql> show slave status\G 此时的主从关系还是正常的
*************************** 1. row ***************************
省略部分输出……
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
省略部分输出……
主库操作：创建从库中的test库，并创建一个表
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| t1 |
| test | 此时主库也有了从库中的test库，表面上看着没毛病
+--------------------+
6 rows in set (0.00 sec)
mysql> use test
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.29 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
从库操作：查看主库中的表在同步到从库没有
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec) 没有同步主库的他t1表到从库
从库查看主从状态：发现主从状态已经异常了
mysql> mysql> show slave status\G
*************************** 1. row ***************************
省略部分输出……
Slave_IO_Running: Yes
Slave_SQL_Running: No
省略部分输出……
Last_Errno: 1007
Last_Error: Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'create database test'
省略部分输出……
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'create database test' 无法创建test库，test库已经存在，就是因为从库写入导致的
省略部分输出……
1 row in set (0.00 sec)
解决从库写入故障方法 1 --以主库为准的恢复（推荐方法）
模拟故障：
从库操作：
（1）删除从库创建的库
mysql> drop database test;
Query OK, 0 rows affected (0.01 sec)
（2）再次查看主从状态，发现还是没有恢复
mysql> mysql> show slave status\G
*************************** 1. row ***************************
省略部分输出……
Slave_IO_Running: Yes
Slave_SQL_Running: No
省略部分输出……
Last_Errno: 1007
Last_Error: Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'create database test'
省略部分输出……
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'create database test' 无法创建test库，test库已经存在，就是因为从库写入导致的
省略部分输出……
1 row in set (0.00 sec)
原因是SQL线程一旦检测到故障，就不会再工作了！重启一下就好了！
（3）重启主从状态
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
（4）再次查看主从状态
mysql> mysql> show slave status\G
*************************** 1. row ***************************
省略部分输出……
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
省略部分输出……
（5）检查数据同步情况
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 | 同步成功
+----------------+
1 row in set (0.00 sec)
解决从库写入故障方法 2 --以从库为准的恢复
模拟故障：
从库操作：
mysql> create table t2(id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t2 |
+----------------+
2 rows in set (0.00 sec)
主库操作：
mysql> use test;
Database changed
mysql> create table t2(id int,name varchar(20) charset utf8mb4);
Query OK, 0 rows affected (0.08 sec)
从库查看主从状态：发现主从已经异常了
mysql> show slave status\G
*************************** 1. row ***************************
省略部分输出…………
Slave_IO_Running: Yes
Slave_SQL_Running: No
省略部分输出…………
Last_Errno: 1050
Last_Error: Error 'Table 't2' already exists' on query. Default database: 'test'. Query: 'create table t2(id int,name varchar(20) charset utf8mb4)'
省略部分输出…………
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1050
Last_SQL_Error: Error 'Table 't2' already exists' on query. Default database: 'test'. Query: 'create table t2(id int,name varchar(20) charset utf8mb4)'
省略部分输出…………
1 row in set (0.01 sec)
解决方法1：跳过错误操作
从库操作：
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> set global sql_slave_skip_counter=1; 跳过一个错误的操作，进入到下一个操作
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
再次查看主从状态：发现主从关系已经恢复，但是数据时不能同步的
mysql> show slave status\G
*************************** 1. row ***************************
省略部分输出……
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
省略部分输出……
主库操作：
mysql> insert into t2 values(1,'abc');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.01 sec)
从库操作：发现数据没有同步
mysql> select * from t2;
Empty set (0.00 sec)
查看主从状态：
mysql> show slave status\G
省略部分输出…………
Last_SQL_Error: Column 1 of table 'test.t2' cannot be converted from type 'varchar(80(bytes))' to type 'varchar(20(bytes) latin1)'
报错：字符集长度不一致，即使主从状态一致，数据页无法同步
省略部分输出…………
从库操作：跳过错误操作
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
主库操作：删除导致主从异常的表，t2表
mysql> drop table t2;
Query OK, 0 rows affected (0.01 sec）
从库操作：
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
解决方法2：最完美的解决办法（推荐）
（1）设置从库只读，防止从库写入，redo-only参数。
从库设置：
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF | read_only 设置为1，从库就为只读状态，但是只能作用在普通用户身上
| super_read_only | OFF | super_read_only设置为1，对全部用户生效，从库只读
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
5 rows in set (0.00 sec)
（2）使用中间件做成读写分离的架构。
6. 主从延时原因分析（面试题）
从库延时主库的时间：
Seconds_Behind_ Master: 0 从库延时主库时间单位为秒
6.1 主库方面
原因（1）：
binlog日志写入不及时
解决方法：双一标准的第二个“1”！
mysql> select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
| 1 | 每次事务提交，立即刷写日志到磁盘，5.6以后的默认就是1。
+---------------+
1 row in set (0.00 sec)
原因（2）：
主库并发业务较高，事务量大，忙不过来
解决方法：
a. 升级硬件
b. 分布式架构
原因（3）：
从库太多
解决方法：
a. 减少从库，读少的情况下可以这样
b. 级联主从（主库+中间库+从库） 读多写少
主从延时补充：
对于Classic Rrplication（传统复制模式），主库是有能力并发运行事务的，但是在Dump_T在传输日志的时候，是以事件为单元来传输日志的，所以导致事务传输工作是串行方式的。
这时在主库TPS（每秒的事务量级）很高时，会产生比较大的主从延时。
那么如何处理这个问题呢？
group commit（在同一时刻提交的事务）=GTID复制+双一
从5.6开始，加入了GTID，在复制时，可以将原来串行的传输模式变成并行的。
但是光有GTID的支持是不够的，还需要“双一”保证，也就是sync_binlog=1。
5.7 中默认就是双一模式，也就是sync_binlog=1。
6.2 从库方面
Classic Rrplication，SQL线程只有一个，只能串行执行relay的事务。
那么如何解决呢？
可以多加几个SQL线程
在5.6中出现了database级别的多线程SQL，但是只能针对不同库下的事务，才能并发。
到5.7版本加入了MTS，才真正实现了事务级别的并发SQL。
7. 延时从库
注意：延时从库跟主从延时是不一样的，延时从库是主动配置的，用来防止误操作同步
7.1 主从复制能解决的故障--数据损坏
（1）物理损坏：
数据库的物理文件被误操作，rm
磁盘坏了等
（2）逻辑损坏：
数据库内部命令误操作，drop
（3）对于传统的主从复制来讲，比较“擅长处理物理损坏”，但是“不擅长逻辑损坏”。
7.2 延时从库设计理念
对从库的SQL线程进行延时设置
7.3 延时多久合适？
生产中建议3-6小时
如果需要用到被延时的数据，可以恢复3-6个小时的日志
7.4 设置方法（从库）
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300; 300为测试环境设置，单位为秒
mysql>start slave;
mysql> show slave status \G
……省略若干行
SQL_Delay: 300 延迟同步5分钟
SQL_Remaining_Delay: NULL 同步时间计时器，主库有操作时触发
……省略若干行
7.5 延时从库使用方法
7.5.1 使用思路
模拟故障：
主库操作：
create database delay charset utf8mb4;
use delay;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
drop database delay; 此时，主库误操作，发生逻辑损坏，从库5分钟后也会执行该操作
现在发现上面这个问题了，解决办法：
（1） 停止从库SQL线程，停止主库业务。
（2） 模拟从库SQL线程，手工恢复relaylog到drop之前的位置点。
（3） 截取relaylog日志，找到起点（relay-log.info中）和终点（drop操作之前）。
（4） 恢复截取日志，验证数据可用性。
开始处理：如果主库有业务，主库也要停掉
1. 停止从库SQL线程
mysql -S /data/3308/msyql.sock
stop slave sql_thread;
2. 找从库中relaylog的起点和终点
show slave status\G
起点：
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 1087
终点：只看左边一列，右边一列对应的是主库的binlog日志点
show relaylog events in 'db02-relay-bin.000002';
找到drop操作
db02-relay-bin.000002 | 1759 drop之前 | Query | 6 | 3504 | drop database delay
3. 截取从库relay log日志
mysqlbinlog --start-position=1087 --stop-position=1759 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql
要确定relay.sql中没有包含drop操作才能下一步
4. 恢复从库数据
set sql_log_bin=0;
source /tmp/relay.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| delay |
| mysql |
| performance_schema |
| sys |
+--------------------+
mysql> use delay
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
8. 过滤复制
把某一个库复制到从节点，可以在主库方面或从库方面来实现
（1）重新恢复主从环境 仅为测试环境操作
主库操作：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 887 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从库操作：
stop slave;
reset slave all;
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=887,
MASTER_CONNECT_RETRY=10;
start slave;
主库方面实现（了解）
主库操作：
mysql> show master status;
省略部分输出…………
Binlog_Do_DB 白名单，在白名单中的库名会记录日志
可以写到配置文件中，但是要小写
Binlog_Ignore_DB 黑名单，在黑名单中的不记录日志
实际工作中只用白名单或黑名单。
从库方面实现
show slave status\G
省略部分输出…………
Replicate_Do_DB: 只会进行复制某些或某个库（库级别的白名单）（使用较多）
Replicate_Ignore_DB: 不复制某些或某个库（库级别的黑名单）（使用较多）
Replicate_Do_Table: 只会进行复制某些或某个表（表级别的白名单）
Replicate_Ignore_Table: 不复制某些或某个表（表级别的黑名单）
Replicate_Wild_Do_Table: 模糊匹配白名单
Replicate_Wild_Ignore_Table: 模糊匹配黑名单
省略部分输出…………
例子：只需要复制xyz库的数据到从库
（1）更改my.cnf配置文件
[root@db01 ~]# vim /data/3308/my.cnf
……省略若干行
replicate_do_db=xyz
……省略若干行
[root@db01 ~]# systemctl restart mysqld3308
（2）检查配置是否生效
show slave status\G
……省略若干行
Replicate_Do_DB: xyz
……省略若干行
注意：如果有多个库的话，写多行即可。
replicate_do_db=xxx
replicate_do_db=xxx
replicate_do_db=xxx
（3）主库创建xyz库与abc库
mysql> create database xyz;
mysql> create database abc;
（4）从库查看同步情况
mysql> show databases;
+--------------------+
| Database |
+--------------------+
|省略部分输出……|
| xyz |
+--------------------+
7 rows in set (0.00 sec)
（5）总结
在SQL线程回放日志时，进行过滤控制
因为在创建非白名单内的库时，从库通过show slave status\G，
查看发现Master_log_file与Read_Master_Log_Pos都发生了变化，
说明创建非白名单内的库时，该库的日志文件已经同步过来，但是并没有进行回放，所以没有该库。
9. 半同步复制（面试题，了解理论即可）
半同步复制和传统复制的区别：
半同步复制是一个插件形式提供的功能。
主库方面多了一个线程：ack_receillver
从库多了一个线程：ACK_send
主库：receillver线程，只有接到从库发来的ACK确认，主库事务才能commit成功。
从库：Acksend只有等relaylog落地才能发送ACK
主库只会等10s，如果ACK还没有收到，会自动替换为异步复制（传统），不等了。
10 .GTID复制
10.0 GTID提供的特性
group commit , MTS
10.1 重点参数（GTID复制必加）
gitd-mode=on 开启GTID
enforce-gtid-consistency=true 强制GTID一致性，主从环境必配
log-slave-updates=1 从库配置，强制刷新从库二进制日志。应用场景5.7：1. 高可用（MHA）2. 级联复制的中间库
10.2 搭建一主两从GTID复制环境
需要3台服务器
10.0.0.51 主库
10.0.0.52 从库
10.0.0.53 从库
（1）清理环境
pkill mysqld
\rm -rf /data/*
\rm -rf /data/*
mkdir -p /data/mysql/data
mkdir -p /data/binlog/
chown -R mysql.mysql /data
10.3 准备配置文件
主库db01：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\\d]>
EOF
从库(db02)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\\d]>
EOF
从库(db03)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\\d]>
EOF
10.4 初始化数据(所有节点)
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
10.5 启动数据库
/etc/init.d/mysqld start
10.6 主库创建用户
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
10. 7 两个从库开始主从
mysql -e "change master to master_host='10.0.0.51',master_user='repl',master_password='123' ,MASTER_AUTO_POSITION=1;start slave; "
mysql -e "show slave status \G"|grep Yes
10.8 GTID复制与传统复制的区别
参数比对：
GTID复制：
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123' ,
MASTER_AUTO_POSITION=1; 指明为GTID复制模式
传统复制：
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=887,
MASTER_CONNECT_RETRY=10;
总结：
MASTER_AUTO_POSITION=1; 指明为GTID复制模式后，
会自动寻找复制起点
