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_versions.png
 
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.
    zip_extract.png
     
  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!
    Disable_fast_shutdown.png
     
  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).
    files_to_move.png
     
  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).
    redo_logs.png
     
  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).
    scarry_errors.png
  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).
    mysql_upgrade.png 
     
  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.
    mysql.user_error.png
     
  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;

    drop_columns.png
     
  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. 
    restartDB_agent.png
 
As shown here, success! We have upgraded to MySQL 5.7.
after_version_numbers.png
 
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 2

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

Thanks for this. I'm planning to migrate from one single server instance to a split server (database/Automate & web). If I license Automate on the new server using my dev license and then import the database, will the Automate server automatically use the old license?

Share this post


Link to post
Share on other sites

You can probably have your existing license running on both. I believe they allow for about 30 days where it can report in from the same IP. I would maybe check wtih them but I have always run the servers at the same time when migrating to new boxes.

 

Share this post


Link to post
Share on other sites

Ah nice, even better. I might give this a go at the weekend - can't do much else at the moment anyway :D

Share this post


Link to post
Share on other sites

@collinsit: I can't find the docs now that I want to link to them, but 10.2 was the last MariaDB build that didn't have features absent in MySQL. In 10.3 things started to diverge where MariaDB implemented features that didn't exist in MySQL. I honestly didn't know where support drew the line, so I went with the highest version that I knew would work. 

The compatibility doc can be found here: https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ 10.0 was designed to be compatible MySQL 5.6, which explains your rocky upgrade process. :) If there's anyone else out there attempting this please keep this in mind. Since MySQL is the destination I'd advise going from MariaDB 10.0 --> MySQL 5.6 --> MySQL 5.7.

 

@lgs141: I'd definitely contact Automate support ahead of time just to make sure everything will jive. To my knowledge the license data is in the config table, so you'd definitely cross the streams if you import everything (but the license is tied to the app server I believe). If you want assistance with this feel free to contact me; I do offer consulting for server splitting.

 

Once y'all are on MySQL if you'd like to do some performance tuning I'm happy to setup trials of https://automationtheory.org/database-tuner/ . In my test environment I can get the server to read 21% faster than the stock configuration.

Share this post


Link to post
Share on other sites

I looked up the version comparisons too and know that 10.0 more closely resembled MySQL 5.6. I would have gone that route if things were hard but realistically, my upgrade wasn't any worse than you outlined in your steps so I didn't want to introduce the added step. I basically had to change the user column that got deleted, pretty much everything else worked the same. From what I understand, I think MySQL introduced password expiry after a year in 5.6 so I would have run into that issue regardless. For personal testing I agree, I would have much rather been on a later version but their support always said don't go past 10.0 which is why I stayed there. I didn't want an unsupported version which is the reason I just did this migration too. MySQL seems to overall be slower as my replication is lagging more than it did with MariaDB. I am having issues keeping it caught up.

The only issue I found today is the old Report Manager reports don't seem to work anymore. I get an error related to not being found in the database now. We don't use a ton of them so it isn't a huge issue but there is one I use pretty often that doesn't work anymore. The biggest pain with that is that I can't engage CW support for help with it as that reporting engine is no longer supported. If I can't figure it out I may have to just ditch that report. We did rewrite it in the new Report Center but that is a piece of crap and the report fails to run most the time and I have to constantly republish it to the database.

I wouldn't mind taking a look at your plugin. I am not a huge fan of having it do dynamic changes and would rather them be hard set in the config file.

Share this post


Link to post
Share on other sites

The plugin aims to be a database-administrator-as-a-service. The target audience would be people who want better performance without thinking too hard about it -- and people who are interested in DBA-ing seem to dislike black box tuning. However, there are some advantages to dynamic modifications:

  • It's compatible with the hosted flavor of Automate
  • They scale with you as you modify resources
  • It's easy to revert for testing
  • We can detect certain load changes and compensate for them

I don't want to too deep into the weeds of the plugin here (there's a seperate thread for that here), but I think it's relevant since most people went to MariaDB for performance reasons. Now that things have come full circle it's time to optimize.

Re: replication: I binlog on my prod Automate server just for point-in-time recovery, and with ~10k agents I generate ~60GB of binlogs a day. I'm not sure if you're experiencing a config issue or an economy-of-scale issue, but I'm happy to consult with you if you'd ever like to chase that rabbit.

Share this post


Link to post
Share on other sites

@Automation Theory Great write-up! Now make a post about migrating from MySQL 5.7 to Percona MySQL 5.7 and setting up PMM for the Automate instance. 😉 

 

Actually according to the docs it's the only other compatible listed MySQL database support mentions. I even pointed it out to them and it was still up after they removed mentions of MariaDB. Weird since that's Linux only and would need to be a split server unless you're running it through a docker container or something which sounds even more unsupported. 

Share this post


Link to post
Share on other sites

Well @Deflect you'd definitely see a performance boost going to Percona, but there are other steps you can take before that. I've written about it here: https://automationtheory.org/the-mysql-performance-problem/ and I'd advise partners to try and tune Windows servers due to support's stance on Linux and other DBMSes. 

I don't have anything ready yet, but I'm doing some R&D on clustered DB's for Automate. If you did end up having performance issues on a single split server offloading 50% of the activity should definitely help -- you'll see more on this once it's fully baked.

If you were going to split off and do a Percona cluster I'd suggest going the VM route; the primary concern with any write-intensive DB performance tuning is I/O, and having fewer layers of abstraction in that department will help. I do offer consulting time, and if you wanted to do this I can definitely assist.

Share this post


Link to post
Share on other sites

@Automation Theory Any recommendations on doing this type of migration with MariadB 10.0 > MySQL 5.6 (as from documentation that is cross compatible) ... or should we look at upgrading Maria to 10.2.x to migrate to MySQL5.7? 

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.

Guest
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...