Master MySQL Upgrade: Key Tips, Commands, and Common Pitfalls

As MySQL evolves, older versions reveal new vulnerabilities; upgrading is required to fix bugs and access new features.

Upgrade considerations

Backup! Backup! Backup!

Upgrading from 5.6 to 5.7 requires first updating to the latest 5.6; cross‑version upgrades (e.g., 5.5 directly to 5.7) are not supported.

The system creates the root@localhost account by default; if the skip_name_resolve option is enabled, you must grant access to 127.0.0.1 separately.

The mysql_install_db command is replaced by mysqld --initialize-insecure after MySQL 5.7.5.

In the mysql.user table, the password column is replaced by authentication_string from 5.7.6 onward. For an In‑place upgrade, run mysql_upgrade to convert the column; for a Logical upgrade, include --add-drop-table in mysqldump and omit --flush-privileges .

Versions prior to 5.7.6 require disabling the grant tables with --skip-grant-tables on the first start of the upgraded instance.

Upgrade methods

In‑Place upgrade

An In‑place upgrade stops the old server, replaces the binaries, starts the new server, and runs the upgrade command.

Steps:

1. Execute a slow shutdown to flush dirty pages:

mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"

2. Stop the old server: mysqladmin -u root -p shutdown 3. If the new server will use the same data directory, back up the old directory.

4. Extract the new MySQL package.

5. If the old /etc/my.cnf points to the same data directory, move the data files to the new directory.

6. Start the new server: mysqld_safe --user=mysql & 7. Run mysql_upgrade to adjust incompatibilities and enable new features: mysql_upgrade -uroot -p 8. Restart the server so the changes take effect:

mysqladmin -uroot -p shutdown
mysqld_safe --user=mysql &

9. Upgrade complete.

Logical upgrade

A Logical upgrade dumps all databases, installs a fresh instance, and imports the dump.

Steps:

1. Export all databases with necessary options:

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

2. Stop the old server: mysqladmin -uroot -p shutdown 3. Install the new MySQL instance, reusing the old configuration where possible and adjusting incompatible settings.

4. Initialize the new instance (this generates a temporary root password): mysqld --initialize-insecure Note: Save the temporary password shown in the terminal or error log.

5. Start the new server: mysqld_safe --user=mysql & 6. Reset the root password:

mysql -u root -p
ALTER USER USER() IDENTIFIED BY 'your new password';

7. Import the previously exported SQL file: mysql -u root -p --force < data-for-upgrade.sql 8. Run mysql_upgrade again to ensure system tables are up‑to‑date: mysql_upgrade -uroot -p 9. Restart the server to apply the upgrade changes:

mysqladmin -uroot -p shutdown
mysqld_safe --user=mysql &

10. Upgrade complete.

Method comparison

In‑place upgrades are fast because they keep the data files, but they cannot cross operating‑system boundaries.

Logical upgrades require export/import, which can be slow for large datasets, but they support cross‑OS migrations.