Question

How to migrate from maria db to mysql 8

hello

I am using mariadb version 10 and I want to migrate to MySQL version 8 to be enabled to use Geography functions, but how to do that, and does this effect currwnt databases, also what the affects will be on performance of queries?

Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Jonathan Tittle • June 23, 2019

@mhs4com

MariaDB, at its core, is a fork of MySQL. If you’re using a table structure that is only available with MariaDB (such as Aria), you would need to convert the tables to InnoDB or MyISAM.

If you’re already using InnoDB or MyISAM, you can use the mysqldump command to create a backup of your databases and then restore each database to the new server with MySQL.

Creating an SQL Dump

mysqldump --opt -Q -u dbuser -p dbname > /path/to/dbname.sql

dbuser would be the username with access to dbname (the name of the database that you’d like to backup). You’ll be prompted to enter the password used for the database. The time it’ll take for the command to complete will depend on the size of the database.

Once you have a database dump of each database, you can then download using SFTP and then upload to the new server, or transfer them to the other server using scp.

scp /path/to/databases/* root@host:/path/to/databases

/path/to/databases in the first argument would be there your database dumps are stored on the server with MariaDB (each of the SQL files from the above mysqldump command). In the second argument, it could be any location on the new server with MySQL.

Restoring Your Database

mysql -u dbuser -p < /path/to/dbname.sql

As far as performance, you likely won’t notice much of a difference between MariaDB 10.x and MySQL 8. Much of the performance gain comes from fine-tuning of the configuration. If you’re running a pretty standard or stock installation, the performance will be comparable.