GTID方式配置主从

事务是什么

事务是由原子性，一致性，隔离性和持久性组成（这四个属性通常称为ACID特性。）

原子性：只要有一步任务没有完成，所有任务都不能进行（要么做完，要么不做）
一致性：最开始为一致性，最后完成后为另一种一致性（从一个一致性到另一个一致性状态，与原子性密切相关）
隔离性：一个事务不能被其他事务干扰
持久性：也称永久性，一旦提交代表这一个事务结束（commit），会永久性保存（没有明确的提交，但也有隐性提交）

手动事务：手动加事务
自动事务：系统自动加事务
（成功提交，失败了其中一个回滚到之前）

GTID

gtid强化了数据库的主备一致性，故障恢复以及容错能力

什么是GTID

是一个已提交的编号（也是全局唯一一个编号）
GTID = source_id:sequence_id

Gtid的作用

Gtid采用了新的复制协议，旧协议是，首先从服务器上在一个特定的偏移量位置连接到主服务器上一个给定的二进制日志文件，然后主服务器再从给定的连接点开始发送所有的事件。
新协议有所不同，支持以全局统一事务ID (GTID)为基础的复制。当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务。GTID复制是全部以事务为基础，使得检查主从一致性变得非常简单。如果所有主库上提交的事务也同样提交到从库上，一致性就得到了保证

工作原理：

1.MASTER产生GTID,保存到Binlog中

2.发送Binlog到SLAVE,保存到Relay Log

3.SQL线程从Relay Log中获取GTID,并设置为gtid_next,然后对比SLAVE端的Binlog是否有记录

4.如果有记录，说明该GTID的事务已经执行，SLAVE会忽略

5.如果没有记录，SLAVE就会从Relay Log中执行该GTID的事务，并记录到Binlog

准备环境：

主机名	IP
master（主）	192.168.230.100
slave（客户端）	192.168.230.96
slave2（客户端2）	192.168.230.98

环境配置：

#先安装二进制MySQL安装包
[root@master ~]# wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
--2021-08-31 16:49:36--  https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
正在解析主机 cdn.mysql.com (cdn.mysql.com)... 223.119.242.56
正在连接 cdn.mysql.com (cdn.mysql.com)|223.119.242.56|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：643790848 (614M) [application/x-tar-gz]
正在保存至: “mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz”

mysql-5.7.22-linu 100%[==========>] 613.97M  3.95MB/s  用时 3m 7s   

2021-08-31 16:52:45 (3.28 MB/s) - 已保存 “mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz” [643790848/643790848])
#将安装包传给另一台（减少时间）
[root@master src]# scp mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz 192.168.230.97:/usr/src/
mysql-5.7.22-linux-glibc2.12-x86_6 100%  614MB  23.6MB/s   00:26    
[root@master src]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz  -C /usr/local/

#修改目录/usr/local/mysql的属主属组及配置环境变量
[root@master src~]# groupadd -r mysql
[root@master src~]# useradd -M -s /sbin/nologin -g mysql mysql
[root@master src~]# cd /usr/local
[root@master local]# mv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql
[root@master local]# chown -R mysql.mysql mysql/
[root@master local]# ll
总用量 0
drwxr-xr-x. 2 root  root    6 8月  12 2018 bin
drwxr-xr-x. 2 root  root    6 8月  12 2018 etc
drwxr-xr-x. 2 root  root    6 8月  12 2018 games
drwxr-xr-x. 2 root  root    6 8月  12 2018 include
drwxr-xr-x. 2 root  root    6 8月  12 2018 lib
drwxr-xr-x. 2 root  root    6 8月  12 2018 lib64
drwxr-xr-x. 2 root  root    6 8月  12 2018 libexec
drwxr-xr-x  9 mysql mysql 129 8月  31 16:55 mysql
drwxr-xr-x. 2 root  root    6 8月  12 2018 sbin
drwxr-xr-x. 5 root  root   49 7月  19 17:19 share
drwxr-xr-x. 2 root  root    6 8月  12 2018 src

[root@master ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@master ~]# source /etc/profile.d/mysql.sh 
[root@master ~]# mkdir -p /opt/data
[root@master ~]# chown -R mysql.mysql /opt/data/

#初始化实例
[root@master ~]# mysqld --initialize --datadir=/opt/data/ --user=mysql
2021-08-31T09:14:41.588710Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-31T09:14:43.181505Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-31T09:14:43.300400Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-31T09:14:43.367718Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: e0de9e1b-0a3b-11ec-b8d5-000c291e56a0.
2021-08-31T09:14:43.370665Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-31T09:14:43.375871Z 1 [Note] A temporary password is generated for root@localhost: LuTftVaGP1+I

