MySQL主从复制原理、实践和常见问题
2023-09-27 14:25:41 时间
码字不易，转载请附原链，搬砖繁忙回复不及时见谅，技术交流请加QQ群：909211071
原 理
目 的
复制文件
binlog
relay log
mysql-bin.index
mysql-relay-bin-index
master.info
rekay-log.info
关于同步格式ROW和STATEMENT
基于语句的复制（STATEMENT）
基于行的复制（ROWS）
配置主从
安装MySQL
master
slave
测试主从
主库：
从库：
同步状态关键指标
当前同步文件
当前同步pos点位
表示同步第二阶段开始，到第三阶段结束之间的延时
全局事务id
实验
解决主备延迟
方案一：直接读主库
方案二：开启半同步（semi-sync）
半同步流程
开启半同步
方案三：判断pos位点
方案四：判断gtid全局事务id
通过并行复制提高relaylog重放效率
原 理
-
主服务器上面的任何修改都会通过自己的 I/O tread(I/O 线程)保存在二进制日志
Binary log里面。 -
从服务器上面也启动一个 I/O thread，通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个
Relay log（中继日志）里面。 -
从服务器上面同时开启一个 SQL thread 定时检查
Relay log(这个文件也是二进制的)，如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。
目 的
- 横向扩展，读写分离，提高服务性能
- 定期同步，实现数据备份
- 离线分析分离，隔离对线上业务的影响
复制文件
binlog
主库将自己的更改根据同步格式记录到binlog中，由从库的 IO 线程连接到主库读取binlog。
relay log
将从库接收到的 binlog 写到本地的relay log中，从库通过SQL线程定时检查relay log，发现更改后则进行重放。
mysql-bin.index
在服务器上开启二进制日志时，同时会生成一个和二进制日志同名的但以.index结尾的文件，这个文件的每一行包含了二进制文件的文件名。MySQL依赖于这个文件来识别二进制日志文件。
mysql-relay-bin-index
中继日志索引文件，和mysql-bin.index作用类似。
master.info
保存从库连接到主库所需要的信息，以文本的方式记录复制用户的密码，所以要注意此文件的权限控制。
rekay-log.info
保存了当前从库复制的二进制日志和中继日志坐标，在从库重启后用于获知从哪个位置开始复制，删除后可能会导致重放执行过的语句。
关于同步格式ROW和STATEMENT
基于语句的复制（STATEMENT）
在基于语句的复制模式下，主库会记录造成数据修改的SQL，当从库读取并重放这些SQL时，就是把这些SQL再执行一遍，这种方式既有优点也有缺点。
优点：
- 基于SQL执行复制，定位问题简单。
- 大部分情况下节约IO成本，比如update全表时仅需要传递一条SQL
缺点：
- 从库更新一个不存在的记录时不会失败，容易错过同步问题。
- 如果使用触发器或存储过程，容易遇到未知问题。
基于行的复制（ROWS）
优点：
- 从库更新一个不存在的记录时会报错并停止复制。
- 能够清晰地知道服务器上发生了哪些更改，更有利于某些数据的恢复。
缺点：
- 由于要记录每一行的更改，所以IO大部分情况下相比基于语句的复制要大。
- 无法判断执行了哪些SQL。
结论：有大范围的更新（一般线上很少），人为的去执行，在执行前，把当前session设置成STATEMENT，其余的都用ROW。
分析过程参考：https://www.cnblogs.com/zhoujinyi/archive/2013/01/15/2836131.html
配置主从
安装MySQL
1、下载linux通用源码：wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.18.tar.gz
2、解压压缩包：tar -zxvf mysql-8.0.18.tar.gz
3、安装依赖工具：yum install cmake gcc-c++ ncurses-devel perl-Data-Dumper boost boost-doc boost-devel git
4、删除CMakeCache.txt
5、使用cmake配置：
sudo cmake \
-DWITH_DEBUG=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_TCP_PORT=3306 \
-DFORCE_INSOURCE_BUILD=1 \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/usr/local/boost \
-DSYSCONFDIR=/etc \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/run/mysql.sock \
在makefile的开头定义CFLAGS 变量：CFLAGS = -g ，否则调试过程中无法跟踪代码
6、编译&&安装：make && make install
7、cd /usr/local/mysql/bin/
8、./mysqld --initialize --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql --user=mysql
9、创建etc目录并移动support-files中的配置文件（hu），更名为my.cnf
10、mysqld --skip-grant-tables
11、直接 mysql 跳过验证登陆
12、将root用户验证设置为空
use mysql
update user set authentication_string="" where user="root";
exit13、/usr/local/mysql/support-files/mysql.server start 重新开启 mysql 服务
14、无需输入密码，直接回车登陆
/usr/local/mysql_slave/bin/mysql -uroot -p --socket=/usr/local/mysql/run/mysql.sock14、更改本地root用户登陆密
alter user 'root'@'localhost' IDENTIFIED with mysql_native_password by '123456';
flush privileges;
master
配置文件：
[mysqld]
port=3306
mysqlx_port=33060
socket=/usr/local/mysql/run/mysql.sock
mysqlx_socket=/usr/local/mysql/run/mysqlx.sock
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
character-set-server=utf8
lower-case-table-names=2
default_authentication_plugin=mysql_native_password
# 主从复制-主机配置
server-id=1
gtid_mode=on
enforce_gtid_consistency=on
# 启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=sys
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
# 设置需要复制的数据库(可设置多个)
binlog-do-db=test
# 设置logbin格式
binlog_format=ROW启动服务：
nohup /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/3306.cnf &登陆主库，创建slave用户并授权：
/usr/local/bin/mysql -uroot -p --socket=/usr/local/mysql/run/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18 Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> create user 'slave'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'slave'@'%';
+-----------------------------------------------+
| Grants for slave@% |
+-----------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `slave`@`%` |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000012
Position: 155
Binlog_Do_DB: test
Binlog_Ignore_DB: sys,mysql,information_schema,performance_schema
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
slave
配置：
[mysqld]
port=3307
mysqlx_port=33070
socket=/usr/local/mysql_slave/run/mysql.sock
mysqlx_socket=/usr/local/mysql_slave/run/mysqlx.sock
basedir=/usr/local/mysql_slave
datadir=/usr/local/mysql_slave/data
character-set-server=utf8
lower-case-table-names=2
default_authentication_plugin=mysql_native_password
# 主从复制-从机配置
server-id=2
gtid_mode=on
enforce_gtid_consistency=on
# 启用中继日志
relay-log=mysql-relay启动从库：
nohup /usr/local/mysql_slave/bin/mysqld --defaults-file=/usr/local/mysql_slave/etc/3307.cnf &登陆并设置主库：
usr/local/mysql_slave/bin/mysql -uroot -p --socket=/usr/local/mysql_slave/run/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.18 Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3307 |
+---------------+-------+
1 row in set (0.02 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> change master to master_host='127.0.0.1',master_port=3306,master_user='slave',master_password='123456',master_log_file='mysql-bin.000012',master_log_pos=155;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 137.0.0.1
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 155
Relay_Log_File: mysql-relay.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000012
Slave_IO_Running: Connecting
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: 155
Relay_Log_Space: 155
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: 0
Master_UUID:
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 moooooooomoore updates
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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
测试主从
主库：
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`goods_id` bigint(20) unsigned NOT NULL,
`name` varchar(200) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx_goods` (`goods_id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into test values(1, 1, 1);
mysql> show master status\G;*************************** 1. row ***************************
File: mysql-bin.000012
Position: 1441
Binlog_Do_DB: test
Binlog_Ignore_DB: sys,mysql,information_schema,performance_schema
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> 从库：
mysql> select * from test;
+----+----------+------+
| id | goods_id | name |
+----+----------+------+
| 1 | 1 | 1 |
+----+----------+------+
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: 127.0.0.1
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 1441
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 1608
Relay_Master_Log_File: mysql-bin.000012
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: 1441
Relay_Log_Space: 1812
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: 1
Master_UUID: 9513a1b0-f5b4-11ea-930d-a5c900a3310f
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:
Executed_Gtid_Set:
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)
ERROR:
No query specified
mysql>
同步状态关键指标
当前同步文件
- 主库：File（mysql-bin.000012）
- 从库：Master_Log_File（ mysql-bin.000012）
当前同步pos点位
- 主库：Position（1441）
- 从库：Read_Master_Log_Pos（1441）
表示同步第二阶段开始，到第三阶段结束之间的延时
- Seconds_Behind_Master：值为0表示健康，值长时间非0则同步存在压力或网络存在延迟
全局事务id
主要观察从库两个id对比，用于发现从库执行主库事务是否被阻塞
- 主库：Executed_Gtid_Set 表示当前主库的最新的事务id
- 从库：Retrieved_Gtid_Set 表示接受到的最新事务id，Executed_Gtid_Set 表示从库当前执行完成的最新事务id
实验
mysql> show master status\G;*************************** 1. row *************************** File: mysql-bin.000013 Position: 3341 Binlog_Do_DB: test
Binlog_Ignore_DB: sys,mysql,information_schema,performance_schema
Executed_Gtid_Set: 9513a1b0-f5b4-11ea-930d-a5c900a3310f:1-10
1 row in set (0.00 sec)mysql> show slave status\G;
...省略...
Retrieved_Gtid_Set: 9513a1b0-f5b4-11ea-930d-a5c900a3310f:10
Executed_Gtid_Set: 266954d6-061b-11eb-9358-4150d020308f:1-2,
9513a1b0-f5b4-11ea-930d-a5c900a3310f:2:10
...省略...执行sql：
mysql> insert into test values(1, 1, 1);再次查看事务id：
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000013
Position: 3634
Binlog_Do_DB: test
Binlog_Ignore_DB: sys,mysql,information_schema,performance_schema
Executed_Gtid_Set: 9513a1b0-f5b4-11ea-930d-a5c900a3310f:1-11
1 row in set (0.00 sec)
ERROR:
No query specifiedmysql> show slave status\G;
...省略...
Retrieved_Gtid_Set: 9513a1b0-f5b4-11ea-930d-a5c900a3310f:10-11
Executed_Gtid_Set: 266954d6-061b-11eb-9358-4150d020308f:1-2,
9513a1b0-f5b4-11ea-930d-a5c900a3310f:2:10-11
...省略...解决主备延迟
方案一：直接读主库
写完主库，手动切换到主库进行select
方案二：开启半同步（semi-sync）
半同步流程
- 事务提交的时候，主库把binlog发给从库
- 从库收到binlog以后，发回给主库一个ack，表示收到了(多个从库主需要收到规定数量从库的确认即可， 数量受参数rpl_semi_sync_master_wait_for_slave_count 影响)
- 主库收到这个ack以后，给客户端返回“事务完成”的确认
开启半同步
默认在mysql配置的data目录下的lib/plugin目录里，作为*.so动态库存储，所以可在服务运行时加载
[why@whydeMacBook-Pro] /usr/local/mysql/lib/plugin$pwd
/usr/local/mysql/lib/plugin
[why@whydeMacBook-Pro] /usr/local/mysql/lib/plugin$ll | grep semi
-rwxr-xr-x 1 why staff 114056 9 13 19:09 semisync_master.so
-rwxr-xr-x 1 why staff 34880 9 13 19:09 semisync_slave.so
[why@whydeMacBook-Pro] /usr/local/mysql/lib/plugin$mysql> show variables like '%plugin%';
+-------------------------------+------------------------------+
| Variable_name | Value |
+-------------------------------+------------------------------+
| default_authentication_plugin | mysql_native_password |
| plugin_dir | /usr/local/mysql/lib/plugin/ |
+-------------------------------+------------------------------+
2 rows in set (0.00 sec)主服务安装：
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.02 sec)从服务安装：
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.02 sec)
主服务半同步配置：
mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)从服务半同步配置：
mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.02 sec)主服务开启半同步：
mysql> set global rpl_semi_sync_master_enabled = ON;
Query OK, 0 rows affected (0.01 sec)从服务开启半同步：
mysql> set global rpl_semi_sync_slave_enabled = ON;
Query OK, 0 rows affected (0.01 sec)方案三：判断pos位点
- 当前事务更新完成后,马上执行 show master status 得到当前主库执行到 的 File 和 Position
- 选定一个从库执行查询语句
- 在从库上执行select master_pos_wait(File, Position, 1)
- 如果返回值是>=0的正整数，则在这个从库执行查询语句否则，到主库执行查询语句或者放弃查询
方案四：判断gtid全局事务id
- 当前事务更新完成后，从返回包直接获取这个 事务的 GTID，记为 gtid1
- 选定一个从库执行查询语句;
- 在从库上执行select wait_for_executed_gtid_set(gtid1, 1)
- 如果返回值是0，则在这个从库执行查询语句 否则，到主库执行查询语句或者放弃
事务更新完再获取事务的gtid需要再对主库执行一次查询，通过更改客户端源码和会话级别变量 setsession_track_gtids，可以在更新成功之后直接返回gtid：
1、修改源码client/mysql.cc，make编译，替换mysql相关bin文件或做软链
mysql_session_track_get_first 方法定义在源码的 sql-common/client.cc 文件，将当前 gtid 存储在 data变量中。
2、数据库层开启每个会话时，设置 setsession_track_gtids=OWN_GTID
通过并行复制提高relaylog重放效率
1. MySQL的复制是基于binlog的。
2. MySQL复制包括两部分，IO线程 和 SQL线程。
3. IO线程主要是用于拉取接收Master传递过来的binlog，并将其写入到relay log
4. SQL线程主要负责解析relay log，并应用到slave中
5. 不管怎么说，IO和SQL线程都是单线程的，然后master却是多线程的，所以难免会有延迟，为了解决这个问题，多线程应运而生了。
6. IO多线程：IO没必要多线程，因为IO线程并不是瓶颈啊
7. SQL多线程：基于库并行，基于group commit并行，基于write-set并行详细参考这篇博客：https://mp.weixin.qq.com/s/oj-DzpR-hZRMMziq2_0rYg?spm=a2c6h.12873639.0.0.c491552bPwO056
相关文章
- Mysql都有那些最需要掌握的原理？
- MySQL JOIN原理
- 【阿里数据库面试题解】MySQL高可用原理
- MySQL+KeepAlived实现双主高可用方案实践
- 【MySQL】主从复制实现原理详解
- MySQL事务处理特性的实现原理
- Linux 操作MySQL常用命令行(转)
- mysql主从(单主）同步原理
- 一文彻底搞懂mysql binlog redolog undolog 作用原理以及运行机制
- mysql 核心日志 redolog、undolog、binlog
- mysql 索引原理
- PHP+MYSQL分页原理
- Entity Framework with MySQL 学习笔记一(复杂类型 Complex Types)
- mysql-cluster集群原理介绍和搭建步骤(四个data/sql节点) (转)
- Mysql 锁表 for update （引擎/事务）
- mysql优化 explain index
- mysql LIMIT 子句用法及原理
- 2023-03-08 MySQL源码分析-数据如何从SQL层传递到innodb引擎层
- 玩转Mysql系列 - 第22篇：mysql索引原理详解
- mysql数据库存储过程异常处理
- 重新开始学习编程系列Day10——超全的MySQL核心原理，从底层剖析MySQL
- 【mysql我能讲两小时023】说说innodb是如何处理一致性非锁定读的?mvcc原理?
- 【mysql我能讲两小时001】mysql使用的索引数据结构是什么?