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.
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.
