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

 

·       DO NOT START DATABASE

 

                             

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