Li_阴宅

这个屌丝很懒，什么也没留下！

热门标签

MySQL基于gtid的主从复制（一主一从、一主多从、双主一从）_gitd

作者：Li_阴宅 | 2024-08-10 16:08:19

踩

gitd

文章目录

1.Gtid概念
2. 什么是Gitd?
3. Gtid的作用
4. Gtid的工作原理
5. 事务的概述
5.1 事务的四大特征
Gtid优缺点
- 配置Gtid（均需关闭防火墙和selinux）

1.Gtid概念

从 MySQL 5.6.5 开始新增了一种基于 GTID 的复制方式。通过 GTID保证了每个在主库上提交的事务在集群中有一个唯一的ID。这种方式强化了数据库的主备一致性，故障恢复以及容错能力。

2. 什么是Gitd?

GTID 全称 global transaction identifier 全局事物id，是MASTER创建的和事物相匹配的ID号；

它不仅在本机上是唯一的，而且在其他服务器上也是唯一的；
gitd与事物一一对应
master每次commit事物的时候就会自动创建gtid
gitd格式是 GTID = source_id:transaction_id (冒号分割前边为uuid，后边为TID)
source_id 一般是发起事物的uuid, 保存在auto.cnf文件中；
transaction_id是事物id， 1-2代表第二个事物；第1-n代表n个事物

3. Gtid的作用

Gtid采用了新的复制协议,旧协议是，首先从服务器上在一个特定位置连接到主服务器上一个给定的二进制日志文件，然后主服务器再从给定的连接点开始发送所有的事件。

新协议有所不同，支持以全局统一事务ID (GTID)为基础的复制。当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务。GTID复制是全部以事务为基础，使得检查主从一致性变得非常简单。如果所有主库上提交的事务也同样提交到从库上，一致性就得到了保证。

4. Gtid的工作原理

①当一个事务在主库端执行并提交时，产生GTID，一起记录到binlog日志中。
②binlog传输到slave,并存储到slave的relaylog后，读取这个GTID的这个值设置gtid_next变量，即告诉Slave，下一个要执行的GTID值。
③sql线程从relay log中获取GTID，然后对比slave端的binlog是否有该GTID。
④如果有记录，说明该GTID的事务已经执行，slave会忽略。
⑤如果没有记录，slave就会执行该GTID事务，并记录该GTID到自身的binlog，
在读取执行事务前会先检查其他session持有该GTID，确保不被重复执行。
⑥在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

5. 事务的概述

事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务；在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务。

5.1 事务的四大特征

一般来说，事务是必须满足4个条件：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。(比如：A向B转账，不可能A扣了钱，B却没有收到)

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
1
2
3
4
5
6
7

Gtid优缺点

优点：

1.一个事务对应一个唯一GTID，一个GTID在一个服务器上只会执行一次
2.GTID是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置
3.减少手工干预和降低服务故障时间，当主机挂了之后通过软件从众多的备机中提升一台备机为主机

缺点：

1.不支持非事务引擎
2.不支持create table … select 语句复制(主库直接报错)
原理：（会生成两个sql，一个是DDL创建表SQL，一个是insert into 插入数据的sql。
由于DDL会导致自动提交，所以这个sql至少需要两个GTID，但是GTID模式下，只能给这个sql生成一个GTID ）
3.不允许一个SQL同时更新一个事务引擎表和非事务引擎表
4.开启GTID需要重启（5.7除外）
5.对于create temporary table 和 drop temporary table语句不支持
6.不支持sql_slave_skip_counter

配置Gtid（均需关闭防火墙和selinux）

一主一从

在主库上授权用于同步的用户

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

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

1
2
3
4
5
6

修改主库配置文件

