MySQL 5.7 EOL: Move to MariaDB Server with 11 Easy Commands

Posted on August 15, 2023 by Paul Rothrock and Kyle Hutchinson

MySQL 5.7 Extended Support from Oracle ends in 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 how to do an in-place migration from MySQL 5.7.43 to MariaDB Server 10.6.14. The commands and instructions shown work for any version of MariaDB Server 10.6.14 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 and two options for migrating tables with partitions. 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 which means there are some incompatibilities you should be aware of. For instance, MySQL 5.7 handles table partitioning differently than MariaDB Server (source). This means any tables that are partitioned in MySQL will need special handling when migrating to MariaDB Server. We’ll cover this exact scenario later in this blog.

Another difference is with JSON support. MySQL stores JSON in a binary format and MariaDB stores it as text (source). There is an easy work around here too, covered in the source shared above. There are other 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.

1. Take a backup of your database. You want to be able to revert back in case anything goes wrong during this process

a. Be sure to copy the my.cnf file to ensure custom configuration is preserved and can be applied to the MariaDB server

2. Stop the mysql database service

systemctl stop mysqld

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

4. For CentOS or other Fedora/RHEL based OS’s only

yum install epel-release

5. Download the MariaDB Repo Setup tool

wget https://r.staging-mdb.com/downloads/mariadb_repo_setup

6. Make the downloaded file executable

sudo chmod +x /path/to/mariadb_repo_setup

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

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

9. Start the MariaDB service

sudo systemctl start mariadb

10. Run mariadb-upgrade process

a. The -p prompts for a password

sudo mariadb-upgrade -u root -p

11. Run the following commands after logging in as a high privilege user, like root, to remove errors from the error log

a. The two optimize commands should not need to be executed starting version 10.6.15 https://jira.mariadb.org/browse/MDEV-30483

b. 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';
optimize table mysql.innodb_table_stats;
optimize table mysql.innodb_index_stats;

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

How to handle partitioned tables for in-place migration

Since the way MySQL and MariaDB handle partitions are different, special consideration is needed when dealing with these tables. Here are two ways to handle partitioned tables.

Logical dump

The first way is to perform a logical dump of the tables. In this case, that would mean using mysqldump for all of the partitioned tables. Then you drop the tables from the database server. Perform the migration, then load the dump file back into the database. This is a safe way to handle the scenario, however one of the downsides is time. Partitioned tables are typically only used on tables with large amounts of data, so doing a dump will take a long time and require enough disk space to store the dump.

Partition exchange

The second way is to leverage partition exchange (source) to move the data of a partition to a new table that is not partitioned. This will require a table of the exact same design for each partition that the partitioned table has. So if there are 19 partitions, then there will need to be 19 new tables. Once those new tables are built, we need to move the data into them. That is where the partition exchange statements will be used. Once the partition exchange statements have been executed you will want to perform a DDL dump of the partitioned tables. This is necessary because we need to drop the partitioned tables before performing the migration and will need to recreate the tables afterwards to move the data back. Once the dump is complete, you drop the tables, migrate the database, recreate the partitioned tables, then run the partition exchange statements again to load the data back into the original tables.

While there is a bit of complexity in this approach, the major benefit is how much less time this process takes. Since essentially there is only a meta-data change happening, there is no copying of data, nor is there a need for more disk space. The initial move of the partitioned data to a non-partitioned table takes around 1 second. To move the data back takes a bit longer because there are validity checks performed on the data. However, I had a 3+GB (disk size) partition load in just under 2 minutes. Included below are SQL statements that will generate all of the necessary non-partitioned tables, partition exchange statements, and two examples of how to dump the DDL of your partitioned tables to recreate them later.

Possible option: you can put your individual tables in a new schema to facilitate a simpler clean-up post migration. That way you can be certain you won’t accidentally drop your partitioned table which you just migrated.

In-place migration steps with partitioned tables using partition exchange

This was tested using a database loaded with the test_db dataset. The employees_partitioned.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.

1. Take a backup of your database. You want to be able to revert back in case anything goes wrong during this process

a. Be sure to copy the my.cnf file to ensure custom configuration is preserved and can be applied to the MariaDB server

2. Run the following command to check if you have any partitioned tables

SELECT table_schema, table_name, COUNT(*) num_of_partitions
FROM information_schema.partitions
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
AND partition_name IS NOT NULL
GROUP BY table_schema, table_name
ORDER BY table_schema, table_name;

