Gtid主从复制

Gtid的概念

全局事务id，Gtid的一个事务对应一个id，而且在做主从同步的时候，每个主上提交的事务id，在复制到集群中都生成一个唯一的事务id。
使用Gtid来代替传统的复制方法，不再使用master-log-file和master-log-pos开启复制而是使用master-position的方法进行复制，但只是从MySQL-5.6.6才开始支持的
传统的slave端，binlog是不用开启的，但是在Gtid上是需要开启binlog日志功能的，目的是记录执行过的Gtid

GTID的工作原理

当一个事务在主库端执行并提交的时候，就会产生GTID并记录到binlog日志中；
binlog传输到slave，并存储到slave的relalog日志后，读取GTID这个值设置的gtid_next变量，即告诉slave下一个要执行的GTID值；
sql线程在relalog日志中获取GTID，然后再对比slave端的binlog日志是否存在该GTID；
如果有记录，说明该GTID事务已经执行，那么slave就会被忽略；
如果没有记录，那么slave就会执行该GTID事务，并记录到GTID自身的binlog日志当中，在读取执行事务前会先检查其他的session持有该GTID，确保不会被重复执行；

6.在解析过程中会判断是否有主键，如果没有就使用二级索引，如果没有就使用全部扫描。

GTID的优势

实现更简单的failover,不用像以前一样需要找log-file和log-pos
能更简单的搭建主从
比传统的复制更加安全
GTID是连续的没有空洞的，保证数据的一致性，零丢失。

GTID主从同步之一主一从

部署MySQL数据库
以下操作主从一样

下载rpm包并解压
[root@slave local]# wget 
https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
[root@slave local]# tar -xf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
创建MySQL用户
[root@master local]# useradd -s -M -r mysql
创建软连接并并修改属主和属组
[root@master local]# ln -s mysql-5.7.32-linux-glibc2.12-x86_64/ mysql
[root@master local]# chown -R mysql
配置环境变量
[root@master local]# echo 'export PATH=/usr/local/mysq/bin:$PATH' > /etc/profile.d/mysql.sh
[root@master local]# source /etc/profile.d/mysql.sh
创建目录用来存放数据库数据，并修改属主和属组
[root@master local]# mkdir /opt/data/
[root@master local]# chown -R mysql.mysql /opt/data/
写配置文件，添加两行内容
[root@master ~]# cd /usr/local/mysql/support-files/
[root@master support-files]# ls
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@master support-files]# cp mysql.server /etc/init.d/mysqld
[root@master support-files]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/opt/data
初始化数据库，然后用生成的随机密码去登录数据库，然后再修改新密码
[root@master local]# mysqld --initialize --user mysql --datadir /opt/data/
[root@master local]# service mysqld start 
Starting MySQL........ SUCCESS! 
[root@master local]# mysql -uroot -p
mysql> set password=password('新密码');

编写配置文件
主库的配置文件

[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
skip-name-resolve

server-id = 10
log-bin = mysql_bin
gtid-mode = on
enforce-gtid-consistency = on
binlog-format = row
log-slave-updates = 1
skip-slave-start = 1

从库的配置文件

[root@slave ~]# vim /etc/my.cnf
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
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

修改完配置文件之后重启MySQL服务

[root@master ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

[root@slave ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@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)

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 'test'@'192.168.182.%' identified by'123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

在从库上面配置基于GTID的复制

mysql> change master to
    -> master_host='192.168.182.138',
    -> master_user='test',
    -> master_password='123',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

启动slave

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.182.138
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql_bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

在主库上创建一个数据库进行测试

mysql> create database box;
Query OK, 1 row affected (0.02 sec)

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

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

先关闭主从同步，再打开同步开能否同步关闭之后的数据
这个可以适用于当主库出现问题需要我们手动解决之后再进行操作的情况下的操作。

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> use box
Database changed

mysql> create table me (id int not null auto_increment primary key,name varchar(100)not null,age tinyint);
Query OK, 0 rows affected (0.01 sec)

mysql> desc me
    -> ;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.33 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_box |
+---------------+
| me            |
+---------------+
1 row in set (0.00 sec)

mysql> desc me
    -> ;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.21 sec)

一主多从

先安装MySQL数据库

下载rpm包并解压
[root@slave local]# wget 
https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
[root@slave local]# tar -xf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
创建MySQL用户
[root@master local]# useradd -s -M -r mysql
创建软连接并并修改属主和属组
[root@master local]# ln -s mysql-5.7.32-linux-glibc2.12-x86_64/ mysql
[root@master local]# chown -R mysql
配置环境变量
[root@master local]# echo 'export PATH=/usr/local/mysq/bin:$PATH' > /etc/profile.d/mysql.sh
[root@master local]# source /etc/profile.d/mysql.sh
创建目录用来存放数据库数据，并修改属主和属组
[root@master local]# mkdir /opt/data/
[root@master local]# chown -R mysql.mysql /opt/data/
写配置文件，添加两行内容
[root@master ~]# cd /usr/local/mysql/support-files/
[root@master support-files]# ls
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@master support-files]# cp mysql.server /etc/init.d/mysqld
[root@master support-files]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/opt/data
初始化数据库，然后用生成的随机密码去登录数据库，然后再修改新密码
[root@master local]# mysqld --initialize --user mysql --datadir /opt/data/
[root@master local]# service mysqld start 
Starting MySQL........ SUCCESS! 
[root@master local]# mysql -uroot -p
mysql> set password=password('新密码');

