GTID配置主从

最新推荐文章于 2025-04-22 08:57:02 发布

原创最新推荐文章于 2025-04-22 08:57:02 发布 · 277 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql

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)