gtid方式配置一主一从、一主多从、两主一从
1.grid的概念
gtid的全称为全局事务标识符（global transaction identifiner），是MySQL 5.6引入的一个特性。gtid保证了MySQL的每一个事务都有一个全局唯一的标识，该标识在本实例甚至主从复制环境都保证全局唯一。
GTID = server_uuid:sequence_id
server_uuid是一个32字节+1字节(/0)的字符串，MySQL第一次启动时生成，并将该信息写入datadir目录下的auto.cnf文件。如果该文件丢失，MySQL会重新生成一个新的server_uuid。相同的server_uuid下的事务对应的sequence_id在binlog文件中是递增且连续有序的，他们以集合的方式呈现。
2.gtid带来得便利性
1）通过gtid定位该事务来自哪个实例
2）搭建主从复制不再需要指定binlog文件以及具体的位点信息，而是通过全局唯一的gtid来做复制，MySQL通过gtid来验证冲突并确保每个事务只会被执行一次。
3）方便DBA的运维管理，快速改变主从复制关系
3.gtid在主从复制中如何工作？
1）master执行的每一个变更操作都生成一个gtid值，并且会把该值记录在binlog中用来主从复制，后续择机更新gtid_executed表记录
2）主从复制模式下，start slave后，从库会将自身执行过的gtid集合信息发送给主库，主库将从库未执行过的binlog信息发送给从库
3）从库读取relaylog中把该值设置为当前的gtid_next值，对具体的事务进行应用，择机更新gtid_executed表记录。期间主库不断的将新产生的binlog event发送至从库
从库应用主库的事务时不会生成新的gtid值，而是将读取的gtid_next的值记录在自己的binlog文件中
4.gtid的工作原理
①当一个事务在主库端执行并提交时，产生GTID，一同记录到binlog日志中。
②binlog传输到slave,并存储到slave的relaylog后，读取这个GTID的这个值设置gtid_next变量，即告诉Slave，下一个要执行的GTID值。
③sql线程从relay log中获取GTID，然后对比slave端的binlog是否有该GTID。
④如果有记录，说明该GTID的事务已经执行，slave会忽略。
⑤如果没有记录，slave就会执行该GTID事务，并记录该GTID到自身的binlog，
在读取执行事务前会先检查其他session持有该GTID，确保不被重复执行。
⑥在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。
5.配置gtid
| 主库 | 192.168.100.146 |
|---|---|
| 从库 | 192.168.100.147 |
5.1授权一个用户，用于主从复制
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 'shenlongfei'@'192.168.100.146' identified by "1";
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'shenlongfei'@'192.168.100.147' ; //授权
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges; //刷新
Query OK, 0 rows affected (0.00 sec)
5.2.修改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 //更改成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 | 1981 | | | b736875b-097e-11ec-b557-000c29810dc2:1-9 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
5.3。配置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-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!
5.4.配置slave主机
在这里我们首先检查一下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.100.147' ,
-> master_user='shenlongfei' ,
-> master_password='1' ,
-> 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.100.146
Master_User: shenlongfei
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5.5.验证
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| shenlongfei |
| mysql |
| performance_schema |
| student |
| shen |
| fei |
+--------------------+
5 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql_bin.000006 | 1376 | | | b736875b-097e-11ec-b557-000c29810dc2:1-10 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
6.一主多从
要求
| 主机 | ip地址 |
|---|---|
| 主库 | 192.168.100.146 |
| 从库1 | 192.168.100.147 |
| 从库2 | 192.168.100.148 |
6.1.修改主配置文件
[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 //更改成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)
6.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
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!
6.2.修改从库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
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
6.3.配置
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.100.146' ,
-> master_user='shenlongfei' ,
-> master_password='1' ,
-> 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.100.146
Master_User: shenlongfei
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 1776
Relay_Log_File: localhost-relay-bin.000004
Relay_Log_Pos: 454
Relay_Master_Log_File: mysql-bin.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
6.4.验证
主里面所有的库已经全部复制过来
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| shenlongfei |
| mysql |
| performance_schema |
| student |
| shen |
| fei |
+--------------------+
5 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql_bin.000006 | 1376 | | | b736875b-097e-11ec-b557-000c29810dc2:1-10 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec）
7.双主一从
要求
| 主机 | ip地址 |
|---|---|
| 主1 | 192.168.100.146 |
| 主2 | 192.168.100.149 |
| 从1 | 192.168.100.147 |
由于主1和从1已经配置，所以我们只需要在配置一个主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 //更改成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)
配置
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)
配置主库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.100.146' ,
-> master_user='shenlongfei' ,
-> master_password='1' ,
-> master_log_file='mysql-bin.000014'
-> master_log_pos=2128
-> for channel 'master-1';
配置主库2
mysql> change master to
-> master_host='192.168.100.149' ,
-> master_user='shenlongfei' ,
-> master_password='1' ,
-> master_log_file='mysql-bin.000002'
-> master_log_pos=154
-> for channel 'master-2';
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.100.146
Master_User: shenlongfei
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 2128
Relay_Log_File: localhost-relay-bin-master@002d1.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2128
Relay_Log_Space: 544
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: b736875b-097e-11ec-b557-000c29810dc2
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: baf6e1ae-0a32-11ec-bf3d-000c29f2278e:1
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master-1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.149
Master_User: shenlongfei
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin-master@002d2.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 544
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: ba11a7ff-097f-11ec-9272-000c29959565
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: baf6e1ae-0a32-11ec-bf3d-000c29f2278e:1
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master-2
Master_TLS_Version:
2 rows in set (0.00 sec)
测试
在这里我们在主库1上创建一个teacher，看看最后的结果。
mysql> create database teacher;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| shenlongfei |
| mysql |
| performance_schema |
| student |
| shen |
| fei |
| teacher |
+--------------------+
5 rows in set (0.00 sec)
然后我们在主2上创建一个shuaiqi。
mysql> create database shuaiqi;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| shenlongfei |
| mysql |
| performance_schema |
| student |
| shen |
| fei |
| teacher |
| shuaiqi |
+--------------------+
5 rows in set (0.00 sec)
最后我们在从库上开启服务后应该得到下面结果
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| shenlongfei |
| mysql |
| performance_schema |
| student |
| shen |
| fei |
| teacher |
| shuaiqi |
+--------------------+
5 rows in set (0.00 sec)
558
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
