MySQL Performance: Converting MySQL to MariaDB

As we explored in our previous article of our MySQL Performance Series: MySQL vs. MariaDB there are very few downsides to using MariaDB over standard MySQL. Our high-availability MariaDB's have proven itself to be a worthy successor with easily mitigated drawbacks.  As the last article in our series we will focus on upgrading to various MySQL and MariaDB version on the following servers:

CentOS 6/7

• MySQL to MariaDB on CentOS 6/7 with cPanel
• MySQL to MariaDB on CentOS 7 with Plesk Onyx 17
• MySQL 5.1-5.5 to MariaDB 5.5 on CentOS 6
• MariaDB 5.5 to MariaDB 10.0 on CentOS 6

Ubuntu 14.04/16.04

• MySQL 5.1-5.5 to MariaDB 5.5 on Ubuntu 14.04
• MySQL 5.7 to MariaDB 10.2 on Ubuntu 16.04

Choosing a MariaDB Upgrade Plan

Selecting the right upgrade plan requires determining which version of MySQL is running on the intended system. A universal method of determining what version of MySQL the server is currently running is to query the server itself to report the version. This works the same despite the Linux distribution running on the server:

mysql -h localhost -e 'SHOW VARIABLES LIKE "version"\G'

Example Output:

~ $ mysql -h localhost -p -e 'SHOW VARIABLES LIKE "version"\G'
*************************** 1. row ***************************
Variable_name: version
Value: 5.5.61-MariaDB

Once you’ve identified your MySQL version, use the key below to locate the correct upgrade procedure for your particular server. Some versions of MySQL require upgrading to MariaDB 5.5 before upgrading to MariaDB 10.x versions.

Upgrading from MySQL  to MariaDB

The upgrade process is similar between most server types. There are differences with the specific commands that are executed, but the overall procedure follows this General Upgrade Procedure:

1. Schedule accurate downtime.
2. Ensure adequate backups.
3. Remove the existing MySQL binaries/packages.
4. Install the correct MariaDB repository.
5. Clear repository caches to expunge old package data.
6. Install MariaDB packages via the system’s package manager.
7. Start MariaDB by starting the MySQL service.
8. Run MySQL upgrade script.
9. Confirm MySQL databases are accessible and data is correct

We’ve included specific procedures below for various Liquid Web systems. Before choosing your applicable upgrade procedure, let us review the first two steps in the above General Upgrade Procedure. These steps are generally universal and are critical to the success of any maintenance plan.

Schedule Accurate Downtime

Scheduling proper maintenance windows to perform changes affecting production environments can be tricky. The maintenance event window must include enough time to perform all necessary tasks in the maintenance plan, plus any troubleshooting and extra time to complete the Reversion Plan if needed.

Ensure Adequate Backups

There is more to a maintenance backup plan than just backing up files and databases. It’s also necessary to form a Reversion Plan, which outlines the process to restore the environment to its original state before the start of the maintenance.
Maintenance events can sometimes lead to unforeseen issues with the alternative of reverting to backups. Having both a valid backup plan and a valid reversion plan to restore services is a critical component of successful maintenance. Below are outlines of both a Sample Backup Plan and a Sample Reversion Plan which can be used with this article’s upgrade procedure outlined.

MySQL to MariaDB on CentOS 6/7 with cPanel

All Liquid Web cPanel server images include MariaDB 5.5 already installed. This includes both CentOS 6 and CentOS 7 server images. There is no need to upgrade unless the MariaDB 10.x series is required. For a manual upgrade, procedures as provided by MariaDB here: Upgrading from MariaDB 5.5 to MariaDB 10.0, otherwise, cPanel provides an easy point-&-click interface which does all the heavy lifting for you. Follow one of the procedures below in either 'Text Only' form or Graphical form.

cPanel MariaDB Upgrade Instructions:

Step A) Confirm backups are present before proceeding at the scheduled time.

Step B) Login to WHM and load the MySQL/MariaDB Upgrades page:

① Use the quick search box on the left and type in: mysql upgrade

② Locate and Click the MySQL/MariaDB Upgrades link.

③ Selected your desired version of MariaDB.        

④ Click the Next button.

Step C)Wait for the “Upgrade Warnings” page to finish loading.

① Read &acknowledge each warning message by enabling each checkbox.

② Click the Continue button.

Step D)Wait for the “Upgrade Type” page to finish loading.

① Select Unattended Upgrade for a seamless upgrade experience.

② Click the Continue button.

Step E) Wait for the Upgrade Process to complete.

