共计 8592 个字符，预计需要花费 22 分钟才能阅读完成。

MySQL 主从同步原理

MySQL 主从同步是在 MySQL 主从复制 (Master-Slave Replication) 基础上实现的，通过设置在 Master MySQL 上的 binlog(使其处于打开状态)，Slave MySQL 上通过一个 I / O 线程从 Master MySQL 上读取 binlog，然后传输到 Slave MySQL 的中继日志中，然后 Slave MySQL 的 SQL 线程从中继日志中读取中继日志，然后应用到 Slave MySQL 的数据库中。这样实现了主从数据同步功能。

MySQL 中主从复制的优点

横向扩展解决方案
在多个从库之间扩展负载以提高性能。在这种环境中，所有写入和更新在主库上进行。但是，读取可能发生在一个或多个从库上。该模型可以提高写入的性能（由于主库专用于更新），同时在多个从库上读取，可以大大提高读取速度。
数据安全性
由于主库数据被复制到从库，从库可以暂停复制过程，可以在从库上运行备份服务，而不会破坏对应的主库数据。
分析
可以在主库上创建实时数据，而信息分析可以在从库上进行，而不会影响主服务器的性能。

Gtid 概念

从 MySQL 5.6.5 开始新增了一种基于 GTID 的复制方式。通过 GTID 保证了每个在主库上提交的事务在集群中有一个唯一的 ID。这种方式强化了数据库的主备一致性，故障恢复以及容错能力。

在原来基于二进制日志的复制中，从库需要告知主库要从哪个偏移量进行增量同步，如果指定错误会造成数据的遗漏，从而造成数据的不一致。借助 GTID，在发生主备切换的情况下，MySQL 的其它从库可以自动在新主库上找到正确的复制位置，这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制位置发生误操作的风险。另外，基于 GTID 的复制可以忽略已经执行过的事务，减少了数据发生不一致的风险。

什么是 Gitd

GTID (Global Transaction ID) 是对于一个已提交事务的编号，并且是一个全局唯一的编号。GTID 实际上是由 UUID+TID 组成的。其中 UUID 是一个 MySQL 实例的唯一标识。TID 代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。
下面是一个 GTID 的具体形式：
3E11FA47-71CA-11E1-9E33-C80AA9429562:23，冒号分割前边为 uuid，后边为 TID。

GTID 集合可以包含来自多个 MySQL 实例的事务，它们之间用逗号分隔。

如果来自同一 MySQL 实例的事务序号有多个范围区间，各组范围之间用冒号分隔。例如：
e6954592-8dba-11e6-af0e-fa163e1cf111:1-5:11-18,
e6954592-8dba-11e6-af0e-fa163e1cf3f2:1-27 可以使用 show master status 实时查看当前事务执行数

Gtid 的作用

Gtid 采用了新的复制协议，旧协议是，首先从服务器上在一个特定的偏移量位置连接到主服务器上一个给定的二进制日志文件，然后主服务器再从给定的连接点开始发送所有的事件。
新协议有所不同，支持以全局统一事务 ID (GTID)为基础的复制。当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务。GTID 复制是全部以事务为基础，使得检查主从一致性变得非常简单。如果所有主库上提交的事务也同样提交到从库上，一致性就得到了保证。

Gtid 的工作原理

①当一个事务在主库端执行并提交时，产生 GTID，一同记录到 binlog 日志中。
②binlog 传输到 slave, 并存储到 slave 的 relaylog 后，读取这个 GTID 的这个值设置 gtid_next 变量，即告诉 Slave，下一个要执行的 GTID 值。
③sql 线程从 relay log 中获取 GTID，然后对比 slave 端的 binlog 是否有该 GTID。
④如果有记录，说明该 GTID 的事务已经执行，slave 会忽略。
⑤如果没有记录，slave 就会执行该 GTID 事务，并记录该 GTID 到自身的 binlog，
在读取执行事务前会先检查其他 session 持有该 GTID，确保不被重复执行。
⑥在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

配置步骤

操作环境

 1. 系统：CentOS 7
2. 数据库：Percona MySQL 5.7
3. 主库：192.168.11.31
4. 从库：192.168.11.32

主库配置

 1.[mysqld]
