MySQL – Upgrading MySQL 5.5 to 5.7

In this blog post, I will enumerate the steps to upgrade MySQL 5.5 to 5.7.

This is also known as the Logical Upgrade which involves exporting SQL from the old MySQL instance using a backup utility (mysqldump), installing the new MySQL server, and applying the SQL to your new MySQL instance.

1. Backup all your databases

 mysqldump -u root -p --add-drop-table --routines --events --all-databases --force > /backup/data-for-upgrade.sql

Note: Use the --routines and --events options with mysqldump (as shown above) if your databases include stored programs. The --all-databases option includes all databases in the dump, including the mysqldatabase that holds the system tables.

2.  Stop MySQL 5.5 instance

service mysqld stop

3. Move old datadir

mv /var/lib/mysql/ /var/lib/mysql-55

4. Install MySQL 5.7

5. Start MySQL

service mysqld start

6. Login to mysql with new root password

# Get temporary root password
grep 'temporary password' /var/log/mysqld.log

# Login to MySQL
mysql –u root –p

# Change root password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password’;

7. Restore databases

mysql -u root -p < /backup/data-for-upgrade.sql

 

We have just performed the most cleanest and most stable upgrade path.

Cheers!