Upgrading a Windows MySQL instance from 5.1 to 8.x
I've got a very old instance of MySQL kicking around (5.1.73 - released in December 2013) that's due for an upgrade. It's actually relatively newly created as it was installed using the Web Platform Installer late last year, so it's surprising it's such an out-dated version, especially as tools like MySQL Workbench aren't able to connect to it! The instructions for upgrading a Windows instance of MySQL are a starting point, but they don't cover some of the errors that I've seen during the process, so here's the steps I took to achieve this.
Preparation
Before doing anything else, I took a backup of the databases using the mysqldump tool. I've got a process running every 24 hours that backs the database up and writes it to Azure Blob Storage, heavily based on the PowerShell script I wrote to backup Sql Server databases to Azure, but even so it's worth taking a full backup beforehand. I also took a full copy of the installed MySQL binaries, which for me were located in the folder C:\Program Files\MySQL\MySQL Server 5.1. Lastly, I stopped the MySQL service and took a file copy of the data folder for MySQL, which for me was C:\ProgramData\MySQL\MySQL Server 5.1\data.
You can never have too many backups when it comes to upgrading something as crucial as a database server.
Updating the MySQL binaries
This is the easy bit of the upgrade process. THe MySQL instructions recommend using the ZIP file distribution to do this, which at the time of writing was listed under Other Downloads, about two thirds of the way down the Downloads page as Windows (x86, 64-bit), ZIP Archive. With this downloaded, and extracted, I copied the contents of the ZIP over the top of the MySQL Server 5.1 folder in C:\Program Files. This preserves the my.ini which contains MySQL configuration, including the installation location and the path to the data files.
The next step is to attempt to restart the service, which is step 6 in the MySQL upgrade instructions, alas doing so results in the service starting and then immediately stopping, so it's time to start diagnosing why this is happening.
Diagnosing issues with the upgrade
My 'go to' here would be to run MySQL from the command line, telling it to log to the console exhaustively with:
mysqld --console --log-error-verbosity=3
Unfortunately, doing this yields no output which is very unhelpful! There's also nothing logged to the Application or System logs in the Windows event log. A bit of internet research told me that I might get something to progress from by running:
mysqld --help
Running that did indeed give me:
2019-01-24T06:29:59.832895Z 0 [ERROR] [MY-000077] [Server] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld.exe: Error while setting value 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'. mysqld.exe Ver 8.0.14 for Win64 on x86_64 (MySQL Community Server - GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Starts the MySQL database server. Usage: mysqld.exe [OPTIONS] For more help options (several pages), use mysqld --verbose --help. 2019-01-24T06:29:59.854938Z 0 [ERROR] [MY-010119] [Server] Aborting
So there's an error, with the value for the setting sql-mode in the config file. Looking at the documentation for this setting it appears that the value NO_AUTO_CREATE_USER isn't on the list of permitted values anymore. The documentation for the sql-mode setting for MySQL 5.7 notes that (emphasis mine):
Assignments to sql_mode that change the NO_AUTO_CREATE_USER mode state produce a warning, except assignments that set sql_mode to DEFAULT. NO_AUTO_CREATE_USER will be removed in a future MySQL release, at which point its effect will be enabled at all times (GRANT will not create accounts).
Removing that line from my.ini and running MySQL from the command line, again with the --console --log-error-verbosity=3 parameters now spat out a whole long output, I'm going to restrict myself to ones that fall into the ERROR category for now, which were:
2019-01-24T18:21:23.474260Z 1 [ERROR] [MY-013090] [InnoDB] Unsupported redo log format (0). The redo log was created before MySQL 5.7.9 2019-01-24T18:21:23.474278Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error. 2019-01-24T18:21:23.705102Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine. 2019-01-24T18:21:23.707834Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed. 2019-01-24T18:21:23.852360Z 0 [ERROR] [MY-010119] [Server] Aborting
So, there's something that's in a format too old for MySQL 8.x to read. A quick search found a stack overflow question that looks like a good starting point, suggesting that there's an "if you've not upgraded yet" option and an "if you've already upgraded" option. I'm going to go with the latter, if it doesn't work I've got backups and can follow the "if you've not upgraded yet" route. So the latter route is to delete the ib_logfile0 and ib_logfile1 files from the data folder, for Windows that's the folder under C:\ProgramData, or the location referenced by the datadir setting in your my.ini.
And again, run mysqld - this time it comes up with the rather frustrating:
2019-01-24T18:28:46.250193Z 1 [ERROR] [MY-013168] [InnoDB] Cannot upgrade server earlier than 5.7 to 8.0
So, it looks like there's a hard block there, and it's off to https://dev.mysql.com/downloads/mysql/5.7.html#downloads to download MySQL 5.7.25.
Doing the upgrade from 5.1 to 5.7
This is more of the same, really, once the ZIP file has been downloaded and extracted, the process of copying the contents over into C:\Program Files\MySQL Server 5.1\ took a blisfully quick 30 seconds (thankyou SSD!) at which point it's back to running mysqld and seeing what comes out:
2019-01-24T21:15:03.047809Z 0 [ERROR] InnoDB: Unsupported redo log format. The redo log was created with MySQL 8.0.14. Please follow the instructions at http://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html
So, it looks like the data files at least got partially upgraded to 8.0.14, so I'm going to take the simple option here and replace them from the backup I took prior to attempting to upgrade to MySQL 8.x, and then re-run mysqld. A lot more logged output but yet another error, specifically:
2019-01-24T21:20:09.352953Z 0 [ERROR] unknown variable 'table_cache=256'
Again thanks to the wonders of the Stack Exchange network, there's an answer to this. The setting table_cache in my.ini needs to be renamed to table_open_cache, which throws up another invalid configuration value:
2019-01-24T21:24:26.852979Z 0 [ERROR] unknown variable 'innodb_additional_mem_pool_size=2M'
It looks like this setting is just plain deprecated, so it's a simple matter of removing/commenting it out and spinning up mysqld once more. Now we get to a message that's pointing in the right direction:
2019-01-24T21:28:50.223420Z 0 [ERROR] Fatal error: mysql.user table is damaged. Please run mysql_upgrade.
So without further ado, it's time to run mysql_upgrade! First startup MySQL by running it with the --skip-grant-tables parameter (see this serverfault post for details) and then run the mysql_upgrade binary. For me, this output a very long stream of messages that looked a little like this:
Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Checking system database. mysql.columns_priv OK mysql.db OK ... ... ... Upgrade process completed successfully. Checking if update is needed.
The penultimate line suggests that all has gone well in upgrading from 5.1 to 5.7, so swap back to the console that's running mysqld and press CTRL-C, then remove the --skip-grant-tables parameter and run it again. This should give output that ends with:
2019-01-24T21:35:51.992698Z 0 [Note] mysqld: ready for connections. Version: '5.7.25' socket: '' port: 3306 MySQL Community Server (GPL)
So that's MySQL 5.1 upgraded to 5.7, next stop 8.0!
Upgrading from 5.7 to 8.0
This should be the last step, starting with stopping MySQL (either by hitting CTRL-C in the console or stopping the service) and then overwriting the binaries with those from the 8.0 ZIP. Once that's done it's back to the console to run mysqld and see what comes out! The first run after copying the new binaries across and there's another couple of errors:
2019-01-24T21:41:36.350442Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001146 - Table 'mysql.component' doesn't exist
2019-01-24T21:41:36.350483Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-003543 - The mysql.component table is missing or has an incorrect definition.
2019-01-24T21:41:36.351592Z 0 [ERROR] [MY-000067] [Server] unknown variable 'query_cache_size=0'.
2019-01-24T21:41:36.351604Z 0 [Warning] [MY-010952] [Server] The privilege system failed to initialize correctly. If you have upgraded your server, make sure you're executing mysql_upgrade to correct the issue.
2019-01-24T21:41:37.053915Z 0 [ERROR] [MY-010119] [Server] Aborting
I'm going to tackle query_cache_size first, and once again it's a question on stack overflow to the rescue! The documentation for MySQL 5.7 indicates that:
The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.
So, time to comment out/remove the setting from my.ini and run mysqld yet again, to see what comes out. This time round, it's succes:
2019-01-24T22:00:44.079438Z 0 [Note] [MY-011323] [Server] Plugin mysqlx reported: 'X Plugin ready for connections. bind-address: '::' port: 33060'
2019-01-24T22:00:44.089842Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
So, leaving mysqld running, I've opened another console and run mysql_upgrade. It's worth mentioning that for version 8 of MySQL, you have to specify the username and password when running mysql_upgrade, so the command will look something like this:
mysql_upgrade.exe -u root -p
Which will then prompt for the password and spit out some output that looks a little bit like this:
Enter password: ********************** Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Upgrading system table data. Checking system database. mysql.columns_priv OK mysql.component OK ... ... [A long list of system database tables here] ... mysql.time_zone_transition_type OK mysql.user OK Found outdated sys schema version 1.5.1. Upgrading the sys schema. Checking databases. sys.sys_config OK ... ... [User database tables here] ... Upgrade process completed successfully. Checking if update is needed.
Success, MySQL has been upgraded from 5.1 to 8.0.14! The last test is to see if it works is to attempt to connect using MySQL Workbench - which it does, so that's the upgrade from a five year old version of MySQL to the most recent version available - release three days ago - done! Hopefully this will come in handy if you're stuck with an old version of MySQL that needs upgrading.