MySQL to MariaDB on CentOS 7 with Plesk Onyx 17

All Liquid Web CentOS 7 server images include MariaDB 5.5 already installed, including our CentOS 7 Plesk Onyx 17 servers. There is no need to upgrade unless the MariaDB 10.x series is required. In these cases, follow the standard MariaDB Plesk Upgrade Procedures here: How to upgrade MySQL 5.5 to 5.6/5.7 or MariaDB 5.5 to 10.0/10.1/10.2 on Linux

MySQL 5.1-5.5 to MariaDB 5.5 on CentOS 6

Due to compatibility limitation, MySQL 5.1, 5.2, 5.3 and 5.5.x must be upgraded to MariaDB 5.5 and cannot be upgraded to MariaDB 10.0 directly. This upgrade plan will walk through getting any one of these deprecated MySQL versions upgraded to MariaDB 5.5.  Once the upgrade to MariaDB 5.5 is complete, follow the How to upgrade MariaDB 5.5 to MariaDB 10.0 on CentOS 6 instructions to continue the upgrade process to MariaDB 10.

Step 1:Confirm backups are present before proceeding at the scheduled time.

Step 2: Stop the MySQL service.

service mysqld stop

Example Output:
~ $ service mysqld restart
Stopping mysqld: [ OK ]

Step 3:Install the MariaDB 5.5 repository for CentOS 6

cat < /etc/yum.repos.d/MariaDB.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOFThis command has no visible output unless errors occur.

Step 4:Clean the yum repository cache

yum clean all
Example Output:

~ $ yum clean all
Loaded plugins: fastestmirror
Cleaning repos: base extras mariadb updates
Cleaning up Everything
Cleaning up list of fastest mirrors

Step 5: Remove MySQL packages

yum remove mysql-server mysqlExample Output:

Running Transaction Test
Transaction Test Succeeded
Running Transaction
Erasing : mysql-server-5.1.73-8.el6_8.x86_64 1/1
warning: /var/log/mysqld.log saved as /var/log/mysqld.log.rpmsave
Verifying : mysql-server-5.1.73-8.el6_8.x86_64 1/1
Removed:
mysql-server.x86_64 0:5.1.73-8.el6_8
Complete!Truncated for brevity.

Step 6: Install the MariaDB-serverandMariaDB-client packages via yum.

yum install MariaDB-server MariaDB-client -y

Example Output:Installed:
MariaDB-client.x86_64 0:10.1.35-1.el6 MariaDB-compat.x86_64 0:10.1.35-1.el6 MariaDB-server.x86_64 0:10.1.35-1.el6
Dependency Installed:
MariaDB-common.x86_64 0:10.1.35-1.el6 boost-program-options.x86_64 0:1.41.0-28.el6
galera.x86_64 0:25.3.23-1.rhel6.el6 jemalloc.x86_64 0:3.6.0-1.el6
Replaced:
mysql-libs.x86_64 0:5.1.73-8.el6_8
Complete!Truncated for brevity.

Step 7:Restart the MySQL service

service mysql startExample Output:

~ $ service mysql start
Starting MariaDB.180808 18:21:13 mysqld_safe Logging to '/var/lib/mysql/cent6.hostname.err'.
180808 18:21:13 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
SUCCESS!Step 8: Run MySQL upgrade tool

mysql_upgradeExample Output:

~ $ mysql_upgrade
MySQL upgrade detected
Phase 1/4: Fixing views from mysql
mysql
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Phase 4/4: Running 'mysql_fix_privilege_tables'
OKTruncated for brevity.

Step 9: Confirm MariaDB server is running as expected

mysql -e 'SHOW VARIABLES LIKE "version"\G'Example Output:

~ $ mysql -e 'SHOW VARIABLES LIKE "version"\G'
*************************** 1. row ***************************
Variable_name: version
Value: 5.5.61-MariaDB

Step 10: The upgrade is complete, check that your databases exist and are working.

[ Optional ] follow the How to upgrade MariaDB 5.5 to MariaDB 10.0 on CentOS 6 instructions.

MariaDB 5.5 to MariaDB 10.0 on CentOS 6

Step 1: Confirm backups are present before proceeding at the scheduled time.

Step 2: Remove all MariaDB packages.