2.datadir=/data/mysql/3306
3.socket=/tmp/mysql.sock
4.symbolic-links=0
5.
6.server_id=31                              # 服务器 ID
7.log-bin=master-bin                        #二进制日志文件名
8.binlog_format = row                       #强烈建议，其他格式可能造成数据不一致
9.log-slave-updates = 1                     #是否记录从服务器同步数据动作
10.gtid-mode = on                            #启用 gitd 功能
11.enforce-gtid-consistency = 1              #开启强制 GTID 一致性
12.master-info-repository = TABLE            #记录 IO 线程读取已经读取到的 master binlog 位置，用于 slave 宕机后 IO 线程根据文件中的 POS 点重新拉取 binlog 日志
13.relay-log-info-repository = TABLE         #记录 SQL 线程读取 Master binlog 的位置，用于 Slave 宕机后根据文件中记录的 pos 点恢复 Sql 线程
14.sync-master-info = 1                      #启用确保无信息丢失；任何一个事务提交后, 将二进制日志的文件名及事件位置记录到文件中
15.slave-parallel-workers = 2                #设定从服务器的复制线程数；0 表示关闭多线程复制功能
16.binlog-checksum = CRC32                   #设置 binlog 校验算法（循环冗余校验码）
17.master-verify-checksum = 1                #设置主服务器是否校验
18.slave-sql-verify-checksum = 1             #设置从服务器是否校验
19.binlog-rows-query-log_events = 1          #用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度
20.sync_binlog = 1                           #保证 master crash safe，该参数必须设置为 1 
21.innodb_flush_log_at_trx_commit = 1        #保证 master crash safe，该参数必须设置为 1

从库配置

 1.[mysqld]
2.server_id = 32
3.log-bin=mysql-bin
4.binlog_format = row
5.gtid-mode = on
6.enforce-gtid-consistency = 1
7.master-info-repository = TABLE
8.relay-log-info-repository = TABLE
9.sync-master-info = 1
10.slave-parallel-workers = 4
11.binlog-checksum = CRC32
12.master-verify-checksum = 1
13.slave-sql-verify-checksum = 1
14.binlog-rows-query-log_events = 1
15.#sync_binlog = 1
16.#innodb_flush_log_at_trx_commit = 1
17.log-slave-updates = 0 # crash safe slave 5.6 版本需要开启
18.relay_log_recovery = 1  # crash safe slave
19.read_only=on        #设置一般用户为只读模式
20.super_read_only=on       #设置 super（root）用户为只读模式
21.#tx_read_only=on     #设置事务为只读模式

主库权限设置

 1.mysql > grant replication slave on *.* to slave@'192.168.11.32' identified by 'slave123';
2.mysql > flush privileges;

自动同步连接主库(方法一)

适用于 master 也是新建不久的情况。
1、如果你的 master 所有的 binlog 还在。可以安装 slave，slave 直接 change master to 到 master 端。
2、原理是直接获取 master 所有的 GTID 并执行。
3、优点：简单方便。
4、缺点：如果 binlog 太多，数据完全同步需要时间较长，并且 master 一开始就启用了 GTUD。

 1.change master to master_host='192.168.11.31',\
2.master_user='slave',master_password='slave123',\
master_port=3306,master_auto_position=1
 
#master_auto_position=1 从库自动找同步点

备份导入连接主库(方法二)

1、Xtrabackup_binlog_info 文件中，包含 global.gtid_purged=’XXXXXX:XXXX’的信息。
2、然后到 slave 去手工的 SET @@GLOBAL.GTID_PURGED=’XXXXXX:XXXX’。
3、恢复备份，开启 change master to 命令。

备份导入连接主库(方法三)

适用于拥有较大数据的情况。（推荐）
1、通过 master 或者其他 slave 的备份搭建新的 slave。
2、原理：获取 master 的数据和这些数据对应的 GTID 范围，然后通过 slave 设置 master_auto_position=1, 自动同步，跳过备份包含的 gtid。
3、缺点：相对来说有点复杂。

将主库设为只读模式

注：生产环境会影响不能写入数据

 1.mysql> flush tables with read lock;
2.Query OK, 0 rows affected (0.00 sec)
3.
4.mysql> set global read_only=on;
5.Query OK, 0 rows affected (0.00 sec)

主库使用 mysqldump 导出

可以同时导出多个数据库，如 music、record

 1.mysqldump --databases <数据库名 >  --single-transaction --order-by-primary -r < 备份文件名 > --routines -h< 服务器地址 >  -P< 端口号 > -u< 用户名 > -p< 密码>
