Moving from MySQL 5.7 to MariaDB 10.1

Warning

Please perform these tests first on a testing environment or a virtual machine.

I never really needed to pay attention to MariaDB until it becomes a possible and viable option. The technical decision in place was obvious, moving into a newer version of MySQL and continuing with it and the unknowns of Oracle being the owner OR moving to MariaDB the well-spoken community replacement for MySQL.

After about an hour of reading, I decided to do the so spoken magical move from MySQL to MariaDB and this is what I have learned.

The next steps were all performed on CentOS 7 with an installed version of MySQL 5.7.13 and used the following article as reference: https://mariadb.com/kb/en/mariadb/yum/

The 1st steps to be performed are the obvious ones:

• mysqldump of all your databases
• backup of /var/lib/mysql
• backup of /etc/my.cnf

The next step is quite obnoxious given that it requires more than a simple yum remove.

    sudo yum remove mysql mysql-server mysql-common

That should remove by default all references from MySQL but, because MariaDB won't pass the install check if there are still references, run the following command:

    sudo yum list installed | grep -i mysql

It will give you a list like this:

    mysql-community-client.x86_64 5.7.13–1.el7 @mysql57-community
    mysql-community-common.x86_64 5.7.13–1.el7 @mysql57-community
    mysql-community-libs.x86_64 5.7.13–1.el7 @mysql57-community
    mysql-community-libs-compat.x86_64 5.7.13–1.el7 @mysql57-community
    mysql-community-server.x86_64 5.7.13–1.el7 @mysql57-community
    mysql57-community-release.noarch el7–7 @/mysql57-community-release-el7–7.noarch

With the exception of libraries like the PHP-MySQL one, all the remaining must be removed. This can be done by simply running:

    mysql-community-client.x86_64 5.7.13–1.el7

If you have read any article that teaches how to perform the migration from MySQL to MariaDB, up to MySQL 5.6 and MariaDB 10.0, the only thing you needed to do to make the migration fully successful was creating the yum repo and installing MariaDB but this option doesn’t work so well.

The following steps are every single step I have performed to install MariaDB 10.1 properly for a MySQL 5.7 migration.

Step 1 — Create the Yum repo

    sudo vi /etc/yum.repos.d/MariaDB.repo

    [mariadb]
    name = MariaDB
    baseurl = http://yum.mariadb.org/10.1/centos7-amd64
    gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
    gpgcheck=1

Step 2 — Install the rpm key

    sudo rpm — import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

Step 3— Completely remove MySQL data

Yes, you read it right, remove /var/lib/mysql and it is the only way to properly do it. If you haven’t done a backup when you started this, then, stop and return to MySQL.

    sudo rm -rf /var/lib/mysql