编写主库的配置文件

[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
skip-name-resolve

server-id = 10
log-bin = mysql_bin
gtid-mode = on
enforce-gtid-consistency = on
binlog-format = row
log-slave-updates = 1
skip-slave-start = 1

然后在主库上授权一个用户

mysql> grant replication slave on *.* to 'test'@'192.168.182.%' identified by'123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

再到从库上编写/etc/my.cnf这个配置文件,两个从库都要做

[root@slave ~]# vim /etc/my.cnf
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
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

[root@slave ~]# vim /etc/my.cnf
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
skip-name-resolve

server-id = 21
log-bin = mysql_bin
binlog_format = row
skip-slave-start = 1
gtid-mode = on
log-slave-updates = 1
enforce-gtid-consistency = on

最后再重启MySQL数据库

[root@master ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

[root@slave ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@slave ~]#


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.00 sec)

mysql> change master to
    -> master_host='192.168.182.138',
    -> master_user='test',
    -> master_password='123',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

启动slave
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.182.138
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000002
          Read_Master_Log_Pos: 545
               Relay_Log_File: slave2-relay-bin.000002
                Relay_Log_Pos: 758
        Relay_Master_Log_File: mysql_bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

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

mysql> use box;
mysql> desc me;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

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

mysql> use box;
mysql> desc me;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

两主一从

首先安MySQL数据库

下载rpm包并解压
[root@slave local]# wget 
https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
[root@slave local]# tar -xf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
创建MySQL用户
[root@master local]# useradd -s -M -r mysql
创建软连接并并修改属主和属组
[root@master local]# ln -s mysql-5.7.32-linux-glibc2.12-x86_64/ mysql
[root@master local]# chown -R mysql
配置环境变量
[root@master local]# echo 'export PATH=/usr/local/mysq/bin:$PATH' > /etc/profile.d/mysql.sh
[root@master local]# source /etc/profile.d/mysql.sh
创建目录用来存放数据库数据，并修改属主和属组
[root@master local]# mkdir /opt/data/
[root@master local]# chown -R mysql.mysql /opt/data/
写配置文件，添加两行内容
[root@master ~]# cd /usr/local/mysql/support-files/
[root@master support-files]# ls
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@master support-files]# cp mysql.server /etc/init.d/mysqld
[root@master support-files]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/opt/data
初始化数据库，然后用生成的随机密码去登录数据库，然后再修改新密码
[root@master local]# mysqld --initialize --user mysql --datadir /opt/data/
[root@master local]# service mysqld start 
Starting MySQL........ SUCCESS! 
[root@master local]# mysql -uroot -p
mysql> set password=password('新密码');

编写数据库的配置文件

主库1
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
skip-name-resolve

server-id = 10
log-bin = mysql_bin
gtid-mode = on
enforce-gtid-consistency = on
binlog-format = row
log-slave-updates = 1
skip-slave-start = 1

主库2
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
skip-name-resolve

server-id = 11
log-bin = mysql_bin
gtid-mode = on
enforce-gtid-consistency = on
binlog-format = row
log-slave-updates = 1
skip-slave-start = 1

编写从库的配置文件

[root@slave ~]# vim /etc/my.cnf
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
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

重启MySQL数据库

[root@master ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

[root@master ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

[root@slave ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@slave ~]#

授权一个用户

主库1
mysql> grant replication slave on *.* to 'test1'@'192.168.182.140' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

主库2
mysql> grant replication slave on *.* to 'test1'@'192.168.182.140' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

在从库上配置基于GTID的复制
mysql> change master to
    -> master_host='192.168.182.139',
    -> master_user='test1',
    -> master_password='123',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> change master to
    -> master_host='192.168.182.138',
    -> master_user='test1',
    -> master_password='123',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)


mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.182.139
                  Master_User: test1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave2-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql_bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

在第二个主库上创建一个库，从库也能同步上去，box是第一个主库上面创建的库
mysql> create database x;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| box                |
| mysql              |
| performance_schema |
| sys                |
| x                  |
+--------------------+
6 rows in set (0.00 sec)

最后编辑于：2021.09.01 18:55:54

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 205,386评论 6赞 479
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 87,939评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,851评论 0赞 341
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,953评论 1赞 278
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,971评论 5赞 369
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,784评论 1赞 283
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,126评论 3赞 399
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,765评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 43,148评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,744评论 2赞 323
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,858评论 1赞 333
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,479评论 4赞 322
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,080评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,053评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,278评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,245评论 2赞 352
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,590评论 2赞 343