GTID方式构建MySQL主从环境并排障

运愁维卧 2021-07-01

332

点击上方【运愁维卧】关注，回复消息“0101”，获取职场N多简历模板、面试经验以及IT资料包~

前期环境说明

10.233采用的cmake编译（参考下文）

使用cmake方式编译安装MySQL5.7.33

[root@ansible_nfs ~]#    more etc/my.cnf 
[mysqld]
user=mysql
basedir=/usr/local/mysql/
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306


[client]
socket=/tmp/mysql.sock
[root@ansible_nfs ~]#

10.234采用的二进制部署（参考下文）

使用二进制方式部署MySQL5.7.33

[mysqld]
user=mysql
basedir=/application/mysql/
datadir=/mysqldata/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306


[client]
socket=/tmp/mysql.sock
[root@web etc]#

构建主从

主库10.234，修改配置文件如下

[root@web etc]#  cat etc/my.cnf 
[mysqld]
user=mysql
basedir=/application/mysql/
datadir=/mysqldata/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/mysqldata/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1


[client]
socket=/tmp/mysql.sock
prompt=db01 [\\d]>
[root@web etc]#

创建binlog目录并授权，重启服务

[root@web etc]# mkdir mysqldata/binlog
[root@web etc]# chown -R mysql.mysql mysqldata/*
[root@web etc]# systemctl restart mysqld

从库10.233，修改配置文件如下

[root@ansible_nfs ~]#   cat etc/my.cnf 
[mysqld]
user=mysql
basedir=/usr/local/mysql/
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
server_id=66
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/usr/local/mysql/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1


[client]
socket=/tmp/mysql.sock
prompt=db02 [\\d]>
[root@ansible_nfs ~]#

创建binlog目录并授权，重启服务

[root@ansible_nfs ]# mkdir usr/local/mysql/data/binlog/
[root@ansible_nfs ]# chown -R mysql.mysql /usr/local/mysql/*
[root@ansible_nfs ]# systemctl start mysqld

在主库10.234中创建用于主从复制的专用用户

db01 [(none)]>grant replication slave  on *.* to sync@'192.168.10.%' identified by 'sync123';

在从库10.233配置主库信息

db02 [(none)]>change master to 
    -> master_host='192.168.10.234',
    -> master_user='sync',
    -> master_password='sync123' ,
    -> MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)


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


db02 [(none)]>show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.234
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 448
               Relay_Log_File: ansible_nfs-relay-bin.000002
                Relay_Log_Pos: 661
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
……

至此采用GTID方式的主从环境就构建完成了。

故障排查

构建主从完成后，手误将从库上的一个数据库wuxi给删除了，导致了主从不同了，查看状态报错如下

主要就是无法再删除数据库wuxi了

Slave_SQL_Running: No
Last_Error: Error 'Can't drop database 'wuxi'; database doesn't exist' on query. Default database: 'wuxi'. Query: 'drop database wuxi'
Last_SQL_Error: Error 'Can't drop database 'wuxi'; database doesn't exist' on query. Default database: 'wuxi'. Query: 'drop database wuxi'

为了重新恢复主从同步，尝试将主库的wuxi也删除，并在从库stop slave及reset slave all后，重新start slave均没解决。既然从库已经删除了，提示无法删除wuxi这个库了。那么就stop slave后，再将wuxi这个库创建上，最后在start slave，那么在同步时候会自动将新建的wuxi这个库给删除了。

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


db02 [(none)]>create database wuxi;
Query OK, 1 row affected (0.01 sec)


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


db02 [(none)]>show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.234
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1066
               Relay_Log_File: ansible_nfs-relay-bin.000005
                Relay_Log_Pos: 454
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
……