2.mysqldump --default-character-set=utf8mb4 --single-transaction --triggers --routines --events --hex-blob --databases muisc record > music_record.sql

记录 GTID_PURGED

 1.grep -r "GLOBAL.GTID_PURGED" music_record.sql
2.SET @@GLOBAL.GTID_PURGED='3cdb9ce6-0d7e-11e8-abe4-001517b5a5f0:1-698887';

将主库设为可读写模式

数据库导出完成后将主库重新设为可读写模式。

 1.mysql> set global read_only=off;
2.mysql> unlock tables;

从库数据导入

 1.##mysql> create database `music`;
2.##mysql -u root -p muisc < /root/music.sql
3.mysql -u root -p < /root/music_record.sql
4.mysql> reset slave all;
5.mysql> reset master;
6.mysql> SET @@GLOBAL.GTID_PURGED='3cdb9ce6-0d7e-11e8-abe4-001517b5a5f0:1-698887';

从库连接主库

1.change master to master_host='192.168.11.31',master_user='slave',master_password='slave123',master_port=3306,master_auto_position=1;

从库启动复制线程

1.mysql> start slave;

从库查看复制状态

 1.mysql> show slave status\G;
2.*************************** 1. row ***************************
3.              Slave_IO_State: Waiting for master to send event
4.                 Master_Host: 192.168.11.31
5.                  Master_User: slave
6.                  Master_Port: 3306
7.                Connect_Retry: 60
8.              Master_Log_File: master-bin.000002
9.          Read_Master_Log_Pos: 14937598310.               Relay_Log_File: db2-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000002
             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: 149375983
              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
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 31
                  Master_UUID: 834449ff-4487-11e8-8b27-000c294b06ca
             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:
1 row in set (0.00 sec)
 
ERROR:
No query specified

检查主从复制通信状态

Slave_IO_State #从站的当前状态
Slave_IO_Running：Yes #读取主程序二进制日志的 I / O 线程是否正在运行
Slave_SQL_Running：Yes #执行读取主服务器中二进制日志事件的 SQL 线程是否正在运行。与 I / O 线程一样
Seconds_Behind_Master #是否为 0，0 就是已经同步了

如果再次查询状态仍然发现 Slave_IO_Running 或者 Slave_SQL_Running 不同时为 YES, 尝试执行

 1.mysql> stop slave;
2.mysql> reset slave;
3.mysql> start slave;

主库查看状态

 1.mysql> show master status;
2.+-------------------+-----------+--------------+------------------+--------------------------------------------+
3.| File              | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
4.+-------------------+-----------+--------------+------------------+--------------------------------------------+
5.| master-bin.000002 | 149375983 |              |                  | 834449ff-4487-11e8-8b27-000c294b06ca:1-254 |
6.+-------------------+-----------+--------------+------------------+--------------------------------------------+
7.1 row in set (0.00 sec)
8.
9.mysql> show slave hosts;
10.+-----------+------+------+-----------+--------------------------------------+
11.| Server_id | Host | Port | Master_id | Slave_UUID                           |
12.+-----------+------+------+-----------+--------------------------------------+
13.|        32 |      | 3306 |        31 | 68303133-4489-11e8-84e9-000c293eaee6 |
14.+-----------+------+------+-----------+--------------------------------------+
15.1 row in set (0.00 sec)
16.
17.mysql> show global variables like '%gtid%';
18.+----------------------------------+--------------------------------------------+
19.| Variable_name                    | Value                                      |
20.+----------------------------------+--------------------------------------------+
21.| binlog_gtid_simple_recovery      | ON                                         |
22.| enforce_gtid_consistency         | ON                                         |
23.| gtid_executed                    | 834449ff-4487-11e8-8b27-000c294b06ca:1-255 |
24.| gtid_executed_compression_period | 1000                                       |
25.| gtid_mode                        | ON                                         |
26.| gtid_owned                       |                                            |
27.| gtid_purged                      |                                            |
28.| session_track_gtids              | OFF                                        |
29.+----------------------------------+--------------------------------------------+
30.8 rows in set (0.00 sec)

其他命令

 1.mysql> show binlog events;
2.mysql> show binlog events in 'master-bin.000001';
3.mysql> show master logs;
4.mysql> show processlist
5.mysql> show full processlist;
好啦！今天的分享到这里就结束了，希望大家持续关注马哥教育官网，每天都会有大量优质内容与大家分享！声明：文章源自网络，版权归原作者所有！

