MariaDB to MySQL Database Conversion

Project Goals 

The objective of this project was to convert my client's existing MariaDB 10.4 database to MySQL 8.0 and migrate all existing data, users, and database objects while ensuring minimal downtime and integrity of data.

Work Summary 

I installed and configured the MySQL 8.0 database on a new host, developed and executed the processes to migrate the database objects, users,  and data, and performed detailed validation.  I also worked with the development team to resolve application issues. 

Platform Technologies

The process of database migration and conversion involved the following primary technologies:

  • Source: The original database was MariaDB 10.4, hosted on an Ubuntu 18.04 server. It consisted of approximately 50 schemas with roughly 140 InnoDB tables each. Some tables were particularly large, containing over half a billion rows. 

  • Target: The target platform utilized MySQL 8.0 which I installed using Percona Distribution for MySQL 8.0, hosted on a newly configured Ubuntu 22.04 server.

  • Application and Processes: A PHP application, actively used for reporting and customer interaction, interfaces with the database. Additionally, several live and batch processes are used for data input, summarization, and distribution.

  • Tools: For this project I used the pt-archiver utility from the Percona toolkit for the migration process. The combination of Bash scripting and SQL stored procedures streamlined the validation of the migration process and the generation of comprehensive reports. 

Challenges and Design Considerations

The conversion process from the MariaDB 10.4 database to Percona for MySQL 8.0 presented several challenges.

  • Downtime Minimization:  The client's business operations depended heavily on the database, making it essential to minimize downtime during the conversion process. To meet this requirement, I tuned the target database to optimize bulk loading. I also engineered the migration process to be multi-threaded to maximize throughput.

  • Compatibility: There are significant dissimilarities between MySQL 8.0 and MariaDB 10.4, such as a different default character set, and storage engine internals. This required some effort to inspect the source and target tables for compatibility issues. 

  • Data Integrity: Ensuring no data loss or integrity issues during the migration was crucial given the critical nature of the data the client's database held.  I developed reports to validate the source and target table migration utilizing a combination of row count comparison and row-by-row matching.

  • Handling Large Tables: The client's database included several large tables, some with over half a billion rows. Special consideration was needed to handle these tables during the transition process, as standard migration strategies were not suitable due to the immense size and complexity of these tables.  To tackle this challenge, I devised a method to "pre-load" the timestamped event-driven data.

  • Application Conflicts: The change in the database platform from MariaDB 10.4 to Percona for MySQL 8.0 caused a few issues with the client's PHP application and framework. I collaborated closely with the client's development team to investigate and propose solutions so that we were able to quickly rectify these issues.

  • Table Index Modification: During the migration process, we identified areas in the application that required performance improvements and modified the table index design in those specific areas. 

Pre-Migration Preparations 

  • My client provided me with a clone of the production database, as well as a new target host  so that I could safely develop the migration processes.

  • Disk Storage file system creation and configuration:  I used standard Linux utilities on the new host to create the file systems for the data directory, an archive directory, as well as the associated /etc/fstab entries. 

  • Installation of MySQL 8.0 using the "Percona Distribution for MySQL 8.0" software suite. Reference : https://docs.percona.com/percona-distribution-for-mysql/8.0/index.html

  • I examined the client's existing MariaDB 10.4 configuration  to determine the best starting point for the new configuration of MySQL 8.0. I created a new MySQL 8.0 configuration, taking into account new and deprecated system variables.  This included modifications to SQL_MODE for application compatibility. I also included temporary enhancements to allow for faster data migration. 

  • Installation of the percona Xtrabackup as well as the Percona Toolkit. 

  • I created an administratively privileged user in the target MySQL 8.0 database to be used for all migration tasks, which could be dropped when the project was complete. Similarly, I created a read only user on the source database to be used to safely extract the user grants, object definitions, and data. 

Migration Process

  • Migration of database users and roles: I used the Percona toolkit utility pt-show-grants to extract the users and grants from the source database. Since MySQL 8.0 no longer allows creating a user and granting permissions in a single statement, I manually modified the output of pt-show-grants to break up the extracted statements into separate CREATE USER and GRANT statements. 

  • Migration of database objects: I simply extracted the SQL statements necessary to create the tables, views, stored procedures, and triggers using the standard mysqldump utility on the source MariaDB database.  I used the "--routines --triggers --compact --no-data  --databases …" options for this step.  Then I ran the generated script in the new Mysql 8.0 database to create the schema, and the associated tables, views, procedures, and triggers.

    A small number of tables did not have indexes that were required for the data migration steps, so I generated a set of CREATE INDEX statements using an information_schema query.

    Finally, I implemented some index changes on certain tables, based on some performance analysis that I conducted while collaborating with the development team. This aided the application responsiveness as well as reduced the amount of disk space used by these tables. 

  • Table Data Migration: With so many schemas and tables, it was not practical to write custom scripts to create, migrate, and validate each one individually.  So my strategy was to create a small set of Bash shell scripts to use the percona toolkit utility "pt-archiver" for handling the actual data migration.  This utility is mature, feature rich and well supported. The key reason to use pt-archiver was that the configuration options allowed for a highly efficient way to migrate the data without having to write custom SQL.

    When executed, internally the pt-archiver script handled the iteration through sets of rows of the source database tables, using SQL statements of the form "SELECT…INTO…OUTFILE",  and then loaded this data into the target database tables using SQL statements of the form "LOAD DATA LOCAL INFILE..INTO TABLE …". 

Data Validation

After executing the data migration processes successfully, I validated each step to ensure accuracy. This involved reviewing the output logs by examining them for errors post-migration. For the actual data validation, I developed a script to compare row counts for each table in the source and target databases. Additionally, I developed a process to compare column values for individual tables. For the larger tables, I performed these comparisons on a representative sample of rows. Finally, I scrutinized the MySQL error log to detect hidden errors.

As an additional step I developed aggregation oriented SQL queries on certain tables, based on common use cases provided by the client's development team. We used this to observe performance improvements as well as prove that the results were identical.

Application Testing & Monitoring

I provided immediate support for the development team as they conducted application testing on the new MySQL 8.0 database. The only real issues were with the queries that were incompatible with the new default SQL MODE setting. Additionally the PHP framework stumbled on how MySQL 8.0 now returns uppercase column names in the result sets of information_schema queries. I assisted by researching and discovering a workaround to keep the application functioning. 

I promptly offered support to the development team during their evaluation of the new MySQL 8.0 database. The primary challenges arose from queries that were incompatible with the updated default SQL MODE setting. Furthermore, the PHP framework encountered difficulties with MySQL 8.0's new style of returning uppercase column names in result sets from information_schema queries. I aided the team by conducting thorough research and successfully identifying a workaround to ensure uninterrupted functionality of the application.