Knowledge Base

DISCLAIMER: Use at your own risk. The below method may not work for everyone, depending on your current server & software settings.

======================CPanel/WHM 11.36 and higher Version Alert==============================
Note: For those of you that are running Cpanel 11.36 and higher, MySQL can no longer be disabled in the Update Preferences page. You may need to follow the instructions here: http://etwiki.cpanel.net/twiki/bin/v...de/RpmVersions then check the RPMCookbook link to learn how to disable a target RPM. However, when we tested with a fresh version of WHM 11.36, we did not touch the rpm.versions file and just followed the instructions in this tutorial, everything still worked as it should have.

Here is what you need to run as root to disable MySQL5/5.1/5.5 to be updated via rpms on the 11.36+ version of WHM:
 

Code:

/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

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

The above will create a new local.versions file that will override the rpm.versions file and MySQL will no longer be updated.

Per CPanel:
 

There is currently a defect in 11.36 that ignores /etc/mysqlupdisable. To work around this defect, until we get a fix, add the following to /etc/cpupdate.conf:

MYSQLUP=never

========================Follow the steps below to finish the MariaDB Installation============================

If you are looking to get rid of MySQL and replace it with MariaDB, please follow these steps:

1. If you are on Cpanel or any other control panel. Be sure to disable MySQL updates. In Cpanel, it's done by creating a mysqlupdisable file in /etc folder:

touch /etc/mysqlupdisable

2. Add the appropriate mariadb repo on your server by going here: https://downloads.mariadb.org/mariadb/repositories/.

Note: If you want to install something like 5.5.28a version, you can go to http://yum.mariadb.org and select the version you want. Keep in mind that x86 is the 32bit version and amd64 is the 64bit version, so choose correctly depending on your OS architecture.

For us it was the following in the /etc/yum.repos.d/MariaDB.repo file:
 

Code:

# MariaDB 5.5 repository list - created 2013-01-19 18:47 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5.28a/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

2a. Remove php* and mysql* from /etc/yum.conf file.

3. Run the following commands after stopping mysql:

Code:

/etc/init.d/mysql stop
cp -r /var/lib/mysql /var/lib/mysql.BAK    (to be safe)
mv /etc/my.cnf /etc/my.cnf.old
yum remove mysql-server mysql-libs mysql-devel mysql*
yum install MariaDB-server MariaDB-client MariaDB-devel php-mysql
/etc/init.d/mysql start
mysql_upgrade
/etc/init.d/mysql restart

NOTE: If after installing mariaDB mysql does not start, check for hung MySQL processes and kill them first.

NOTE: If, after you run the yum install line above and get the following errors:

Code:

Transaction Check Error:
  file /usr/lib64/libmysqlclient.so.16.0.0 from install of MariaDB-compat-5.5.28a-1.x86_64 conflicts with file from package MySQL-shared-5.1.66-1.cloud.x86_64
  file /usr/lib64/libmysqlclient_r.so.16.0.0 from install of MariaDB-compat-5.5.28a-1.x86_64 conflicts with file from package MySQL-shared-5.1.66-1.cloud.x86_64

Then you can run the following to fix the issue:

Code:

yum remove MySQL-shared-5.1.66-1.cloud.x86_64

(your MySQL may be different from the one here, depending on your old setup.) 
Then just re-run the yum install line again.

Alternatively, you can follow this how to and use their RPMs to install mariadb: http://blog.host4offshore.com/replac...entos-vps.html. 
How to fully remove MySQL before installing MariaDB: https://kb.askmonty.org/en/how-can-i...ql-to-mariadb/.
RPM location: https://downloads.mariadb.org/mariadb/5.5.28a/.

You should now have mariadb running on your server instead of mysql.

You can also check the error log in /var/lib/mysql for anything that may not be compatible with Mariadb.

Few Notes:

• Disabling MySQL in the Update Preferences in WHM is a must.
• CPANEL EXCLUSIVE: If you replace MySQL with MariaDB on your Cpanel server you may have to recompile PHP manually further down the road. See below on how to successfully recompile Apache/PHP to the latest versions in Cpanel and still have MariaDB working.

How to recompile/update PHP/Apache using EasyApache with MariaDB running:

The problem is that after you installed MariaDB, you had to completely disable MySQL from updating in WHM and with the /etc/mysqlupdisable file. So, when you run EasyApache with the MySQL modules, you may get errors and it will revert to the last saved file. Because ultimately you need to have 'mysql' showing when running 'php -m' command, otherwise your databases won't work with the sites. Here is a work around for this.

