目录
一、主从复制原理
1.简要原理
2.涉及到的文件
3.涉及到的线程
4.主从复制执行步骤（重点）
二、主从复制搭建
1.准备两台以上的数据库实例，要求数据库版本一致
2.区分不同角色
3.主库开启二进制日志
4.主库创建专用复制用户，赋予权限
5.备份主库数据，恢复从库
6.开启从库复制功能
7.检查两个从库的主从状态，双Yes代表主从正常
三、主从复制监控
1.主库监控
2.从库监控
四、主从复制故障原因分析
1.连接主库出现问题
2.请求日志问题
3.主库执行了reset master，从库的处理办法
4.日志回放失败，执行不了SQL
五、主从复制延时
1.主从复制是否延时的判断
2.主库方面可能原因
3.从库方面可能原因
4.外部因素
六、过滤复制
1.介绍
2.配置方法
3.模拟操作
七、延时从库
1.介绍
2.为什么要有延时从库？
3.配置延时
4.延时从库实战模拟
八、半同步复制及无损复制
1.——5.6版本
2.——5.7版本
3.——5.8版本
九、GTID主从复制
1.优势
2.构建（略）
3.GTID主从复制构建时的不同点
4.GTID主从复制监控
十、主从复制架构演变
1.一主一从
2.一主多从
3.双主（都可读写）
4.多级主从
5.高级架构
一、主从复制原理
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.准备两台以上的数据库实例，要求数据库版本一致
参考：MySQL多实例-CSDN博客
2.区分不同角色
51主库
52、53从库
3.主库开启二进制日志
4.主库创建专用复制用户，赋予权限---有坑
创建用户的时候地址要注意，grant赋权也要注意，要用GRANT ALL PRIVILEGES ON *.* TO 'replmha'@'%' WITH GRANT OPTION
mysql>create user 'replmha'@'%' IDENTIFIED with mysql_native_password BY 'ok';
Query OK, 0 rows affected (0.00 sec)mysql> select user,host,authentication_string,plugin from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| replmha | % | $A$005$)fcB"IXZmo{qr%)71hR72t5sUOD3H27kNo8uGnWX8/mkwadbMlpTdSyw9B | caching_sha2_password |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| repl | localhost | *31330A9B24799CC9566A39CBD78CEF60E26C906F | mysql_native_password |
| root | localhost | | caching_sha2_password |
| yizuo | localhost | *31330A9B24799CC9566A39CBD78CEF60E26C906F | mysql_native_password |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
7 rows in set (0.00 sec)mysql> GRANT ALL PRIVILEGES ON *.* TO 'replmha'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)mysql> show grants for 'replmha'@'%';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for replmha@% |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `replmha`@`%` WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `replmha`@`%` WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)mysql> show master status;
+---------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000005 | 1111 | | | 93909ace-1b58-11ef-81d8-000c2912a662:1-24 |
+---------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
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;
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 eventMaster_Host: localhostMaster_User: replMaster_Port: 3306Connect_Retry: 10Master_Log_File: binlog.000003Read_Master_Log_Pos: 1841Relay_Log_File: localhost-relay-bin.000004Relay_Log_Pos: 361Relay_Master_Log_File: binlog.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 1841Relay_Log_Space: 1858Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 51Master_UUID: 93909ace-1b58-11ef-81d8-000c2912a662Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 93909ace-1b58-11ef-81d8-000c2912a662:3-7Executed_Gtid_Set: 93909ace-1b58-11ef-81d8-000c2912a662:1-7Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_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 eventMaster_Host: localhost Master_User: replMaster_Port: 3306Connect_Retry: 10Master_Log_File: binlog.000003Read_Master_Log_Pos: 1841Relay_Log_File: localhost-relay-bin.000004Relay_Log_Pos: 361Relay_Master_Log_File: binlog.000003Slave_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: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 1841Relay_Log_Space: 1858Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_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: 51Master_UUID: 93909ace-1b58-11ef-81d8-000c2912a662Master_Info_File: mysql.slave_master_infoSQL_Delay: 0 //——延时从库的状态SQL_Remaining_Delay: NULL //——延时从库的状态Slave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_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: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace:
1 row in set (0.00 sec)
四、主从复制故障原因分析
1.连接主库出现问题
1）连接信息有误 （注意创建的用户权限，GRANT ALL PRIVILEGES ON）
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.000003Read_Master_Log_Pos: 2483Relay_Master_Log_File: binlog.000003Exec_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 eventMaster_Host: localhostMaster_User: replMaster_Port: 3306Connect_Retry: 10Master_Log_File: binlog.000003Read_Master_Log_Pos: 3125Relay_Log_File: localhost-relay-bin.000007Relay_Log_Pos: 1003Relay_Master_Log_File: binlog.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: worldReplicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_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 -ADatabase 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 eventMaster_Host: localhostMaster_User: replMaster_Port: 3306Connect_Retry: 10Master_Log_File: binlog.000003Read_Master_Log_Pos: 5807Relay_Log_File: localhost-relay-bin.000002Relay_Log_Pos: 321Relay_Master_Log_File: binlog.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: worldReplicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_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 eventMaster_Host: localhostMaster_User: replMaster_Port: 3306Connect_Retry: 10Master_Log_File: binlog.000003Read_Master_Log_Pos: 5991Relay_Log_File: localhost-relay-bin.000005Relay_Log_Pos: 4467Relay_Master_Log_File: binlog.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 5991Relay_Log_Space: 4885Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: //gtid复制错误日志Replicate_Ignore_Server_Ids: Master_Server_Id: 51Master_UUID: 93909ace-1b58-11ef-81d8-000c2912a662Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_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 //接收到的GTIDExecuted_Gtid_Set: 93909ace-1b58-11ef-81d8-000c2912a662:1-16 //执行过的GTIDAuto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace:
十、主从复制架构演变
1.一主一从
2.一主多从
3.双主（都可读写）
4.多级主从
5.高级架构
