MySQL DBA - Tips and Techniques Magazine

14 Oct 2014

Move Master back to Original Server

After losing the Master and having promoted a slave to Master.  Now Move back to the Master.

On Old Master Database

·       Log on to my sql and make a note of Master_log_file and read_master_log_pos


            mysql –uuser-ppassword


            Show master status



| File                        | Position | Binlog_Do_DB | Binlog_Ignore_DB |


| mysql-bin.000007 |          98 |                            |                                |



1.     Shut down master database


            /sbin/service mysql stop        or         /etc/rc.d/mysql.server stop


·       Take a backup of the database


·       Copy backup file to PC


            cd <backup_file_location>


            cp <backup_file> /tmp


            chmod 777 <backup_file>


            scp file to PC





On New Master Database

·       Install mysql


·       Copy backup file to slave server – make sure there is enough space!


·       Check you can connect to the database


            Mysql –uroot –ppassword


·       Restore backup file to database (assumes bzip'd file)


            cd <file_name>


            nohup bunzip2 <file_name> | mysql –uroot –ppassword &


·       Check progress – are files being created


            cd  /opt/Tiscali/mysql.5.0.19             or         cd /var/lib/mysql


            ls –ltr


            cd data            or         cd ibdata


            ls –ltr


·       View my.cnf, check


            server_id = a unique value


            all lines starting master- are commented out


            remove 'read only' or 'readonly' entry



On Old Master – make this the slave

·       Start mysql


·       restart mysql as a test:


            sudo bash


            /sbin/service mysql stop;

            /sbin/service mysql start;



·       Check slave status


            mysql –uroot –ppassword 


            show slave status;


(Check replication is ok)



No comments:

Post a Comment