by Prabha Repalle – Lead Consultant
If you plan on migrating from MariaDB to MySQL, you have a few options to consider. In this blog post, we will explore two possible methods that involve utilizing built-in and open-source tools to help you with the migration process.
Method 1: Export MariaDB Dump and Import into MySQL
Method 2: Migrate MariaDB to MySQL Using MySQL Workbench
Method 1: Export MariaDB Dump and Import into MySQL
MySQL Dump is a backup tool that creates logical backups of databases. It enables the replication of database objects and table data and assists in transferring or backing up to another SQL server. Additionally, it can generate CSV/XML or other delimited text formats.
Pre-Migration Steps:
- Environment: MariaDB 10.6, and MySQL 8.0.
- Check for incompatibilities between MariaDB and MySQL.
Move non-InnoDB storage engine in MariaDB into InnoDB used in MySQL:
1. Verify the storage engines on your MariaDB database.
SELECT * FROM INFORMATION_SCHEMA.TABLES;
2. Find out the tables that are incompatible with MySQL 8.0.
3. Modify the engine on the MariaDB server or change it on MySQL when loading data.
ALTER TABLE table_schema_and_table-name ENGINE=InnoDB;
4. Check and modify the inappropriate functions.
Check data types of MariaDB and MySQL:
5. Find the data type list in the database.
SELECT DATA_TYPE from INFORMATION_SCHEMA.COLUMNS whereTABLE_SCHEMA = ’database_name’ and table_name =’ table_name’;
6. Understand the different names of data types in the two databases.
7. Change the data type to the one MySQL supports accordingly.
ALTER TABLE table_name MODIFY address data_type;
Migration Process:
8. Create a dump in MariaDB.
mysqldump -u username-p database_name > source_dump.sql
9. Stop and uninstall MariaDB.
10. Remove or move configuration file.
11. Use the MySQL shell to create a new database with the same name as the one in MariaDB.
CREATE DATABASE target_database;
12. Log in to MySQL as root.
mysql -u root -p password
13. Exit MySQL shell and navigate to the dump file.
14. Load the dump file into MySQL:
mysql -u username -p new_database < source_dump.sql
Post Migration:
15. Verify the migration:
mysql -u root -p mydatabase -e “show tables”;
16. Check the database size.
SELECT TABLE_SCHEMA AS `Database`,
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM information_schema.TABLES
GROUP BY table_schema;
17. Check the table size.
SELECT table_name AS “Table”,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS “Size (MB)”
FROM information_schema.TABLES
WHERE table_schema = “database_name”;
18. See if the table and row counts match.
SELECT COUNT (*) FROM table_name;
19. Get MySQL row count of all tables in a database.
SELECT
table_name,
table_rows
FROM
information_schema.tables
WHERE
table_schema = ‘classicmodels’
ORDER BY table_name;
ORDER BY (data_length + index_length) DESC;
Method 2: Migrate MariaDB to MySQL Using MySQL Workbench
MySQL Workbench is an integrated tool that offers a visual interface for designing databases, developing SQL code, and administering MySQL databases. It allows users to perform various tasks related to MySQL database management from a single interface. Additionally, the tool enables users to migrate multiple databases to MySQL.
Migration Process:
1. Go to Database> Migrate to start the migration wizard in MySQL Workbench.
2. Select and specify the source database information, test the connection, and click Next.
3. Configure the connection details for the target database and verify its connection.
4. Specify the objects to migrate on the Source Objects page. The migration starts.
5. Edit the generated SQL for all objects, show migration issues, or modify the target object’s name and columns on the View drop-down of Manual Edit.
6. Select create schema in target RDBMS on the next page. Wait for the creation to finish. And check or edit the created objects on the Create Target Results page.
7. Configure data migration on the Data Transfer Settings page. Select Next to start transferring the data.
8. See the migration report after the process and click Finish to close the wizard.
9. Log into the target database to verify whether the migrated schema and tables are consistent with the source.
10. Check the database size.
SELECT TABLE_SCHEMA AS `Database`,
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM information_schema.TABLES
GROUP BY table_schema;
11. Check the table size.
SELECT table_name AS “Table”,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS “Size (MB)”
FROM information_schema.TABLES
WHERE table_schema = “database_name”
12. See if the table and row counts match.
SELECT COUNT (*) FROM table_name;
13. Get the MySQL row count of all tables in a database.
SELECT
table_name,
table_rows
FROM
information_schema.tables
WHERE
table_schema = ‘classicmodels’
ORDER BY table_name;
ORDER BY (data_length + index_length) DESC;
Note: Although MariaDB and MySQL are similar, there might be some variations in syntax or features that could cause problems while migrating. Therefore, it is advisable to test the migrated database thoroughly before utilizing it in a production environment.
Determine the best migration method for your business needs.
Contact our expert team or the author directly to learn more about migrating from MariaDB to MySQL and discover the best method for your unique environment.
Prabha Repalle – Lead Consultant
Email: [email protected]
LinkedIn: www.linkedin.com/in/prabhakarrepalle/