gtid主从
Gtid配置主从（一主一从）
准备工作
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器
主数据库：IP：192.168.170.128 无数据，二进制安装mysql
从数据库：IP：192.168.170.129 无数据，二进制安装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.170.129' identified by '123';
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'123' -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='192.168.218.131',
-> master_user='repl',
-> 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.218.131
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql_bin.000001
Slave_IO_Running: Yes //此处必须为YES
Slave_SQL_Running: Yes //此处必须为YES
测试主从复制
//在主库上创建一个数据库school
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)
//从库去查看是否复制成功
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.01 sec)
//以上结果显示设置成功
//但是如果是在从库创建或者修改是不会同步到主库
若想停掉主从复制功能
//在从库上停掉
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.170.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000001
Read_Master_Log_Pos: 319
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 532
Relay_Master_Log_File: mysql_bin.000001
Slave_IO_Running: No //已经停掉
Slave_SQL_Running: No //已经停掉
Gtid配置主从（一主二从）
准备工作
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器
主数据库：IP：192.168.170.128 无数据，二进制安装mysql
从数据库：IP：192.168.170.129 无数据，二进制安装mysql
从数据库：IP：192.168.170.130 无数据，二进制安装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.170.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
//刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
//在主机192.168.170.131上测试是否可以登录
[root@localhost ~]# mysql -urepl -p123 -h192.168.170.128
mysql>
//登录成功
配置从数据库
//配置主机192.168.170.131
//在/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 = 21 //不要和主机192.168.218.133的重复，这里设置成21
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='192.168.170.131',
-> master_user='repl',
-> 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.170.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000001
Read_Master_Log_Pos: 614
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 827
Relay_Master_Log_File: mysql_bin.000001
Slave_IO_Running: Yes //此处必须是YES
Slave_SQL_Running: Yes //此处必须是YES
测试主从复制
//主库内有库school，在从库192.168.170.128内查看是否同步成功
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)
//结果显示可以看到库school，一主而从设置成功
配置一主而从需要注意点
从库在/etc/my.cnf下添加内容时server-id要和另一个从库区分开，不能一样，但是要大于主库即可
在从库编写change master 时注意master_host要写主库IP，这里是192.168.170.128
在创建用户时，因为是两个从库，可以设置成同一网段，上述配置即设置为192.168.170.%
Gtid配置主从（二主一从）
准备工作
主数据库：IP：192.168.170.128 无数据，二进制安装mysql
主数据库：IP：192.168.170.131 无数据，二进制安装mysql
从数据库：IP：192.168.170.129 无数据，二进制安装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.128.129' identified by '123';
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 -p123 -h192.168.170.131
mysql>
//登录成功
配置主库
//192.168.170.128这台主库在配置一主二从的时候配置过了，这里就不重复配置了，现在配置192.168.170.131这台主机
[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 = 9 //区别于另外一台主库，设置为9
gtid-mode = on
enforce-gtid-consistency = on
log-bin = mysql_bin
binlog-format = row
log-slave-updates = 1
skip-slave-start = 1
//重启服务
[root@localhost ~]# vi /etc/my.cnf
[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)
配置从库
//之前配置一主二从的时候已经在/etc/my.cnf添加了内容，这里不重复添加
[root@localhost ~]# 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
#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
//将信息存储库设置为table格式
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)
//配置主从复制
mysql> change master to
-> master_host='192.168.170.128',
-> master_user='repl',
-> master_password='123',
-> master_auto_position=1 for channel 'master01';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> change master to
-> master_host='192.168.170.131',
-> master_user='repl',
-> master_password='123',
-> master_auto_position=1 for channel 'master02';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
//开始主从复制
mysql> start slave ;
Query OK, 0 rows affected (0.01 sec)
//查看主从复制状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.170.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000001
Read_Master_Log_Pos: 1027
Relay_Log_File: localhost-relay-bin-master01.000002
Relay_Log_Pos: 1240
Relay_Master_Log_File: mysql_bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.170.131
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000003
Read_Master_Log_Pos: 194
Relay_Log_File: localhost-relay-bin-master02.000004
Relay_Log_Pos: 407
Relay_Master_Log_File: mysql_bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试
//在主库192.168.170.131上创建库test,创建表tescher
mysql> desc tescher;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)
//从库查看是否同步成功
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec) //可以看到库192.168.170.131上创建的库test和192.168.170.128上的库school
mysql> desc tescher;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
//在表tescher插入几条数据
mysql> insert tescher(name,age) values('zhangsan',10),('lisi',20),('wangwu',30);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from tescher;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 10 |
| 2 | lisi | 20 |
| 3 | wangwu | 30 |
+----+----------+------+
3 rows in set (0.00 sec)
//在从库查看表tescher中的内容
mysql> select * from tescher;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 10 |
| 2 | lisi | 20 |
| 3 | wangwu | 30 |
+----+----------+------+
3 rows in set (0.00 sec)
//在主机128上创建了库test1,测试是否可以看到
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
| test |
| test1 |
+--------------------+
7 rows in set (0.00 sec)
浙公网安备 33010602011771号