使用gtid搭建一主两从数据库

重点提示：如果备份一个GTID的主库，记住备份语句一定不要将才参数设为OFF：--set-gtid-purged=OFF

GTID 是全局唯一的一个id号，在二进制binlog当中，所有的事物都有一个GTID编号，而且这边编号是连续不可间断的，

对于主从复制类型的数据库，有以下3个重要参数：

gtid-mode=on # 开启gtid

enforce-gtid-consistency=true #强制gtid的一致性主从复制必须要配置的参数

log-slave-updates=1 #配置在从库中的参数，在高可用环境中除了主节点之外的所有节点都要开启二进制日志使用环境(高可用环境和级联环境的中间库）

总之配GTID复制的时候都加上以下这3个参数就行了：

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

搭建gtid一主两从复制案例：

1准备三台虚拟机：

2清理掉之前的mysql环境：

 

pkill mysqld
\rm -rf /data/*
mkdir -p /data/mysql/data
mkdir -p /data/binlog/
chown -R mysql.mysql /data

 

3准备配置文件：

主库db01:

cat > /etc/my.cnf <<EOF
mysqld]
user=mysql
basedir=/application/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=201
port=3306
secure-file-priv=/tmp
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
autocommit=0
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/mysql/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db01 [\d]>
socket=/tmp/mysql.sock
EOF

从库db02

cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/application/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=202
port=3306
secure-file-priv=/tmp
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
autocommit=0
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/mysql/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db02 [\d]>
socket=/tmp/mysql.sock
EOF

从库db03

cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/application/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=203
port=3306
secure-file-priv=/tmp
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
autocommit=0
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/mysql/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db03 [\d]>
socket=/tmp/mysql.sock
EOF

4分别对三台虚拟机的节点进行初始化数据

 

mysqld --initialize-insecure --user=mysql --lower-case-table-names=1 --datadir=/data/mysql/data --basedir=/application/mysql

 

5启动三台虚拟机数据库，如果、etc/init.d目录下没有mysqld文件，执行下列步骤再启动：

[root@db01 init.d]# find /application/* -name mysql.server
/application/mysql/support-files/mysql.server
[root@db01 init.d]# cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@db01 init.d]# /etc/init.d/mysqld start

6关闭三台虚拟机防火墙：

防火墙状态：systemctl status firewalld.service

Active: inactive (dead)/active(running)

关闭firewall：systemctl stop firewalld.service

重启：systemctl restart firewalld.service

启动：systemctl start firewalld.service

禁止firewall开机启动:systemctl disable firewalld.service

设置开机启用：systemctl enable firewalld.service

查看服务是否开机启动：systemctl is-enabled firewalld.service;echo 

查看已启动的服务列表：systemctl list-unit-files|grep enabled

查看已经开放的端口：firewall-cmd --list-ports

7在主库创建复制用户：

[root@db01 init.d]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

db01 [(none)]>db01 [(none)]>grant all privileges on *.* to repl@'10.0.0.%' identified by "123" WITH GRANT OPTION ;
Query OK, 0 rows affected, 1 warning (0.07 sec)

db01 [(none)]>

db01 [(none)]>flush privileges;
Query OK, 0 rows affected (0.01 sec)

8在另外两台虚拟机中开启主从并查询状态：

db02 [(none)]>CHANGE MASTER TO

-> MASTER_HOST='10.0.0.201',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123',
-> MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.09 sec)

db02 [(none)]>start slave;
Query OK, 0 rows affected (0.05 sec)

db02 [(none)]>show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.201
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

 9如果io线程出现 Slave_IO_Running: Connecting可能是因为防火墙未关闭，如果Slave_SQL_Running: NO请用以下方式试试处理：

主库：

db01 [(none)]>select user,host from mysql.user
    -> ;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| repl          | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.07 sec)

db01 [(none)]>drop user repl@'10.0.0.%';
Query OK, 0 rows affected (0.05 sec)

db01 [(none)]>grant all privileges on *.* to repl@'10.0.0.%' identified by "123" WITH GRANT OPTION ;
Query OK, 0 rows affected, 1 warning (0.05 sec)

db01 [(none)]>select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| repl          | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

db01 [(none)]>flush privileges;
Query OK, 0 rows affected (0.01 sec)

db01 [(none)]>reset master;
Query OK, 0 rows affected (0.12 sec)

db01 [(none)]>select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| repl          | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

从库：

db02 [(none)]>stop slave;
Query OK, 0 rows affected (0.01 sec)

db02 [(none)]>reset slave all;
Query OK, 0 rows affected (0.03 sec)
db02 [(none)]>CHANGE MASTER TO
    ->   MASTER_HOST='10.0.0.201',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='123',
    ->   MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.09 sec)

db02 [(none)]>start slave;
Query OK, 0 rows affected (0.05 sec)

db02 [(none)]>show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.201
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: db02-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes