MySQL 5.7 EOL: Move to MariaDB Server with 11 Easy Commands
This blog was updated to reflect the fact that the MySQL 5.7 EOL date has now passed. An earlier version of this blog covered how to handle partitioned tables for in-place migrations. Partitioned tables are now handled automatically in MariaDB Server 10.6.15 and newer versions.
MySQL 5.7 Extended Support from Oracle ended October 2023 (source 1, source 2). For those of you that initially selected MySQL to retain some degree of vendor neutrality and affinity towards open source, the Oracle track of migration to MySQL 8.0 and on to Oracle Heatwave and Oracle Cloud may not suit you, and you may want to consider a switch to MariaDB Server. Obviously, not all migrations are the same level of effort. Thanks to compatibility with MySQL protocols, it’s easy to make the switch to MariaDB Server and gain the added benefits of Oracle compatibility features, open source columnar storage and enhanced features such as MaxScale database proxy, advanced audit capabilities, and temporal tables.
This blog walks you through a scripted example of an in-place migration from MySQL 5.7.43 to MariaDB Server 10.6.15. The commands and instructions shown work for any version of MariaDB Server 10.6.15 and greater, and work for both MariaDB Community Server and MariaDB Enterprise Server. It will give you some general pointers on how to perform a database migration project, plus, it goes through upgrading an environment that has database replication running.
MariaDB Server is well known for being a drop-in replacement for MySQL. It has displaced MySQL in nearly all the Linux distributions as the default and MariaDB Server has been downloaded over 1 billion times. As we developed distinct features for MariaDB, the code bases have diverged more and more. There are some cases and scenarios to be aware of, so please spend time reading over this complete list of compatibilities between MySQL and MariaDB Server to help identify any potential issues before attempting a migration.
11 commands for an in-place migration to MariaDB from MySQL with no partitioned tables
This was tested using a database loaded with the test_db dataset. The employees.sql was used to test the partitioned table handling options. This was run on an AWS ec2 t3a.medium server running CentOS7 and all non-SQL commands are for CentOS. The version of MariaDB installed is 10.6.15. These instructions work for any version of MariaDB Server 10.6.15+.
Before you start, take a backup of your database. You want to be able to revert back in case anything goes wrong during this process. Be sure to copy the my.cnf file to ensure custom configuration is preserved and can be applied to the MariaDB server
1. Stop the mysql database service
systemctl stop mysqld
2. Remove the installed MySQL packages
yum remove mysql-*
a. If you have put in custom values to the my.cnf file, this should automatically be archived to a my.cnf.rpmsave
3. For CentOS or other Fedora/RHEL based OS’s only
yum install epel-release
4. Download the MariaDB Repo Setup tool
wget https://r.mariadb.com/downloads/mariadb_repo_setup
5. Make the downloaded file executable
sudo chmod +x /path/to/mariadb_repo_setup
6. Run the following command to setup the MariaDB repo for your server
sudo /path/to/mariadb_repo_setup --skip-tools --skip-maxscale --mariadb-server-version="10.6"
7. Install the MariaDB Server package
a. This will include any necessary dependencies
sudo yum install MariaDB-server
b. At this point you should copy the contents of your saved my.cnf or the my.cnf.rpmsave file into the /etc/my.cnf.d/server.cnf file. That way prior saved configurations will be in place when MariaDB starts up
i. Make sure to remove any invalid entries
8. Start the MariaDB service
sudo systemctl start mariadb
9. Run mariadb-upgrade process
a. The -p prompts for a password
sudo mariadb-upgrade -u root -p
10. Run the following commands after logging in as a high privilege user, like root, to remove errors from the error log
a. The delete from mysql.plugin statement is necessary only to remove an error message. There is no harm in leaving this entry in the table. Note, this needs to be done even in the case of upgrading to MySQL 8
delete from mysql.plugin where name = 'validate_password';
11. Restart the MariaDB service to ensure all changes have been made and are recognized by the service
sudo systemctl restart mariadb
NOTE: For replicated architectures simply start by upgrading the replicas to MariaDB Server in sequence, verifying that replication is working after each one. When complete, upgrade the primary database and you’re done.
General Migration Guidance
This applies to any migration, whether you’re going from MySQL 5.7 to MySQL 8.0 or to MariaDB Server 10.6+. There are three main things that will need to be done early and often. Plan, research, and test.
Plan: The migration process will likely require you to adjust your plan as information comes to light during the process. You may spend a lot of time addressing all the different things which interact with your database. Such as: your application, batch jobs, reports, and ETL to name a few. Plan to test all of these things after a test migration has been done to ensure functionality. Capture any issues that arise and determine if and how to move forward. Repeat this process until you feel comfortable moving forward or determine that you can not.
Research: The research process will be a constant and iterative process as well. It goes hand in hand with testing and as any issues arise, research will follow to determine what to do. There are a lot of resources available in the MariaDB knowledge base which will help you identify potential issues with your migration from MySQL. Here is another good source of information to help with moving from MySQL. With proper research you can find possible issues before testing, such as if you have partitioned tables or use the JSON datatype, as mentioned above.
Test: Good planning and research are great. However, they won’t remove the need to test. Testing finds the gaps and will give you a more accurate understanding of where you are in the migration process. Be sure to perform a backup of your data before engaging in any work. This will allow you to quickly revert back in case there is an issue during your migration process. It will be necessary for both testing and doing the final Production migration.
Don’t forget to backup any configuration files, such as my.cnf, as well. This is crucial information and can sometimes be overlooked. Also, review information like this knowledge base article to find out which system variables are not used in MariaDB that MySQL uses, or to update the default values. Finally, MariaDB is here to help if you need additional assistance, see our MariaDB Migration Service.
Get Started with MariaDB Server
Go to mariadb.com/downloads to download the free MariaDB Community Server, along with connectors and everything else you need to get going with MariaDB! Customers also have access to the MariaDB Enterprise Server for production deployments.
Additional Resources
Migrate to MariaDB Server from MySQL 5.7 using a single command