#保存密码
[root@master ~]# echo 'LuTftVaGP1+I' > pass
[root@master ~]# vi /etc/my.cnf
[root@master ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysql.pid
port = 3306
skip-name-resolve

#cp一份文件后修改下basedir与datadir的路径
[root@master support-files]# cp mysql.server /etc/init.d/mysqld
[root@master support-files]# vi /etc/init.d/mysqld 

#开启下MySQL
[root@master ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/master.err'.
. SUCCESS! 

#查询程序文件依赖的包，没有则查看安装包由哪个仓库可以安装
[root@master ~]# ldd /usr/local/mysql/bin/mysql
        linux-vdso.so.1 (0x00007ffe2bfb8000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f692a02e000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f6929e25000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f6929c21000)
        libncurses.so.5 => not found
        libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f692988c000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f692950a000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f69292f2000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f6928f30000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f692a24e000)
        libtinfo.so.5 => not found
#查询到后直接安装
[root@master ~]# yum whatprovides libncurses.so.5
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查：0:37:01 前，执行于 2021年08月31日 星期二 16时52分20秒。
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility
                                            : libraries
仓库        ：BaseOS
匹配来源：
提供    : libncurses.so.5

[root@master yum.repos.d]# yum -y install ncurses-compat-libs
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查：0:02:37 前，执行于 2021年08月31日 星期二 17时37分53秒。
软件包 ncurses-compat-libs-6.1-7.20180224.el8.i686 已安装。
依赖关系解决。
============================================================================
 软件包                  架构       版本                   仓库        大小
============================================================================
安装:
 ncurses-compat-libs     x86_64     6.1-7.20180224.el8     BaseOS     331 k

事务概要
============================================================================
安装  1 软件包

查询密码并修改密码
[root@master ~]# cat pass 
LuTftVaGP1+I

[root@master ~]# mysql -uroot -pLuTftVaGP1+I
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password = password('1');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye

#另两台环境一样，自行配置

使用GTID方法配置一主一从

实例：

#配置
mysql> grant replication slave on *.* to 'repl'@'192.168.230.96' identified by '1'; 
Query OK, 0 rows affected, 1 warning (0.01 sec)

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

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

server-id = 20
gtid-mode = on
enforce-gtid-consistency = on
log-bin = mysql_bin
binlog-format = row
log-slave-updates = 1
skip_slave-updates = 1

#检查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.03 sec)

#进行重启以下再次查看
[root@slave ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL.Logging to '/opt/data/slave.err'.
... ERROR! The server quit without updating PID file (/opt/data/mysql.pid).

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.230.100',
    -> master_user='repl',
    -> master_password='1',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)

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

#验证主从复制

主库：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ll                 |
+--------------------+
4 rows in set (0.02 sec)

mysql> create database lly;
Query OK, 1 row affected (0.00 sec)

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

从库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ll                 |
| lly                |
+--------------------+
5 rows in set (0.02 sec)

gtid主从复制(一主两从)

[root@slave]# 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
log_slave_updates = 1
gtid_mode = on
enforce_gtid_consistency = on
skip_slave_start=1

从库2：:
[root@slave2 ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
skip-name-resolve

server_id = 22
log_bin = mysql-bin
binlog_format = row
log_slave_updates = 1
gtid_mode = on
enforce_gtid_consistency = on
skip_slave_start=1

mysql> grant replication slave on *.* to 'repl'@'192.168.230.100' identified by '1';

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

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

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.230.100
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000004
          Read_Master_Log_Pos: 607
               Relay_Log_File: slave-relay-bin.000005
                Relay_Log_Pos: 820
        Relay_Master_Log_File: mysql_bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

验证主从复制
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 |      607 |              |                  | 216a775c-095f-11ec-b186-000c299fb9ba:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ll                 |
| lly                |
+--------------------+
5 rows in set (0.04 sec)

#gtid主从复制(两主一从)

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

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

[root@master ~]# 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 = 11
gtid-mode = on
enforce-gtid-consistency = on
log-bin = mysql_bin 
binlog-format = row
log-slave-updates = 1
skip_slave_start = 1 

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


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

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

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

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

验证主从复制

mysql> create database hh;
Query OK, 1 row affected (0.06 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hh                 |
| mysql              |
| performance_schema |
| ll                 |
| lly                |
+--------------------+
5 rows in set (0.05 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| ll                 |
| lly                |
+--------------------+
6 rows in set (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hh                 |
| mysql              |
| performance_schema |
| ll                 |
+--------------------+
7 rows in set (0.02 sec)