How to update a MySQL master-slave setup

Sometimes you'll get a requirement to upgrade the MySQL setup for VA fixes.

Upgrading Steps

• Upgrade the Slave DB node
• Sync the Master DB to the Slave DB node
• Upgrade the Master DB node
• Sync the Slave DB to the Master

Slave and Master nodes Upgrading steps
Slave Node

• Download required Mysql rpm version
• Stop Slave
• Store/Save Slave (including Master) Coordinates
• Shutdown Slave MySQL Server
• Backup Slave Datadir : (If possible create an OS backup of this datadir in case its needed to fall back).
  i. Taking DB dump
  nohup sh -c 'mysqldump -u root -pxxxxxxxxx --all-databases --single-transaction --quick --lock-tables=false --compress --order-by-primary > /data1 /mysqlBackup/dataDump/masterBack.sql' &
  ii. Taking data directory backup
  datadir=/var/lib/mysql
  iii. Taking config and log back
         /etc/my.cnf
         log-error=/var/log/mysqld.log
• Install new version :
  i. RPM Install
     Typical command looks like this : "rpm Uvh mysql-community {server,client,common,libs-compat,libs}-*.rpm"
• Start Slave Server
• Execute "mysql_upgrade" : Typical command is:
• mysql_upgrade --user=<user_name> --password --host=<host_name> --port=<port_num> [other_options]
• Start Slave again

Master Node

• Stop Slave
• Store/Save Slave (including Master) Coordinates
• Shutdown Master MySQL Server
• Backup Master Datadir : (If possible create an OS backup of this datadir in case its needed to fall back)
  i. Taking DB dump
  nohup sh -c 'mysqldump -u root -pxxxxxxxxx --all-databases --single-transaction --quick --lock-tables=false --compress --order-by-primary > /data1/mysqlBackup/dataDump/masterBack.sql' &
  ii. Taking data directory backup
  datadir=/var/lib/mysql
  iii. Taking config and log back
  /etc/my.cnf
  log-error=/var/log/mysqld.log
• Install new version :
  i. RPM Install :
  Typical command looks like this : "rpm Uvh mysql-community{server,client,common,libs-compat,libs}-*.rpm"
• Start Master Server
• Execute "mysql_upgrade" : Typical command is
  mysql_upgrade --user=<user_name> --password --host=<host_name> --port=<port_num> [other_options]
• Start Slave again
  i. Perform validation
  ii. via application
  iii. Review MySQL Server Error Log file