MySQL主从GTID的配置

2021-12-23 00:41

2990

主从复制的作用

确保数据安全；做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据的丢失。
提升I/O性能；随着日常生产中业务量越来越大,I/O访问频率越来越高，单机无法满足，此时做多库的存储，有效降低磁盘I/O访问的频率，提高了单个设备的I/O性能```。

测试环境

plaintext

复制代码

Linux配置：2核 4G  （本地虚拟机2台）
Linux版本：CentOS Linux release 7.9.2009 (Core)
MySQL版本：5.7.33

主机IP地址：
192.168.70.133（主）
192.168.70.134（从）

配置前的准备工作

首先默认已经安装好两个相同版本的MySQL数据库，数据库的配置都是一样的。

plaintext

复制代码

# 查看防火墙状态
systemctl status firewalld

# 查看MySQL数据占用的端口
ss -naltp|grep mysql

# 检查MySQL数据库端口是否开放
firewall-cmd --list-all

防火墙开启的时候，一定要开放MySQL的端口，这个很简单，也很重要。

开启GTID模式

在两个数据库中，都要开启GTID模式

plaintext

复制代码

# 查看GTID配置状态
show variables like '%gtid%';

图片描述

plaintext

复制代码

开启步骤：

1.	SET GLOBAL ENFORCE_GTID_CONSISTENCY = 'WARN';

2.	SET GLOBAL ENFORCE_GTID_CONSISTENCY = 'ON';

3.	SET GLOBAL GTID_MODE = 'OFF_PERMISSIVE';

4.	SET GLOBAL GTID_MODE = 'ON_PERMISSIVE';

5.	SET GLOBAL GTID_MODE = 'ON';

要永久启用，需要在my.cnf配置文件中添加参数

plaintext

复制代码

 gtid-mode = ON
 enforce-gtid-consistency = ON

图片描述

执行后的状态：

plaintext

复制代码

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)

图片描述

在主库中执行

192.168.70.133（主）

plaintext

复制代码

# 刷新
flush privileges;

# 主库上创建repl用户
grant replication slave on *.* to 'repl'@'192.168.70.134' identified by 'repl@123!';

查看主节点二进制日志信息

plaintext

复制代码

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000006
         Position: 2604
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 604bb04d-2d6c-11ec-a50a-000c29810f0a:1-6
1 row in set (0.00 sec)

图片描述

在从库中执行

192.168.70.134（从）

plaintext

复制代码

# 刷新数据库
flush privileges;

## 从库上执行
change master to
master_host='192.168.70.133',
master_user='repl',
master_password='repl@123!',
master_auto_position=1;

## 从库中执行,启动复制线程
start slave;

查看复制状态

plaintext

复制代码

show slave status \G

详情

plaintext

复制代码

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.70.133
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 621
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 834
        Relay_Master_Log_File: mysql-bin.000006
             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: 621
              Relay_Log_Space: 1041
              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: 2236000448
                  Master_UUID: 604bb04d-2d6c-11ec-a50a-000c29810f0a
             Master_Info_File: /data/mysql/my3306/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: 604bb04d-2d6c-11ec-a50a-000c29810f0a:1-2
            Executed_Gtid_Set: 5dab439a-2d6c-11ec-a430-000c2915f9c9:1,
604bb04d-2d6c-11ec-a50a-000c29810f0a:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

图片描述

现在表示主从复制已经配置成功啦，下面我们进行数据测试，主库导入数据，看看从库是否会同步。

主库添加测试数据

在主库192.168.70.133中执行

plaintext

复制代码

# 创建数据库ncayu_test
CREATE DATABASE `ncayu_test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 使用数据库，并创建数据表books
use ncayu_test;

CREATE TABLE `books`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `book_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '书名',
  `publication_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '加入书架时间',
  `author` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '作者',
  `price` decimal(5, 2) NOT NULL COMMENT '书籍定价',
  `introduction` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '书籍简介',
  `remark` varchar(3072) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '书籍信息管理' ROW_FORMAT = Dynamic;

# 插入数据

INSERT INTO `books` VALUES (100172, 'Spring实战（第五版）', '2021-12-10 23:20:20', '张卫滨', 99.00, '本书堪称Spring开发人员的“瑞士军刀”', 'Spring');
INSERT INTO `books` VALUES (100173, '鸟哥的Linux私房菜', '2021-12-10 23:22:51', '鸟哥', 118.00, '经久不衰的Linux经典教程，前三版蝉联两岸三地Linux图书畅销榜榜首', 'Linux');

从库中查看数据是否同步

plaintext

复制代码

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

mysql> use ncayu_test;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_ncayu_test |
+----------------------+
| books                |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from books \G
*************************** 1. row ***************************
              id: 100172
       book_name: Spring实战（第五版）
publication_time: 2021-12-10 23:20:20
          author: 张卫滨
           price: 99.00
    introduction: 本书堪称Spring开发人员的“瑞士军刀”
          remark: Spring
*************************** 2. row ***************************
              id: 100173
       book_name: 鸟哥的Linux私房菜
publication_time: 2021-12-10 23:22:51
          author: 鸟哥
           price: 118.00
    introduction: 经久不衰的Linux经典教程，前三版蝉联两岸三地Linux图书畅销榜榜首
          remark: Linux
2 rows in set (0.00 sec)

mysql>

图片描述
数据已经同步到从库中了，说明配置的MySQL一主一从是成功的。