一、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.91.129 |
| slave | 192.168.91.130 |
授权一个用户，用于主从复制
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 'gaofan'@'192.168.91.130' identified by "gf123！";
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'gaofan'@'192.168.91.130' ;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
修改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.000014 | 1951 | | | b736875b-097e-11ec-b557-000c29810dc2:1-9 |
+------------------+----------+--------------+------------------+------------------------------------------+
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!
配置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)
mysql> change master to
-> master_host='192.168.91.129' ,
-> master_user='gaofan' ,
-> master_password='gf123!' ,
-> 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.91.129
Master_User: gaofan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000014
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 |
| gaofan |
| 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.000006 | 1389 | | | b736875b-097e-11ec-b557-000c29810dc2:1-10 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
发现master主机插入的数据，已经在slave主机进行了同步，
五、一主多从
环境
| 主库 | 主机 | 192.168.91.129 |
| 从库1 | 主机 | 192.168.91.130 |
| 从库2 | 主机 | 192.168.91.131 |
配置一主多从，只需在主库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.91.129' ,
-> master_user='gaofan' ,
-> master_password='gf123!' ,
-> 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.91.129
Master_User: gaofan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 1776
Relay_Log_File: localhost-relay-bin.000004
Relay_Log_Pos: 454
Relay_Master_Log_File: mysql-bin.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gaofan |
| 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 | 750 | | | b736875b-097e-11ec-b557-000c29810dc2:1-10,
ba11a7ff-097f-11ec-9272-000c29959565:1,
ddd6f885-0a26-11ec-81cc-000c29f2278e:1 |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+
六、双主多从
从服务器配置
按照前面配置主库的方法，配置两台主库，具体步骤省略
查看主库信息
//主库1
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000014 | 2128 | | | b736875b-097e-11ec-b557-000c29810dc2:1-10 |
+------------------+----------+--------------+------------------+-------------------------------------------+
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.000002 | 154 | | | b736875b-097e-11ec-b557-000c29810dc2:1-10 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
从库配置
修改配置文件省略，具体步骤和前面一致
配置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.91.129' ,
-> master_user='gaofan' ,
-> master_password='gf123!' ,
-> master_log_file='mysql-bin.000014'
-> master_log_pos=2128
-> for channel 'master-1'; //创建隧道
配置主库2
mysql> change master to
-> master_host='192.168.91.130' ,
-> master_user='gaofan' ,
-> master_password='gf123!' ,
-> master_log_file='mysql-bin.000002'
-> master_log_pos=154
-> 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.91.129
Master_User: gaofan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 2128
Relay_Log_File: localhost-relay-bin-master@002d1.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000014
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: 2128
Relay_Log_Space: 544
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: b736875b-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.91.130
Master_User: gaofan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin-master@002d2.000002
Relay_Log_Pos: 320
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: 154
Relay_Log_Space: 544
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: ba11a7ff-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上创建数据库gaofan
mysql> create database gaofan;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gaofan |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
主库2上创建数据库xumeng
mysql> create database xumeng;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xumeng |
+--------------------+
5 rows in set (0.00 sec)
从库上开启从服务后查看
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gaofan |
| mysql |
| performance_schema |
| sys |
| xumeng |
+--------------------+
6 rows in set (0.00 sec)
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/5638.html
