How to re-sync the Mysql DB if Master and slave have different database incase of Mysql replication?

This is the full step-by-step procedure to resync a master-slave replication from scratch:

At the master:

RESET MASTER;FLUSH TABLES WITH READ LOCK;SHOW MASTER STATUS;

And copy the values of the result of the last command somewhere.

Without closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:

mysqldump -u root -p --all-databases > /a/path/mysqldump.sql

Now you can release the lock, even if the dump hasn't ended yet. To do it, perform the following command in the MySQL client:

UNLOCK TABLES;

Now copy the dump file to the slave using scp or your preferred tool.

At the slave:

Open a connection to mysql and type:

STOP SLAVE;

Load master's data dump with this console command:

mysql -uroot -p < mysqldump.sql

Sync slave and master logs:

RESET SLAVE;CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

Where the values of the above fields are the ones you copied before.

Finally, type:

START SLAVE;

To check that everything is working again, after typing:

SHOW SLAVE STATUS;

you should see:

Slave_IO_Running: YesSlave_SQL_Running: Yes

That's it!

The documentation for this at the MySQL site is woefully out of date and riddled with foot-guns (such as interactive_timeout). Issuing FLUSH TABLES WITH READ LOCK as part of your export of the master generally only makes sense when coordinated with a storage/filesystem snapshot such as LVM or zfs.

If you are going to use mysqldump, you should rely instead on the --master-data option to guard against human error and release the locks on the master as quickly as possible.

Assume the master is 192.168.100.50 and the slave is 192.168.100.51, each server has a distinct server-id configured, the master has binary logging on and the slave has read-only=1 in my.cnf

To stage the slave to be able to start replication just after importing the dump, issue a CHANGE MASTER command but omit the log file name and position:

slaveserver> CHANGE MASTER TO MASTER_HOST='192.168.100.50', MASTER_USER='replica', MASTER_PASSWORD='asdmk3qwdq1';

Issue the GRANT on the master for the slave to use:

masterserver> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.100.51' IDENTIFIED BY 'asdmk3qwdq1';

Export the master (in screen) using compression and automatically capturing the correct binary log coordinates:

mysqldump --master-data --all-databases --flush-privileges | gzip -1 > replication.sql.gz

Copy the replication.sql.gz file to the slave and then import it with zcat to the instance of MySQL running on the slave:

zcat replication.sql.gz | mysql

Start replication by issuing the command to the slave:

slaveserver> START SLAVE;

Optionally update the /root/.my.cnf on the slave to store the same root password as the master.

If you are on 5.1+, it is best to first set the master's binlog_format to MIXED or ROW. Beware that row logged events are slow for tables which lack a primary key. This is usually better than the alternative (and default) configuration of binlog_format=statement (on master), since it is less likely to produce the wrong data on the slave.

If you must (but probably shouldn't) filter replication, do so with slave options replicate-wild-do-table=dbname.% or replicate-wild-ignore-table=badDB.% and use only binlog_format=row

This process will hold a global lock on the master for the duration of the mysqldump command but will not otherwise impact the master.

If you are tempted to use mysqldump --master-data --all-databases --single-transaction (because you only using InnoDB tables), you are perhaps better served using MySQL Enterprise Backup or the open source implementation called xtrabackup (courtesy of Percona)

Unless you are writing directly to the slave (Server2) the only problem should be that Server2 is missing any updates that have happened since it was disconnected. Simply restarting the slave with "START SLAVE;" should get everything back up to speed.