本文介绍了MySQL的GTID（全局事务标识符）特性，它简化了主从复制和故障恢复过程，确保主备一致性。详细讲解了GTID的工作原理，如何启用GTID，并提供了配置主从服务器的步骤，包括一主多从和双主多从的配置示例。此外，还展示了验证主从同步状态的方法。
一、Gtid简介
从 MySQL 5.6.5 开始新增了一种基于 GTID 的复制方式。通过 GTID保证了每个在主库上提交的事务在集群中有一个唯一的ID。这种方式强化了数据库的主备一致性，故障恢复以及容错能力。
二、Gtid的工作原理
GTID是MySQL 5.6的新特性，其全称是Global Transaction Identifier，可简化MySQL的主从切换以及Failover。GTID用于在binlog中唯一标识一个事务。当事务提交时，MySQL Server在写binlog的时候，会先写一个特殊的Binlog Event，类型为GTID_Event，指定下一个事务的GTID，然后再写事务的Binlog。主从同步时GTID_Event和事务的Binlog都会传递到从库，从库在执行的时候也是用同样的GTID写binlog，这样主从同步以后，就可通过GTID确定从库同步到的位置了。
三、Gtid的作用
Gtid采用了新的复制协议，旧协议是，首先从服务器上在一个特定的偏移量位置连接到主服务器上一个给定的二进制日志文件，然后主服务器再从给定的连接点开始发送所有的事件。
新协议有所不同，支持以全局统一事务ID (GTID)为基础的复制。当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务。GTID复制是全部以事务为基础，使得检查主从一致性变得非常简单。如果所有主库上提交的事务也同样提交到从库上，一致性就得到了保证。
四、配置
主机名 ip
master 192.168.58.120
slave 192.168.58.121
1.1 授权用户，用于主从
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | OFF |
| gtid_executed_compression_period | 1000 |
| gtid_mode | OFF |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
8 rows in set (0.01 sec)
mysql> create user 'xu'@'192.168.58.121' identified by "xu1";
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'xu'@'192.168.58.121' ;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
1.2开启GTID的必备条件
MySQL 5.6
- gtid_mode=ON(必选)
- log_bin=ON(必选)
- log-slave-updates=ON(必选)
- enforce-gtid-consistency(必选)
MySQL 5.7
- gtid_mode=ON(必选)
- enforce-gtid-consistency（必选）
- log_bin=ON（可选）
- log-slave-updates=ON（可选）
1.3 修改master主配文件
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
#replication config
log-bin = mysql-bin #开启二进制日志
server-id = 1
gtid-mode = on #开启gtid模式
enforce-gtid-consistency = on #强制gtid一致性，开启后对特定的create table不被支持
binlog-format = row #默认为mixed混合模式，更改成row复制，为了数据一致性
log-slave-updates = 1 #从库binlog才会记录主库同步的操作日志
skip-slave-start = 1 #跳过slave复制线程
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000011 | 1924 | | | c736875b-097e-11ec-b557-000c29810dc2:1-8 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
slave主配文件
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
# replication config
server-id = 2
log-bin = mysql-bin
binlog-format = row
skip-slave-start = 1
log-slave-updates = 1
gtid-mode = on
enforce-gtid-consistency = on
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
检查gtid状态
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to
-> master_host='192.168.153.133' ,
-> master_user='xu' ,
-> master_password='xu1' ,
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave ;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.58.120
Master_User: xu
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 133
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
验证
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| xu |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql_bin.000005 | 1423 | | | c736875b-097e-11ec-b557-000c29810dc2:1-9 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
已经在slave主机进行了同步，
一主多从
主机名 ip
master 192.168.58.120
slave 192.168.58.121
slave2 192.168.58.122
配置一主多从，只需在主库master再上添加从库就行
第一台主库配置与之前配置一致
第一台从库与上一致
第二台从库配置
slave主配文件
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
# replication config
server-id = 3 //第三台从服务器id 不重复
log-bin = mysql-bin
binlog-format = row
skip-slave-start = 1
log-slave-updates = 1
gtid-mode = on
enforce-gtid-consistency = on
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS
配置slave主机
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to
-> master_host='192.168.58.120' ,
-> master_user='xu' ,
-> master_password='xu1' ,
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave ;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.58.120
Master_User: xu
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 1542
Relay_Log_File: localhost-relay-bin.000005
Relay_Log_Pos: 511
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| xu |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+
| mysql_bin.000008 | 694 | | | b736875b-097e-16ec-b557-000c29810dc2:1-10,
ba15a7ff-097f-11ec-9272-000c29859565:1,
dca6f885-0a26-11ec-81cc-000c29g2278e:1 |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+
双主多从
主库配置如之前配置的方法一致详细步骤省略
(1)查看主库
//主库1
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000011 | 2064 | | | c736875b-097e-11ec-b557-000c26782dc2:1-8 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
主库2
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000004 | 176 | | | c736875b-097e-11ec-b557-000c26782c2:1-10 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
(2)从库配置
修改配置文件省略，具体步骤和前面一致
配置slave主机
//检查gtid模式状态
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
在从库上配置主库信息
//配置主库1
//下面两条命令不执行，后续会报错
mysql> set global master_info_repository='table';
Query OK, 0 rows affected (0.00 sec)
mysql> set global relay_log_info_repository='table';
Query OK, 0 rows affected (0.00 sec)
mysql> change master to
-> master_host='192.168.58.120' ,
-> master_user='xu' ,
-> master_password='xu1' ,
-> master_log_file='mysql-bin.000011'
-> master_log_pos=2064
-> for channel 'master-1'; //创建隧道
配置主库2
mysql> change master to
-> master_host='192.168.58.121' ,
-> master_user='xu' ,
-> master_password='xu1' ,
-> master_log_file='mysql-bin.000004'
-> master_log_pos=176
-> for channel 'master-2'; //创建隧道
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
//查看发现都已经开启
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.58.120
Master_User: xu
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 2064
Relay_Log_File: localhost-relay-bin-master@002d1.000002
Relay_Log_Pos: 312
Relay_Master_Log_File: mysql-bin.000011
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: 2064
Relay_Log_Space: 523
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: c736875b-097e-11ec-b557-000c29810dc2
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: baf6e1ae-0a32-11ec-bf3d-000c29f2278e:1
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master-1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.58.121
Master_User: xu
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 176
Relay_Log_File: localhost-relay-bin-master@002d2.000002
Relay_Log_Pos: 312
Relay_Master_Log_File: mysql-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: 176
Relay_Log_Space: 523
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: 2
Master_UUID: ca11a7ff-097f-11ec-9272-000c29959565
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: baf6e1ae-0a32-11ec-bf3d-000c29f2278e:1
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master-2
Master_TLS_Version:
2 rows in set (0.00 sec)
//测试
主库1上创建数据库xu
mysql> create database xu;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| xu |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
主库2上创建数据库student
mysql> create database student;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| student |
+--------------------+
5 rows in set (0.00 sec)
从库上开启从服务后查看
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| xu |
| mysql |
| performance_schema |
| sys |
| student |
+--------------------+
6 rows in set (0.00 sec)
扫一扫
789
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
