Jump to content
Automation Theory

Guide: Migrating from MariaDB to MySQL

Recommended Posts

Hi Everyone,
Since ConnectWise no longer supports MariaDB I've created a guide to help partners migrate to MySQL. The vendor provided documentation involves a long and convoluted logical backup process that can be avoided. This guide is the fastest method of upgrading to MySQL while allowing 100% ability to revert back in case of failure (since we'll be leaving MariaDB intact).
Before we go any further please note that you can farm out this task if you want to focus on automation and not database administration: https://automationtheory.org/product/mysql-patching/
Now, onto the upgrade process (with pictures!)! Please note that this is a development server with four different versions of SQL installed -- you may see items in screenshots that are not applicable to a normal server (like LabMySQL not running when restarting the Database Agent).
Here's a screenshot showing the existing version of MariaDB installed:
Before we get started make sure you have your root password and enough disk space to hold twice your DB (we're going to duplicate it in order to do a "fail-safe" upgrade). This guide assumes that the DB upgrade is taking place on the same server and does not cover any application-level changes required for moving the database to a new host. 
  1. Download and extract the ZIP file installer for MySQL 5.7 (link). Standard deployments extract to C:\MySQL, but it's not all that important (just remember where you put it).
  2. MySQL 5.7 does not include a data directory -- this is good, as we're going to do what's known as a physical backup where we move the existing MariaDB data directory to our new install. If you have downloaded MySQL 5.6 for whatever reason it DOES have this directory and it should be deleted.
  3. Open up SQLyog (or your editor of choice) and run the command "SET GLOBAL innodb_fast_shutdown=0" (not case sensitive). This will force data in the buffer files to be written to the actual tables (the buffer file formats aren't compatible, so we'll be deleting them). WARNING: Failure to do this will lead to potential data loss in three steps!
  4. Stop the service LabMySQL.
  5. Once the service has stopped copy the entire data folder from the MariaDB base directory to the extracted MySQL base directory. Also, your my.ini file can be moved there (MySQL will automatically apply it on startup without special instructions if it's in this location).
  6. Delete the files ib_logfile0 and ib_logfile1 (and if you're running an exotic configuration and you have more than two ib_logfileX files delete any others).
  7. Open an administrative command prompt in the bin directory of the new MySQL server and run the command "mysqld.exe --console". Scary error messages are to be expected, but the application should stay running. If it closes something has gone wrong (feel free to troubleshoot with the provided console messages -- but please note that this would be a good time to get a second opinion if the issue seems exotic).
  8. Open a second administrative command prompt in the bin directory (leaving the other one open). This time run the command "mysql_upgrade.exe -u root -p --force". This command will produce lots of crunchy messages; it's MySQL seeing the table files in a different format and doing all the under the hood conversions to make them match MySQL 5.7 formatting (note: the screenshot shows the wrong syntax).
  9. Once the mysql_upgrade has finished, go back to the command window where the server (mysqld) is running. Press CTRL+C to stop it, then up arrow to get the start command and re-launch it. When the server starts up it should have fewer error messages, but it will likely have errors about the mysql.user table.
  10. If you do not see errors about the mysql.user table, skip this step. Otherwise, open SQLyog and connect to the DB (note: if you can't get logged in this would be another good stopping point for a second opinion). Run the following commands to remove the extra columns that MariaDB has that MySQL doesn't.
    alter table mysql.user drop column default_role;
    alter table mysql.user drop column max_statement_time;
    alter table mysql.user drop column password_expired;

  11. At this point, it's worth repeating steps 8 and 9 to make sure that the application will be happy when connecting.
  12. Restart the Database Agent service. In the console you'll see some failed login attempts; this is normal. After 1-2 minutes try logging into the application. 
As shown here, success! We have upgraded to MySQL 5.7.
Important: We're not done yet! The service LabMySQL is still pointed at the old database instance. If the console closes or the server reboots you'll lose MySQL. To correct this close the application, stop the server, delete the old service (sc delete LabMySQL), and run the following command "mysqld --install LabMySQL". Please note that you can add the option " --defaults-file=C:\path\my.ini" but as previously mentioned this is not required if the my.ini file is in the base directory of the MySQL server.
At this point, MySQL should be the persistent database for the Automate server. Start the service LabMySQL, restart the Database Agent, and verify the application launches. To be extra sure you can restart the server to make sure that the service starts on boot. Please note that the old database is 100% intact at this point.
Hopefully this guide has been useful to you. If you ever need support for the Automate database and want a professional DBA opinion feel free to reach out to us: https://automationtheory.org/ 
  • Like 1

Share this post

Link to post
Share on other sites

I think my favorite part are the descriptions like "Crunchy" and "Exotic" :)


Share this post

Link to post
Share on other sites

Thanks so much for sharing this. While your instructions don't explicitly say this, I think it's wise to shutdown IIS and the LTDBAgent services to essentially stop Automate services and traffic while working on the database like this.

Sadly when I gave this a shot last night I got stuck. After running the mysql_upgrade, I wasn't seeing any authentication errors but quickly found I couldn't continue since every time I logged in with my root user account, I was given an error saying I needed to update the password with the ALTER USER command. Unfortunately doing that just resulted in an error about a wrong row count in the mysql.users table. And all my research on that issue pointed me to just running mysql_upgrade again. But not matter how many times I did that, I was stuck. I finally just reverted back to MariaDB (which was easy given this upgrade method!!).

Any ideas how to overcome or avoid this user auth issue? Should I maybe have tried running the user table cleanup commands while I had my first session open maybe BEFORE the mysql_upgrade command?

Share this post

Link to post
Share on other sites

Yes, it wouldn't hurt to stop the front end services -- but since we stop the DB you won't have data inconsistencies.

As for your password reset -- the MySQL manual has a nice reference for this. You'll create a file with the SQL statement to reset the password and call it with the --init-file=C:\path\reset.sql switch.

Please keep in mind that user accounts in MySQL are host specific (my prod Automate server has 3 root accounts). If you moved hosts you might need to create another account.

Since your new instance still exists you can troubleshoot at your leisure and then re-migrate once you have the kinks worked out.

Share this post

Link to post
Share on other sites

Thank you for the awesome write up. I had been planning to do this for a while but figured I had to when LabTech refused to help me with a database crash last week.

I did run into a few issues so figured I would add them here in the hopes that they might help someone else. One thing is, we run VMware SRM to replicate our servers from our primary datacenter to our DR datacenter. This made testing this a lot easier as I was able to bubble test the whole thing and work through any issues. We have a 24x7 NOC which I manage and a 20 minute SLA so obviously downtime is a huge issue for us. I did the bubble test a couple times and documented what I did so I could do it quickly when I had to. I ended up doing the whole thing in about 10 to 15 minutes last night, most of which was copying the actual data directory.

We also run a secondary database server that replicates from the primary server for reporting purposes. MySQL replication works a little differently than MariaDB so there were some extra challenges there that I won't go into unless someone needs to know those. We have 3 servers for LabTech (app, db and db2) and one for ScreenConnect.

1. The first issue I ran into when starting MySQL from the command line is it wouldn't start. I reviewed the console error messages and saw that it was erroring on some lines in my my.ini that obviously weren't MySQL compatible. I removed those and it started up. This was great to have SRM to test this as it took a bit of time. I would recommend if your server is virtual you maybe clone it and test this offline too.
2. Once it started up I ran into the issue @tlphipps ran into with the  password having expired. I found a command that would reset that so I would login to MySQL using mysql -u root -p. I tried to run ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘<password>’, ‘root’@’localhost’ PASSWORD EXPIRE NEVER. I got an error about mysql user columns not matching. I tried to delete the colums listed in the original post but couldn't as my password had expired.
3. I stopped the MySQL process and started it again with mysqld --console --skip-grant-tables. Once it loaded up again I tried to delete the columns but it said they didn't exist. I compared my database table another MySQL 5.7 install I had and found a column called is_role. I typed alter user mysql.user drop column is_role; which it accepted. I think this might be because the picture in the original post shows MariaDB 10.2 which LabTech never supported officially so I was still on 10.0 still. I hate that they are so far behind on versions.
4. I killed the MySQL process and ran mysqld --console to startup normally. I typed the ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘<password>’, ‘root’@’localhost’ PASSWORD EXPIRE NEVER and it worked this time. It lets you use the same password as well which was nice so I didn't have to fix any connection strings on other servers.

5. I killed the process again just for good measure and started it again. Now I was able to run mysql-upgrade -u root -p --force and it successfully upgraded. I killed the process and restarted it and ran it again, just for good measure.

Everything else worked great. I took the downtime window to update all the ODBC connectors and stuff too as those were 5 years old and they recommend using the 8.0 ones now. The other part I did was setup the new directory and copy the my.ini file ahead of time so the folders were all there before the migration, just helped to reduce the downtime a bit. If you have the option to clone the server and do the initial testing ahead of time it will help a lot to shorten the actual maintenance. The main issues it helped me with was ironing out any incompatibilities in the my.ini file.

Hopefully this helps others but thank you @Automation Theoryfor the initial start, you made the process so easy that adding my parts cut a normal 3 to 4 hour migration down to 10 or 15 minutes.

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...