MySQL主从复制-GTID

1. GTID概念

全局事务标识：global transaction identifiers。

GTID与事务一一对应，并且全局唯一ID。

一个GTID在一个服务器上只执行一次。

MySQL-5.6.5开始支持GTID。

GTID组成：

GTID = server_uuid:transaction_id

2. 配置GTID（两个节点的MySQL都已经安装好）

配置主从my.cnf  在MySQL服务启动的前提下做

1、在主服务器上：

（1）修改配置文件： 

[root@lzl ~]# vi /etc/my.cnf

在最后一行添加内容

server_id=8
gtid_mode=on
enforce_gtid_consistency=on
#binlog
log_bin=master-binlog
log-slave-updates=1
binlog_format=row
#relay log
skip-slave-start=1

（2）修改完配置文件，service  mysql restart 重启一下再进数据库

[root@lzl ~]# service mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@lzl ~]# mysql -uroot -p
Enter password: 

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.07 sec)

mysql> use test;
Database changed
mysql> create table cool(id int,name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into cool(id,name) values(1,'xiangming');
Query OK, 1 row affected (0.05 sec)

mysql> select * from cool;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | xiangming |
+------+-----------+
1 row in set (0.01 sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.60.%' identified by '000000';
Query OK, 0 rows affected, 1 warning (0.39 sec)

mysql> exit
Bye

退出数据库后重启一下

[root@lzl ~]# service mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

（3）在主服务器上备份全库：

[root@lzl ~]# mysqldump -uroot -ptest --master-data=2 --single-transaction --set-gtid-purged=OFF -A > /tmp/mysqlbak_`date +%Y%m%d`.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@lzl ~]# service mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

（4）查看端口

[root@lzl ~]# ss -ntl
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN      0      128     *:22                  *:*                  
LISTEN      0      128    :::22                 :::*                  
LISTEN      0      70     :::3306               :::*  

[root@lzl ~]# ll /tmp/mysqlbak_20210518.sql 
-rw-r--r--. 1 root root 872441 May 18 02:42 /tmp/mysqlbak_20210518.sql

    用data同步一下时间

（5）复制到从节点上，并去从节点查看

[root@lzl ~]# scp /tmp/mysqlbak_20210518.sql root@192.168.60.9:/tmp/
The authenticity of host '192.168.60.9 (192.168.60.9)' can't be established.
ECDSA key fingerprint is SHA256:yfhFf20ZhHOC8k7Gh0XWXpnCy90l99dVH07pbH+k8zw.
ECDSA key fingerprint is MD5:05:7c:5c:52:88:29:6e:9c:61:42:00:62:92:85:94:55.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.60.9' (ECDSA) to the list of known hosts.
root@192.168.60.9's password: 
mysqlbak_20210518.sql                                  100%  852KB  35.1MB/s   00:00 

[root@hh ~]# cd /tmp/
[root@hh tmp]# ll mysqlbak_20210518.sql 
-rw-r--r--. 1 root root 872441 May 18 02:44 mysqlbak_20210518.sql

2、在从服务器上

（1）修改配置文件：

[root@hh ~]# vi /etc/my.cnf

在最后一行添加

server_id=9
gtid_mode=on
enforce_gtid_consistency=on
#binlog
log_bin=slave-binlog
log-slave-updates=1
binlog_format=row
#relay log
skip-slave-start=1

 （2）修改完配置文件重启一下再进数据库：

[root@hh ~]# service mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

[root@hh tmp]# mysql -uroot -p
Enter password: 

（3）查看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)

（4）配置从节点的连接：

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.60.8',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='000000',
    -> MASTER_PORT=3306,
    -> MASTER_AUTO_POSITION=1,
    -> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

（5）启动slave，如果Slave_IO_Running与Slave_SQL_Running都是yes就代表从服务器配置成功

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.60.8
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master-binlog.000004
          Read_Master_Log_Pos: 854145
               Relay_Log_File: hh-relay-bin.000005
                Relay_Log_Pos: 466
        Relay_Master_Log_File: master-binlog.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
    .......

    .......