Last_IO_Errno: 1236
slave查看show slave status\G;
显示Last_IO_Errno: 1236
模拟方法：master上执行reset master
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
[root@data-1-1 ~]
# tail -3 /data/3306/mysql-bin.index
/data/3306/mysql-bin
.000020
/data/3306/mysql-bin
.000021
/data/3306/mysql-bin
.000022
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "reset master;"
[root@data-1-1 ~]
# tail -3 /data/3306/mysql-bin.index
/data/3306/mysql-bin
.000001
[root@data-1-1 ~]
# ll /data/3306/
total 36
drwxr-xr-x 10 mysql mysql 4096 Jan 16 22:03 data
-rw-r--r-- 1 mysql mysql 1899 Oct 29 2013 my.cnf
-rwx------ 1 mysql mysql 1310 Jan 10 19:14 mysql
-rw-rw---- 1 mysql mysql 107 Jan 17 16:55 mysql-bin.000001
-rw-rw---- 1 mysql mysql 28 Jan 17 16:55 mysql-bin.index
-rw-rw---- 1 mysql mysql 6 Jan 11 00:04 mysqld.pid
-rw-r----- 1 mysql root 10264 Jan 14 00:10 mysql_oldboy3306.err
srwxrwxrwx 1 mysql mysql 0 Jan 11 00:04 mysql.sock
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "create database t99;"
|
slave上检查同步状态
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.1.81
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000022
Read_Master_Log_Pos: 107
Relay_Log_File: data-1-2-relay-bin.000010
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000022
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 107
Relay_Log_Space: 455
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:
'could not find next log; the first event '
mysql-bin.000019
' at 556,<br>the last event read from '
/data/3306/mysql-bin
.000022
' at 107, the last byte read from '
/data/3306/mysql-bin
.000022
' at 107.'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row
in
set
(0.00 sec)
ERROR:
No query specified
|
问题原因：
根据主从同步的原理，slave向master请求binlog时，master不仅把log发送过来，还会发送日志文件是哪个，以及下一次读取的位置信息。
master上执行了reset master命令后，导致日志文件从1开始了，这样从库根据之前获取的位置信息就不存在了，自然报错
解决方法：
master上找出当前日志文件最接近上一次slave同步的位置。下面可以找到是at 4，也可以使用at 107
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
[root@data-1-1 ~]
# mysqlbinlog /data/3306/mysql-bin.000001 >bin-1.log
[root@data-1-1 ~]
# less bin-1.log
[root@data-1-1 ~]
# cat bin-1.log
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170117 16:55:09 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.32-log created 170117 16:55:09 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
7dt9WA8BAAAAZwAAAGsAAAABAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADt231YEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#170117 16:56:18 server id 1 end_log_pos 188 Query thread_id=150 exec_time=0 error_code=0
SET TIMESTAMP=1484643378/*!*/;
SET @@session.pseudo_thread_id=150/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *
//
*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database t99
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@data-1-1 ~]
#
|
slave执行如下过程
stop slave;
change master to master_log_file='mysql-bin.000001',master_log_pos=107;
start slave;
show slave status\G;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_log_file=
'mysql-bin.000001'
,master_log_pos=107;
Query OK, 0 rows affected (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host: 10.0.1.81
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 188
Relay_Log_File: data-1-2-relay-bin.000002
Relay_Log_Pos: 334
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 188
Relay_Log_Space: 493
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row
in
set
(0.00 sec)
ERROR:
No query specified
mysql>
|
额外补充点：
这个reset master 会影响和它直接连接的slave，而不是影响slave级联的slave。
比如下面主从架构，master上执行reset master，slave1会被影响到，报错，slave2不出报错，但是slave2和slave1数据库里数据都是一样的。
master--->slave1---->slave2
因为slave1作为级联的中继端，会产生binlog，而slave2获取的是slave1的binlog
Last_SQL_Errno: 1007
模拟方法：
slave2从库执行reset slave
（master--->slave1---->slave2）此处我是在slave2上执行的reset slave操作，slave2在data-1-1机器上，slave1在data-1-2机器上
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
mysql> reset slave;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.1.82
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 0
Relay_Log_Space: 126
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: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12
1 row
in
set
(0.00 sec)
ERROR:
No query specified
|
继续同步，出现1007报错
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host: 10.0.1.82
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 348
Relay_Log_File: relay-bin.000006
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1007
Last_Error: Error
'Can'
t create database
'tt'
; database exists
' on query. Default database: '
tt
'. Query: '
create database tt'
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 541385
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: 1007
Last_SQL_Error: Error
'Can'
t create database
'tt'
; database exists
' on query. Default database: '
tt
'. Query: '
create database tt'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12
1 row
in
set
(0.00 sec)
ERROR:
No query specified
|
解决方法：找到master点的最新位置信息at 348
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
[root@data-1-2 ~]
# mysqlbinlog /application/mysql/data/mysql-bin.000004 >bin4.log
[root@data-1-2 ~]
# tail -10 bin4.log
# at 267
#170117 16:56:18 server id 1 end_log_pos 348 Query thread_id=150 exec_time=618 error_code=0
SET TIMESTAMP=1484643378/*!*/;
create database t99
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@data-1-2 ~]
# mysqlbinlog /application/mysql/data/mysql-bin.000004 >bin4.log
[root@data-1-2 ~]
# tail -10 bin4.log
# at 348
#170117 17:43:17 server id 1 end_log_pos 429 Query thread_id=152 exec_time=3 error_code=0
SET TIMESTAMP=1484646197/*!*/;
create database t10
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@data-1-2 ~]
#
|
slave2上执行如下，主从同步恢复正常
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
mysql> change master to master_log_file=
'mysql-bin.000004'
,master_log_pos=348;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host: 10.0.1.82
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 429
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 334
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 429
Relay_Log_Space: 484
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12
1 row
in
set
(0.00 sec)
ERROR:
No query specified
mysql>
|
Last_Errno: 1008
1008：数据库不存在，删除数据库失败
master-->slave1-->slave2
data-1-1上有master（端口是3306），data-1-2机器是slave1（端口3306），data-1-1上还有个slave2（端口是3307）
模拟1008错误，在slave2上把一部分库删除。由于slave1还master和slave2，这里拿slave2做实验
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "drop database d19;"
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "drop database d29;"
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "drop database dd10;"
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "drop database t16;"
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "drop database t10;"
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| t1 |
|
test
|
+--------------------+
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| d19 |
| d29 |
| dd10 |
| mysql |
| performance_schema |
| t1 |
| t10 |
| t16 |
|
test
|
| uu |
| zyx |
+--------------------+
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "drop database d19;"
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "drop database d29;"
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "drop database dd10;"
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "drop database t16;"
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "drop database t10;"
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show databases;"
|
slave2出现错误1008错误
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show slave status\G;"
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host: 10.0.1.82
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 2367
Relay_Log_File: relay-bin.000005
Relay_Log_Pos: 1055
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1008
Last_Error: Error
'Can'
t drop database
'd19'
; database doesn
't exist'
on query. Default database:
'd19'
. Query:
'drop database d19'
Skip_Counter: 0
Exec_Master_Log_Pos: 1970
Relay_Log_Space: 1602
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: 1008
Last_SQL_Error: Error
'Can'
t drop database
'd19'
; database doesn
't exist'
on query. Default database:
'd19'
. Query:
'drop database d19'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12
[root@data-1-1 ~]
#
|
解决过程：
其实也就3步：1：停止slave。 2：跳过错误。3：开启slave
stop slave
set global sql_slave_skip_counter=5
start slave
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "stop slave;"
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "set global sql_slave_skip_counter=5;"
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "start slave;"
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show slave status\G;" | grep Last_SQL_Errno
Last_SQL_Errno: 0
[root@data-1-1 ~]
# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show slave status\G;"
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host: 10.0.1.82
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 2367
Relay_Log_File: relay-bin.000006
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 2367
Relay_Log_Space: 1748
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12
[root@data-1-1 ~]
#
|
168
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
