MySQL示例DTID主从原理解析 MySQL示例DTID主从原理解析

1.GTID基本概念
2.GTID优点
3.GTID的工作原理
4.GTID比传统复制的优势
5.启动的方法
6.GTID(一主一从)配置

6.1环境：
6.2在主库上给从库授权：
6.3确保数据一致操作
6.4配置主库
6.5配置从库
6.6配置主从复制

7.GTID（一主俩从）

8.GTID（俩主一从）

1.最新环境
2.所有服务器均关闭防火墙或者放行防火墙
3.授权连接

master01库授权普通用户
slave进行连接
master02授权普通用户
slave进行连接

4.分别进行配置文件修改

5.分别重启

6.在进行GTID多主一从配置前，先引入一个概念

7.slave从库以root用户登录进行GTID配置

GTID(俩主一从)测试：
slave相关命令：
解决问题

1.GTID基本概念 MySQL 5.6.5开始支持的，全局事务标识符（GTID（Global Transaction ID））是创建的唯一标识符，并与在源（主）服务器上提交的每个事务相关联。
此标识符不但是唯一的，而且在给定复制设置中的所有服务器上都是唯一的。
所有交易和所有GTID之间都有一对一的映射关系。
它由服务器ID以及事务ID组合而成。
这个全局事务ID不仅仅在原始服务器上唯一，在所有存在主从关系的mysql服务器上也是唯一的。
正是因为这样一个特性使得mysql的主从复制变得更加简单，以及数据库一致性更可靠。
一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。

2.GTID优点 【MySQL示例DTID主从原理解析】保证同一个事务在某slave上绝对只执行一次，没有执行过的gtid事务总是会被执行。
不用像传统复制那样保证binlog的坐标准确，因为根本不需要binlog以及坐标。
故障转移到新的master的时候很方便，简化了很多任务。
很容易判断master和slave的数据是否一致。只要master上提交的事务在slave上也提交了，那么一定是一致的。

3.GTID的工作原理

文章图片

1.当一个事务在主库端执行并提交时，产生GTID，一同记录到binlog日志中。
2.binlog传输到slave,并存储到slave的relaylog后，读取这个GTID的这个值设置gtid_next变量，即告诉Slave，下一个要执行的GTID值。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有该GTID。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会执行该GTID事务，并记录该GTID到自身的binlog，在读取执行事务前会先检查其他session持有该GTID，确保不被重复执行。
6、在解析过程中会判断是否有主键，如果有就用二级索引，如果没有就用全部扫描。

4.GTID比传统复制的优势 1.更简单的实现故障转移（failover），不需要找log_file,log_pos
2.更简单的搭建主从复制
3.更加安全
4.GTID是连续没有空洞的，因此主数据库发生冲突时，可以添加空事件的方式进行跳过

5.启动的方法

方法一：如果是新搭建的服务器，直接启动即可
方法二：如果是以及跑的服务器，需要重启一下mysql server

启动前，先关闭master的写入，保证master端和slave端数据保持同步，所有slave需要加上skip_slave_start=1的配置参数，避免启动后还是使用之前的复制协议

6.GTID(一主一从)配置
6.1环境：

centos8.0 ip：192.168.136.239 有数据 hostname：mysql01
centos8.0 ip：192.168.136.219 无数据 hostname：mysql02

#二进制安装以及mysql自启动服务略

6.2在主库上给从库授权：

mysql> grant replication slave on *.* to 'slave'@'192.168.136.219' identified by 'slave'; Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)#俩服务器均关闭防火墙[root@mysql01 ~]# systemctl stop firewalld[root@mysql01 ~]# setenforce 0[root@mysql02 ~]# systemctl stop firewalld[root@mysql02 ~]# setenforce 0从库测试连接：[root@mysql02 ~]# mysql -u slave -p'slave' -h192.168.136.239Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement.mysql>

6.3确保数据一致操作