[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/opt/data
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
port=3306
character-set-server=utf8
log-error=/var/log/mysqld.log
pid-file=/opt/data/mysql.pid

#replication  config
log-bin = mysql-bin 
server-id = 1
gtid-mode = on #开启gtid模式
enforce-gtid-consistency = on #强制gtid一致性，开启后对特定的create table不被支持
binlog-format = row  #默认为mixed混合模式，更改成row复制，为了数据一致性
log-slave-updates = 1  #从库binlog才会记录主库同步的操作日志
skip-slave-start = 1   #跳过slave复制线程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

重启

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

查看主库状态

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql_bin.000009 |      588 |              |                  | 394478b8-057a-11ec-962c-000c2933c088:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8

修改从库配置文件

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

# replication config
server-id = 5   
log-bin = mysql-bin 
binlog-format = row 
skip-slave-start = 1
log-slave-updates = 1
gtid-mode = on 
enforce-gtid-consistency = on  

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

查看从库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.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

mysql> change master to
    -> master_host='192.168.216.179',
    -> master_user='lhj',
    -> 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.216.179
                  Master_User: lhj
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000009
          Read_Master_Log_Pos: 588
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 801
        Relay_Master_Log_File: mysql_bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
# IO SQL 均为yes说明配置成功
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

简单验证一下

主库建个库

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

从库查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
| test3              |
+--------------------+
8 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

查看从库状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.216.179
                  Master_User: lhj
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000009
          Read_Master_Log_Pos: 912
               Relay_Log_File: localhost-relay-bin.000003
                Relay_Log_Pos: 414
        Relay_Master_Log_File: mysql_bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: No 
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1007
                   Last_Error: Error 'Can't create database 'test3'; database exists' on query. Default database: 'test3'. Query: 'create database test3'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 588
              Relay_Log_Space: 2405
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1007
               Last_SQL_Error: Error 'Can't create database 'test3'; database exists' on query. Default database: 'test3'. Query: 'create database test3'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 394478b8-057a-11ec-962c-000c2933c088
             Master_Info_File: /opt/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 210831 10:13:45
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 394478b8-057a-11ec-962c-000c2933c088:3-4
            Executed_Gtid_Set: 394478b8-057a-11ec-962c-000c2933c088:1-2,
7f2baac9-0972-11ec-8dad-000c29b81e94:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)
# 发现sql 显示为no了，但是数据确确实实同步过来了，报错显示为不能创建test3，猜想可能是之前就创建了，不能创重复的库所以同步不了
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

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

mysql> drop database test3;
Query OK, 0 rows affected (0.00 sec)

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

1
2
3
4
5
6
7
8
9

关闭主从，删除test3然后开启主从，恢复正常

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.216.179
                  Master_User: lhj
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000009
          Read_Master_Log_Pos: 912
               Relay_Log_File: localhost-relay-bin.000008
                Relay_Log_Pos: 454
        Relay_Master_Log_File: mysql_bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 912
              Relay_Log_Space: 1127
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 394478b8-057a-11ec-962c-000c2933c088
             Master_Info_File: /opt/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 394478b8-057a-11ec-962c-000c2933c088:3-4
            Executed_Gtid_Set: 394478b8-057a-11ec-962c-000c2933c088:1-4,
7f2baac9-0972-11ec-8dad-000c29b81e94:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

添加一个从库，形成一主多从

与上面从库一样的步骤，仅需改变server-id

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.216.179
                  Master_User: lhj
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000009
          Read_Master_Log_Pos: 912
               Relay_Log_File: localhost-relay-bin.000008
                Relay_Log_Pos: 454
        Relay_Master_Log_File: mysql_bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
# 确保为双yes即可
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

双主一从

主库1与主库2配置一样仅需改变serverid

从库修改配置文件后查看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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

在从库上配置主库信息
先设置总体参数

mysql> set global master_info_repository='table';
Query OK, 0 rows affected (0.00 sec)

mysql> set global relay_log_info_repository='table';
Query OK, 0 rows affected (0.00 sec)

1
2
3
4
5
6

配置主库1

mysql> change master to
    -> master_host='192.168.216.179', ##主库1的ip
    -> master_user='lhj',
    -> master_password='123' ,
    -> master_auto_position=1 for channel 'master01';#channel是一个独立的slave服务，master01是服务中的主库别名
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> change master to
    -> master_host='192.168.216.181', # 主库2的ip
    -> master_user='lhj',
    -> master_password='123' ,
    -> master_auto_position=1 for channel 'master02';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

开启主从

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

在2个主库中创建不同的库

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

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

1
2
3
4
5
6

在从库中查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lhj111             |
| lhj222             |
| mysql              |
| performance_schema |
| sys                |
| test5              |
| test7              |
+--------------------+
8 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

在主库中查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| lhj111             |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
| test3              |
| test5              |
+--------------------+
10 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| lhj222             |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
| test3              |
| test7              |
+--------------------+
10 rows in set (0.02 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

互不影响
slave相关命令：

命令	作用
show slave status；	//查看全部slave状态
show slave status for channel ‘naem’;	//查看单个slave状态
reset slave;	#重置全部slave
reset slave for channel ‘master01’;	#重置单个slave
stop slave for channel ‘master01’;	#暂停单个slave
start slave for channel ‘master01’;	#开启单个slave

本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】