GTID配置主从

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)