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> //登录成功
//在/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)
//在/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