Migrating from MySQL to Mariadb (CentOS)

Here's an excerpt of the background, followed by a record of the operations I migrated from MySQL 5.5.31 to mariadb 5.5.31 on CentOS 6.4. Finally, I found a better way to migrate.

1. Background information

MySQL is the most popular open source relational database in the world. 2008, Sun acquired MySQL. Then, in 2010, Oracle bought Sun, and MySQL fell into Oracle's hands. Oracle's relationship with the open source community has not been very good, and even MySQL has been made into a community and enterprise version, not free commercial use, the MySQL Enterprise version of the new features unfair open source code, which makes users very unhappy. MARIADB has no such problems. Compared to MySQL, MARIADB has the advantage of:

Free commercial use (MySQL has community and Enterprise Editions, thanks to Oracle)

Maria Storage Engine

PBXT Storage Engine

XTRADB Storage Engine

Federatedx Storage Engine

Faster replication Query Processing

Thread pool

Run faster

More extended function Modules

Unicode Sort Support

So I decided to take a look at the virtual machine to see how to migrate from MySQL to MARIADB, although it is unpredictable that Michael Widenius will not be able to sell MARIADB to which big company.

Originally MARIADB's design is completely compatible with MySQL, including APIs and client protocols, so that it can easily become a replacement for MySQL, so the migration from MySQL to MARIADB is basically called "Upgrade", just like the software upgrade version. MARIADB's technical documentation reads:

If it is the same base version (for example, 5.5), you can uninstall MySQL and install MARIADB, so you can use it. You do not even need to back up and restore the database. Of course, for insurance, it's very necessary to back up all the databases before you upgrade, just in case.

If the main version of the MARIADB is high, after the above uninstall MySQL and install MARIADB operation, you need to use Mysql_upgrade upgrade. Yes, even the command line is the same as when using MySQL.

All original clients and connectors (PHP, Perl, Python, Java, and so on) do not need any change to work properly, because MARIADB is using the same client protocol as MySQL and the client library file is binary compatible.

2. Migration Operation Record (the following 3 method is better)

This VPS is installed CentOS, the current version is the 6.4,mysql version is 5.5.31 (uses the Remi Source), the virtual machine configuration and this extremely similar. At first not very understanding, the implementation of this relatively silly operation.

A. Configure the MARIADB installation source first.

The main version of the current mariadb is 5.5, my CentOS is 32-bit, save the following installation source configuration as Mariadb.repo, upload to the server/ETC/YUM.REPOS.D folder:

[MARIADB]

Name = MARIADB

Enable=1

Priority=3

BaseURL = http://yum.mariadb.org/5.5/centos6-x86

Gpgkey=https://yum.mariadb.org/rpm-gpg-key-mariadb

Gpgcheck=1

Where priority=3 is the priorities priority plug-in for YUM.

B. Uninstall the original MYSQL and related packages.

# yum Remove mysql*

The above is matched with a * to remove all MySQL packets (including other packages that are deleted due to package dependencies, such as Postfix). Because there is a conflict when you install directly, for example:

File/usr/share/mysql/ukrainian/errmsg.sys from install to mariadb-server-5.5.31-1.i686 conflicts with file from package mysql-libs-5.5.32-1.el6.remi.i686

Well, it seems that because the Php-mysql also deleted (in fact this should not be deleted), so phpMyAdmin also because of dependency and was deleted, I did not look carefully.

So it's best not to use wildcards *, but selectively delete several major,

# yum remove MySQL mysql-server mysql-libs

But I didn't test it again because there was a better way behind it.

C. Then install mariadb and corresponding PHP extensions.

It also specifies several major installation packages, and the rest lets yum own dependency checking.

# yum Install mariadb-server mariadb-client php-mysql

or instead of installing Mariadb-server, MARIADB galera cluster with synchronous multiple primary cluster characteristics (synchronous multi-master cluster) is changed:

# yum Install mariadb-galera-server mariadb-client galera

Here is more funny, did not pay attention to the front of the Php-mysql to uninstall, the results of running the database after the visit to WordPress, the results encountered the following error:

Your PHP installation appears to being missing the MySQL extension abound is required by WordPress.

D. Completion, repair

Regardless of whether the version is upgraded, it does not hurt to perform database upgrade instructions:

# mysql_upgrade-p

See if the database version is upgraded:

# mysql-u Root-p-e ' show global variables like ' version ';

Enter Password:

+---------------+----------------+

| variable_name | Value |

+---------------+----------------+

| Version | 5.5.31-mariadb |

+---------------+----------------+

Add MARIADB to the system from boot list,

# chkconfig--levels 345 MySQL on

Well, it used to be mysqld MySQL, and now it's MySQL, a letter d.

Start it,

# service MySQL Start

This time the Web site on the server should be working properly, access to the database is not a problem.

If necessary, you can reinstall the postfix after installing MARIADB. If you find the phpMyAdmin hint without access, it is uninstalled and you need to reinstall and configure phpMyAdmin.

In addition, the original MySQL and phpMyAdmin configuration files have been cleared and need to be reconfigured because of previous uninstall operations. In particular, MySQL's profile/etc/my.cnf because MARIADB creates a MY.CNF.D folder to store its own profile, but it first loads my.cnf, and the contents of the [Mysqld] section of the original MySQL configuration are valid.

3. Better Upgrade method

Or, under the system configuration described above, add the MARIADB installation source first, and then perform the system upgrade directly,

# Yum Update--skip-broken

MARIADB related installation packages will be installed, unwanted MySQL packages will be uninstalled, and the system should not be patched after the upgrade. Look at the previous part of the output:

==============================================================================================================

Package Arch Version Repository Size

==============================================================================================================

Installing:

Mariadb-galera-server i686 5.5.29-1 mariadb M

Replacing mysql.i686 5.5.21-1.el6.remi

Replacing mysql-server.i686 5.5.21-1.el6.remi

Mariadb-server i686 5.5.31-1 mariadb M

Replacing mysql-server.i686 5.5.21-1.el6.remi

mariadb-shared i686 5.5.31-1 MARIADB 1.0 M

Replacing mysql-libs.i686 5.5.21-1.el6.remi

Kernel i686 2.6.32-358.11.1.el6 Updates M

Libjpeg-turbo i686 1.2.1-1.el6 base 176 k

Replacing libjpeg.i686 6b-46.el6

Updating:

.........

Here I feel the mariadb design idea that seamless replacement for MySQL is not a gimmick, but a real technology. So I am not in a hurry to upgrade to the VPS to mariadb, anyway after CentOS will be used mariadb, then the natural migration bar.