mysql 生产问题处理

mysql死锁处理

处理方式1

-- 查看 InnoDB 状态
SHOW ENGINE INNODB STATUS;
 
-- 查看死锁日志部分
-- ... 省略其他输出信息 ...
------------------------
LATEST DETECTED DEADLOCK
------------------------
... 死锁信息 ...
 
-- 根据日志中的线程ID kill 线程
KILL 线程ID1;
KILL 线程ID2;

处理方式2

show processlist;
select * from information_schema.processlist where time>100 and command<>'sleep';
# 查看有没运行的时间很长的，
kill xxx

mysql 因事务导致的集群服务宕机（主从数据不一致）

背景

今天在批量导入时，由于客户重复导入相同文件，导致出现死锁，但是并没有找到对应死锁的线程, 数据库当时的状态是只可读不可写

当我随便修改一条数据，出现报错

Mysql server is running with the --super-read-only option so it cannot execute xxx

通过报错可以看到此时mysql变成了只读模式，这是因为集群已经出现了异常，mysql的自我保护机制触发。

错误处理

当出现只读后，我百度了一下，他们说把read-only模式改成off，但是当我操作后依然不起作用，甚至出现了更严重的后果

从节点与主节点数据不一致了

错误排查

1. 查看集群状态

# 先进ProxySQL服务
mysql -uproxysqladmin -pxxx -h127.0.0.1 -P6032
# 查询集群状态，是不是有问题
select hostgroup_id, hostname, port,status from runtime_mysql_servers order by hostgroup_id;
select * from mysql_server_group_replication_log order by time_start_us desc  limit 6;

# 获取直接在navicat上执行查看集群状态
SELECT * FROM performance_schema.replication_group_members;

2. 重启节点

# 先重启主节点，再重启从节点,注意要使用systemctl命令，不能用service mysql restart
systemctl restart mysqld

3. 一般重启后，主从节点应该是能正常的，但是如果主从节点数据不一致了，那么从节点的状态依然是不对的，

   这时就需要重新创建集群了。备份主库数据 -> 发送到从节点 -> 还原数据 -> 重启从节点

4. 集群引导

# 这条命令开启了集群引导模式。在集群尚未初始化的情况下，必须先设置此选项为ON才能开始集群的初始化过程
SET GLOBAL group_replication_bootstrap_group=ON;
# 启动Group Replication服务。当集群处于引导模式时，这条命令会创建一个新的集群实例。
start group_replication;
# 在成功启动并加入集群后，应将此选项设置回OFF，以防止其他节点意外地创建新的集群实例
SET GLOBAL group_replication_bootstrap_group=OFF;

5. 备份主库数据(bash执行)

xtrabackup --user=root --password={{mysql_root_password}} --backup --target-dir={{mysql_backup_path}} --no-server-version-check

6. 发送备份数据至从节点

scp -r {{mysql_backup_path}} name@ip:/xx/xx

如果没有scp命令，可直接用以下命令安装

rm -rf /etc/yum.repos.d/*.repo
curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
curl -o /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum makecache
yum install openssh-clients -y

如果服务器未开启root登录，那么可以先在从服务器上用root创建对应文件夹，再将文件夹归属改掉就行

chown -R name:name /xx/xx

7. 从节点执行以下命令,一条一条复制执行即可

#!/bin/sh
#尝试停止mysql服务（避免重复安装时出现错误）
echo "【开始】停止MySQL服务"
systemctl stop mysqld
echo "【完成】停止MySQL服务"
#尝试删除数据目录（避免重复安装时出现错误）
echo "【开始】删除MySQL数据目录与bin-log目录"
rm -rf {{mysql_data_path}} {{mysql_bin_log_path}}
echo "【完成】删除MySQL数据目录与bin-log目录"
#重新创建数据库目录与bin-log目录
echo "【开始】重新创建MySQL数据目录与bin-log目录，并更改所有权"
mkdir {{mysql_data_path}} {{mysql_bin_log_path}} && chown -R mysql:mysql {{mysql_data_path}} {{mysql_bin_log_path}}
echo "【完成】重新创建MySQL数据目录与bin-log目录，并更改所有权"
#检查待还原数据库
echo "【开始】检查待还原数据库"
xtrabackup --prepare --target-dir={{mysql_backup_path}}
echo "【完成】检查待还原数据库"
#还原数据库
echo "【开始】还原数据库"
xtrabackup --prepare --target-dir={{mysql_backup_path}}
echo "【开始】还原数据库"
xtrabackup --copy-back --target-dir={{mysql_backup_path}}
echo "【完成】还原数据库"
echo "【开始】修复数据库与bin-log目录所有权"
chown -R mysql:mysql {{mysql_data_path}} {{mysql_bin_log_path}}
echo "【完成】修复数据库与bin-log目录所有权"
echo "【开始】启动数据库服务"
systemctl start mysqld
echo "【完成】启动数据库服务"

#执行到这一步，一般就能解决了，下面的不用执行了
echo "【开始】重置集群节点GTID"
mysql -e "reset slave;" --connect-expired-password
echo "【完成】重置集群节点GTID"
echo "【开始】重新启动数据库服务"
systemctl restart mysqld
echo "【完成】重新启动数据库服务"
#获取集群状态
sleep 10
mysql -e "SELECT * FROM performance_schema.replication_group_members;" --connect-expired-password

8. 再用查看集群的命令查看集群状态即可

# 直接在navicat上执行查看集群状态
SELECT * FROM performance_schema.replication_group_members;