yum remove MariaDB-*Example Output:
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Erasing : MariaDB-server-5.5.61-1.el6.x86_64 1/2
Erasing : MariaDB-client-5.5.61-1.el6.x86_64 2/2
Verifying : MariaDB-server-5.5.61-1.el6.x86_64 1/2
Verifying : MariaDB-client-5.5.61-1.el6.x86_64 2/2
Removed:
MariaDB-client.x86_64 0:5.5.61-1.el6 MariaDB-server.x86_64 0:5.5.61-1.el6
Complete!Truncated for brevity.

Step 3: Remove existing MariaDB repository.

rm -f /etc/yum.repos.d/MariaDB*.repoThis command has no visible output unless errors occur.

Step 4: Create MariaDB 10.O Yum repository file.

cat < /etc/yum.repos.d/MariaDB.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF This command has no visible output unless errors occur.

Step 5: Clean the Yum cache.

yum clean all

Example Output:

~ $ yum clean all
Loaded plugins: fastestmirror
Cleaning repos: base extras mariadb updates
Cleaning up Everything
Cleaning up list of fastest mirrors


Step 6: Install MariaDB-server and MariaDB-client package via Yum.

yum install MariaDB-server MariaDB-clientExample Output:

Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : MariaDB-client-10.0.36-1.el6.x86_64 1/2
Installing : MariaDB-server-10.0.36-1.el6.x86_64 2/2
Verifying : MariaDB-server-10.0.36-1.el6.x86_64 1/2
Verifying : MariaDB-client-10.0.36-1.el6.x86_64 2/2
Installed:
MariaDB-client.x86_64 0:10.0.36-1.el6 MariaDB-server.x86_64 0:10.0.36-1.el6
Complete!
Truncated for brevity.

Step 7: Start the MySQL Service.

service mysql start


Step 8: Run the MySQL Upgrade script.

mysql_upgrade

Step 9: The upgrade is complete, check that your databases exist and are working.

MySQL 5.1-5.5 to MariaDB 5.5 on Ubuntu 14.04

Step 1: Confirm backups are present before proceeding at the scheduled time.

Step 2: Install the MariaDB 5.5 repository with this one-liner it’ll install the necessary required tools (if missing), the Ubuntu 14.04 GPG key and the MariaDB 5.5 repository while updating the apt package caches.

sudo apt-get install software-properties-common -y
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu trusty main'
sudo apt-get update
Example Output:
~ $ sudo apt-get install software-properties-common -y > /dev/null
~ $ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
Executing: gpg --ignore-time-conflict --no-options --no-default-keyring --homedir /tmp/tmp.PtpHIXMNvY --no-auto-check-trustdb --trust-model always --keyring /etc/apt/trusted.gpg --primary-keyring /etc/apt/trusted.gpg --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
gpg: requesting key 1BB943DB from hkp server keyserver.ubuntu.com
gpg: key 1BB943DB: public key "MariaDB Package Signing Key <package-signing-key@mariadb.org>" imported
gpg: Total number processed: 1
gpg: imported: 1
~ $ sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu trusty main'
~ $ sudo apt-get update > /dev/nullTruncated for brevity.

Step 3: Stop mysql service.

sudo service mysql stop
Example Output:
~ $ sudo service mysql stop
mysql stop/waiting

Step 4: Rename MySQL config file /etc/mysql/my.cnf to /etc/mysql/my.cnf.bak. This step is necessary to ensure all incompatible directives/variables have been removed from the configuration to prevent problems with the installation.

sudo mv /etc/mysql/my.cnf{,.bak}

Step 5: Install the mariadb-server package. This will simultaneously remove the existing MySQL binaries and then install the new MariaDB binaries.
sudo apt-get install mariadb-server -y

Step 6: During the installation an on-screen dialog will appear prompting to change the MySQL root password twice. Follow the instructions to either change the password or skip the change by providing a blank password.

Step 7: When completed successfully, the mysql service should automatically start and the mysql_upgrade script is completed automatically during the installation process.

A) Check that the mysql service is running.sudo service mysql status
Example Output:
~ $ sudo service mysql status
mysql start/running, process 4745


B) Check that the mysql_upgrade was successful.sudo mysql_upgrade

Example Output:
~ $ sudo mysql_upgrade
This installation of MySQL is already upgraded to 5.5.61-MariaDB, use --force if you still need to run mysql_upgrade

Step 8: The upgrade is complete, check that your databases exist and are working.

MySQL 5.7 to MariaDB 10.2 on Ubuntu 16.04

Step 1: Confirm backups are present before proceeding at the scheduled time.

Step 2: Check the local root account has a proper password. Before upgrading to MariaDB 10.2, check the ‘root’ @ ’localhost’ user to ensure a password is assigned otherwise, the auth_socket plugin uses an empty password. This is common practice in older setups and causes problems with the upgrade process due to the default mode of MariaDB 10.2 in Strict SQL.

