When MySQL slave db doesn't shutdown correctly, the next time you start it, the error may occur and complaints one of the log files is not found in mysql directory.
here is the procedure to fix it.
1). MYSQL > show slave status\G;
the output as the following
Slave_IO_State:
Master_Host: 127.0.01
Master_User: remotereplicator
Master_Port: 33061
Connect_Retry: 60
Master_Log_File: HP2-bin.000016
Read_Master_Log_Pos: 819453275
Relay_Log_File: mysqld-relay-bin.000020
Relay_Log_Pos: 3311066
Relay_Master_Log_File: HP2-bin.000016
Slave_IO_Running: No
Slave_SQL_Running: No
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: 819453275
Relay_Log_Space: 0
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
1 row in set (0.02 sec)
write down the Relay_Maste_Log_File and Read_Master_Log_File_Pos value. in this case, they are HP2-bin.000016 and 819453275 respectly.
2). MYSQL> stop slave;
3). MYSQL>reset slave;
4). MYSQL>start slave;
5). MYSQL>show slave status\G;
the output should be look like this
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: remotereplicator
Master_Port: 33061
Connect_Retry: 60
Master_Log_File: HP2-bin.000003
Read_Master_Log_Pos: 3493
Relay_Log_File: mysqld-relay-bin.000006
Relay_Log_Pos: 233
Relay_Master_Log_File: HP2-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1007
Last_Error: Error 'Can't create database 'manufacture';
database exists' on query. Default database: 'manufacture'. Query: 'create
database manufacture'
Skip_Counter: 0
Exec_Master_Log_Pos: 98
Relay_Log_Space: 3628
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
1 row in set (0.00 sec)
ERROR:
No query specified
-- it tells you the database has exists. this is a good sight. it means the
slave is working.
6). MYSQL> stop slave;
7). MYSQL> CHANGE MASTER TO MASTER_HOST='127.0.01',
MASTER_USER='$remotereplicator',
MASTER_PASSWORD='$pass',MASTER_LOG_FILE='$log_file',
MASTER_LOG_POS=$log_pos;
in here,
$remotereplicator is the remote replication user
$pass is the remote replication user's password
$log_file is the remote replication file. in this case. it is HP2-bin.000016
$log_pos is the remote position, in this case, it is 819453275
8). MYSQL> start slave;
9). to confirm it is working.
MYSQL> show slave status\G;
the values of the Slave_IO_Running and the Slave_SQL_Running should be
"Yes"
