MySQL Gtid主从配置_搭建mysql gtid 主从要关闭数据库吗

Gtid配置主从（一主一从）

准备工作

• 搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器
• 主数据库：IP：192.168.218.131 无数据，二进制安装mysql
• 从数据库：IP：192.168.218.133 无数据，二进制安装mysql

## mysql Gtid主从配置

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

//配置前先关闭防火墙，主从数据库都需要关闭
[root@localhost ~]# systemctl disable --now firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@localhost ~]# setenforce 0 
[root@localhost ~]# vi /etc/selinux/config 
SELINUX=disabled

//创建同步账号
mysql> grant replication slave on *.* to 'repl'@'192.168.218.133' identified by 'repl123';

Query OK, 0 rows affected, 1 warning (0.00 sec)

//刷新权限
mysql> flush privileges;    
Query OK, 0 rows affected (0.00 sec)

//使用从数据库登录测试一下
[root@localhost ~]# mysql -urepl -p'repl123' -h192.168.218.131
mysql>   //登录成功

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

• 配置主数据库

//在/etc/my.cnf下添加内容
[root@localhost ~]# vi /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

# replication config 
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@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

//查看状态
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
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

• 配置从数据库

//在/etc/my.cnf下添加内容
[root@localhost ~]# vi /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 

#replication config 
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@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

//查看状态
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='1
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