How to get lost data to mysql replication slave

2
votes

I'm having MASTER-SLAVE mysql replication setup.

suddenly slave crashed. And MASTER worked without any problem. Now I started the SLAVE again. And working fine. Data sync happening OK.

But the problem is, the data is not there on SLAVE at the time when slave was down. (new tables and rows of some tables).

I also did 

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxxxxx', MASTER_LOG_POS=xxxxx

with the latest position of master. But lost tables and rows not coming to SLAVE.(row counts are lesser than master on some tables;)

NOTE: Current data syncing is working. But the problem is I can't get lost data.

Is there a way to get those data without restarting master database , without going through the whole process again ?

Thanks

1
mysqlreplicationmysql-management

1 Answers

3
votes

This is how I do it when the slave gets out of sync....

On the master...

# mysqldump -u user -p --all_databases --master-data > all_mysql_data.sql

Then on the slave...

# mysql -u user -p -e 'slave stop;'

# mysql -u user -p < all_mysql_data.sql

# mysql -u user -p -e 'slave start;'

# mysql -u user -p -e 'show slave status\G;'

It would be good for you to read up on mysqldump for your version. Pay close attention to the --all_databases and --master-data options. Very powerful Ju-ju. ;)