How to migrate from maria db to mysql 8

June 21, 2019 388 views
MariaDB MySQL

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?

1 Answer

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

Have another answer? Share your knowledge.