June 19, 2014

How to install and run WordPress with MariaDB

image from wikipedia

WordPress has evolved from a specialized blogging platform into today's most widely used open source content management software (CMS), with thousands of third-party themes and plugins. WordPress was developed with MySQL as a back end, but because MariaDB is designed as a binary drop-in replacement of the original MySQL, you can replace MySQL with MariaDB for your WordPress installation and take advantage of its better performance, along with new features such as the two new database engines: XtraDB, which replaces InnoDB, and Aria, a crash-safe alternative for MyISAM.

To run your existing WordPress scripts with the new RDBMS, start by migrating your existing MySQL database system to MariaDB.

First, establish an SSH connection to your server. Stop the MySQL service with the command /etc/init.d/mysqld stop. You should see the confirmation Stopping mysqld: [ OK ].

Make sure you have backups of your databases before proceeding with any database service migration or upgrade. The location of the MySQL database data folder and the configuration file are similar on most Linux distributions, which means you ought to be able to use the following commands to copy data to your backup location:

cp /var/lib/mysql/ backup/ -r
cp /etc/my.cnf backup/

Alternatively, you can create a complete backup of all the databases with the command mysqldump --all-databases > allDBbackup.sql. Include the --user=root --password parameters after the mysqldump command if you have set a password for your MySQL server root user.

Next, remove the MySQL service from your server. Start by getting a list of the installed MySQL packages:

rpm -qa | grep mysql
mysql-5.1.69-1.el6_4.i686
mysql-devel-5.1.69-1.el6_4.i686
mysql-libs-5.1.69-1.el6_4.i686
mysql-server-5.1.69-1.el6_4.i686
php-mysql-5.3.3-23.el6_4.i686

Uninstall the MySQL server and client, the libraries, and the development tools by executing the command yum remove mysql mysql-server mysql-devel mysql-libs.

Verify your distribution and kernel versions:

 

cat /etc/redhat-release
CentOS release 6.4 (Final)

uname -a
Linux test.server 2.6.32-358.23.2.el6.i686 #1 SMP Wed Oct 16 17:21:31 UTC 2013 i686 i686 i386 GNU/Linux

Based on the information those commands report, generate the correct MariaDB repository file and store it under the /etc/yum.repos.d/ folder:

[root@test yum.repos.d]# cat MariaDB.repo
# MariaDB 5.5 CentOS repository list - created 2014-06-01 14:13 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-x86
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Because the URL of the GPG key used to verify the integrity of the package downloaded from the repository is included in the above file, you can immediately proceed with the MariaDB installation:

 

yum clean all
yum install MariaDB-server MariaDB-client

Yum will retrieve and import the GPG key during the installation procedure. It will install the MariaDB-common and MariaDB-compat dependencies and the MariaDB server and client applications.

Start the MariaDB service and verify its version:

/etc/init.d/mysql start
Starting MySQL... SUCCESS!

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

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

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

MariaDB [(none)]> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.5.37-MariaDB-34.0 |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.5.37-MariaDB      |
| version_comment         | MariaDB Server      |
| version_compile_machine | i686                |
| version_compile_os      | Linux               |
+-------------------------+---------------------+
7 rows in set (0.01 sec)

If you had customized your MySQL configuration, you can copy the settings from the my.cnf file located in your backup directory to the new /etc/my.cnf file. Then import the databases with the command mysql < allDBbackup.sql.

Congratulations – you've just replaced MySQL with MariaDB for your WordPress site. You can verify that your WordPress database is correctly imported by running a few common MySQL queries:

mysql
MariaDB [(none)]> use wp1;
MariaDB [wp1]> explain wp_options;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| option_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| option_name  | varchar(64)         | NO   | UNI |         |                |
| option_value | longtext            | NO   |     | NULL    |                |
| autoload     | varchar(20)         | NO   |     | yes     |                |
+--------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

MariaDB [wp1]> select * from wp_options where option_name like "%home%";
+-----------+-------------+-----------------------+----------+
| option_id | option_name | option_value          | autoload |
+-----------+-------------+-----------------------+----------+
|        36 | home        | http://testdomain.com/| yes      |
+-----------+-------------+-----------------------+----------+
1 row in set (0.01 sec)

You can also load your WordPress sites in a web browser and confirm their functionality.

Installing a fresh WordPress

Since MariaDB is a drop-in replacement for MySQL, installing WordPress with MariaDB does not differ from the standard method with MySQL. Basically, you download the latest WordPress package, extract the archive in a directory on your server, then access the corresponding location with your web browser to complete the installation procedure. During the first step of the setup you are prompted to provide the database details. Assuming you've installed MariaDB already as shown above, you can create the new database and assign a user to it from the command-line interface:

mysql
MariaDB [(none)]> create database new_wp;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> grant all privileges on new_wp.* to wpuser@localhost identified by 'myp@Ssw0Rd';
Query OK, 0 rows affected (0.01 sec)

Then you can complete the WordPress installation from the web interface and start using your new content management system.

About Svetoslav Savov

Svetoslav Savov an a Linux enthusiast with more than 8 years of experience on a Senior position in one of the leading web hosting companies. He has a master degree in the Computer networks and enjoys working with open-source software solutions. In his spare time he practices extreme sports and travels around the world. e-mail address: svetlio_81@yahoo.com

Read all posts by Svetoslav Savov