Mysql – How to reset slave without over write of master dump in thesql

MySQLmysql-5.1mysqldump

My master having position is
File Position

mysql-bin.000046 430674598
I lost my data dir of mysql on master after that I have backup of slave which restored in master.Now Both having different log position .But I am afraid of the starting slave.the dump which i stored in master will rewrite on my salve.
slave position is
Relay_Master_Log_File:
mysql-bin.000040
Exec_Master_Log_Pos: 933087278
Relay_Log_Space: 767900934
Please suggest me way to configure it properly.
how to resync my slave ?**

Best Answer

Nice! Now the case is you have lost your datadir on master and some how you have the master log position!! Care to tell us how?

Anyways... In such case we will have to reconstruct the master from slave. But because your slave is behind master (not caughtup with binary logs) we need to fix that first.

Backup your slave before working

The slave co-ordinates are mysql-bin.000040 and some-log-pos...

This means that when your datadir was getting "lost" slave was still lagging behind.

Now, if your slave has fetched all the required binlogs (relay logs) from master before master went down then just start sql thread and let it catchup.

On Slave: Start slave sql_thread;

This will let the slave catch-up with latest available changes from master.

If your binary logs were not yet shipped (via replication) to slave, then you might want to ship them manually and play it on slave. (Provided they're available - as you say you have co-ordinates)

mysqlbinlog mysql-bin.xxxx > mysql-bin.xxxx.sql

scp all sql files to slave (You may choose to compress them)

cat mysql-bin.xxxx | mysql -uroot -p

After all these slave will have the "latest" data.

Assuming that that's the only thing we have got: - Restore your master from slave's backup. - Reconfigure replication from master to slave afresh.

For next time onwards:

  • Configure backup on slave & binary log backups if PiT restores required.
  • Have log_slave_updates.
  • Don't delete datadir on master :P
Related Question