Mysql主从复制模式
主从复制有一主一从、主主复制、一主多从、多主一从等多种模式. 我们可以根据它们的优缺点选择适合自身企业情况的主从复制模式进行搭建 .
- 一主一从
- 主主复制 (互为主从模式): 实现Mysql多活部署
- 一主多从: 提高整个集群的读能力
- 多主一从: 提高整个集群的高可用能力
- 级联复制: 减轻主节点进行数据同步的压力
MySQL 主从复制原理
MySQL
服务的主从架构都是通过
binlog
日志文件来进行的。
具体流程如下：
- 在主服务上打开binlog记录每一步的数据库操作
- 然后，从服务上会有一个IO线程，负责跟主服务建立一个TCP连接，请求主服务将binlog传输过来
- 这时，主库上会有一个IO dump线程，负责通过这个TCP连接把binlog日志传输给从库的IO线程
- 主服务器MySQL服务将所有的写操作记录在 binlog 日志中，并生成 log dump 线程，将 binlog 日志传给从服务器MySQL服务的 I/O 线程。
- 接着从服务的IO线程会把读取到的binlog日志数据写入自己的relay日志文件中。
- 然后从服务上另外一个SQL线程会读取relay日志里的内容，进行操作重演，达到还原数据的目的。
注意：
- 主从复制是异步的逻辑的 SQL 语句级的复制
- 复制时，主库有一个 I/O 线程，从库有两个线程，即 I/O 和 SQL 线程
- 实现主从复制的必要条件是主库要开启记录 binlog 的功能
- 作为复制的所有 MySQL 节点的 server-id 都不能相同
- binlog 文件只记录对数据内容有更改的 SQL 语句，不记录任何查询语句
- 双方MySQL必须版本一致，至少需要主服务的版本低于从服务
- 两节点间的时间需要同步
MySQL 主从复制方式
MySQL5.6 开始主从复制有两种方式：
- 基于 binlog 日志的Pos，进行主从复制
- 基于 GTID（全局事务标示符），进行主从复制
接下来，根据一主一从来配置主从复制的方式。
案例：基于Pos主从复制
主服务器配置
第一步：开启 binlog 日志
查询 binlog 日志是否开启
mysql> show variables like 'log_bin%';
MySQL5.7 版本中，binlog默认是关闭的，8.0版本默认是打开的，打开binlog功能，需要修改配置文件my.ini(windows)或my.cnf(linux)，然后重启数据库。
在配置文件中的[mysqld]部分增加如下配置:
# binlog刷盘策略
sync_binlog=1
# log-bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则binlog文件默认会放在data数据目录下
log-bin=mysql-binlog
# 其他配置
binlog_format = row # 日志文件格式，下面会详细解释
expire_logs_days = 15 # 执行自动删除距离当前15天以前的binlog日志文件的天数， 默认为0， 表示不自动删除
max_binlog_size = 800M # 单个binlog日志文件的大小限制，默认为 1GB再次查询 binlog 日志是否开启
第二步：修改my.cnf文件 ,添加服务id
# Server Id是数据库服务器id，随便写一个数都可以，这个id用来在mysql集群环境中标记唯一mysql服务器，集群环境中每台mysql服务器的id不能一样，不加启动会报错
server-id=130
第三步：重启
mysql
服务
[root@xc0tfmuy0yaql06x ~]# systemctl restart mysqld
第四步：主机给从机授备份权限
登陆mysql
[root@xc0tfmuy0yaql06x ~]# mysql -uroot -p登陆后,授权.
mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' identified by 'root';
第五步：刷新权限
mysql> FLUSH PRIVILEGES;
第六步：查询
master
的状态
mysql> show master status;
从服务器配置
第一步：修改
my.conf
文件
[mysqld]
server-id=131
第二步：重启
mysqld
服务
systemctl restart mysqld 1
第三步：重启并登录到
MySQL
进行配置
Slave
mysql>change master to
master_host='113.125.182.96',
master_port=3306,
master_user='root',
master_password='root',
master_log_file='mysql-binlog.000002',
master_log_pos=589,
MASTER_AUTO_POSITION=0;
注意：
语句中间不要断开，
master_port
为
MySQL
服务器端口号（无引号），
master_user
为
执行同步操作的数据库账户，
593
无单引号（此处的
1109
就是
show master status
中看到的
position
的值，这里的
mysql
-
bin.000001
就是
file
对应的值）。
第四步：启动从服务器复制功能
mysql>start slave;
第五步：检查从服务器复制功能状态
mysql> show slave status \G;
……………………(省略部分)
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
……………………(省略部分)
注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均 属错误)。
测试
通过Pos配置的主从复制已全部完成,现在我们通过测试,看是否配置成功.
在主服务器中,选择自己已创建的数据库
mysql> use gorgor;在主服务器中, 创建表
mysql> CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
`create_time` datetime NOT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;在主服务器中,查看表的信息.
在从服务器中,查看表的信息
发现已同步过去从库了,代表配置成功了.
案例：基于GTID的主从复制
什么是
GTID
？
从
MySQL 5.6.5
开始新增了一种基于
GTID
的复制方式。
GTID
即全局事务
ID
（
Global Transaction
Identifier
），其保证每个主节点上提交的事务，在从节点可以一致性的复制。
这种方式强化了数据库的主备一致性，故障恢复以及容错能力。
GTID
在
一主一从
情况下没有优势，对于两主以上
的结构优势异常明显，可以在数据不丢失的情况下切换新主。
GTID
实际上是由
UUID+TID (
即
transactionId)
组成的，其中
UUID(
即
server_uuid)
产生于
auto.conf
文
件，是一个
MySQL
实例的唯一标识。
TID
代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以
GTID
能够保证每个
MySQL
实例事务的执行。
GTID
在一组复制中，全局唯一。
通过
GTID
的UUID
可以知道这个事务在哪个实例上提交的。
server_uuid 查询命令
[root@xc0tfmuy0yaql06x ~]# cat /var/lib/mysql/auto.cnf;
查询GTID
mysql> show master status;mysql> show master status;
+---------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
| mysql-binlog.000006 | 4874 | | | f93b2c7b-8a01-11ef-b45c-fa163ec11a89:1-6
+---------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)Executed_Gtid_Set 就是GTID,
UUID: f93b2c7b-8a01-11ef-b45c-fa163ec11a89
TID: 1-6
TID
是在该主库上生成的事务序列号，从1
开始，
1
-6
代表第六个事务；第
1
-
n
代表
n
个事
务.
GTID复制的限制：
由于基于GTID的复制依赖于事务，在使用GTID时有些MySQL特性则不支持。
1. 事务中混合多个存储引擎会产生多个GTID。
当使用GTID时候若在同一事务中更新包括了非事务引擎如MyisAM和事务性引擎InnoDB表的操作则会导致GTID分配给同一个事务。
2.主从库的表存储引擎不一致则会导致数据不一致
若主从库的存储引擎不一致，比如一个是事务存储引擎一个是非事务存储引擎 则会导致事务和GTID之间的一对一的关系被破坏，导致基于GTID的复制不能正确运行。
3.基于GTID模式复制 不支持create table .. select 语句：
因为使用基于行模式的复制时该语句实际上被记录为2个单独的事件，一个是创建表，一个是将原表中的数据插入到刚刚创建的新表中。当在事务中执行该语句时候在某些情况下，这两个事务可能接受到相同的事务ID，这意味着包含插入的事务将被从库跳过。因此不支持此语句.
4.不支持create temporary table和drop temporary table：
使用GTID复制模式时，不支持create temporary table 和 drop temporary table。但是在autocommit=1的情况下可以创建临时表，master创建临时表不产生GTID信息，所以不会同步到slave，但是在删除临时表的时候会产生GTID会导致主从中断。
5.不推荐在GTID模式的实例下进行mysql_upgrade:
因为mysql_upgrade的过程要创建或修改系统表(非事务引擎)，所以不建议在开启GTID模式的实例上使用带有 --write-binlog选项的mysql_upgrade。
GTID
主从复制原理
1. Master
更新数据时，会在事务前产生
GTID
一同记录到
binlog
日志中
2. Slave
的
IO Thread
将变更后的
binlog
写入到本地的
relaylog
中，这其中含有
Master
的
GTID
3.
SQL Thread
读取这个
GTID
的值并设置
GTID_NEXT
变量，告诉
Slave
下一个要执行的
GTID
值，然后对比
Slave
端的
binlog
是否有该
GTID
- 如果有，说明该 GTID 的事务已经执行 Slave 会忽略
- 如果没有，Slave 就会执行该 GTID 事务，并记录该 GTID 到自身的 binlog
4.
在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有二级索引就用全表扫描
搭建GTID同步集群
他的搭建方式跟我们上面的主从架构整体搭建方式差不多。只是需要在
my.cnf
中修改一些配置。
主服务器配置
gtid_mode=on
enforce_gtid_consistency=on从服务器配置
gtid_mode=on
enforce_gtid_consistency=on
# 做级联复制的时候，再开启。允许下端接入slave
log_slave_updates=1
# 避免启动后还是使用老的复制协议
skip_slave_start=1在从库上，使用--skip-slave-start选项启动从数据库，也可以在my.cnf中加入skip-slave-start配置，这样不会立即启动从数据库服务器上的复制进行，方便做进一步配置。
启动
GTID
的两种情况
分别重启主服务和从服务，就可以开启
GTID
同步复制，启动方法有两种情况
情况一：如果是新搭建的服务器，直接启动就行了
情况二：如果是在已经跑的服务器，需要重启
mysqld
- 启动之前要先关闭master的写入，保证所有slave端都已经和master端数据保持同步
- 所有slave需要加上skip_slave_start=1的配置参数，避免启动后还是使用老的复制协议
# 避免启动后还是使用老的复制协议
skip_slave_start=1
使用
GTID
的方式，
salve
重新挂载
master
端：
启动以后最好不要立即执行事务，先
change master
上，然后在执行事务。
使用下面的
sql
切换
slave
到新的
master
。
# 停止从节点
stop slave;
# 切换主节点配置，比基于pos简单不少
change master to
master_host='113.125.182.96',
master_port=3306,
master_user='root',
master_password='root',
master_auto_position=1;
# 启动从节点
start slave;
检查从服务器复制功能状态
mysql> show slave status \G;
……………………(省略部分)
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
……………………(省略部分)
注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均 属错误)。
异常
此时,通过结果发现, mysql从Pos模式切到GTID模式后启动主从，主从异常报错1236
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 113.125.182.96
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: VM-20-3-centos-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
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: 0
Relay_Log_Space: 154
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: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID set sent by the slave is '', and the missing transactions are '458fa628-89dc-11ef-809d-00163e019b7a:1'.'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 130
Master_UUID: f93b2c7b-8a01-11ef-b45c-fa163ec11a89
Master_Info_File: /var/lib/mysql/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: 241015 18:04:31
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
异常信息
Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID set sent by the slave is '', and the missing transactions are '458fa628-89dc-11ef-809d-00163e019b7a:1'.'解决办法(从节点从来没有过gtid 值的时候）
在主节点执行：
mysql> show variables like '%gtid_purged%';
+---------------+----------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------+
| gtid_purged | 458fa628-89dc-11ef-809d-00163e019b7a:1 |
+---------------+----------------------------------------+
1 row in set (0.00 sec)
然后再在从节点设置gtid_purged为主节点gtid_purged值。
stop slave;
set global gtid_purged="458fa628-89dc-11ef-809d-00163e019b7a:1";
start slave;
show slave status\G然后通过show slave status\G,发现Slave_IO及Slave_SQL进程都正常运行，即YES状态.
测试
在主服务器中,插入一条数据
INSERT INTO `gorgor`.`user`(`id`, `name`, `age`, `create_time`, `update_time`) VALUES (2, 'fairy', 28, '2024-10-15 17:47:56', NULL);在从服务器中,查看表的数据,可以查出此数据
mysql> select * from user;
+----+--------+-----+---------------------+-------------+
| id | name | age | create_time | update_time |
+----+--------+-----+---------------------+-------------+
| 1 | gorgor | 30 | 2024-10-15 15:56:25 | NULL |
| 2 | fairy | 28 | 2024-10-15 17:47:56 | NULL |
+----+--------+-----+---------------------+-------------+
2 rows in set (0.00 sec)
![[CTF夺旗赛] CTFshow Web13-14 详细过程保姆级教程~](https://i-blog.csdnimg.cn/direct/378ca64e7e1c42eaa0b85938a2c6bf7c.png#pic_center)
