Upgrade MySQL to MariaDB 10 (Part 2 – Upgrade MariaDB/MySQL 5.5 to Version 10.0)

MariaDB is a relational database management system (rDBMS) that was forked from MySQL, the de-facto database for most popular open-source web platform and apps. MariaDB is intended as the enhanced drop-in replacement for MySQL, so that developers and database administrators can switch to MariaDB easily due to high compatibility with MySQL, library binary equivalency and exact matching with MySQL APIs and commands.

This tutorial shows the step-by-step guide on upgrading and converting from MySQL version 5.1 firstly to MariaDB version 5.5, and then subsequently to MariaDB 10.0, in CentOS 6.6 system. The procedure should works on most Red Hat Enterprise Linux (RHEL), CentOS, Fedora, Scientific Linux and similar distributions version 5, 6 and 7.

MariaDB 10 is the version which MariaDB deviated from MySQL 5.6 to indicate that not all features of MySQL 5.6 is implemented in MariaDB 10, while MariaDB includes even more features that are not available on MySQL, such as ExtraDB, Aria, Cassandra, CONNECT storage engine. MariaDB 10 also important in such a way that it includes several much wanted features, including full-text indexes search support.

If you haven’t upgraded to MariaDB 5.5, visit the tutorial Part 1 – Install and Upgrade MySQL to MariaDB 5.5.

Part 2 – Upgrade MariaDB/MySQL 5.5 to MariaDB 10.0

Note: Since MariaDB is drop-in replacement for MySQL for version <= 5.5, if you're on MySQL 5.5, you can follow the steps outlined below to upgrade to MariaDB 10 directly, without having to switch to MariaDB 5.5 beforehand.

1. If you continue to upgrade process from MySQL 5.1 to MariaDB 5.5 to MariaDB 10.0, skip this step. Else, backup your databases.
2. Ensure that all software packages are up-to-date before upgrading:

   yum update
   
   

3. Stop the MariaDB or MySQLservice:

   service mysql stop

4. Uninstall and remove MariaDB 5.5 or MySQL 5.5:

   yum remove mysql mysql-server

   Note

   For MariaDB, the command actually removes MariaDB-server and MariaDB-client. Your system may have “MariaDB-common” and “MariaDB-compat” or “mysql-libs” and “mysql-devel” packages installed. If they’re not been removed as part of dependencies, use yum remove MariaDB-common MariaDB-compat or yum remove mysql-libs mysql-devel to uninstall them too (mysql-libs is also drop-in replacement for MariaDB-compat).
   Tip

   Sometimes yum will include a lot of still needed dependencies to be removed with MySQL. In this case, use the rpm -e –nodeps package_name to remove just the individual package without affecting any dependencies. For example, rpm -e –nodeps MariaDB-server.
5. Visit MariaDB repository configuration generator to setup a MariaDB 10 repo by selecting your distro and release.

   

   Edit the file named MariaDB.repo (or any name that you chose) in /etc/yum.repos.d directory, and replace the content inside it with the custom MariaDB YUM repository entry for CentOS generated.

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

   For CentOS 6.6, the content of /etc/yum.repos.d/MariaDB.repo is:

   # MariaDB 10.0 CentOS repository list - created 2015-01-25 17:31 UTC
   # http://mariadb.org/mariadb/repositories/
   [mariadb]
   name = MariaDB
   baseurl = http://yum.mariadb.org/10.0/centos6-amd64
   gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
   gpgcheck=1

6. Clean the repository cache information:

   yum clean all

7. Install MariaDB 10.0:

   yum install MariaDB-server MariaDB-client

   

8. When installation is completed, start the MariaDB database service:

   service mysql start

9. Run the MariaDB upgrade script:

   mysql_upgrade

   

   Note

   If you encounter the following error, use mysql_upgrade -p then enter database root password.

   ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

10. Verify that the MariaDB 10.0 is running by logging into the database system using command-line client:

    mysql

    The output should indicates MariaDB:

    Welcome to the MariaDB monitor. Commands end with ; or \g.
    Your MariaDB connection id is 12
    Server version: 10.0.15-MariaDB MariaDB Server

    Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    MariaDB [(none)]>

The installation and upgrading to MariaDB 10.0 has completed. By default, MariaDB no longer uses /etc/my.cnf as the single configuration file for the rDBMS. Instead, the configuration file has been split into several files located inside "/etc/my.cnf.d" directory, namely mysql-clients.cnf, server.cnf and tokudb.cnf.

To complete the migration to MariaDB 10.0, relocate the MySQL configuration settings from the backed up my.cnf to appropriate section in appropriate new .cnf file for MariaDB 10. Also take note of variables which have changed between versions. If you does not like the new MariaDB .cnf structure, you can continue to use a single my.cnf file, replacing the MariaDB 10 default version.