MySQL主从复制技术(GTID)
配置MySQL基于GTID的复制
这篇微博是基于GTID实现两个服务器的主从复制。
基于二进制文件建立的主从复制见我的另一篇微博:https://blog.csdn.net/liuhuan_study/article/details/81017395
GTID主从复制的配置思路
一、环境准备
MySQL 主库 masterIP: 172.16.7.129
MySQL 从库 slaveIP: 172.16.7.128
二、修改配置文件
修改后主库的配置文件为:
[mysqld]
server-id=1
binlog_format = row
expire_logs_days = 30
max_binlog_size= 100M
port=3306
basedir=/opt/obj/mysql
datadir=/opt/obj/mysql/data
log-bin=mysql-bin
gtid-mode=on
enforce_gtid_consistency = ON
binlog-checksum = CRC32
master-verify-checksum = 1
enforce-gtid-consistency=true
修改后从库的配置文件为:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=2
log-bin=/var/lib/mysql/mysql-bin
gtid-mode=on
enforce-gtid-consistency=true
skip-slave-start = true
expire_logs_days = 30
max_binlog_size= 100M
master-info-repository = table
relay-log-info-repository = table
relay-log-recovery = ON
三、创建连接用户,连接主从复制
在主数据库上操作:
mysql> CREATE USER 'repl'@'172.16.7.128' IDENTIFIED BY 'slavepass';#创建用户
如果创建用户出现密码规则错误,执行下一句,否则,跳过
set global validate_password_policy=0;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.16.7.128'; #分配权限
mysql>ALTER USER 'repl'@'172.16.7.128' IDENTIFIED WITH mysql_native_password BY 'slavepass';
mysql>flush privileges; #刷新权限
mysql>show master status;
红线处等下要用
在从数据库上操作:
mysql>CHANGE MASTER TO MASTER_HOST='172.16.7.129', MASTER_USER='repl', MASTER_PASSWORD='slavepass', master_auto_position=1;
mysql> reset master;
mysql> SET @@GLOBAL.GTID_PURGED='4abaa7cd-8365-11e8-9bdc-000c29975404:1-32';
mysql> start slave;
主从复制连接成功,进行测试。
在主数据库新建数据库,新建完成可在从数据库查到。具体的测试步骤就省略了。