When you setup replication you need to make sure that no other data is available in the slave that can cause the data to be inconsistent. But if you run into problems like I had, you might notice that the mysql replication stops as though there is an error.
What I actually forgot was that I had already deleted the “test” database on the master database. On the slave I did not do that while importing the dump. So the database remained on the slave. When I tried to test replication I had created a database called “test” to see it appear on the slaves. When I deleted it again I noticed it did not disappear on the slave. When I ran the query “SHOW SLAVE STATUS\G” I saw the error “Error ‘Can’t create database ‘test’; database exists’ on query. Default database: ‘test’. Query: ‘CREATE DATABASE test'” which of course stopped the replication process.
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 452952
Relay_Log_File: relay-bin.000004
Relay_Log_Pos: 20503
Relay_Master_Log_File: master-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 'test'; database exists' on query. Default database: 'test'. Query: 'create database test'
Skip_Counter: 0
Exec_Master_Log_Pos: 452612
Relay_Log_Space: 20992
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 'test'; database exists' on query. Default database: 'test'. Query: 'create database test'
1 row in set (0.00 sec)
If you find yourself in this situation and you are sure you know way this problem happened like I do, there is a way to solve it. It does not involve a new dump from the master that needs to be loaded into the slave.
Skip one error in the replication
There is the possibility to skip one sql statement and let the replication continue. Please be warned, this can get your database into an inconsistent state if you skip an sql statement from the replication. Make sure you understand why this error happened and that it will not cause inconsistency between your databases if you skip this sql statement.
If you are sure you want to do this, the first thing to do is to stop the replication process. The second command will set a variable telling the replication process to skip one sql statement before continuing the replication process, and then of course the replication process needs to be started again.
STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; SHOW SLAVE STATUS \G
The last statement will show the status of the replication. Check to verify that the replication is running now. What is important is that there is no error shown any more and the Seconds_Behind_Master shows a “0”, not a NULL. It might be that you notice the Seconds_Behind_Master will show a number higher then 0 which indicates that the replication is running but the mysql server still needs to catch up with the master. Depending on your traffic on the master mysql will take some time to catch up. In a low traffic environment the replication should be very fast and you might not notice any other value then 0 here.
Read more of my posts on my blog at http://blog.tinned-software.net/.
