Our slave, which is just used for backups, is out of sync. It has crashed out on a key restraint.
I would like to find a way to resync up the slave without taking the master offline, I know I can do it that way but I believe it is possible not to.
I have "High Performance MySQL" infront of me and it points me in the direction of maatkit, specifically mk-table-sync.
For the life of me I can't get mk-table-sync to work.
I run it like so on the slave:
root@machine:~# mk-table-sync --sync-to-master --dry-run 127.0.0.1
# Syncing h=127.0.0.1
# DELETE REPLACE INSERT UPDATE ALGORITHM EXIT DATABASE.TABLE
# 0 0 0 0 Chunk 0 database.case_study_product
# 0 0 0 0 Chunk 0 database.case_study_region
# 0 0 0 0 Chunk 0 database.case_study_sector
# 0 0 0 0 Chunk 0 database.contact
# 0 0 0 0 Chunk 0 database.contact_issue
# 0 0 0 0 Chunk 0 database.feedback
# 0 0 0 0 Chunk 0 database.feedback_rating
# 0 0 0 0 Chunk 0 database.feedback_usefulness
# 0 0 0 0 Chunk 0 database.help
# 0 0 0 0 Chunk 0 database.help_issue
# 0 0 0 0 Chunk 0 database.search_weight
# 0 0 0 0 Chunk 0 database.contented_content
# 0 0 0 0 Nibble 0 database.contented_content_index
# 0 0 0 0 Chunk 0 database.contented_content_log
I know for a fact that contented_content and contented_content_index are out of sync. But from what I can tell form that output maatkit thinks everything is fine.
Here is the output of slave status:
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.40.12
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000023
Read_Master_Log_Pos: 25832973
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 19098703
Relay_Master_Log_File: mysql-bin.000001
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: 1452
Any help, pointers…ask for more info..etc
Best Answer
Right after a lot of head scratching and playing around on my virtual environment this is how I managed to sync my slave up to the master.
1) In the database (mydb) on the master I want to create the following table:
2) On the master run the following command:
3) On the slave run the following command:
When I tried running the checksum replicate command on the slave before running the sync command that didn't do anything at all.
The slave connected and working in my example, also I disabled foreign keys checks because we are using INNODB and was getting foreign key constraint issues when running the sync.