mysql传统主从及GTID主从原理及配置_mysql gtid主从原理

MySQL主从

文章目录

• MySQL主从
• • @[toc]
  • 1. 传统主从
  • • 1.1 主从原理
  • 2. GTID主从
  • • 2.1 GTID原理
  • 3. 传统主从配置
  • 4. GTID主从配置

1. 传统主从

• 主从作用
  • 实时灾备，用于故障切换
  • 读写分离，提供查询服务
  • 备份，避免影响业务
• 主从形式
  • 一主一丛
  • 主主复制
  • 一主多从（扩展系统读取的性能，以为读是在从库中读取的）
  • 多主一从（5.7版本开始支持）
  • 联级复制

1.1 主从原理

• 主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程
• 从库生成两个线程，一个I/O线程，一个SQL线程
  • I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中
  • SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的

2. GTID主从

​ GTID实际上是由UUID+TID (即transactionId)组成的。其中UUID(即server_uuid) 产生于auto.conf文件(cat /data/mysql/data/auto.cnf)，是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。GTID在一组复制中，全局唯一。

2.1 GTID原理

​ 从服务器连接到主服务器之后，把自己执行过的GTID、获取到的GTID发给主服务器，主服务器把从服务器缺少的GTID及对应的transactions发过去补全即可。

​ 当主服务器挂掉的时候，找出同步最成功的那台从服务器，直接把它提升为主即可。如果硬要指定某一台不是最新的从服务器提升为主， 先change到同步最成功的那台从服务器， 等把GTID全部补全了，就可以把它提升为主了。

GTID的工作流程：

• master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
• slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
• sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
• 如果有记录，说明该GTID的事务已经执行，slave会忽略。
• 如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
• 在解析过程中会判断是否有主键，如果有就用二级索引，如果没有就用全部扫描。

优势：

• 更简单的实现‎故障转移‎，不用像传统主从那样需要找log_file和log_pos

• 更简单的搭建主从复制

• 比传统的复制更加安全

• GTID是连续的没有空洞的，保证数据的一致性，零丢失

3. 传统主从配置

需求:
搭建两台mysql服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境说明：

配置：

• 在两台初始化环境虚拟机做以下相同配置

[root@master ~]# dnf -y install mariadb-server
Last metadata expiration check: 0:09:52 ago on Mon 01 Aug 2022 09:28:03 PM CST.
Dependencies resolved.
······
[root@master ~]# systemctl disable --now firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master ~]# vim /etc/selinux/config 
SELINUX=disabled
[root@master ~]# setenforce 0
[root@master ~]# systemctl start mariadb
[root@master ~]# ss -antl
State   Recv-Q  Send-Q    Local Address:Port     Peer Address:Port  Process  
LISTEN  0       128             0.0.0.0:22            0.0.0.0:*              
LISTEN  0       80                    *:3306                *:*              
LISTEN  0       128                [::]:22               [::]:*              
[root@master ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.28-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> set password = password('123!com');
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> quit
Bye

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

注意：
需要确保主从数据库的数据是一致的，因为此实验都为初始化环境，所以无需做备份

• 在主数据库里创建一个同步账号授权给从数据库使用

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.183.133' identified by '123!com';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> quit;
Bye
[root@master ~]# 

1
2
3
4
5
6
7
8
9
10

• 配置主数据库

[root@master ~]# cd /etc/my.cnf.d/
[root@master my.cnf.d]# ls
auth_gssapi.cnf  enable_encryption.preset  mysql-clients.cnf
client.cnf       mariadb-server.cnf
[root@master my.cnf.d]# vi mariadb-server.cnf 
	 ······
	 21 log-bin = mysql-bin	//启用binlog日志
     22 server-id = 10	//数据库服务器唯一标识符，主库的server-id至必须比从库的大
······

//重启服务
[root@master my.cnf.d]# systemctl restart mariadb
[root@master my.cnf.d]# cd
[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

//查看主库的状态
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> 

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

• 配置从数据库

[root@slave ~]# vi /etc/my.cnf.d/mariadb-server.cnf	 
	 21 server-id = 20	//设置从库的唯一标识符，从库的server-id至必须大于主库的该值
     22 relay-log = myrelay	//启用中继日志relay-log，名称可自定义，一般常用myrelay
     
//重启服务
[root@slave ~]# systemctl restart mariadb

//配置并启动主从复制
ariaDB [(none)]> change master to master_host='192.168.183.131', master_user='repl', master_password='123!com', master_log_file='mysql_bin.000001', master_log_pos=328;
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)

//以列的形式查看从服务器的状态
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.183.131
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 328
                Relay_Log_File: myrelay.000002
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql-bin.000002
              Slave_IO_Running: Yes	//此处必须为yes
             Slave_SQL_Running: Yes	//此处必须为yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 

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

• 测试验证

//在主库创建一个数据库george
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)

MariaDB [(none)]> create database george;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| george             |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> 


//在从库上查看也同样存在
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| george             |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)


//在主库插入数据
MariaDB [(none)]> use george;
Database changed
MariaDB [george]> create table student (id int not null,name varchar(50));
Query OK, 0 rows affected (0.003 sec)

MariaDB [george]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.001 sec)

MariaDB [george]> insert student values(1,'tom'),(2,'jerry');
Query OK, 2 rows affected (0.001 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [george]> 


//在从库进行查看
MariaDB [(none)]> show tables from george;
+------------------+
| Tables_in_george |
+------------------+
| student          |
+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> desc george.student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.001 sec)

MariaDB [(none)]> select * from george.student;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | jerry |
+----+-------+
2 rows in set (0.000 sec)

MariaDB [(none)]> 

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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

主从同步功能实现。

4. GTID主从配置

环境说明：
提前关闭防火墙及selinux

mysql安装方式：二进制安装

• 主库配置

//添加配置文件
[root@master ~]# vim /etc/my.cnf
 10 log-bin=mysql_bin
 11 server-id=10
 12 gtid_mode=on
 13 enforce-gtid-consistency=true
 14 log-slave-updates=on

[root@master ~]# service mysqld restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL.Logging to '/opt/data/master.err'.
. SUCCESS! 

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

• 从库配置

[root@slave ~]# vim /etc/my.cnf

 10 server-id=20
 11 relay-log=myrelay
 12 gtid_mode=on
 13 enforce-gtid-consistency=true
 14 log-slave-updates=on
 15 read_only=on
 16 master-info-repository=TABLE
 17 relay-log-info-repository=TABLE

[root@slave ~]# service mysqld restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL.Logging to '/opt/data/slave.err'.
. SUCCESS! 

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

• 主库授权复制用户

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

1
2
3

• 从库设置要同步的主库信息，并开启同步

mysql> change master to master_host='192.168.183,131',
    -> master_port=3306,master_user='repl',master_password='123com',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.183,131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: myrelay.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 

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

• 验证测试

//主库创建数据库george_1
mysql> create database george_1;
Query OK, 1 row affected (0.00 sec)

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


//在从库查看，同步到从库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| george_1           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows 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