a. If you get results that look like the following, then you have tables with partitions

+----------------+------------+-------------------+
| table_schema   | table_name | num_of_partitions |
+----------------+------------+-------------------+
| employees_part | salaries   |            19 |
| employees_part | titles     |            19 |
+----------------+------------+-------------------+

3. Generate individual tables for each partition of the partitioned tables

SELECT CONCAT('CREATE TABLE ',table_schema,'.', table_name,'_',partition_name,' LIKE ',table_schema,'.', table_name,'; ALTER TABLE ',table_schema,'.', table_name,'_',partition_name,' REMOVE PARTITIONING;') AS stmt
FROM information_schema.partitions
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
AND partition_name IS NOT NULL
ORDER BY table_schema, table_name, PARTITION_ORDINAL_POSITION;

a. Output should look like the following, with an entry for every partition of all your partitioned tables

CREATE TABLE employees_part.salaries_p01 like employees_part.salaries; ALTER TABLE employees_part.salaries_p01 REMOVE PARTITIONING;

b. There are other ways to create the tables that won’t require a REMOVE PARTITIONING statement. However, those are much more verbose and I liked the succinct nature of the example

4. Take the above output and load it into a file and execute it

5. Execute the following commands to generate the partition exchange statements

SELECT CONCAT('ALTER TABLE ',table_schema,'.', table_name,' EXCHANGE PARTITION ',partition_name,' WITH TABLE ',table_schema,'.', table_name,'_',partition_name,';') AS stmt
FROM information_schema.partitions
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
AND partition_name IS NOT NULL
ORDER BY table_schema, table_name, PARTITION_ORDINAL_POSITION;

a. Output should look like the following, with an entry for every partition of all your partitioned tables

ALTER TABLE employees_part.salaries EXCHANGE PARTITION p01 WITH TABLE employees_part.salaries_p01;

6. Take the above output and load it into a file and execute it

7. Use either command below to generate the DDL of your partitioned tables

a. The first command will need to be executed per table and the output will need to be copied to file to be executed later

SHOW CREATE TABLE employees_part.salaries;

b. The second command leverages mysqldump to generate the DDL

mysqldump --databases employees_part --tables salaries titles --no-data > partitioned_tables.sql

8. Execute the following command to generate statements to drop your partitioned tables

SELECT DISTINCT CONCAT('DROP TABLE ',table_schema,'.', table_name,';') AS stmt
FROM information_schema.partitions
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
AND partition_name IS NOT NULL;

a. Output should look like the following

+-------------------------------------+
| stmt                                |
+-------------------------------------+
| DROP TABLE employees_part.salaries; |
| DROP TABLE employees_part.titles;   |
+-------------------------------------+

9. Take the above output and load it into a file and execute it

10. Stop the mysql database service

systemctl stop mysqld

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

12. For CentOS or other Fedora/RHEL based OS’s only

yum install epel-release

13. Download the MariaDB Repo Setup tool

wget https://r.staging-mdb.com/downloads/mariadb_repo_setup

14. Make the downloaded file executable

sudo chmod +x /path/to/mariadb_repo_setup

15. 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"

16. Install the MariaDB Server package

a. This will include any necessary dependencies

sudo yum install MariaDB-server

i. Make sure to remove any invalid entries

17. Start the MariaDB Service

sudo systemctl start mariadb

18. Run mariadb-upgrade process

a. The -p prompts for a password

sudo mariadb-upgrade -u root -p

19. Run the following commands after logging in as a high privilege user, like root, to remove errors from the error log

a. The two optimize commands should not need to be executed starting version 10.6.15 https://jira.mariadb.org/browse/MDEV-30483

delete from mysql.plugin where name = 'validate_password';
optimize table mysql.innodb_table_stats;
optimize table mysql.innodb_index_stats;

20. Restart the MariaDB service to ensure all changes have been made and are recognized by the service

sudo systemctl restart mariadb

21. Recreate the partitioned tables from the DDL saved in step 7

22. Take the output generated in step 5 and saved to a file in step 6 and run the statements again. This will repopulate the partitioned tables with the data stored to individual tables

23. Drop the individual tables as they are no longer needed

NOTE: For replicated architectures, start by using the steps in the previous section to exchange the partitions with a set of tables on the primary database. This will push these changes down to the replicas and prepare them for the upgrade. Then upgrade each replica in turn, followed by upgrading the primary.

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 staging-mdb.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