Mysql主备问题记录

felixxue

发布于 2022-12-30 09:59:53

1.2K0

发布于 2022-12-30 09:59:53

文章被收录于专栏：xueflyxuefly

1. 备上报错找不到log文件，

问题背景：在配置好的mysql主备环境上，正常运行状态下，两台服务器断电，上电后报错如下：

mysql报错

错误日志：

2019-08-05 09:35:43 29817 [ERROR] Slave I/O: error reconnecting to master 'root@172.103.201.105:23306' - retry-time: 10  retries: 1, Error_code: 2003
2019-08-05 09:35:55 29817 [Note] Slave: connected to master 'root@172.103.201.105:23306',replication resumed in log 'mysql-bin.000005' at position 963
2019-08-05 09:35:55 29817 [ERROR] Error reading packet from server: Could not open log file (server_errno=1236)
2019-08-05 09:35:55 29817 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Could not open log file', Error_code: 1236

在主上发现mysql服务无法启动，日志报错：

2019-08-05 10:06:08 22242 [Note] InnoDB: 5.6.30 started; log sequence number 3827823
/var/hms/base_service/mysql/mysql/bin/mysqld: File '/var/hms/base_service/mysql/backup/mysql-bin.000006' not found (Errcode: 2 - No such file or directory)
2019-08-05 10:06:08 22242 [ERROR] Failed to open log (file '/var/hms/base_service/mysql/backup/mysql-bin.000006', errno 2)
2019-08-05 10:06:08 22242 [ERROR] Could not open log file
2019-08-05 10:06:08 22242 [ERROR] Can't init tc log
2019-08-05 10:06:08 22242 [ERROR] Aborting

打开mysql-bin.index，发现index中文件已经记录到mysql-bin.000006，而真实不存在这个文件，因此mysql启动时报找不到binlog日志文件；解决此问题第一步：编辑mysql-bin.index，删除mysql-bin.000006记录，重启mysql，服务即可启动第二步，在备机上发现主备同步状态未有任何改变；仍然报错，究其原因，实际上就是备机上的relay-bin log与主机上的mysql-bin log不匹配导致，思路就是清理掉备机上的relay log 在备机上进入mysql命令行执行：

stop slave;
reset slave;
CHANGE MASTER TO MASTER_HOST='xx.xx.xx.xx',
    MASTER_PORT=3306,
    MASTER_USER='root',
    MASTER_PASSWORD='$rootpasswd',
    MASTER_AUTO_POSITION=1,
    MASTER_CONNECT_RETRY=10,
    MASTER_HEARTBEAT_PERIOD=10000 
start slave;

再执行 show slave status\G，可以看到：