CentrioHost Blog

What is MariaDB ?

MariaDB is a binary drop in replacement of the same MySQL version (for example MySQL 5.1 -> MariaDB 5.1, MariaDB 5.2 & MariaDB 5.3 are compatible. MySQL 5.5 will be compatible with MariaDB 5.5). What this means is that:

• Data and table definition files (.frm) files are binary compatible.
• All client APIs, protocols and structs are identical. All filenames, binaries, paths, ports, sockets, and etc… should be the same.
• All MySQL connectors (PHP, Perl, Python, Java, .NET, MyODBC, Ruby, MySQL C connector etc) work unchanged with MariaDB.
• The mysql-client package also works with MariaDB server.
• The shared client library is binary compatible with MySQL’s client library.

This means that for most cases, you can just uninstall MySQL and install MariaDB and you are good to go. (No need to convert any datafiles if you use same main version, like 5.1). You must however still run mysql_upgrade to finish the upgrade. This is needed to ensure that your mysql privilege and event tables are updated with the new fields MariaDB uses. MariaDB do a monthly merges with the MySQL code base to ensure its compatibility and get any and all features and bug fixes Oracle adds.

There are some installation issues with PHP5 that you should be aware of ( REF:https://mariadb.com/kb/en/installation-issues-with-php5 )

What are the advantages of using MariaDB Server ?

• Faster and safer replication: Group commit for the binary log. This makes many setups that uses replication and lot’s of updates more than 2x times faster.
• Indexes for the MEMORY(HEAP) engine are faster. According to a simple test, 24% faster on INSERT for integer index and 60% faster for index on a CHAR(20) column.
• CHECKSUM TABLE is faster.
• MariaDB improved the performance of character set conversions (and removed conversions when they were not really needed). Overall speed improvement is 1-5 % (according to sql-bench) but can be higher for big results sets with all characters between 0x00-0x7f.
• Pool of Threads in MariaDB 5.1 and even better in MariaDB 5.5. This allows MariaDB to run with 200,000+ connections and with a notable speed improvement when using many connections.
• There are some improvements to the DBUG code to make its execution faster when debug is compiled in but not used.
• Our use of the Aria storage engine enables faster complex queries (queries which normally use disk-based temporary tables). The Aria storage engine is used for internal temporary tables, which should give a speed up when doing complex selects. Aria is usually faster for temporary tables when compared to MyISAM because Aria caches row data in memory and normally doesn’t have to write the temporary rows to disk.
• The test suite has been extended and now runs much faster than before, even though it tests more things.

How can you replace MySQL with MariaDB Server in cPanel installed systems ?

Below you will find some steps on how to affectively stop cPanel/WHM from maintaining MySQL and begin utilizing MariaDB for any and all database activity on your server. MariaDB is “An enhanced, drop-in replacement for MySQL” (Source: https://mariadb.com) .

We recommend only experienced systems administrators perform the above and we are not responsible for any possible data loss.

Step 1: Backup existing MySQL data

Make sure to save all existing data just in case there are any issues.

# mysqldump --all-databases --routines --triggers > /home/db_dump/alldb.sql
# service mysql stop
# cp -r /var/lib/mysql/mysql /home/db_dump/
# service mysql start

Step 2: Disable the targets so cPanel no longer handles MySQL updates #ONLY FOR cPanel 11.36+

The following will mark the versions of MySQL we distribute as uninstalled so they are no longer maintained by cPanel/WHM

# /scripts/update_local_rpm_versions --edit target_settings.MySQL50 uninstalled
# /scripts/update_local_rpm_versions --edit target_settings.MySQL51 uninstalled
# /scripts/update_local_rpm_versions --edit target_settings.MySQL55 uninstalled

Step 3: Remove existing MySQL RPMâ$™s so theres a clean slate for MariaDB

Important: The below command will uninstall the MySQL RPMâ$™s!

# /scripts/check_cpanel_rpms --fix --targets=MySQL50,MySQL51,MySQL55

Step 4: Create a yum repository for MariaDB

Access https://downloads.mariadb.org/mariadb/repositories and select the DISTRO and place the repo content to /etc/yum.repos.d/MariaDB.repo

#nano /etc/yum.repos.d/MariaDB.repo

---------
# MariaDB 5.5 CentOS repository list - created 2013-06-23 21:13 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=

--------

Step 5: install MariaDB using the following command

# yum install MariaDB-server MariaDB-client MariaDB-devel

If you are having any dependency problem, please remove PHP from the /etc/yum.conf file and then run yum command again. Please add it back to yum conf once the package is installed.

# /etc/init.d/mysql start
# mysql_upgrade
# /etc/init.d/mysql restart

Please make sure to add PHP back to exclude section of /etc/yum.conf file.

Final Step: Rebuild Easyapache/PHP to ensure modules are intact/working

# /scripts/easyapache --build

Done !