GTID的含义
1、全局唯一,一个事务对应一个GTID
2、替代传统的binlog+pos复制;使用master_auto_position=1自动匹配GTID断点进行复制
3、MySQL5.6开始支持
4、在传统的主从复制中,slave端不用开启binlog;但是在GTID主从复制中,必须开启binlog
5、slave端在接受master的binlog时,会校验GTID值
6、为了保证主从数据的一致性,多线程同时执行一个GTID
GTID主从复制的优点
- GTID同步时会开启多个sql线程
- binlog在row模式下,占用磁盘空间较小
- GTID会保存主从相关信息
- 基于GTID的主从复制不需要手动配置binglog的名字和位置点
GTID配置一主一从
主库 | 从库 | |
---|---|---|
ip | 192.168.8.131 | 192.168.8.130 |
master主配置文件
[root@node2 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysqld.pid
skip-name-resolve
server-id = 10
gtid-mode = on
enforce-gtid-consistency = on
log-bin = mysql_bin
binlog-format = row
log-slave-updates = 1
skip-slave-start = 1
slave配置文件
[root@node1 ~]# cat /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
server-id = 20
log-bin = mysql_bin
binlog-format = row
skip-slave-start = 1
gtid-mode = on
log-slave-updates = 1
enforce-gtid-consistency = on
slave 检查gtid状态
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.00 sec)
重启mysqld
# 重启从库
[root@node1 ~]# systemctl restart mysqld
# 查看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 |
+----------------------------------+-----------+
8 rows in set (0.01 sec)
主库操作
# 创建授权账户
mysql> grant replication slave on *.* to 'repl'@'192.168.8.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 重启主库
[root@node2 ~]# systemctl restart mysqld
从库操作
mysql> change master to master_host='192.168.8.131',master_user='repl',master_password='123',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.8.131
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000005
Read_Master_Log_Pos: 154
Relay_Log_File: node1-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql_bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
在主库中插入数据
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table one(id int,name varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into one values(1,'zhangshan'),(2,'wangwu');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from one;
+------+-----------+
| id | name |
+------+-----------+
| 1 | zhangshan |
| 2 | wangwu |
+------+-----------+
2 rows in set (0.00 sec)
在从库上查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| one |
+----------------+
1 row in set (0.00 sec)
mysql> select * from one;
+------+-----------+
| id | name |
+------+-----------+
| 1 | zhangshan |
| 2 | wangwu |
+------+-----------+
2 rows in set (0.00 sec)
GTID一主两从
主库 | 从库1 | 从库2 | |
---|---|---|---|
ip | 192.168.8.131 | 192.168.8.130 | 192.168.8.132 |
一主两从的配置 将一主一从中的从库配置在第二台从库上再配置一遍(下面步骤只配置第二台从库)
# 配置从库文件
[root@node3 ~]# cat /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
server-id = 20
log-bin = mysql_bin
binlog-format = row
skip-slave-start = 1
gtid-mode = on
log-slave-updates = 1
enforce-gtid-consistency = on
重启数据库,查看gtid状态
[root@node3 ~]# systemctl restart mysqld
[root@node3 ~]# mysql -uroot -p123
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 |
+----------------------------------+-----------+
8 rows in set (0.01 sec)
mysql> change master to master_host='192.168.8.131',master_user='repl',master_password='123',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.8.131
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000005
Read_Master_Log_Pos: 154
Relay_Log_File: node1-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql_bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
在主库上插入数据
mysql> create database class;
Query OK, 1 row affected (0.00 sec)
mysql> use class;
Database changed
mysql> create table one(id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into one values(1,'zhangshan'),(2,'lisi');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from one;
+------+-----------+
| id | name |
+------+-----------+
| 1 | zhangshan |
| 2 | lisi |
+------+-----------+
2 rows in set (0.00 sec)
在两台从库上查看
[root@node1 ~]# mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| class |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from one;
+------+-----------+
| id | name |
+------+-----------+
| 1 | zhangshan |
| 2 | lisi |
+------+-----------+
2 rows in set (0.00 sec)
[root@node3 ~]# mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| class |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from one;
+------+-----------+
| id | name |
+------+-----------+
| 1 | zhangshan |
| 2 | lisi |
+------+-----------+
2 rows in set (0.00 sec)
GTID两主一从
主库1 | 主库2 | 从库 | |
---|---|---|---|
ip | 192.168.8.131 | 192.168.8.132 | 192.168.8.130 |
在两台主库中做相同的配置 ( 这里只配置第二台主库)
[root@node1 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysqld.pid
skip-name-resolve
server-id = 10
gtid-mode = on
enforce-gtid-consistency = on
log-bin = mysql_bin
binlog-format = row
log-slave-updates = 1
skip-slave-start = 1
在两台主库中创建授权账户
[root@node1 ~]# cat /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
server-id = 20
log-bin = mysql_bin
binlog-format = row
skip-slave-start = 1
gtid-mode = on
log-slave-updates = 1
enforce-gtid-consistency = on
master_info_repository = table
relay_log_info_repository = table
mysql> grant replication slave on *.* to 'repl2'@'192.168.8.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@node1 ~]# systemctl restart mysqld
配置从库
# 从库配置文件
mysql> change master to master_host='192.168.8.132',master_user='repl2',master_password='123',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> create database book;
Query OK, 1 row affected (0.00 sec)
mysql> use book;
Database changed
mysql> create table one(id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into one values(1,'xiaoli'),(2,'xiaowang');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from one;
+------+-----------+
| id | name |
+------+-----------+
| 1 | xiaoli |
| 2 | xiaowang |
+------+-----------+
2 rows in set (0.00 sec)
在从库上查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| class |
| book |
+--------------------+
5 rows in set (0.00 sec)