I recently had the misfortune of spending a week at work tasked with testing how we can upgrade our ancient MySQL databases into the modern era only using the inbuilt tools without any utilities (due to a highly locked-down environment). I thought it best to share a clean version of docos so if someone else is in the same niche-ish situation, they’re able to navigate the upgrade without trawling 20 forum posts and docs.
You’ll see a lot of people reference the ‘sql_upgrade’ utility, this however was depreciated in 2019 with v8.0.16 and the server now will upgrade any outdated databases on start-up depending on the –upgrade option.
Another helpful utility that may be of use if you’re upgrading to a version >= 8.0.4 is “Upgrade checker”, see MySQL Shell 8.0.4: Introducing “Upgrade checker” utility.
While v8.0 IS backwards compatible with v5.7, there were changes to the storage engine used for multiple core system tables (mysql, information_schema, performance_schema, sys) and new/depreciated tables between versions. Because of this the database contents needs upgrading too.
The plan is then to create a Source/Replica (or Slave/Master if we’re talking in v5.7 terms) between the two versions, then disable the old Source and update the DNS CNAME record to point to our new source. The backup replicas will of course need to be updated because v5.7 aren’t forwards compatible.
Current setup & Assumptions
- Worth noting a few settings and environment details in this setup.
- You have a functional MySQL v5.7.* server you’re replicating from
- Other MySQL v5.7.* are replicating from this source server
- Not necessarily relevant to upgrade but important if they exist
- Stripped down basic ‘my.cnf‘ config file
- The MySQL directory layout is as follows
- A shared DNS CNAME is used to point to the source server
- Not strictly relevant to the overall setup but all servers operate on FreeBSD 12.2
Procedure
Initialise
First things, our test machine (Server B, who will be our future Source) should be initialised and replicating from source (A) as cleanly as possible, a ‘cold start’.
- Clear out the ‘./data‘ directory
|
1 |
cd /var/db/mysql/data; rm -rf *; cd .. |
Initialise the database
MySQL Installation Guide | v5.7 | 9.1 Initializing the Data Directory
|
1 |
/usr/local/libexec/mysqld --console --initialize --user=mysql --datadir=/var/db/mysql/data |
A “temporary password” will be provided in this output which is flagged as expired. It has to be changed once you gain access to execute any basic commands.
- Start the ‘mysql-server‘ service.
|
1 2 |
service mysql-server status service mysql-server start |
- Access the SQL shell and change the expired password
|
1 2 |
mysql -u root -p Enter password: |
|
1 2 |
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD'; Query OK, 0 rows affected (0.02 sec) |
- If successful access to shell, exit and continue to import the database
MySQL 5.7 Reference Manual | v5.7 | 4.5.4 mysqldump — A Database Backup Program
|
1 2 3 4 5 |
ServerA $ mysqldump --defaults-file=/root/.my.cnf -u root --flush-logs --master-data=2 --events --ignore-table=mysql.event --all-databases --lock-all-tables --add-drop-table > /var/tmp/server_A-mysql.sql ServerB $ mysql -u root -p < /var/tmp/server_A-mysql.sql ServerB $ mysql -u root -e "FLUSH PRIVILEGES;" |
This will update all user logins/privileges and populate the ‘./data’ directory.
- Test the database population by querying it
|
1 2 3 4 5 6 7 8 9 |
mysql> SELECT User FROM mysql.user; +-----------------+ | User | +-----------------+ | webuser | ... | appuser | +-----------------+ 89 rows in set (0.01 sec) |
Upgrading
As mentioned in the beginning, while MySQL is backwards compatible between versions 8.0 and 5.7, it isn’t simply plug n play. To avoid these issues, allow the MySQL server to upgrade the database itself in-place before beginning operations/replication.
The default value for the MySQL Server option –upgrade is AUTO so we can upgrade the version while the database is imported then restart the server.
For better logging of this upgrade it’s worth adding: ‘log_error_verbosity=3‘ to the ‘my.cnf‘ file (in-case anything goes wrong).
Assuming the initialisation went smoothly and the databases are imported correctly, continue with the below steps:
- Stop the service, then upgrade the MySQL server version to 8.0
|
1 2 |
service mysql-server stop pkg install mysql80-server |
- Restart the service and review the ‘./data/hostname.ci.com.au.err‘ logs and wait.
This upgrade may take a number of minutes to complete but you can review it’s progress in the log file (especially if ‘log_error_verbosity‘ is set to 3).
NOTE: The upgrade duration depends on database size. Roughly 5-15 minutes for small databases (<10GB), and proportionally longer for larger datasets. Do not interrupt this process otherwise it’ll corrupt and you’ll need to start again. While this upgrade is taking place, don’t attempt to start the service until it’s complete otherwise duplicate processes will be created and you’ll see similar errors to below
|
1 |
2026-02-12T12:34:03.501434+11:00 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 35 |
You’ll know the upgrade is complete once you see the below line(s).
|
1 2 |
2026-02-10T14:56:55.287131+11:00 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80023' started. 2026-02-10T14:57:24.609682+11:00 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80023' completed. |
- Run some checks to ensure the validity of the databases.
Make sure the user tables are the same between servers. There may be some changes to the core system tables but these are due to the version increase. Some sanity checks you can run is comparing the checksum value:
|
1 |
mysql> CHECKSUM TABLE database.table; |
Swapping the Source and Replica
Now is the tricky part, this section should be done during quiet or off-peak hours to ensure the minimum amount of disturbances to any clients using the databases. Now because Server A is the live source and constantly receiving updates, we need the most recent data available before we make the swap.
First, verify if GTID is enabled on both servers:
|
1 |
mysql> SHOW VARIABLES LIKE 'gtid_mode'; |
If the result shows ‘ON’ for both servers, follow the GTID instructions. Otherwise, use log file positioning.
NOTE: The below examples use passwords in plaintext for clarity. In production consider:
- MySQL’s
CHANGE MASTER TOwithMASTER_PASSWORDfrom a secure credential store - Encrypted connections (MASTER_SSL=1)
- Restricted access to configuration files
GTID
If Global Transaction Identifier (GTID) is enabled for both hosts, the slave will automatically know where to pick up from and greatly simplifies this step. Run the below command to point to source.
|
1 2 3 |
mysql> CHANGE MASTER TO MASTER_HOST = 'Server_A', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = 'PASSWORD', MASTER_AUTO_POSITION = 1; mysql> START SLAVE; mysql> SHOW SLAVE STATUS \G |
Log file positioning
If GTID is disabled, so the binary log file positions need to be retrieved from the backup we restored. To find these, read the backup file and on the ~20th line you should see something like this:
|
1 |
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=154; |
We can use this information to begin replication. Connect to the MySQL shell of the newly v8.0 machine (B) and run the below:
|
1 2 |
CHANGE MASTER TO MASTER_HOST='SERVER_A', MASTER_USER='repl', MASTER_PASSWORD='PASSWORD', MASTER_CONNECT_RETRY=60, MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=154 |
Note in the above command ‘repl’ is a user on the Source with replication privileges.
|
1 2 |
mysql> START SLAVE; mysql> SHOW SLAVE STATUS \G |
Do normal checks to ensure that the replication is functioning (e.g. ‘Slave_SQL_Running_State‘ is happily waiting for more updates).
If replication fails to start, verify:
- The log file and position are correct in the dump file
- The ‘repl‘ user (or whatever use you’re using for replication) exists on Server A with replication privileges
- Network is functioning between servers (test with ‘telnet serverA 3306‘)
- Binary logging is enabled on Server A (‘SHOW VARIABLES LIKE ‘log_bin’;‘)
We’ve now created the below setup:
Swap in the new Source
Now that we have the most updated information available to Server B on MySQL v8.0, we can swap the old version out. If you make a mistake here you can always bring up the old source (Server A) as the main source again.
- Stop the old source from reading:
|
1 2 |
ServerA-mysql> flush logs; # Creates a new binary log file, makes it easier to track what isn't replicated ServerA-mysql> SET @@GLOBAL.read_only = ON; |
- Reset the slave database’s slave config, allowing it to believe it’s a master:
|
1 2 3 |
ServerB-mysql> SHOW SLAVE STATUS \G ServerB-mysql> STOP SLAVE; ServerB-mysql> RESET SLAVE ALL; |
- Update the CNAME to the new source (Server B)
Now that’s done you can move onto the on/offsite replicas. These can be done during office hours or whenever as there is already a valid working connection for the MySQL clients.
The final setup layout doesn’t change by much, the CNAME just now points to Server B instead of A.
The On/Offsite Replicas
During the swap, the backups servers C and D (the existing v5.7 replicas) will continue replicating from A until the CNAME changes, then they will break. Until these machines are updated they cannot replicate because as mentioned previously, v5.7 cannot replicate from v8.0. They must be:
- Upgraded to v8.0 using the same procedure as Server B
OR
- Replaced with new v8.0 instances replicating from Server B
Do not attempt to point v5.7 replicas at a v8.0 source – they are not forwards compatible.
Rollback Plan
If anything starts to go wrong, remember you can always fall back to Server A and try again. As below:
- Set Server B to read-only
|
1 |
mysql> SET @@GLOBAL.read_only = ON; |
- Update the CNAME back to Server A
- Re-enable writes on Server A if you disabled them
|
1 |
mysql> SET @@GLOBAL.read_only = OFF; |
- Troubleshoot and try again
Conclusion
I hope this was at least slightly useful to anyone in the same boat as myself and helps with any upgrading projects you’re endeavouring.
Remember to keep Server A running as a backup for at least 24-48 hours (or longer!) after the upgrade to ensure the setup is stable before fully decommissioning.