gtid方式配置一主一从、一主多从、两主一从

最新推荐文章于 2024-08-24 02:04:49 发布

快乐风男！

最新推荐文章于 2024-08-24 02:04:49 发布

阅读量205

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/yanghx2/article/details/120030106

版权

本文详细介绍了MySQL的全局事务标识符（GTID）特性，包括其概念、优势、工作原理以及如何配置一主一从、一主多从、两主一从的主从复制。GTID简化了主从复制的配置，通过全局唯一的ID确保事务在主从间正确传播，提高了DBA的运维效率。此外，还展示了如何授权用户、修改配置文件以及验证复制效果。

摘要由CSDN通过智能技术生成

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.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.197.129' ,
    -> master_user='yanghaixing' ,
    -> 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.197.129
                  Master_User: yanghaixing
                  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             |
| yang               |
| yyy                |
+--------------------+
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.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 |
| yanghaixing         |
| mysql              |
| performance_schema |
| student             |
| yang              |
| yyy                |
+--------------------+
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.双主一从

要求
由于主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='yanghaixing' ,
    -> 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.197.129' ,
    -> master_user='yanghaixing' ,
    -> 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.197.129
                  Master_User: yanghaixing
                  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 |
| yanghaixing         |
| mysql              |
| performance_schema |
| student             |
| yang              |
| yyy                |
| teacher            |            
+--------------------+
5 rows in set (0.00 sec)

然后我们在主2上创建一个yyds。

mysql> create database yyds;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| yanghaixing         |
| mysql              |
| performance_schema |
| student             |
| yang               |
| yyy                |
| teacher            |
| yyds            |
+--------------------+
5 rows in set (0.00 sec)

最后我们在从库上开启服务后应该得到下面结果

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| yanghaixing         |
| mysql              |
| performance_schema |
| student             |
| yang               |
| yyy                |
| teacher            |
| yyds            |
+--------------------+
5 rows in set (0.00 sec)