MySQL 基于GTID主从
1、GTID主从概念
GTID是一个基于原始mysql服务器生成的一个已经被成功执行的全局事务ID，它由服务器ID以及事务ID组合而成。这个全局事务ID不仅仅在原始服务器器上唯一，在所有存在主从关系 的mysql服务器上也是唯一的。正是因为这样一个特性使得mysql的主从复制变得更加简单，以及数据库一致性更可靠。
1、全局事务标识：global transaction identifiers。
2、GTID是一个事务一一对应，并且全局唯一ID。
3、一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。
4、GTID用来代替传统复制方法，不再使用MASTER_LOG_FILE+MASTER_LOG_POS开启复制。而是使用MASTER_AUTO_POSTION=1的方式开始复制。
5、MySQL-5.6.5开始支持的，MySQL-5.6.10后开始完善。
6、在传统的slave端，binlog是不用开启的，但是在GTID中slave端的binlog是必须开启的，目的是记录执行过的GTID（强制）。
2、工作原理
1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中
2、 slave端的i/o 线程将变更的binlog，写入到本地的relay log中
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录
4、如果有记录，说明该GTID的事务已经执行，slave会忽略
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog
6、在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描
3、Gtid的作用
Gtid采用了新的复制协议，旧协议是，首先从服务器上在一个特定的偏移量位置连接到主服务器上一个给定的二进制日志文件，然后主服务器再从给定的连接点开始发送所有的事件。
新协议有所不同，支持以全局统一事务ID (GTID)为基础的复制。当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务。GTID复制是全部以事务为基础，使得检查主从一致性变得非常简单。如果所有主库上提交的事务也同样提交到从库上，一致性就得到了保证。
3、配置Gtid
一主一从
3.1 授权用户,用于主从复制
mysql> create user 'cai'@'192.168.200.145' identified by "123";
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'cai'@'192.168.200.146';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
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)
3.2修改主配文件
[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.000011 | 1728 | | | 79a7d3f4-0a4b-11ec-ba7f-000c298 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
4. 修改从服务主配置文件
[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-bin
binlog-format = row
skip-slave-start = 1
log-slave-updates = 1
gtid-mode = on
enforce-gtid-consistency = on
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
## 检查gtid状态
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 |
+----------------------------------+-----------+
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to
-> master_host='192.168.153.133' ,
-> master_user='xu' ,
-> master_password='xu1' ,
-> 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.200.145
Master_User: cai
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 1728
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5. 验证结果
mysql> create database xym;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| cai |
+--------------------+
5 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql_bin.000005 | 1478 | | | c736875b-097e-11ec-b557-000c29810dc2:1-9 |
+------------------+----------+--------------+------------------+-------------------------------------------+
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| cai |
+--------------------+
5 rows in set (0.00 sec)
一主多从
配置一主多从，还需要在主库master再上添加从库就行
第一台主库配置与之前配置一致
第一台从库与上一致
第二台从库配置
## 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 = 3 //第三台从服务器id 不能与前面使用过的一样
log-bin = mysql-bin
binlog-format = row
skip-slave-start = 1
log-slave-updates = 1
gtid-mode = on
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 |
+----------------------------------+-----------+
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to
-> master_host='192.168.58.120' ,
-> master_user='xu' ,
-> master_password='xu1' ,
-> 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.200.147
Master_User: cai
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 1458
Relay_Log_File: localhost-relay-bin.000012
Relay_Log_Pos: 464
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| cai |
+--------------------+
5 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+
| mysql_bin.000008 | 694 | | | b736875b-097e-16ec-b557-000c29810dc2:1-10,
ba15a7ff-097f-11ec-9272-000c29859565:1,
dca6f885-0a26-11ec-81cc-000c29g2278e:1 |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+
双主多从
//查看两个主库状态
主库1:
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 | 1953 | | | 79a7d3f4-0a4b-11ec-ba7f-000c2988823d:1-9 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
主库2:
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003| 2248 | | | 79a7d3f4-0a4b-11ec-ba7f-000c2988823d:1-9 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
从库配置
//查看状态
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)
//重置slave
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
配置主库1
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.200.145' ,
-> master_user='ao' ,
-> master_password='123' ,
-> master_log_file='mysql-bin.000002'
-> master_log_pos=1953
-> for channel 'master-1';
//配置主库2
mysql> change master to
-> master_host='192.168.200.145' ,
-> master_user='ao' ,
-> master_password='123' ,
-> master_log_file='mysql-bin.000003'
-> master_log_pos=2468
-> for channel 'master-2';
//开启slave
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.200.145
Master_User: xym
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1953
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 2166
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
//主库1上创建数据库xym
mysql> create database cai;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| cai |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
//主库2上创建数据库ao
mysql> create database ao;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| cai |
| ao |
+--------------------+
5 rows in set (0.00 sec)
//slave上查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| cai |
| ao |
+--------------------+
6 rows in set (0.00 sec)
1279
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
