Can I transfer Mysql 5.7 databases to MariaDB 10.2 with admin backup/transfer?

Richard G

Verified User
Joined
Jul 6, 2008
Messages
4,218
Location
Maastricht
I'm just wondering. They say MariaDB 10.2 is Mysql 5.7 compatible.

We want to move servers (old server to replacement server). The new server (server B) already has MariaDB 10.2 running.
The old server (server A) has Mysql 5.7 running.

First I was thinking to do a mysql 5.7 conversion to MariaDB 10.2 on server A.

Today I used admin backup/transfer to transfer the admin account from server A with mysql 5.7 to server B with MariaDB 10.2. The admin account has Wordpress on it.
It looks like this works. At least the database connection works. ;)

Since we have to transfer all accounts to the new server anyway I'm wondering... Is it possible to just transfer all accounts from server A (Mysql 5.7) to server B (MariaDB 10.2) with admin backup/transfer without issues?

If yes, this would spare us from the Mysql 5.7<->MariaDB 10.2 conversion which would be a great help.

Anybody tried this before?
 

wattie

Verified User
Joined
May 31, 2008
Messages
992
Location
Bulgaria
Yes, it will work. You can even migrate MySQL 8.0 database that way which elsewhere is impossible.

The so called "backups" are actually dump files - created with the mysqldump utility. When you open them with simple text editor you'll see that it's just a collection of SQL commands - usually CREATE DATABASE, CREATE TABLE, INSERT. Therefore incompatibilities are very rare - the SQL language does not change a lot.

You can expect a problem if a user have stored procedures or stored functions. This is also very rare for shared hosting - people usually don't do that. They are a coding practice for larger software projects.

Also if something fails with particular restore, you are still not lost at all. You can start executing the SQL commands one by one (say via mysql command line or phpmyadmin) and see where it stops with error. Then look at the command and rewrite it to make it compatible. It's not hard.
 

Richard G

Verified User
Joined
Jul 6, 2008
Messages
4,218
Location
Maastricht
Also if something fails with particular restore, you are still not lost at all. You can start executing the SQL commands one by one
That would be too much for say 60 accounts with databases. :)

The reason I ask this is that I do have 1 particular server with MariaDB 10.2 on it, and I can restore those accounts to any other Mysql 10.2 server (so it's even same version database) but none of the accounts will work after transfer because they can't connect to the database. I've send in a ticket for that.
However, from other 10.2 servers to the new server it's no issue transferring and it looks as from 5.7 from another server it's no issue either.

But because normally mysql 5.7 needs converting to Mariadb 10.2 I just want to be sure. That why I like to here from somebody who already done this.
 

wattie

Verified User
Joined
May 31, 2008
Messages
992
Location
Bulgaria
I never experienced any restore issues. I said that you can deal with errors just because they may eventually come up - but it's unlikely.
 

Richard G

Verified User
Joined
Jul 6, 2008
Messages
4,218
Location
Maastricht
No thank you. 10.2 is far enough for the time being. :)

We successfully transferred from the Mysql 5.7 to the MariaDB 10.2 server last night. All accounts are working well so now we can cancel the old server.
Thank you for your answers!
 
Top