点击上方【运愁维卧】关注，回复消息“0101”，获取职场N多简历模板、面试经验以及IT资料包~
前期环境说明
使用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 ~]#
使用二进制方式部署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]#
构建主从
[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
[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:
……
此时故障解决，恢复主从复制。
在主库创建tianyi库
到从库上查看，OK！
★收藏 | 《实战网络设计手写笔记》
★收藏 | 《Linux基础入门手写笔记》
MySQL多实例配置
MySQL主从复制搭建
延时从库的配置及删库恢复案例
收藏 | 常用正则表达式速查手册
收藏 | 运维常见故障及分析处理28计
★收藏 | 网络设备巡检命令整理
基本/高级ACL实验配置
理论+实验详解NAT技术
SAN存储网络交换机系列之初始化
华为 | GRE over IPSec的组网
★IPSecVPN介绍 & IPSecVPN实验演示
★JumpServer堡垒机，预防飞来之“锅”
京公网安备 11010802041100号