MySQL主从（gtid模式）

最新推荐文章于 2024-04-26 13:29:49 发布

凤箫声动.

最新推荐文章于 2024-04-26 13:29:49 发布

阅读量1.2k

点赞数

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Yanghu4112/article/details/120028584

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

GTID复制原理：

基于GTID的复制是MySQL 5.6后新增的复制方式.
GTID (global transaction identifier) 即全局事务ID, 保证了在每个在主库上提交的事务在集群中有一个唯一的ID.
在原来基于日志的复制中, 从库需要告知主库要从哪个偏移量进行增量同步, 如果指定错误会造成数据的遗漏, 从而造成数据的不一致.
而基于GTID的复制中, 从库会告知主库已经执行的事务的GTID的值, 然后主库会将所有未执行的事务的GTID的列表返回给从库. 并且可以保证同一个事务只在指定的从库执行一次.

GTID是由server_uuid和事物id组成，格式为：GTID=server_uuid:transaction_id。server_uuid是在数据库启动过程中自动生成，每台机器的server-uuid不一样。uuid存放在数据目录的auto.conf文件中，而transaction_id就是事务提交时系统顺序分配的一个不会重复的序列号。

GTID的好处：

（1）GTID使用master_auto_position=1代替了binlog和position号的主从复制搭建方式，相比binlog和position方式更容易搭建主从复制。

（2）GTID方便实现主从之间的failover，不用一步一步的去查找position和binlog文件。

环境：

数据库角色	IP
主数据库	192.168.75.160
从数据库	192.168.75.150

一主一从

主库配置

创建用户进行授权


mysql> create user 'yang'@'192.168.75.150' identified by '1'; 
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'yang'@'192.168.75.150' ;
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

配置master配置文件

[root@localhost ~]# cat /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  //默认为mixd混合模式，更改成row复制
log-slave-updates = 1  //日志更新打开
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)

从库配置

配置文件

//配置文件
[root@localhost ~]# cat /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!

查看GID


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

mysql> change master to
    -> master_host='192.168.75.160' ,
    -> master_user='yang' ,
    -> master_password='1234' ,
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 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.75.160
                  Master_User: yang
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1017
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 1230
        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: 1017
              Relay_Log_Space: 1441
              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: 79a7d3f4-0a4b-11ec-ba7f-000c2988823d
             Master_Info_File: /opt/data/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: 79a7d3f4-0a4b-11ec-ba7f-000c2988823d:1-4
            Executed_Gtid_Set: 79a7d3f4-0a4b-11ec-ba7f-00c2988823d:1-4
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)


+--------------------+
5 rows in set (0.00 sec)

测试

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yyh                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 |     1173 |              |                  | 79a7d3f4-0a4b-11ec-ba7f-000c2988823d:1-5 |
+------------------+----------+--------------+----------

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yyh                |
+--------------------+
5 rows in set (0.00 sec)

一主多从

环境

数据库角色	IP
主数据库	192.168.75.160
从数据库1	192.168.75.150
从数据库2	192.168.75.170

主数据库：

//给新数据库进行创建用户并授权
mysql> create user 'ang'@'192.168.75.170' identified by "1";
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'ang'@'192.168.75.170' ;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

从库（192.168.75.170）配置

//配置文件
[root@localhost ~]# cat /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! 

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

mysql> change master to
    -> master_host='192.168.75.160' ,
    -> master_user='ang' ,
    -> master_password='1' ,
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 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.75.160
                  Master_User: ang
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1017
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 1230
        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: 1017
              Relay_Log_Space: 1441
              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: 79a7d3f4-0a4b-11ec-ba7f-000c2988823d
             Master_Info_File: /opt/data/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: 79a7d3f4-0a4b-11ec-ba7f-000c2988823d:1-4
            Executed_Gtid_Set: 79a7d3f4-0a4b-11ec-ba7f-00c2988823d:1-4
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)


+--------------------+
5 rows in set (0.00 sec)

双主多从

主库

//主库1
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000014 |     2128 |              |                  | b736875b-097e-11ec-b557-000c29810dc2:1-10 |
+------------------+----------+--------------+------------------+-------------------------------------------+
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.000002 |      154 |              |                  | b736875b-097e-11ec-b557-000c29810dc2:1-10 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

从库

//检查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)


在从库上配置主库信息

//主库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.75.160' ,
    -> master_user='yang' ,
    -> 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.75.150' ,
    -> master_user='ang' ,
    -> 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.72.131
                  Master_User: yang
                  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.72.132
                  Master_User: xk
                  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上创建数据库yyh
mysql> create database yyh;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| yyh                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


主库2上创建数据库yonghu
mysql> create database yonghu;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| yonghu             |
+--------------------+
5 rows in set (0.00 sec)



从库上开启从服务后查看
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| yyh                |
| mysql              |
| performance_schema |
| sys                |
| yonghu             |
+--------------------+
6 rows in set (0.00 sec)

凤箫声动.

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
MySQL主从（gtid模式）

GTID复制原理：基于GTID的复制是MySQL 5.6后新增的复制方式.GTID (global transaction identifier) 即全局事务ID, 保证了在每个在主库上提交的事务在集群中有一个唯一的ID.在原来基于日志的复制中, 从库需要告知主库要从哪个偏移量进行增量同步, 如果指定错误会造成数据的遗漏, 从而造成数据的不一致.而基于GTID的复制中, 从库会告知主库已经执行的事务的GTID的值, 然后主库会将所有未执行的事务的GTID的列表返回给从库. 并且可以保证同一个事务只
复制链接

扫一扫