mysql 重写日志 主备_Mysql主备问题记录

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，可以看到：

备机配置正常

持续更新中