Migrate from MySQL to MariaDB (CentOS)

Next, we will extract a background, and then the operation records I migrated from MySQL 5.5.31 to MariaDB 5.5.31 on CentOS 6.4. Finally, I found a better migration method.

1. Background

MySQL is the most popular open source relational database in the world. In 2008, Sun acquired MySQL. Then in 2010, Oracle acquired Sun again, so MySQL fell into the hands of Oracle. Oracle has never had a good relationship with the open-source community, and even MySQL has been made community and enterprise, so it cannot be used for free. The new features of MySQL Enterprise Edition are not open source code, which makes users uncomfortable. MariaDB does not have this problem. Compared with MySQL, MariaDB has the following advantages:

Free commercial use of MySQL can be divided into community edition and Enterprise Edition, thanks to Oracle)

Maria storage engine

PBXT storage engine

XtraDB storage engine

FederatedX storage engine

Faster query and processing of Replication

Thread Pool

Faster running

More extended functional modules

Supports Unicode sorting

So I decided to first look at how to migrate MySQL to MariaDB on the virtual machine, although it is unpredictable that I don't know when Michael Widenius will sell MariaDB to which big company.

In the past, MariaDB was designed to be fully compatible with MySQL, including APIs and client protocols, so that it can easily become a substitute for MySQL. Therefore, migrating from MySQL to MariaDB can basically be called an "Upgrade ", it's like a software upgrade. MariaDB's technical documentation is written as follows:

For the same basic version, such as 5.5), you can directly uninstall MySQL and install MariaDB. You do not even need to back up and restore the database. Of course, it is necessary to back up all databases before the upgrade, just in case.

If the main version of MariaDB is high, you need to use mysql_upgrade to upgrade it after you uninstall MySQL and install MariaDB. Yes, even the command line is the same as when MySQL is used.

All the original clients and connectors such as PHP, Perl, Python, and Java can work normally without any changes, because MariaDB and MySQL use the same client protocol, the client library files are also Binary compatible.

2. The 3 method below the migration operation record is better)

This VPS is installed with CentOS, the current version is 6.4, and MySQL version is 5.5.31 uses the remi source). The configurations of virtual machines are extremely similar. I didn't know much about it at the beginning. I performed this silly operation.

A. Configure the installation source of MARIADB first.

Currently, MariaDB's main version is 5.5, and my CentOS is 32-bit. Save the following installation source configuration as MariaDB. repo:/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 
 
 

Priority = 3 is the PRIORITIES priority plug-in for YUM.

B. Uninstall the original MYSQL and related packages.

 
 

  
  
# yum remove mysql* 
 
 

In the preceding example, we used * to delete all mysql data packets, including other packages deleted due to the package dependency, such as postfix ). Because conflicts may occur during direct installation, for example:

File/usr/share/mysql/ukrainian/errmsg. sys from install of 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 this should not be deleted because php-mysql is also deleted. Therefore, phpMyAdmin is also deleted due to dependency. I did not take a closer look.

Therefore, it is best not to use wildcard *, but to selectively delete several main,

 
 

  
  
# yum remove mysql mysql-server mysql-libs 
 
 

But I didn't test it again, because there are better methods later.

C. Install MARIADB and the corresponding PHP extension.

It also specifies several major installation packages, and the rest allows yum to perform dependency checks on its own.

 
 

  
  
# yum install MariaDB-server  MariaDB-client php-mysql 
 
 

Or, if MariaDB-server is not installed, change to MariaDB Galera cluster with the synchronous multi-master Cluster feature of synchronizing multiple master clusters:

 
 

  
  
# yum install MariaDB-Galera-server MariaDB-client galera 
 
 

The funny thing is that I did not pay attention to uninstalling php-mysql. As a result, I ran the database and accessed WordPress. The result encountered the following error:

Your PHP installation appears to be missing the MySQL extension which is required by WordPress.

D. Complete and patch

No matter whether the version is upgraded or not, there is no harm in executing the Database Upgrade command:

 
 

  
  
# mysql_upgrade -p 
 
 

Check whether the database version has been 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 auto-start list with the system,

 
 

  
  
# chkconfig --levels 345 mysql on 
 
 

Well, in the past MySQL was mysqld, And now mysql, a letter d is missing.

Start it,

 
 

  
  
# service mysql start 
 
 

At this time, the website on the server should be able to work normally and there is no problem in accessing the database.

If necessary, you can reinstall the postfix after installing MariaDB. If phpMyAdmin prompts that you have no access permission, it is uninstalled and you need to reinstall and configure phpMyAdmin.

In addition, the configuration files of MySQL and phpMyAdmin have been cleared and need to be reconfigured. In particular, the MySQL configuration file/etc/my. cnf, because although MariaDB creates a my. cnf. d folder to store your configuration file, but it will first load my. cnf, And the content in the [mysqld] section of the original MySQL configuration is still valid.

3. Better upgrade Methods

In the system configuration described above, add the MariaDB installation source and then directly execute the system upgrade,

# Yum update -- skip-broken

MariaDB-related installation packages will be installed, and unwanted MySQL packages will be uninstalled. You do not need to fix the system 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 19 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 32 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 24 M 
  
  
libjpeg-turbo i686 1.2.1-1.el6 base 176 k 
  
  
replacing libjpeg.i686 6b-46.el6 
  
  
Updating: 
  
  
......... 
 
 

Here, I feel that MariaDB's design philosophy is that the seamless replacement of MySQL is not a gimmick, but a real technology. So I am not in a rush to upgrade the VPS to MariaDB. Anyway, it will not be long before CentOS will adopt MariaDB. Then we will migrate it naturally.