MySQL基于gtid主从复制(一主一从、一主多从、双主一从)
MySQL基于gtid主从复制
什么是GTID?
- 全局唯一,一个事务对应一个GTID
- 替代传统的binlog+pos复制;使用master_auto_position=1自动匹配GTID断点进行复制
- MySQL5.6开始支持
- 在传统的主从复制中,slave端不用开启binlog;但是在GTID主从复制中,必须开启binlog
- slave端在接受master的binlog时,会校验GTID值
- 为了保证主从数据的一致性,多线程同时执行一个GTID
工作原理
- master更新数据时,会在事务前产生GTID,一同记录到binlog日志中
- slave端的i/o 线程将变更的binlog,写入到本地的relay log中
- sql线程从relay log中获取GTID,然后对比slave端的binlog是否有记录
- 如果有记录,说明该GTID的事务已经执行,slave会忽略
- 如果没有记录,slave就会从relay log中执行该GTID的事务,并记录到binlog
- 在解析过程中会判断是否有主键,如果没有就用二级索引,如果没有就用全部扫描
配置Gtid
一主一从
环境:
数据库角色 | IP |
---|---|
主数据库 | 192.168.72.131 |
从数据库 | 192.168.72.132 |
授权一个用户,用于主从复制
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | OFF |
| gtid_executed_compression_period | 1000 |
| gtid_mode | OFF |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
8 rows in set (0.01 sec)
mysql> create user 'xk'@'192.168.72.132' identified by "123";
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'xk'@'192.168.72.132' ;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
修改master主配文件
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
#replication config
log-bin = mysql-bin #开启二进制日志
server-id = 1
gtid-mode = on #开启gtid模式
enforce-gtid-consistency = on #强制gtid一致性,开启后对特定的create table不被支持
binlog-format = row #默认为mixed混合模式,更改成row复制,为了数据一致性
log-slave-updates = 1 #从库binlog才会记录主库同步的操作日志
skip-slave-start = 1 #跳过slave复制线程
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000014 | 1951 | | | b736875b-097e-11ec-b557-000c29810dc2:1-9 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
slave主配文件
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
# replication config
server-id = 2
log-bin = mysql-bi