During a recent security scan of backend components, several minor vulnerabilities were detected. To prevent issues during future deployments, I decided to upgrade the MySQL installation. The upgrade strategy involves replacing MySQL binaries only, upgrading the replica node first, promoting it to the primary, and then upgrading the original primary.

Upgrade Overview

Current version: 5.7.24
Target version: 5.7.28

Prreequisites

1. Download the MySQL 5.7.28 binary package: mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz from the official MySQL downloads page.
2. Create a backup of existing data using mysqldump: ``` mysqldump -u -p -S /path/to/mysql.sock -F -A -B | gzip > ~/mysql_backup/20191020/dump.sql.gz

3. Archive the configuration files: ``` cp <mysql_datadir>/*.cnf ~/mysql_backup/20191020/

Step-by-Step Upgrade Process

1. Export Database

mysqldump -u<user> -p<pwd> -S /var/run/mysqld/mysqld.sock -F -A -B | gzip > /home/backup/20191029/dump.sql.gz

2. Stop MySQL Service

If using supervisor for processs management:

supervisorctl stop mysqld

For manual shutdown without a process manager:

mysql -u root -p -S /data/mysql3308/mysql3308.sock --execute="SET GLOBAL innodb_fast_shutdown=0"
mysqladmin -u root -p -S /data/mysql3308/mysql3308.sock shutdown

3. Archive Configuration and Startup Scripts

cp /opt/mysql/*.cnf /home/backup/20191029/
cp /opt/mysql/bin/start.sh /home/backup/20191029/

4. Extract New Binary Package

tar -zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz -C /opt/

5. Update Symbolic Link

unlink mysql
# or
mv mysql mysql.old.20191029
ln -s /opt/mysql-5.7.28-linux-glibc2.12-x86_64 mysql

6. Restore Configuration Files

cp /home/backup/20191029/*.cnf /opt/mysql/
cp /home/backup/20191029/start.sh /opt/mysql/bin/

7. Start MySQL Service

supervisorctl start mysqld

8. Run Compatibility Check

mysql_upgrade -u<user> -p<pwd> -S /var/run/mysqld/mysqld.sock

This command verifies all tables are compatible with the new version and updates system tables if necessary. Since this is a minor version upgrade within the same major release, system tables typically remain compatible.

9. Restart MySQL to Apply Changes

supervisorctl restart mysqld