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)