Dec
01
2015
--

How to upgrade your master server with minimal downtime

Master SlaveHere’s a step-by-step guide on how to invert roles for master and slave so you can perform a master server upgrade, and then switch roles back to the original setup.

* While following this guide consider server-A as your original master and server-B as your original slave. We will assume server-B already produces binlogs and that both nodes have log-slave-updates=1 in my.cnf


Check this following link for more details on log-slave-updates:
https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_log-slave-updates

1. Prepare the original Slave to invert roles.

Make sure the slave is not in read-only mode. This should be set to 0, if not change it:

server-B> SELECT @@global.read_only;
server-B> SET GLOBAL read_only=0

server-B> SET GLOBAL read_only=0


* For critical service, you might also want to make sure the slave is actually a perfect replica by running a checksum of the tables with pt-table-checksum.

 

Check

SHOW SLAVE STATUSG

  on server-B until you see Seconds_Behind_Master is zero

server-B> SHOW SLAVE STATUSG

 

When server-B is fully caught up, then issue 

FLUSH TABLES WITH READ LOCK;

  in a mysql client prompt in server-A

At this point your users will suffer a service request interruption and downtime starts ticking.

* DO NOT CLOSE this mysql client; otherwise the lock will be lost. We’ll use this same session to run the rest of the commands in server-A.

server-A> FLUSH TABLES WITH READ LOCK;

 

Get master coordinates:

server-A> SHOW MASTER STATUS;

 

Run 

SHOW SLAVE STATUSG

  in server-B
Repeat until

Relay_Master_Log_File

  and

Exec_Master_Log_Pos

  matches file and position from the previous step.

server-B> SHOW SLAVE STATUSG

 

Check if

SHOW MASTER STATUS;

  is not changing on server-B (to ensure that there are no queries local to server-B), then stop the slave

server-B> SHOW MASTER STATUS;
server-B> STOP SLAVE;
server-B> RESET SLAVE ALL;

 

Run

SHOW MASTER STATUSG

  in server-B and save this information in a safe place.

Also make sure binlogs from that position and onwards are kept until you bring server-A back online. Otherwise you’ll need to rebuild server-A.

server-B> SHOW MASTER STATUSG


2. Reverse roles for Master – Slave and upgrade original Master

Direct traffic to server-B (point VIP to server-B, or through DNS -not really recommended-, or by updating the configuration files of API services).

In a separate shell session in server-A, stop mysql

Now server-A can be shut down and serviced

* At this point you’re safe to upgrade server-A.

* After you’ve finished upgrading your server you can continue this guide to put server-A back as master.

 

Start MySQL in server-A (with skip-slave-start!) and run

RESET SLAVE ALL;


server-A> RESET SLAVE ALL;

 

Reconfigure slave in A with 

CHANGE MASTER TO ...

  and start it
Make sure to put the same

MASTER_LOG_FILE

  and

MASTER_LOG_POS

  values as you previously got from running

SHOW MASTER STATUSG

  on server-B.

 

server-A> CHANGE MASTER TO MASTER_HOST='<IP-of-server-B>', MASTER_USER='<your-replication-user>, MASTER_PASSWORD='<replication-user-password>', MASTER_LOG_FILE='<file-from-serverB>, MASTER_LOG_POS=<pos-from-serverB->;
server-A> SET GLOBAL read_only=1;
server-A> START SLAVE;

 

Make sure replication is running

Repeat until you see

Seconds_Behind_Master

  is 0.

server-A> SHOW SLAVE STATUSG

* Here is the moment where the roles are fully inverted, and A is an up-to-date slave of B.

 

3. Prepare the original Master to set back the original roles.

Prepare

When server-A is fully caught up, then issue

FLUSH TABLES WITH READ LOCK;

  in a mysql client prompt in server-B.

DO NOT CLOSE this mysql client on server-B; otherwise the lock will be lost. We’ll use this same session to run the rest of the commands in server-B.

server-B> FLUSH TABLES WITH READ LOCK;

* DO NOT CLOSE THIS CLIENT SESSION! *

 

Check master status

server-B> SHOW MASTER STATUS;

 

Run

SHOW SLAVE STATUSG

  in server-A. Repeat until

Relay_Master_Log_File

  and

Exec_Master_Log_Pos

  matches the file and position from

SHOW MASTER STATUS;


server-A> SHOW SLAVE STATUSG

 

Get master coordinates:

server-A> SHOW MASTER STATUSG
server-A> SET GLOBAL read_only=0;

4. Set back the original roles for Master – Slave

Direct traffic to server-A (point VIP to server-A, or through DNS -not really recommended-, or by updating the configuration files of API services).

In a separate shell session in server-B, restart mysql.

Make server-B again a slave of server-A:

server-B> CHANGE MASTER TO MASTER_HOST='<IP-of-server-A>', MASTER_USER='<your-replication-user>, MASTER_PASSWORD='<replication-user-password>', MASTER_LOG_FILE='<file-from-server-A>, MASTER_LOG_POS=<pos-from-server-A>;
server-B> SET GLOBAL read_only=1;
server-B> START SLAVE;
server-B> SHOW SLAVE STATUSG

 

Stop the slave threads in server-A:

server-A> STOP SLAVE;
server-A> RESET SLAVE ALL;

 

* If you have a chained replication setup you should take into consideration the log_slave_updates variable, especially as it is used in this case and should be enabled.
i.e.: In a replication chain A > B > C, for C to receive updates from A, B will need to log them to the binary logs and that is what this option does.

For more information regarding this variable, please check the following link:
https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_log_slave_updates

The post How to upgrade your master server with minimal downtime appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com