mysql 掉电_mysql因为掉电，导致不同步的恢复

最新推荐文章于 2023-07-03 21:09:50 发布

邦加拉斯

最新推荐文章于 2023-07-03 21:09:50 发布

阅读量281

点赞数

文章标签： mysql 掉电

本文链接：https://blog.csdn.net/weixin_42401159/article/details/114334510

版权

有mysql-mm数据库一套，36 和37 ，由于掉电，导致其中一台数据库down机，然后查看数据库日志发现

如果目前以36为主，37为slave

查看37服务器

2015-06-19 10:23:09 3109 [ERROR] Slave I/O: error connecting to master 'repl_user@ip' - retry-time: 60 retries: 1, Error_code: 2003

2015-06-19 10:24:09 3109 [Note] Slave I/O thread: connected to master 'repl_user@ip',replication started in log 'mysql-bin.000377' at position 16063934

2015-06-19 11:19:22 3109 [Warning] Hostname 'localhost' does not resolve to 'ip'.

2015-06-19 11:19:22 3109 [Note] Hostname 'localhost' has the following IP addresses:

2015-06-19 11:19:22 3109 [Note] - ::1

2015-06-19 11:19:22 3109 [Note] - 127.0.0.1

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host:ip

Master_User: repl_user

Master_Port: 3366

Connect_Retry: 60

Master_Log_File: mysql-bin.000389

Read_Master_Log_Pos: 23841730

Relay_Log_File: mysql-bin.000464

Relay_Log_Pos: 852

Relay_Master_Log_File: mysql-bin.000377

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB: mysql,information_schema,performance_schema,test

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1032

Last_Error: Could not execute Delete_rows event on table zjstms.tak_trackdetails; Can't find record in tak_trackdetails', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000377,

end_log_pos 16064750

Skip_Counter: 0

Exec_Master_Log_Pos: 16064503

Relay_Log_Space: 322379477

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 1032

Last_SQL_Error: Could not execute Delete_rows event on table zjstms.tak_trackdetails; Can't find record in 'tak_trackdetails', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000377,

end_log_pos 16064750

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: aa90a402-a73c-11e4-8ab7-a0369f338279

Master_Info_File: /usr/local/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp: 150623 17:25:59

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

如果下面参数是NO，对应修改的的参数是：

Slave_IO_Running: 指的就是从服务器上负责读取主服务器的线程工作状态。从服务器用这个专门的线程链接到主服务器上，并把日志拷贝回来。

Slave_SQL_Running: 指的就是专门执行sql的线程。它负责把复制回来的Relaylog执行到自己的数据库中。这两个参数必须都为Yes 才表明复制在正常工作。

Master_Log_File & Read_Master_Log_Pos：下一个传输的主日志信息(其实就是主服务器上的日志位置)。

Relay_Master_Log_File & Exec_Master_Log_Pos：下一个执行的主日志信息。

Relay_Log_File & Relay_Log_Pos：下一个执行的中继日志信息。

从字面意思看， (Relay_Master_Log_File, Exec_Master_Log_Pos)和(Relay_Log_File, Relay_Log_Pos)是对应的，它们表示的是Slave中的SQL进程中正在执行的语句的位置，表明的是Slave和Master之间的同步状态。当Slave中Relay_Master_Log_File和Master_Log_File相同且Read_Master_Log_Pos和Exec_Master_Log_Pos完全相同时，表明Slave和Master处于完全同步的状态。既然有了(Relay_Master_Log_File, Exec_Master_Log_Pos)能够表明同步状态，还要(Relay_Log_File, Relay_Log_Pos)做什么呢？我感觉(Relay_Log_File, Relay_Log_Pos) 是给程序看的，记录Relay log中的执行点，而(Relay_Master_Log_File, Exec_Master_Log_Pos)可以给人看，能够清楚的表明Master和Slave之间的同步状态。

Slave_IO_Running：对应：Master_Log_File & Read_Master_Log_Pos

Slave_SQL_Running 对应：Relay_Master_Log_File & Exec_Master_Log_Pos

如果显示No，则说明前面某些配置步骤出错，或者对应的日志文件有问题。

解决方案：

STOP SLAVE ;

change master to master_log_file='mysql-bin.000377', master_log_pos=16063934;

start SLAVE;

Slave_SQL_Running: NO

Seconds_Behind_Master: NULL

一般是事务回滚造成的：

解决方法:

stop slave;

set global sql_slave_skip_counter =1 ;

mysql> START SLAVE SQL_THREAD ;

start slave;

之后Slave会和Master去同步主要看:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master是否为0，0就是已经同步了

查看你跳过去的日志内容，最后检查表中数据是否一致；

mysqlbinlog mysql-bin.000097|tail

mysqlbinlog mysql-bin.000097 --start-datetime="2010-01-07 11:25:56" --stop-datetime="2010-01-07 13:23:50"

目前以37为主，36为slave

再去看36服务器上：

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: ip2

Master_User: repl_user

Master_Port: 3366

Connect_Retry: 60

Master_Log_File: mysql-bin.000461

Read_Master_Log_Pos: 9636028

Relay_Log_File: mysql-bin.000379

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000461

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB: mysql,information_schema,performance_schema,test

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 9636028

Relay_Log_Space: 497

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 1236

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size; the first event 'mysql-bin.000461' at 9636028, the last event read from '/usr/local/mysql/mysql_log/mysql-bin.000461' at 4, the last byte read from '/usr/local/mysql/mysql_log/mysql-bin.000461' at 4.'

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 2

Master_UUID: 48625158-a758-11e4-8b6b-a0369f33803b

Master_Info_File: /usr/local/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp: 150623 15:14:45