The following script will print the local root user details.

mysql -e 'select User,Host,Plugin,authentication_string from user where authentication_string = "" and plugin = "auth_socket";' mysql
Example Output:
~ $ mysql -e 'select User,Host,Plugin,authentication_string from user where authentication_string = "" and plugin = "auth_socket";' mysql
+------+-----------+-------------+-----------------------+
| User | Host | Plugin | authentication_string |
+------+-----------+-------------+-----------------------+
| root | localhost | auth_socket | |
+------+-----------+-------------+-----------------------+

Below are two examples of how to update these entries.

Automagic Method: The following one-liner reads the password from /root/.my.cnf and uses that password to update the MySQL user entry, if the file exists.

sudo test -e /root/.my.cnf && sudo mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '$(awk -F"[=']" 'tolower($1)~/password/{print $(NF-1)}' /root/.my.cnf)'; select User,Host,Plugin,authentication_string from user where user = 'root' and host = 'localhost';" mysql || echo “ERROR: /root/.my.cnf does not exist, are you root?”
Example Output:
~ $ sudo test -e /root/.my.cnf && sudo mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '$(awk -F"[=']" 'tolower($1)~/password/{print $(NF-1)}' /root/.my.cnf)'; select User,Host,Plugin,authentication_string from user where user = 'root' and host = 'localhost';" mysql || echo “ERROR: /root/.my.cnf does not exist, are you root?”
+------+-----------+-----------------------+-------------------------------------------+
| User | Host | Plugin | authentication_string |
+------+-----------+-----------------------+-------------------------------------------+
| root | localhost | mysql_native_password | *40C5E49F0CC7BDC637FEEDFBF14FF100C37619D7 |
+------+-----------+-----------------------+-------------------------------------------+

Manual Method: The following syntax is necessary to add a proper password to the root user account. Don’t forget to update the /root/.my.cnf file with the correct password or your update may encounter errors. Be sure to substitute the correct password for SuperSecretPass.

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘SuperSecretPass’;

Step 3: Install the MariaDB 10.2 repository. This is a multi-command stanza that will install the necessary required tools, GPG key, repository and update the package caches.

sudo apt-get install software-properties-common -y > /dev/null
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo add-apt-repository 'deb [arch=amd64,arm64,i386,ppc64el] http://ftp.osuosl.org/pub/mariadb/repo/10.2/ubuntu xenial main'
sudo apt-get update > /dev/null

Example Output:
~ $ sudo apt-get install software-properties-common -y
~ $ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
Executing: /tmp/tmp.J8PdET9w5B/gpg.1.sh --recv-keys
--keyserver
hkp://keyserver.ubuntu.com:80
0xF1656F24C74CD1D8
gpg: requesting key C74CD1D8 from hkp server keyserver.ubuntu.com
gpg: key C74CD1D8: public key "MariaDB Signing Key <signing-key@mariadb.org>" imported
gpg: Total number processed: 1
gpg: imported: 1 (RSA: 1)
~ $ sudo add-apt-repository 'deb [arch=amd64,arm64,i386,ppc64el] http://ftp.osuosl.org/pub/mariadb/repo/10.2/ubuntu xenial main'
~ $ sudo apt-get update > /dev/nullTruncated for brevity.

Step 4: Stop mysql service.

sudo service mysql stop
Example Output:
~ $ sudo service mysql stop
mysql stop/waiting

Step 5: Rename MySQL config file /etc/mysql/my.cnf to /etc/mysql/my.cnf.bak. This step is necessary to ensure all incompatible directives/variables have been removed from the configuration to prevent problems with the installation.
sudo mv /etc/mysql/my.cnf{,.bak}
Step 6: Install the mariadb-server package. This will simultaneously remove the existing MySQL binaries and then install the new MariaDB binaries.

sudo apt-get install mariadb-server -yExample Output:
~ $ sudo apt install mariadb-server -y</code?

Step 7: During the installation an on-screen dialog will appear prompting that the root password could not be changed because one is already in place. Press enter to proceed.

Step 8: When completed successfully, the mysql service should automatically start.
sudo service mysql statusExample Output:
~ $ sudo service mysql status
mysql start/running, process 4745

Step 9: Finally, run mysql_upgrade to complete the upgrade.
sudo mysql_upgradeThe output illustrates normal errors when upgrading.

Step 10: The upgrade is complete, check that your databases exist and are working.