1.对主库进行锁表mysql> flush tables with read lock; 2.对主库进行全备[root@mysql01 ~]# mysqldump -uroot -A > /clq/all-databases-20210519.sql3.拷贝到从库主机上去[root@mysql01 ~]# scp /clq/all-databases-20210519.sql root@192.168.136.219:/backup/[root@mysql02 backup]# ll-rw-r--r--. 1 root root 873527 5月19 16:40 all-databases-20210519.sql4.从库上进行主库的恢复[root@mysql02 backup]# mysql -uroot -pHuawei0917@ < all-databases-20210519.sql

6.4配置主库

[mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306user = mysqlpid-file = /opt/data/mysql.pidskip-name-resolve#skip-grant-tableslog-bin = master_bin#开启主库日志server-id = 10#服务唯一标识idgtid-mode = on#GTID模式开启enforce_gtid_consistency = on #强制gtid模式一致性log-slave-updates = 1#从库允许更新日志，同步操作日志binlog_format = row#binlog日志格式为行格式, 默认是mixed混合模式skip_slave_start = 1#跳过从库开启，以主库开始开启#重启systemctl restart mysqld

6.5配置从库

[root@mysql02 data]# cat /etc/my.cnf[mysqld]basedir = /usr/local/mysql datadir = /opt/data socket = /tmp/mysql.sock port = 3306user = mysqlpid-file = /opt/data/mysql.pidskip-name-resolve#skip-grant-tables gtid_mode=onenforce_gtid_consistency=onserver-id=20log-bin=slave_binlog#开启从库日志log_slave-updates=1#从库允许更新binlog_format=row#格式为行skip-slave_start=1#重启systemctl restart mysqld

查看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|+----------------------------------+-----------+8 rows in set (0.00 sec)

6.6配置主从复制

#从库上root登录配置#help change master to 可以查看帮助文档实例mysql> change master to-> master_host='192.168.136.239',-> master_user='slave',-> master_password='slave',-> master_port=3306,#主库端口-> master_auto_position=1; #位置#master_use_gtid = current_posQuery 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; Slave_IO_Running: ConnectingSlave_IO_Running: YesSlave_SQL_Running: Yes保证系统一致性授权一致性

（一主一从GTID）测试：
主库创建一个数据库test，进行测试查看
从库创建一个数据库test02，进行测试查看

#主库创建一个test数据库mysql> create database test; mysql> show databases; +--------------------+| Database|+--------------------+| information_schema || mysql|| performance_schema || sys|| test|+--------------------+#从库上查看同步情况mysql> show databases; +--------------------+| Database|+--------------------+| information_schema || mysql|| performance_schema || sys|| test|+--------------------+6 rows in set (0.00 sec)#从库创建test02库mysql> create database test02; Query OK, 1 row affected (0.00 sec)#主库上查看mysql> show databases; +--------------------+| Database|+--------------------+| information_schema || mysql|| performance_schema || sys|#是没有test02库的| test|+--------------------+5 rows in set (0.00 sec)

小结：主库上的数据操作会同步到从库上面去，而从库上的数据操作与主库没联系

7.GTID（一主俩从）第三台mysql连接的话，相应配置

第3台mysql ，版本:centos8 ip:192.168.136.230 主机名:mysql03

[root@mysql03 ~]# cat /etc/my.cnf [mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306user = mysqlpid-file = /opt/data/mysql.pidskip-name-resolve#skip-grant-tables# replication configlog-bin = master_binserver-id = 21#id必须与之前不同gtid-mode = onenforce-gtid-consistency = onlog-slave-updates = 1binlog-format = rowskip-slave-start = 1#查看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|+----------------------------------+-----------+#由于之前只权限了一个ip，此刻在mysql01主数据库上再授权一个ipmysql> grant replication slave on *.* to 'slave'@'192.168.136.230' identified by 'slave'; Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)#测试连接[root@mysql ~]#mysql -uslave -pslave -h192.168.136.239mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 17Server version: 5.7.33-log MySQL Community Server (GPL)Copyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement.mysql> #mysql03从库上root用户连接进行相应配置[root@mysql03 ~]#mysql -uroot -p1mysql> change master to-> master_host='192.168.136.239',#主库ip-> master_user='slave',#主库授权的普通用户-> master_password='slave',-> master_port=3306,#主库端口-> master_auto_position=1; #位置从1开始同步#也可以查看帮助进行配置mysql> help change master to; CHANGE MASTER TOMASTER_HOST='source2.example.com',MASTER_USER='replication',MASTER_PASSWORD='password',MASTER_PORT=3306,MASTER_LOG_FILE='source2-bin.001',MASTER_LOG_POS=4,MASTER_CONNECT_RETRY=10; URL: https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html#开启mysql> start slave; mysql> show slave status\G; *************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.136.239Master_User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: master_bin.000002Read_Master_Log_Pos: 2172Relay_Log_File: mysql-relay-bin.000002Relay_Log_Pos: 2387Relay_Master_Log_File: master_bin.000002Slave_IO_Running: YesSlave_SQL_Running: Yes#显示俩个yes则运行成功！#mysql03查看数据库，数据库内容也同步成功mysql> show databases; +--------------------+| Database|+--------------------+| information_schema || mysql|| performance_schema || sys|| test|+--------------------+5 rows in set (0.00 sec)

8.GTID（俩主一从）
1.最新环境

版本	ip	主机名	身份
centos8	192.168.136.239	master01	主库
centos8	192.168.136.219	master02	主库
centos8	192.168.136.230	slave	从库

2.所有服务器均关闭防火墙或者放行防火墙

[root@master01 ~]# systemctl stop firewalld[root@master01 ~]# systemctl disable firewalld[root@master02 ~]# systemctl stop firewalld[root@master02 ~]# systemctl disable firewalld[root@slave ~]# systemctl stop firewalld[root@slave ~]# systemctl disable firewalld

3.授权连接

master01库授权普通用户

mysql> grant replication slave on *.* to'user'@'192.168.136.%' identified by 'user';

slave进行连接

[root@slave ~]# mysql -uuser -p'user' -h192.168.136.239mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.7.33 MySQL Community Server (GPL)Copyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement.

master02授权普通用户

mysql> grant replication slave on *.* to'app'@'192.168.136.%' identified by 'app'; Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

slave进行连接

[root@slave ~]# mysql -uapp -papp -h192.168.136.219mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.7.33 MySQL Community Server (GPL)Copyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement.mysql>

4.分别进行配置文件修改

#master01主机：[root@master01 ~]# cat /etc/my.cnf [mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306user = mysqlpid-file = /opt/data/mysql.pidskip-name-resolveskip-grant-tableslog-bin = master_binserver-id = 10gtid-mode = onenforce-gtid-consistency = onlog-slave-updates = 1binlog-format = rowskip-slave-start = 1#master02主机[mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306user = mysqlpid-file = /opt/data/mysql.pidskip-name-resolve#replication configlog-bin = master_binserver-id = 11gtid-mode = onenforce-gtid-consistency = onlog-slave-updates = 1binlog-format = rowskip-slave-start = 1#slave主机[mysqld]basedir = /usr/local/mysqldatadir = /opt/datasocket = /tmp/mysql.sockport = 3306user = mysqlpid-file = /opt/data/mysql.pidskip-name-resolvelog-bin = slave_binserver-id = 13gtid-mode = onenforce-gtid-consistency = onlog-slave-updates = 1binlog-format = rowskip-slave-start = 1

5.分别重启

[root@master01 ~]# systemctl restart mysqld[root@master02 ~]# systemctl restart mysqld[root@slave ~]# systemctl restart mysqld

6.在进行GTID多主一从配置前，先引入一个概念
channel(频道)：每一个channel都是一个独立的slave服务，都有一个IO_THREAD和SQL_THREAD，原理和普通复制一样，只是需要在change master to语句后面使用FOR Channel来进行区分slave
在使用channel时需要将从库的master-info-repository、relay-log-info-repository设置为table，否则会报错。
将信息存储库设置为table格式

方式一（mysql内设置）:set global master_info_repository='table'; set global relay_log_info_repository='table'; 方式二（/etc/my.cnf内设置）:3.在my.cnf中设置master_info_repository= TABLE relay_log_info_repository = TABLE#检查是否更改成功mysql> show variables where variable_name in('relay_log_info_repository','master_info_repository'); +---------------------------+-------+| Variable_name| Value |+---------------------------+-------+| master_info_repository| TABLE || relay_log_info_repository | TABLE |+---------------------------+-------+

7.slave从库以root用户登录进行GTID配置

#slave从库上配置俩个主库GTID复制mysql> change master to-> master_host='192.168.136.219',#mysql02主库ip-> master_user='app',#mysql02主库授权的普通用户-> master_password='app',#mysql02主库授权的普通用户密码-> master_port=3306,#主库端口-> master_auto_position=1 for channel 'master01'; #位置从1开始同步,并且第一个slave取名master01mysql> change master to-> master_host='192.168.136.239',#mysql01主库ip-> master_user='user',-> master_password='user',-> master_port=3306,#主库端口-> master_auto_position=1 for channel 'master02'; #位置从1开始同步,并且第一个slave取名master01#查看俩个slave状态mysql> show slave status\G; *************************** 1. row ***************************Slave_IO_State: Master_Host: 192.168.136.219Master_User: appMaster_Port: 3306Connect_Retry: 60Master_Log_File: Read_Master_Log_Pos: 4Relay_Log_File: slave02-relay-bin-master1.000001Relay_Log_Pos: 4Relay_Master_Log_File: Slave_IO_Running: NoSlave_SQL_Running: No#都是关闭的Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 0Relay_Log_Space: 154Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0Master_UUID: Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Master_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: b4326a77-0a31-11ec-a991-000c298d3571:1-2,d68b404d-0a35-11ec-9df1-000c29581959:1Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: master1Master_TLS_Version: *************************** 2. row ***************************Slave_IO_State: Master_Host: 192.168.136.239Master_User: userMaster_Port: 3306Connect_Retry: 60Master_Log_File: Read_Master_Log_Pos: 4Relay_Log_File: slave02-relay-bin-master2.000001Relay_Log_Pos: 4Relay_Master_Log_File: Slave_IO_Running: NoSlave_SQL_Running: NoReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 0Relay_Log_Space: 154Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0Master_UUID: Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Master_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: b4326a77-0a31-11ec-a991-000c298d3571:1-2,d68b404d-0a35-11ec-9df1-000c29581959:1Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: master2Master_TLS_Version: 2 rows in set (0.00 sec)#开启俩个slavemysql> start slave; #再次查看状态

GTID(俩主一从)测试：

#master01主库创建一个test数据库mysql> create database test; Query OK, 1 row affected (0.00 sec)mysql>show databases; +--------------------+| Database|+--------------------+| information_schema || mysql|| performance_schema || sys|| test|+--------------------+5 rows in set (0.00 sec)#master02主库上查看mysql>show databases; +--------------------+| Database|+--------------------+| information_schema || mysql|| performance_schema || sys|#没有内容+--------------------+4 rows in set (0.00 sec)#slave从库查看mysql> show databases; +--------------------+| Database|+--------------------+| information_schema || mysql|| performance_schema || sys|| test|#已经同步了test库+--------------------+5 rows in set (0.00 sec)#mysql02主库创建一个RHCA数据库mysql> create database RHCA; Query OK, 1 row affected (0.01 sec)mysql>show databases; +--------------------+| Database|+--------------------+| information_schema || RHCA|| mysql|| performance_schema || sys|+--------------------+5 rows in set (0.00 sec)#slave从库mysql> show databases; +--------------------+| Database|+--------------------+| information_schema || RHCA|| mysql|| performance_schema || sys|#有了mysql01主库的test库和mysql02的RHCA的库| test|+--------------------+6 rows in set (0.00 sec)

slave相关命令：

show slave status； //查看全部slave状态
show slave status for channel ‘naem'; //查看单个slave状态
reset slave; #重置全部slave
reset slave for channel ‘master1'; #重置单个slave
stop slave for channel ‘master1'; #暂停单个slave
start slave for channel ‘master1'; #开启单个slave

虽然我在做的过程没有遇到错误，但是下面这个是最最容易出现的错误
配置完开启slave出现报错