HostSEO Blog

Stories and News from IT Industry, Reviews & Tips | Technology Blog


How to install MariaDB Server in cPanel/WHM servers

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 !

Subscribe Now

10,000 successful online businessmen like to have our content directly delivered to their inbox. Subscribe to our newsletter!

Archive Calendar

SatSunMonTueWedThuFri
 12
3456789
10111213141516
17181920212223
24252627282930

Born in 2004 ... Trusted By Clients n' Experts

SEO Stars

They never made me feel silly for asking questions. Help me understand how to attract more people and improve my search engine ranking.

Read More

Emily Schneller Manager at Sabre Inc
SEO Stars

Took advantage of Hostseo's superb tech support and I must say, it is a very perfect one. It is very fast, servers reliability is incredible.

Read More

Leena Mäkinen Creative producer
SEO Stars

We're operating a worldwide network of servers with high quality standards requirements, we’ve choose hostseo to be our perfect partner.

Read More

Ziff Davis CEO at Mashable
SEO Stars

It’s very comfortable to know I can rely about all technical issues on Hostseo and mostly that my website and emails are safe and secured here.

Read More

Isaac H. Entrepreneur
SEO Stars

With hostseo as a hosting partner we are more flexible and save money due to the better packages with great pricing, free SEO n' free SSL too!

Read More

Madeline E. Internet Professional