• In EasyApache, uncheck all MySQL related options and recompile. When it's finished, if you run 'php -m' you will notice that there is no mysql option.
• We will need to manually recompile php. Do the following commands.
• cd /home/cpeasyapache/src/ then go to the folder starting with php-. It will be whatever latest version you have installed. For me it was php-5.3.20.
• Run the following next: ./configure --disable-fileinfo --enable-bcmath --enable-calendar --enable-exif --enable-ftp --enable-gd-native-ttf --enable-libxml --enable-magic-quotes --enable-mbstring --enable-pdo=shared --enable-soap --enable-sockets --enable-zip --with-kerberos --prefix=/usr/local --with-apxs2=/usr/local/apache/bin/apxs --with-bz2 --with-curl=/opt/curlssl/ --with-curlwrappers --with-freetype-dir=/usr --with-gd --with-gettext --with-imap=/opt/php_with_imap_client/ --with-imap-ssl=/usr --with-jpeg-dir=/usr --with-libdir=lib64 --with-libexpat-dir=/usr --with-libxml-dir=/opt/xml2/ --with-mcrypt=/opt/libmcrypt/ --with-mm=/opt/mm/ --with-mime-magic --with-mysql=/var/lib --with-mysql-sock=/var/lib/mysql/mysql.sock --with-mysqli=/usr/bin/mysql_config --with-openssl=/usr --with-openssl-dir=/usr --with-pcre-regex=/opt/pcre --with-pdo-mysql=shared --with-pdo-sqlite=shared --with-pic --with-png-dir=/usr --with-pspell --with-sqlite=shared --with-ttf --with-xmlrpc --with-xpm-dir=/usr --with-zlib --with-zlib-dir=/usr 
  
  Note: You can get these options from info.php or by running php -i|grep configure (here you have to remove '' syntax).
  Note: If you run PHP using suphp, make sure you have your permissions set correctly or you will get an Internal Server Error. Fcgi and dso will work without any issues.
• make
• make test
• make install
• /etc/init.d/httpd restart
• In WHM change the PHP Handler for PHP5 or 4 (whichever one you are running) to dso if fcgi and suphp return errors.

When you now run 'php -m' you should see mysql as one of the compiled options. So you should be all good to go. This will need to be done every time Apache/PHP are recompiled using EasyApache. 

To sum up, we ran this test on a Centos 6 64bit VM with Cpanel. One time we had to manually recompile PHP per the explanation above and it would only work with dso. However, upon several consecutive tries when we moved the my.cnf file before uninstalling mysql and installing mariadb, we could easily recompile php/apache with the mysql options enabled via EasyApache as usual. Furthermore, all php handlers (dso, fcgi, suphp) worked with our site (a simple smf forum).

Sample my.cnf file for Mariadb is below. Make modifications as you deem necessary.
 

# MariaDB database server configuration file.
#
# You can copy this file to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/se...variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/lib/mysql/mysql.sock
nice = 0

# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
user=mysql

# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings = 2
#
# Enable the slow query log to see queries with especially long duration
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 1
#log_slow_rate_limit = 1000
log_slow_verbosity = query_plan
log-bin = mysql-bin
sync_binlog = 1
expire_logs_days= 10
max_binlog_size = 100M
wait_timeout = 30

#server-id = last octet of IP address
#character-set-server = utf8

# * Fine Tuning
#
max_connections = 200
connect_timeout = 8
# wait_timeout = 600
max_allowed_packet = 50M
thread_cache_size = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size = 800M
max_heap_table_size = 3G
max_connect_errors = 10000

# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover = BACKUP
key_buffer_size = 1G
#open-files-limit = 2000
table_cache = 1000
myisam_sort_buffer_size = 500M
concurrent_insert = 2
read_buffer_size = 8M
read_rnd_buffer_size = 1M

# Cache only tiny result sets, so we can fit more in the query cache.
query_cache_limit = 200M
query_cache_size = 700M
query_cache_min_res_unit = 1K
low_priority_updates = 1
# for more write intensive setups, set to DEMAND or OFF
#query_cache_type = DEMAND

# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine = InnoDB
# you can't just change log file size, requires special procedure
#innodb_log_file_size = 50M
innodb_buffer_pool_size = 2G
innodb_log_buffer_size = 10M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[isamchk]
key_buffer = 16M

To upgrade to the latest MariaDB version, you may need to modify the repo path in your /etc/yum.repos.d/MariaDB.repo file to the version of choice. Then stop mysql and run 
 

Code:

mysql_upgrade

If you are upgrading from, for example, 5.5.28a to 5.5.29, and the above command does not do the upgrade, you will need to uninstall the current version of MariaDB and reinstall the latest one using the steps outlined in the first post of this thread.

1. Stop mysql and backup any databases/configuration files that you may need.
2. Run the following command to uninstall the current version of MariaDB:

Code:

yum remove MariaDB-common MariaDB-compat MariaDB-client MariaDB-devel MariaDB-server

3. Update your MariaDB repo file to point to the latest version (5.5 is the current latest tree).
4. Follow the installation steps in the first post to install the latest version via yum.