MySQL Performance: Converting MySQL to MariaDB
- MySQL Performance: Identifying Long Queries
- MySQL Performance: MyISAM vs InnoDB
- MySQL Performance: How To Leverage MySQL Database Indexing
- MySQL Performance: MySQL vs. MariaDB
- MySQL Performance: Converting MySQL to MariaDB
- MySQL Performance: System Config & Routine Maintenance
- MySQL Performance: InnoDB Buffers & Directives
- MySQL Performance: MyISAM
- MySQL Performance: MySQL/MariaDB Indexes
- MySQL Performance: Intro to JOINs in SQL
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
Ubuntu 14.04/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:
- Schedule accurate downtime.
- Ensure adequate backups.
- Remove the existing MySQL binaries/packages.
- Install the correct MariaDB repository.
- Clear repository caches to expunge old package data.
- Install MariaDB packages via the system’s package manager.
- Start MariaDB by starting the MySQL service.
- Run MySQL upgrade script.
- 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.

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.
Sample Backup Plan
+Sample Reversion Plan
+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 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.
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
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 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
This command has no visible output unless errors occur.
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
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 mysql
Example Output:
Running Transaction Test
Truncated for brevity.
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!
Step 6: Install the MariaDB-serverandMariaDB-client packages via yum.
yum install MariaDB-server MariaDB-client -y
Example Output:Installed:
Truncated for brevity.
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!
Step 7:Restart the MySQL service
service mysql start
Example Output:

~ $ service mysql start
Step 8: Run MySQL upgrade tool
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!
mysql_upgrade
Example Output:
~ $ mysql_upgrade
Truncated for brevity.
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'
OK
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.
Step 2: Remove all MariaDB packages.
yum remove MariaDB-*
Example Output:Running Transaction Test
Truncated for brevity.
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!
Step 3: Remove existing MariaDB repository.
rm -f /etc/yum.repos.d/MariaDB*.repo
This command has no visible output unless errors occur.
Step 4: Create MariaDB 10.O Yum repository file.
cat < /etc/yum.repos.d/MariaDB.repo
This command has no visible output unless errors occur.
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
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-client
Example Output:
Truncated for brevity.
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!
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.
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
Truncated for brevity.
~ $ 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/null
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
Example Output:
+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.
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
Truncated for brevity.
~ $ 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/null
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 -y
Example 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 status
Example Output:~ $ sudo service mysql status
mysql start/running, process 4745
Step 9: Finally, run mysql_upgrade to complete the upgrade.sudo mysql_upgrade
The output illustrates normal errors when upgrading.
Example Output:
+Step 10: The upgrade is complete, check that your databases exist and are working.
Related Articles:
- Using MySQL Command Line to Create a User
- How to Use Disk Quotas in Dedicated Linux Servers With cPanel
- How to Use Disk Quotas in Dedicated Linux Servers with Plesk
- Remove a MySQL User on Linux via Command Line
- Remove Permissions for a MySQL User on Linux via Command Line
- Grant Permissions to a MySQL User on Linux via Command Line

About the Author: Jason Potter
A veteran of the IT Support field, I have more than a decade of experience in systems administration, web hosting, and cPanel servers. I enjoy writing and providing complicated technical concepts in layman terms. On my free time, I enjoy playing several types video games, automation scripting and just living life with my wife and two kids.
Our Sales and Support teams are available 24 hours by phone or e-mail to assist.
Latest Articles
How to Install Apache Tomcat on Linux (AlmaLinux)
Read ArticleWhat is CentOS? Everything You Need to Know
Read ArticleWhat is CentOS? Everything You Need to Know
Read ArticleRedis as Cache: How It Works and Why You Should Use It
Read ArticleRefer-a-Friend Program for Website Hosting: Get $100 for Each Friend!
Read Article