Fixing MySQL Replication-with minimum downtime

Its a very critical task for a system Admin/DBA to fix the broken MySQL replication when the data in the system is crucial and does not afford any type of data loses.


Before starting the following tutorial Please fire stop slave on both the server if you have master master replication on slave server if have master slave.to avoid any kind of data loses.                 

mysql>stop slave;

If you are using INNODB as a storage/database engine they its also very critical to fixx the replication bacause though you drop the database from server innodb will never release the hard disk space acquired by the database






Note* before doing any thing Take the backup of all the databases available in the mysql.

ibdata stores all of the UNDO LOGS thus GROWS due to the deletes and space is never reclaimed.

To regain the harddisk space  you need to delete the ibdata file and log file of mysql which are in the /var/lib/mysql

Note* For our convenience we can keep the backup of ibdata and iblogs file 

cp -ar /var/lib/mysql/ib* /tmp/
rm /var/lib/mysql/ib*
service mysqld restart

This process with again regenerate the mysql back with the shrink ibdata file and log file.and mysql will again come up then you can restore all the other databases which are not under the replication.

then take the Mysql Consistent snapshot from the other server(which is having the latest data).
open two terminal and from one login into the mysql
select the database which is under the  rpelication

mysql> use <database name>
mysql> flush tables with read lock;
mysql> show master status;

 Note down the master log file and log position of master or take a snapshot of the terminal.

on the other terminal start taking the backup using mysqldump

#mysqldump -u root -p <databasename> > <databasename.sql>

A SQL dump file get generated and we can copy the file to other server(affected server)

#scp <databasename.sql> <user>@<serverip>:/tmp

after the dump completes fire on first terminal
mysql >unlock tables;
and close the terminal on master.

On the affected server open the mysql and fire 
 mysql >SET sql_log_bin =0;

This will ignore the bin log while restore and You are safe during the restore that data not get replicated from one to another server
then use the restore command  which can be done in two ways.
using the shell and other from mysql

#mysql -u root -p <databasename> < <databasename>.sql
or 
#mysql -u root -p 
mysql> use <databasename>;
mysql> source  <path/to/databasename.sql> 

Note  if my file is in  tmp and database file name as newdb.sql then
mysql> source /tmp/newdb.sql 

ofter the restore please note down the servers master log possition useg command

mysql> show master status;
mysql >SET sql_log_bin =1; # enable the bin log on the affected server

then use change master to command and again re-point the servers

change master to
master_host='ipaddress of master',
master_user='user_having_replication slave_privilege',
master_password='user_password' ,
MASTER_LOG_FILE ='mysql log name of master which we have noted first',
 MASTER_LOG_POS = position no  in log filewhich we have noted first ;

And we have done with the fixing of the server with the minimal down time.