Installing MariaDB 10.1 with that folder there you will get a noticeable error with InnoDB that, doesn’t matter how many times you search, you won't find a good solution for it. The following is a copy of the error in it’s full:

    [vagrant@localmachine ~]$ sudo journalctl -xe
    — Unit mariadb.service has failed.
    —
    — The result is failed.
    Jun 27 17:46:46 vagrant systemd[1]: Unit mariadb.service entered failed state.
    Jun 27 17:46:46 vagrant systemd[1]: mariadb.service failed.
    Jun 27 17:46:46 vagrant polkitd[656]: Unregistered Authentication Agent for unix-process:12731:154478 (system bus name :1.31, object path /org/freedesktop/PolicyKit1/AuthenticationAgent,
    Jun 27 17:47:25 vagrant sudo[12754]: vagrant : TTY=pts/0 ; PWD=/home/vagrant ; USER=root ; COMMAND=/bin/systemctl start mariadb.service
    Jun 27 17:47:25 vagrant polkitd[656]: Registered Authentication Agent for unix-process:12755:158423 (system bus name :1.33 [/usr/bin/pkttyagent — notify-fd 5 — fallback], object path /org
    Jun 27 17:47:25 vagrant systemd[1]: Starting MariaDB database server…
    — Subject: Unit mariadb.service has begun start-up
    — Defined-By: systemd
    — Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
    —
    — Unit mariadb.service has begun starting up.
    Jun 27 17:47:26 vagrant mysqld[12760]: 2016–06–27 17:47:26 140668548266112 [Note] /usr/sbin/mysqld (mysqld 10.1.14-MariaDB) starting as process 12760 …
    Jun 27 17:47:26 vagrant mysqld[12760]: 2016–06–27 17:47:26 140668548266112 [Note] InnoDB: Using mutexes to ref count buffer pool pages
    Jun 27 17:47:26 vagrant mysqld[12760]: 2016–06–27 17:47:26 140668548266112 [Note] InnoDB: The InnoDB memory heap is disabled
    Jun 27 17:47:26 vagrant mysqld[12760]: 2016–06–27 17:47:26 140668548266112 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    Jun 27 17:47:26 vagrant mysqld[12760]: 2016–06–27 17:47:26 140668548266112 [Note] InnoDB: Memory barrier is not used
    Jun 27 17:47:26 vagrant mysqld[12760]: 2016–06–27 17:47:26 140668548266112 [Note] InnoDB: Compressed tables use zlib 1.2.7
    Jun 27 17:47:26 vagrant mysqld[12760]: 2016–06–27 17:47:26 140668548266112 [Note] InnoDB: Using Linux native AIO
    Jun 27 17:47:26 vagrant mysqld[12760]: 2016–06–27 17:47:26 140668548266112 [Note] InnoDB: Using SSE crc32 instructions
    Jun 27 17:47:26 vagrant mysqld[12760]: 2016–06–27 17:47:26 140668548266112 [Note] InnoDB: Initializing buffer pool, size = 128.0M
    Jun 27 17:47:26 vagrant mysqld[12760]: 2016–06–27 17:47:26 140668548266112 [Note] InnoDB: Completed initialization of buffer pool
    Jun 27 17:47:26 vagrant mysqld[12760]: 2016–06–27 17:47:26 140668548266112 [Note] InnoDB: Highest supported file format is Barracuda.
    Jun 27 17:47:26 vagrant mysqld[12760]: InnoDB: No valid checkpoint found.
    Jun 27 17:47:26 vagrant mysqld[12760]: InnoDB: If you are attempting downgrade from MySQL 5.7.9 or later,
    Jun 27 17:47:26 vagrant mysqld[12760]: InnoDB: please refer to http://dev.mysql.com/doc/refman/5.6/en/upgrading-downgrading.html
    Jun 27 17:47:26 vagrant mysqld[12760]: InnoDB: If this error appears when you are creating an InnoDB database,
    Jun 27 17:47:26 vagrant mysqld[12760]: InnoDB: the problem may be that during an earlier attempt you managed
    Jun 27 17:47:26 vagrant mysqld[12760]: InnoDB: to create the InnoDB data files, but log file creation failed.
    Jun 27 17:47:26 vagrant mysqld[12760]: InnoDB: If that is the case, please refer to
    Jun 27 17:47:26 vagrant mysqld[12760]: InnoDB: http://dev.mysql.com/doc/refman/5.6/en/error-creating-innodb.html
    Jun 27 17:47:26 vagrant mysqld[12760]: 2016–06–27 17:47:26 140668548266112 [ERROR] Plugin ‘InnoDB’ init function returned error.
    Jun 27 17:47:26 vagrant mysqld[12760]: 2016–06–27 17:47:26 140668548266112 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
    Jun 27 17:47:26 vagrant mysqld[12760]: 2016–06–27 17:47:26 140668548266112 [Note] Plugin ‘FEEDBACK’ is disabled.
    Jun 27 17:47:26 vagrant mysqld[12760]: 2016–06–27 17:47:26 140668548266112 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
    Jun 27 17:47:26 vagrant mysqld[12760]: 2016–06–27 17:47:26 140668548266112 [ERROR] Unknown/unsupported storage engine: InnoDB
    Jun 27 17:47:26 vagrant mysqld[12760]: 2016–06–27 17:47:26 140668548266112 [ERROR] Aborting
    Jun 27 17:47:26 vagrant systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
    Jun 27 17:47:26 vagrant systemd[1]: Failed to start MariaDB database server.
    — Subject: Unit mariadb.service has failed
    — Defined-By: systemd
    — Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
    —
    — Unit mariadb.service has failed.
    —
    — The result is failed.
    Jun 27 17:47:26 vagrant systemd[1]: Unit mariadb.service entered failed state.
    Jun 27 17:47:26 vagrant systemd[1]: mariadb.service failed.
    Jun 27 17:47:26 vagrant polkitd[656]: Unregistered Authentication Agent for unix-process:12755:158423 (system bus name :1.33, object path /org/freedesktop/PolicyKit1/AuthenticationAgent,
    Jun 27 17:47:40 vagrant sudo[12775]: vagrant : TTY=pts/0 ; PWD=/home/vagrant ; USER=root ; COMMAND=/bin/systemctl status mariadb.service
    Jun 27 17:48:10 vagrant sudo[12777]: vagrant : TTY=pts/0 ; PWD=/home/vagrant ; USER=root ; COMMAND=/bin/journalctl -xe
    lines 2790–2842/2842 (END)

Step 4 — Install Maria DB

Now that the folder is removed and you got all SQL dumps, install MariaDB

    sudo yum install MariaDB-server MariaDB-client

Final Step — Start service and restore DBS

Now all that is remaining is to restore the service and install the SQL dumps.

    sudo systemctl start mariadb

It is not a pleasant solution but it is a solution until they get the magical migration working just like it does for MariaDB 10.0

I have done a good couple of hours of searches and tests until I came to the conclusion that, at least at this moment, there is no direct easy magical migration path from MySQL 5.7 to MariaDB 10.1, but there is a path. During this period what I have found out is that their IRC channel is very useful and you won't find as many answers out there as you will for MySQL, to make matters harder when searching for something to help troubleshoot a lot comes back with MySQL answers.

Hope this helps in the same way that helped me.

Notes:

While doing all of these tests yesterday I have missed one important part of the process: https://dev.mysql.com/doc/refman/5.5/en/upgrading.html

I will be testing again, but, based on the results that I have got with another test that I have done with MariaDB 10.0 I believe that those steps before the process and running this:

    mysql_upgrade -u  -p

after the full install should fix all issues that were described before making the magic fully work.

This post was originally at: https://bit.ly/31SUCmu