MySQL-GTID主从复制的实现

MySQL-GTID主从复制

基于GTID的主从复制

一、 gtid的含义

Global Transaction Identifier，全局事务标识

阿里云的rds目前已经使用gtid

基于gtid的主从复制原理

每个mysql数据库上都有一个唯一uuid

每个事务生成一个id

gtid由上面两者组合： uuid+事务id

二、gtid的优势

相对使用binlog+位置的方法来说

gtid让配置主从更加方便

从提升为主时比较方便

三、实验搭建（两台服务器）
主服务器上的配置

[root@master ~]# vim /etc/my.cnf.d/mysql-server.cnf
gtid_mode=ON
enforce-gtid-consistency=ON

[root@master ~]# systemctl start mysqld

[root@slave ~]# vim /etc/my.cnf.d/mysql-server.cnf
gtid_mode=ON
enforce-gtid-consistency=ON

[root@slave ~]# systemctl start mysqld

从服务器上的配置

change master to master_host='192.168.108.146',
master_port=3306,
master_user='rep1',
master_password='Test@1234',
MASTER_AUTO_POSITION = 1;

mysql> change master to master_host='192.168.108.146',
    -> master_port=3306,
    -> master_user='rep1',
    -> master_password='Test@1234',
    -> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

配置完成后

在主服务器上查看

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 |      553 |              |                  | fef98802-f238-11eb-a5b1-000c2932e2f3:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

查看gtid表

mysql> desc gtid_executed;
+----------------+----------+------+-----+---------+-------+
| Field          | Type     | Null | Key | Default | Extra |
+----------------+----------+------+-----+---------+-------+
| source_uuid    | char(36) | NO   | PRI | NULL    |       |
| interval_start | bigint   | NO   | PRI | NULL    |       |
| interval_end   | bigint   | NO   |     | NULL    |       |
+----------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)