new Install and migrate from mysql to mariadb

Question

I have a server landscape, running mysql 5.7 on debian 8 in some GTID based replication configuration.

All is high load servers and 200+GB databases.

Now I got some new servers. I like to run that new servers on debian 9 and the recommended mariadb 10.2.X expanding it later to a MariaDB Galera Cluster.

I've read some, how to migrate from mysql to mariadb, but anyhow none of them seems to fit for me.

Different GTID handling and innodb format between mysql and mariadb does scare me a bit.

After all I have to run some tests.

So, my idea is:

on one of the masters (mysql 5.7),

-flush tables with read lock

-make a snapshot (LVM) of the database partition

-show master status to get the GTID executed

-unlock tables

-copy that snapshot to a new server to /var/lib/mysql

-install mariadb on the new server

would mariadb upgrade that data in /var/lib/mysql

Any recommendation or another ideas on that?

Ju

Answer 1

So, for the after world.

I did it by following my idea and it worked out well.

A few minor things has to be taken into account.

After copying data over from one server to the other.

The *.pem and the auto.cnf files in /var/lib/mysql/ have to be deleted on the new machine. Deleting auto.cnf is really important because it holds the servers ID and you don't wanna end up running several servers with same server ID, what will cause you problems afterwards.

On a fresh installed machine, before installing mariadb, there isn't a user mysql nor group mysql on the system, so change the /var/lib/mysql and it's sub folders to be owned by root:root.

I copied the entire /etc/mysql folder from source machine to destination machine, to make the mariadb install script believe there was a running mysql installation before on that machine. In the config file /etc/mysql/mysql.conf.d/mysql.conf I changed the value of the server_id variable to be unique in the entire server landscape.

After that you are ready to fire the mariadb installation on the new machine using apt. While installing, mariadb install script detects the mysql stuff and tries to run the mysql-upgrade on it. That will probably fail, as in my case, but that's not a big deal.

It is because the install script tries to run mysql-upgrade as root user on your freshly installed mariadb server with old data in place. But there are differences between mysql and mariadb in system tables and in what root on a local machine can do and what not, and system tables needs to be upgraded by the upgrade script.

I never run things as root user on the database, so I have my own user with rights do do everything on each of my databases, and that user credentials come with the data from the old machine. So I run the update script just from the command line using my user credentials instead of the root credentials.

And it turned out to run well, complaining about some missing stuff, but that is what the update script is written for. To detect missing or changed stuff and correct it. Restart mysql and there we are. All my users and 230+GB of data right into access managed now by mariadb on a debian 9 machine.

Hope it helps someone.

Ju