MySQL 5.7基于GTID及多线程主从复制

MySQL 主从同步原理

什么是 Gitd

Gtid 的作用

Gtid 的工作原理

从库配置

主库权限设置

自动同步连接主库(方法一)

备份导入连接主库(方法二)

备份导入连接主库(方法三)

将主库设为只读模式

主库使用 mysqldump 导出

从库连接主库

从库启动复制线程

从库查看复制状态

其他命令

开源堡垒机JumpServer配置教程：使用步骤与配置

申请腾讯混元的API Key并且使用LobeChat调用混元AI

系统加固-Linux不允许用户使用密码登录，只能使用密钥登录

【开源安全保护】如何安装JumpServer堡垒机

Docker部署搭建一个开源强大的图书管理系统

如何寻找IO杀手进程

腾讯云8核16G5M服务器多少钱？CVM和轻量配置价格表，真优惠！

系统加固-Linux不允许用户使用密码登录，只能使用密钥登录

详解：Nginx 反向代理、后端检测模块

详解：shell采集系统信息

	1. 系统：CentOS 7
	2. 数据库：Percona MySQL 5.7
	3. 主库：192.168.11.31
	4. 从库：192.168.11.32

	1.[mysqld]
	2.datadir=/data/mysql/3306
	3.socket=/tmp/mysql.sock
	4.symbolic-links=0
	5.
	6.server_id=31 # 服务器 ID
	7.log-bin=master-bin #二进制日志文件名
	8.binlog_format = row #强烈建议，其他格式可能造成数据不一致
	9.log-slave-updates = 1 #是否记录从服务器同步数据动作
	10.gtid-mode = on #启用 gitd 功能
	11.enforce-gtid-consistency = 1 #开启强制 GTID 一致性
	12.master-info-repository = TABLE #记录 IO 线程读取已经读取到的 master binlog 位置，用于 slave 宕机后 IO 线程根据文件中的 POS 点重新拉取 binlog 日志
	13.relay-log-info-repository = TABLE #记录 SQL 线程读取 Master binlog 的位置，用于 Slave 宕机后根据文件中记录的 pos 点恢复 Sql 线程
	14.sync-master-info = 1 #启用确保无信息丢失；任何一个事务提交后, 将二进制日志的文件名及事件位置记录到文件中
	15.slave-parallel-workers = 2 #设定从服务器的复制线程数；0 表示关闭多线程复制功能
	16.binlog-checksum = CRC32 #设置 binlog 校验算法（循环冗余校验码）
	17.master-verify-checksum = 1 #设置主服务器是否校验
	18.slave-sql-verify-checksum = 1 #设置从服务器是否校验
	19.binlog-rows-query-log_events = 1 #用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度
	20.sync_binlog = 1 #保证 master crash safe，该参数必须设置为 1
	21.innodb_flush_log_at_trx_commit = 1 #保证 master crash safe，该参数必须设置为 1

	1.[mysqld]
	2.server_id = 32
	3.log-bin=mysql-bin
	4.binlog_format = row
	5.gtid-mode = on
	6.enforce-gtid-consistency = 1
	7.master-info-repository = TABLE
	8.relay-log-info-repository = TABLE
	9.sync-master-info = 1
	10.slave-parallel-workers = 4
	11.binlog-checksum = CRC32
	12.master-verify-checksum = 1
	13.slave-sql-verify-checksum = 1
	14.binlog-rows-query-log_events = 1
	15.#sync_binlog = 1
	16.#innodb_flush_log_at_trx_commit = 1
	17.log-slave-updates = 0 # crash safe slave 5.6 版本需要开启
	18.relay_log_recovery = 1 # crash safe slave
	19.read_only=on #设置一般用户为只读模式
	20.super_read_only=on #设置 super（root）用户为只读模式
	21.#tx_read_only=on #设置事务为只读模式

	1.mysql > grant replication slave on . to slave@'192.168.11.32' identified by 'slave123';
	2.mysql > flush privileges;

	1.change master to master_host='192.168.11.31',\
	2.master_user='slave',master_password='slave123',\
	master_port=3306,master_auto_position=1

	#master_auto_position=1 从库自动找同步点

	1.mysql> flush tables with read lock;
	2.Query OK, 0 rows affected (0.00 sec)
	3.
	4.mysql> set global read_only=on;
	5.Query OK, 0 rows affected (0.00 sec)

	1.mysqldump --databases <数据库名 > --single-transaction --order-by-primary -r < 备份文件名 > --routines -h< 服务器地址 > -P< 端口号 > -u< 用户名 > -p< 密码>
	2.mysqldump --default-character-set=utf8mb4 --single-transaction --triggers --routines --events --hex-blob --databases muisc record > music_record.sql

	1.grep -r "GLOBAL.GTID_PURGED" music_record.sql
	2.SET @@GLOBAL.GTID_PURGED='3cdb9ce6-0d7e-11e8-abe4-001517b5a5f0:1-698887';

	1.##mysql> create database `music`;
	2.##mysql -u root -p muisc < /root/music.sql
	3.mysql -u root -p < /root/music_record.sql
	4.mysql> reset slave all;
	5.mysql> reset master;
	6.mysql> SET @@GLOBAL.GTID_PURGED='3cdb9ce6-0d7e-11e8-abe4-001517b5a5f0:1-698887';

	1.mysql> show slave status\G;
	2.************************* 1. row *************************
	3. Slave_IO_State: Waiting for master to send event
	4. Master_Host: 192.168.11.31
	5. Master_User: slave
	6. Master_Port: 3306
	7. Connect_Retry: 60
	8. Master_Log_File: master-bin.000002
	9. Read_Master_Log_Pos: 14937598310. Relay_Log_File: db2-relay-bin.000002
	Relay_Log_Pos: 321
	Relay_Master_Log_File: master-bin.000002
	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: 149375983
	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
	Last_IO_Errno: 0
	Last_IO_Error:
	Last_SQL_Errno: 0
	Last_SQL_Error:
	Replicate_Ignore_Server_Ids:
	Master_Server_Id: 31
	Master_UUID: 834449ff-4487-11e8-8b27-000c294b06ca
	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:
	1 row in set (0.00 sec)

	ERROR:
	No query specified

	1.mysql> stop slave;
	2.mysql> reset slave;
	3.mysql> start slave;

	1.mysql> show master status;
	2.+-------------------+-----------+--------------+------------------+--------------------------------------------+
	3.\| File \| Position \| Binlog_Do_DB \| Binlog_Ignore_DB \| Executed_Gtid_Set \|
	4.+-------------------+-----------+--------------+------------------+--------------------------------------------+
	5.\| master-bin.000002 \| 149375983 \| \| \| 834449ff-4487-11e8-8b27-000c294b06ca:1-254 \|
	6.+-------------------+-----------+--------------+------------------+--------------------------------------------+
	7.1 row in set (0.00 sec)
	8.
	9.mysql> show slave hosts;
	10.+-----------+------+------+-----------+--------------------------------------+
	11.\| Server_id \| Host \| Port \| Master_id \| Slave_UUID \|
	12.+-----------+------+------+-----------+--------------------------------------+
	13.\| 32 \| \| 3306 \| 31 \| 68303133-4489-11e8-84e9-000c293eaee6 \|
	14.+-----------+------+------+-----------+--------------------------------------+
	15.1 row in set (0.00 sec)
	16.
	17.mysql> show global variables like '%gtid%';
	18.+----------------------------------+--------------------------------------------+
	19.\| Variable_name \| Value \|
	20.+----------------------------------+--------------------------------------------+
	21.\| binlog_gtid_simple_recovery \| ON \|
	22.\| enforce_gtid_consistency \| ON \|
	23.\| gtid_executed \| 834449ff-4487-11e8-8b27-000c294b06ca:1-255 \|
	24.\| gtid_executed_compression_period \| 1000 \|
	25.\| gtid_mode \| ON \|
	26.\| gtid_owned \| \|
	27.\| gtid_purged \| \|
	28.\| session_track_gtids \| OFF \|
	29.+----------------------------------+--------------------------------------------+
	30.8 rows in set (0.00 sec)

	1.mysql> show binlog events;
	2.mysql> show binlog events in 'master-bin.000001';
	3.mysql> show master logs;
	4.mysql> show processlist
	5.mysql> show full processlist;
	好啦！今天的分享到这里就结束了，希望大家持续关注马哥教育官网，每天都会有大量优质内容与大家分享！声明：文章源自网络，版权归原作者所有！