目录
一、主从复制原理
1.简要原理
通过二进制日志，达到两台以上mysql实例数据同步
2.涉及到的文件
主库：binlog
从库：relay_log：存储请求过来的binlog
master.info：保存主库信息（IP/PORT/USER/PASSWORD/BINLOG位置点）
relay_log.info：记录从库回放relay log的位置点信息
3.涉及到的线程
主库：dump线程-日志投递线程
从库：IO线程-连接主库，请求日志
SQL线程-回放日志
4.主从复制执行步骤（重点）
1）从库执行change master to语句，把IP、PORT、USER、PASSWORD、BINLOG起点记录到master.info中。从库执行start slave，——>会开启IO、SQL线程
2）从库IO线程开始工作，读取了master.info后连接主库
3）主库连接层收到请求，验证通过后，生成dump线程和从库IO线程交互
4）从库IO线程通过master.info得到binlog起点，找主库dump线程请求最新的binlog
5）主库dump线程监控着binlog变化，接收到从库IO请求，截取最新的binlog，传给IO线程
6）从库IO线程接收到日志binlog，临时存储在缓存中（主库工作到此为止）
7）从库IO线程将接收到的日志存储到relay-log中，并更新master.info的位置点（IO线程工作结束）
8）SQL线程读取relay-log.info，获取到上次回放到的relay-log的位置点
9）SQL线程回放新的relay-log，再次更新relay-log.info中的位置点，结束
补充：
1）relay-log-purge线程，对relay-log有自动清理的功能
2）主库dump线程实时监控binlog的变化，自动通知给从库IO线程，但这只是通知，不是立即传输数据，要等IO线程来请求
二、主从复制搭建
1.准备两台以上的数据库实例，要求数据库版本一致
2.区分不同角色
51主库
52、53从库
3.主库开启二进制日志
4.主库创建专用复制用户，赋予权限
create user repl@'localhost' identified by 'ok';
grant replication slave on *.* to repl@'localhost' with grant option;
5.备份主库数据，恢复从库
mysqldump -uroot -p -A >/data/3306/backups/mdpbackup/mdpbase2024-05-27
mysql -S /tmp/mysql3307.sock < /data/3306/backups/mdpbackup/mdpbase2024-05-27
mysql -S /tmp/mysql3308.sock < /data/3306/backups/mdpbackup/mdpbase2024-05-27
6.开启从库复制功能
1）查看从库复制的起点
vim mdpbase2024-05-27
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '93909ace-1b58-11ef-81d8-000c2912a662:1-2';
-- MySQL dump 10.13 Distrib 8.0.20, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 8.0.20
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
--
-- GTID state at the beginning of the backup
--
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '93909ace-1b58-11ef-81d8-000c2912a662:1-2';
--
-- Current Database: `mysql`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `mysql`;
--
-- Table structure for table `columns_priv`
--
2）设置连接信息
查看如何设置：help change master to
在从库3307和从库3308上分别运行以下命令：
CHANGE MASTER TO
MASTER_HOST='localhost',
MASTER_USER='repl',
MASTER_PASSWORD='ok',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000003',
MASTER_LOG_POS=722,
MASTER_CONNECT_RETRY=10; --连接失败的时候，10秒发起一次重试
start slave;
mysql> CHANGE MASTER TO
-> MASTER_HOST='localhost',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='ok',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='binlog.000003',
-> MASTER_LOG_POS=722,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
7.检查两个从库的主从状态，双Yes代表主从正常
show slave status \G | grep "Running"
查看：Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 1841
Relay_Log_File: localhost-relay-bin.000004
Relay_Log_Pos: 361
Relay_Master_Log_File: binlog.000003
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:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1841
Relay_Log_Space: 1858
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
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 51
Master_UUID: 93909ace-1b58-11ef-81d8-000c2912a662
Master_Info_File: mysql.slave_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:
Retrieved_Gtid_Set: 93909ace-1b58-11ef-81d8-000c2912a662:3-7
Executed_Gtid_Set: 93909ace-1b58-11ef-81d8-000c2912a662:1-7
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
三、主从复制监控
1.主库监控
show processlist;
mysql> show processlist;
+-----+-----------------+-----------------+------+-------------+-------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------------+------+-------------+-------+---------------------------------------------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 93989 | Waiting on empty queue | NULL |
| 854 | repl | localhost:41398 | NULL | Binlog Dump | 18657 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 855 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 857 | repl | localhost:41402 | NULL | Binlog Dump | 18457 | Master has sent all binlog to slave; waiting for more updates | NULL |
+-----+-----------------+-----------------+------+-------------+-------+---------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)
show slave hosts;
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 53 | | 3308 | 51 | d72a973f-1464-11ef-89ef-000c2912a662 |
| 52 | | 3307 | 51 | cf2051af-1464-11ef-bb21-000c2912a662 |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)
2.从库监控
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 1841
Relay_Log_File: localhost-relay-bin.000004
Relay_Log_Pos: 361
Relay_Master_Log_File: binlog.000003
Slave_IO_Running: Yes //——Yes不一定没问题
Slave_SQL_Running: Yes //——Yes不一定没问题
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: 1841
Relay_Log_Space: 1858
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
Last_IO_Errno: 0 //——可看log-error文件
Last_IO_Error: //——可看log-error文件
Last_SQL_Errno: 0 //——可看log-error文件
Last_SQL_Error: //——可看log-error文件
Replicate_Ignore_Server_Ids:
Master_Server_Id: 51
Master_UUID: 93909ace-1b58-11ef-81d8-000c2912a662
Master_Info_File: mysql.slave_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:
Retrieved_Gtid_Set: 93909ace-1b58-11ef-81d8-000c2912a662:3-7 //——GTID复制状态
Executed_Gtid_Set: 93909ace-1b58-11ef-81d8-000c2912a662:1-7 //——GTID复制状态
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
四、主从复制故障原因分析
1.连接主库出现问题
1）连接信息有误
2）网络故障
3）防火墙
4）最大连接数上限
2.请求日志问题
1）主库日志损坏
2）日志起点写错
3）从库和主库的server_id重复（server_id和server_uuid都不可以重复）
3.主库执行了reset master，从库的处理办法
stop slave；
reset slave all；
CHANGE MASTER TO：
...........
MASTER_LOG_FILE=binlog.00001（日志归一）
...........
start slave；
4.日志回放失败，执行不了SQL
重新构建主从，把握一个原则，一切以主库为主
五、主从复制延时
1.主从复制是否延时的判断
1）从库：show slave status：
0不代表绝对没延时，根据这一个参数判断不准确，只能用做参考
2）更准确的方法：查看主库从库binlog日志是否一致，文件和position都一致代表没有延时
主库：
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000003 | 2483 | | | 93909ace-1b58-11ef-81d8-000c2912a662:1-8 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
从库：
mysql> show slave status\G;
*************************** 1. row ***************************
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 2483
Relay_Master_Log_File: binlog.000003
Exec_Master_Log_Pos: 2483
2.主库方面可能原因
1）提供binlog时产生问题：
binlog日志写入磁盘不及时——>及时写入磁盘——>sync_binlog=1
2）传输binlog时产生问题：
——>开启GTID功能，提高dump线程并行传输能力
3.从库方面可能原因
1）relay log落地出现问题
2）SQL线程回放慢：
由于SQL线程只有一个，只能串行回放relay log，由于主库可以并发事务，可以并行传输日志，如果出现大事务，并发事务量大，都会导致从库较高的回放延时。
——>5.6版本GTID模式下，可以开启多个SQL线程，但5.6多SQL回放时，只能针对不同库回放，功能较为鸡肋
——>6.7版本GTID模式下，可以开启多个SQL线程，真正实现了并行回放
4.外部因素
网络速度慢
主从配置相差大
六、过滤复制
1.介绍
部分数据复制
级联过滤复制：
双主过滤复制：
2.配置方法
1）主库设置只写入某库的日志（很少用）
Binlog_Do_DB 白名单
Binlog_Ignore_DB 黑名单
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000003 | 2483 | | | 93909ace-1b58-11ef-81d8-000c2912a662:1-8 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
2）从库的过滤复制是在从库的SQL线程控制的，SQL只回放部分日志，实现了过滤
库级别复制：
replicate_do_db=world
replicate_do_db=yizuo ——>多库写多行
replicate_ignore_db=test
表级别复制：
replicate_do_table=world.city
replicate_ignore_table=
模糊：
replicate_wild_do_table=world.c*
replicate_wild_ignore_table=
3.模拟操作
1）修改从库配置文件，重启从库
2）查看从库状态，确认过滤库被配置成功
3）主库在yizuo库执行create table city_bak1操作
mysql> CREATE TABLE `city_bak1` (
-> `id` int NOT NULL AUTO_INCREMENT COMMENT '编号',
-> `name` varchar(100) DEFAULT NULL COMMENT '城市名',
-> `province` varchar(100) DEFAULT NULL,
-> `population` varchar(100) DEFAULT NULL COMMENT '人口',
-> `district` varchar(100) DEFAULT NULL COMMENT '街区',
-> PRIMARY KEY (`id`),
-> KEY `idx_name` (`name`),
-> KEY `idx_nn` (`name`(5)) /*!80000 INVISIBLE */
-> ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-----------------+
| Tables_in_yizuo |
+-----------------+
| city |
| city_bak |
| city_bak1 |
+-----------------+
3 rows in set (0.00 sec)
4）确认主库日志状态和从库日志状态
binlog日志和position应该是一致的，这是因为从库会正常把binlog日志拿过来，但是SQL线程会过滤回放
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000003 | 3125 | | | 93909ace-1b58-11ef-81d8-000c2912a662:1-9 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 3125
Relay_Log_File: localhost-relay-bin.000007
Relay_Log_Pos: 1003
Relay_Master_Log_File: binlog.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: world
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: 3125
5）确认从库SQL线程过滤成功
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| world |
| yizuo |
+--------------------+
6 rows in set (0.00 sec)
mysql> use yizuo;
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_yizuo |
+-----------------+
| city |
| city_bak |
+-----------------+
2 rows in set (0.00 sec)
七、延时从库
1.介绍
人为配置的一种特殊从库，主库变更，在延时时间过后，从库才执行
2.为什么要有延时从库？
逻辑损坏：DROP、DELETE、TRUNCATE、UPDATE——>延时从库可以解决
物理损坏：磁盘、文件损坏——>主从复制可解决
3.配置延时
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_DELAY=300; //单位：秒
Query OK, 0 rows affected (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
show slave status\G;
*************************** 1. row ***************************
SQL_Delay: 300 //延时时间
SQL_Remaining_Delay: NULL //最近的一个sql还有多少秒执行
4.延时从库实战模拟
1）主库drop
mysql> drop city_bak7;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'city_bak7' at line 1
mysql> drop table city_bak7;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| city_bak |
| city_bak2 |
| city_bak3 |
| city_bak4 |
| city_bak5 |
| city_bak6 |
| city_bak88 |
+-----------------+
8 rows in set (0.00 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000003 | 5807 | | | 93909ace-1b58-11ef-81d8-000c2912a662:1-15 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
2）监控故障
3）挂维护页
4）停主从：确认一下日志都过来了再停主从
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 5807
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: binlog.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: world
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: 4613
5）修复数据：模仿SQL线程回放relay，回放到drop之前，使用relay修复数据
起点：relay-log.info SQL线程上一次执行到的位置点
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 321
终点：drop之前
show relaylog events in 'localhost-relay-bin.000002';
mysql> show relaylog events in 'localhost-relay-bin.000002';
+----------------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| localhost-relay-bin.000002 | 4 | Format_desc | 52 | 125 | Server ver: 8.0.20, Binlog ver: 4 |
| localhost-relay-bin.000002 | 125 | Previous_gtids | 52 | 156 | |
| localhost-relay-bin.000002 | 156 | Rotate | 51 | 0 | binlog.000003;pos=4613 |
| localhost-relay-bin.000002 | 200 | Format_desc | 51 | 0 | Server ver: 8.0.20, Binlog ver: 4 |
| localhost-relay-bin.000002 | 321 | Gtid | 51 | 4692 | SET @@SESSION.GTID_NEXT= '93909ace-1b58-11ef-81d8-000c2912a662:13' |
| localhost-relay-bin.000002 | 400 | Query | 51 | 5256 | use `world`; CREATE TABLE `city_bak88` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '编号',
`name` varchar(100) DEFAULT NULL COMMENT '城市名',
`province` varchar(100) DEFAULT NULL,
`population` varchar(100) DEFAULT NULL COMMENT '人口',
`district` varchar(100) DEFAULT NULL COMMENT '街区',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_nn` (`name`(5)) /*!80000 INVISIBLE */
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /* xid=935 */ |
| localhost-relay-bin.000002 | 964 | Gtid | 51 | 5335 | SET @@SESSION.GTID_NEXT= '93909ace-1b58-11ef-81d8-000c2912a662:14' |
| localhost-relay-bin.000002 | 1043 | Query | 51 | 5411 | BEGIN |
| localhost-relay-bin.000002 | 1119 | Table_map | 51 | 5485 | table_id: 202 (world.city_bak88) |
| localhost-relay-bin.000002 | 1193 | Write_rows | 51 | 5563 | table_id: 202 flags: STMT_END_F |
| localhost-relay-bin.000002 | 1271 | Xid | 51 | 5594 | COMMIT /* xid=936 */ |
| localhost-relay-bin.000002 | 1302 | Gtid | 51 | 5671 | SET @@SESSION.GTID_NEXT= '93909ace-1b58-11ef-81d8-000c2912a662:15' |
| localhost-relay-bin.000002 | 1379 | Query | 51 | 5807 | use `world`; DROP TABLE `city_bak7` /* generated by server */ /* xid=940 */ |
+----------------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)
截取relay-log：
mysqlbinlog --start-position=321 --stop-position=1379 localhost-relay-bin.000002 >/tmp/relay.sql重设置从库
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.02 sec)
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
恢复relay-log到从库
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/relay.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Charset changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
6）业务恢复，从库替代主库工作
7）主从修复
八、半同步复制及无损复制
1.——5.6版本
gtid传输，并行传输日志，串行SQL，可以缓解
2.——5.7版本
增强半同步复制，无损复制
3.——5.8版本
MGR高可用方案更加完善（推荐使用）
九、GTID主从复制
1.优势
1）每个事务都有唯一逻辑编号，并具备幂等性
2）截取binlog时更加灵活，方便（--include-gtids，--exclude-gtids）
3）主从复制时，可以并行传输，dump传输日志并行，SQL线程回放并行
4）主从复制搭建、监控延时都很方便，更好的保证了主从一致性
2.构建（略）
3.GTID主从复制构建时的不同点
在change master to时加入MASTER_AUTO_POSITION=1
加入后，在第一次构建主从时，会自动检查最后一个relaylog的gtid信息，如果没有gtid，会自动检查有没有GLOBAL_GTID_PURGED=‘server_uuid:1-10’参数，如果两个都没有，就从主库的第一个GTID事件开始全新复制binlog日志
也会自动检查relaylog的gtid和binlog的最后一个gtid是否一致，如不一致，会自动去请求主库的binlog日志
注意：备份主库数据，恢复至从库的方式构建gtid主从，不要set_gtid_purged=off
4.GTID主从复制监控
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 5991
Relay_Log_File: localhost-relay-bin.000005
Relay_Log_Pos: 4467
Relay_Master_Log_File: binlog.000003
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:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 5991
Relay_Log_Space: 4885
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
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error: //gtid复制错误日志
Replicate_Ignore_Server_Ids:
Master_Server_Id: 51
Master_UUID: 93909ace-1b58-11ef-81d8-000c2912a662
Master_Info_File: mysql.slave_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:
Retrieved_Gtid_Set: 93909ace-1b58-11ef-81d8-000c2912a662:3-16 //接收到的GTID
Executed_Gtid_Set: 93909ace-1b58-11ef-81d8-000c2912a662:1-16 //执行过的GTID
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
十、主从复制架构演变
1.一主一从
2.一主多从
3.双主（都可读写）
4.多级主从
5.高级架构
