How to upgrade MySQL or MariaDB properly

If you've used the Site Health tool of your WordPress installation and it reported that your server has an Outdated SQL server, you probably want to upgrade the database software to the new, faster version, to improve the performance of your web site.

First things first though: what is described below worked for us, but it MAY NOT WORK for your specific server configuration. THE FOLLOWING INFORMATION IS PROVIDED "AS-IS" WITHOUT A WARRANTY OF ANY KIND. BY CHOOSING TO USE THE FOLLOWING INFORMATION, YOU ASSUME THE ENTIRE RISK OF SELECTION, APPLICATION, AND USE OF THE INFORMATION. INDEPENDENT OF THE FORGOING PROVISIONS, IN NO EVENT AND UNDER NO LEGAL THEORY, INCLUDING WITHOUT LIMITATION, TORT, CONTRACT, OR STRICT PRODUCTS LIABILITY, SHALL WINABILITY SOFTWARE CORPORATION OR ANY OF ITS SUPPLIERS BE LIABLE TO YOU OR ANY OTHER PERSON OR ENTITY FOR ANY INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES OF ANY KIND, INCLUDING WITHOUT LIMITATION, DAMAGES FOR LOSS OF GOODWILL, WORK STOPPAGE, COMPUTER MALFUNCTION, OR ANY OTHER KIND OF COMMERCIAL DAMAGE, EVEN IF WINABILITY SOFTWARE CORPORATION HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. THIS LIMITATION SHALL NOT APPLY TO LIABILITY FOR DEATH OR PERSONAL INJURY TO THE EXTENT PROHIBITED BY APPLICABLE LAW. IN NO EVENT SHALL WINABILITY SOFTWARE CORPORATION'S LIABILITY FOR ACTUAL DAMAGES FOR ANY CAUSE WHATSOEVER, AND REGARDLESS OF THE FORM OF ACTION, EXCEED THE AMOUNT OF THE PURCHASE PRICE PAID FOR THE SOFTWARE LICENSE OR ONE UNITED STATES DOLLAR, WHICHEVER IS THE GREATER.

IF YOU DO NOT AGREE TO FULLY ACCEPT THE RISK OF USING THIS INFORMATION, STOP HERE. If you do agree, let's continue.

Login to the server and check the current version of the MySQL server:

$ mysql -V
mysql  Ver 15.1 Distrib 5.5.64-MariaDB, for Linux (x86_64) using readline 5.1

This tells us that the server indeed is running a pretty old version 5.5 of MariaDB. The current version as of this writing is 10.4, so let's upgrade it. The example below if for a server having the Centos 7 flavor of Linux. If your distribution is different you may need to make adjustments to the commands below as appropriate.

Next step is to do a backup of your databases and/or of the complete server. It's quite possible that something will go wrong during the update. The simplest way to solve such problems would be to restore the complete system from the backup and start over. If you don't know how to do the backup, stop here and learn that first. Got the backup? Tested it? If yes, let's continue.

Check that the system is up to date:

$ sudo yum update
No packages marked for update

Now, a lot of other online tutorials you may find elsewhere suggest that in order to install a new version of MariaDB, you should uninstall the old version first. This is certainly a valid approach. The problem with it, however, is that when you uninstall MySQL or MariaDB, all other software packages that depend on it would be removed as well. It should not be a big inconvenience if you are setting up a fresh server: you probably don't have a great number of packages dependent on MySQL, and it would not be too difficult to install and configure them again after installing the new version of MySQL.

If, however, you are upgrading MySQL on an established server, you probably want to minimize the changes to the configuration of other software packages. To do that, we can try to upgrade the MySQL/MariaDB package without uninstalling the old version first. That's the approach we take below.

Next step would be to add the MariaDB repository. The MariaDB web site has a helpful web page that can generate the repository file appropriate for your Linux distribution. To create the file, we use the nano editor:

$ sudo nano /etc/yum.repos.d/MariaDB.repo

Paste the content of the repository file suggested by the MariaDB web site into nano:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
enabled=0
gpgcheck=1

Press Ctrl+X to close nano, press Y to confirm it's OK to save to file, and now check for updates again. This time it should inform us that a new version of MariaDB available:

$ sudo yum update
mariadb                                                  | 2.9 kB     00:00
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-client.x86_64 0:10.4.12-1.el7.centos will be obsoleting
--> Processing Dependency: MariaDB-common for package: MariaDB-client-10.4.12-1.el7.centos.x86_64
---> Package MariaDB-compat.x86_64 0:10.4.12-1.el7.centos will be obsoleting
---> Package MariaDB-server.x86_64 0:10.4.12-1.el7.centos will be obsoleting
--> Processing Dependency: galera-4 for package: MariaDB-server-10.4.12-1.el7.centos.x86_64
---> Package mariadb.x86_64 1:5.5.64-1.el7 will be obsoleted
---> Package mariadb-libs.x86_64 1:5.5.64-1.el7 will be obsoleted
---> Package mariadb-server.x86_64 1:5.5.64-1.el7 will be obsoleted
--> Running transaction check
---> Package MariaDB-common.x86_64 0:10.4.12-1.el7.centos will be installed
---> Package galera-4.x86_64 0:26.4.3-1.rhel7.el7.centos will be installed
--> Processing Dependency: libboost_program_options.so.1.53.0()(64bit) for package: galera-4-26.4.3-1.rhel7.el7.centos.x86_64
--> Running transaction check
---> Package boost-program-options.x86_64 0:1.53.0-27.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package                 Arch     Version                       Repository
                                                                           Size
================================================================================
Installing:
 MariaDB-client          x86_64   10.4.12-1.el7.centos          mariadb    12 M
     replacing  mariadb.x86_64 1:5.5.64-1.el7
 MariaDB-compat          x86_64   10.4.12-1.el7.centos          mariadb   2.8 M
     replacing  mariadb-libs.x86_64 1:5.5.64-1.el7
 MariaDB-server          x86_64   10.4.12-1.el7.centos          mariadb    26 M
     replacing  mariadb-server.x86_64 1:5.5.64-1.el7
Installing for dependencies:
 MariaDB-common          x86_64   10.4.12-1.el7.centos          mariadb    81 k
 boost-program-options   x86_64   1.53.0-27.el7                 base      156 k
 galera-4                x86_64   26.4.3-1.rhel7.el7.centos     mariadb   9.3 M

Transaction Summary
================================================================================
Install  3 Packages (+3 Dependent packages)

Total download size: 50 M
Is this ok [y/d/N]:

We can see in the table above that 3 packages with the new version of MariaDB will replace the appropriate packages with the old version. We reply Y to allow the download to proceed and enter Y once more to allow it to download the package signing key:

Downloading packages:
warning: /var/cache/yum/x86_64/7/mariadb/packages/MariaDB-common-10.4.12-1.el7.centos.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 1bb943db: NOKEY
Public key for MariaDB-common-10.4.12-1.el7.centos.x86_64.rpm is not installed
(1/6): MariaDB-common-10.4.12-1.el7.centos.x86_64.rpm      |  81 kB   00:00
(2/6): MariaDB-compat-10.4.12-1.el7.centos.x86_64.rpm      | 2.8 MB   00:00
(3/6): boost-program-options-1.53.0-27.el7.x86_64.rpm      | 156 kB   00:00
(4/6): MariaDB-client-10.4.12-1.el7.centos.x86_64.rpm      |  12 MB   00:01
(5/6): galera-4-26.4.3-1.rhel7.el7.centos.x86_64.rpm       | 9.3 MB   00:00
(6/6): MariaDB-server-10.4.12-1.el7.centos.x86_64.rpm      |  26 MB   00:00
--------------------------------------------------------------------------------
Total                                               29 MB/s |  50 MB  00:01
Retrieving key from https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
Importing GPG key 0x1BB943DB:
 Userid     : "MariaDB Package Signing Key <package-signing-key@mariadb.org>"
 Fingerprint: 1993 69e5 404b d5fc 7d2f e43b cbcb 082a 1bb9 43db
 From       : https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : MariaDB-compat-10.4.12-1.el7.centos.x86_64                   1/9
  Installing : MariaDB-common-10.4.12-1.el7.centos.x86_64                   2/9
warning: /etc/my.cnf created as /etc/my.cnf.rpmnew
  Installing : MariaDB-client-10.4.12-1.el7.centos.x86_64                   3/9
  Installing : boost-program-options-1.53.0-27.el7.x86_64                   4/9
  Installing : galera-4-26.4.3-1.rhel7.el7.centos.x86_64                    5/9
  Installing : MariaDB-server-10.4.12-1.el7.centos.x86_64                   6/9
  Erasing    : 1:mariadb-server-5.5.64-1.el7.x86_64                         7/9
warning: /var/log/mariadb/mariadb.log saved as /var/log/mariadb/mariadb.log.rpmsave
  Erasing    : 1:mariadb-5.5.64-1.el7.x86_64                                8/9
  Erasing    : 1:mariadb-libs-5.5.64-1.el7.x86_64                           9/9
  Verifying  : MariaDB-server-10.4.12-1.el7.centos.x86_64                   1/9
  Verifying  : MariaDB-client-10.4.12-1.el7.centos.x86_64                   2/9
  Verifying  : galera-4-26.4.3-1.rhel7.el7.centos.x86_64                    3/9
  Verifying  : MariaDB-common-10.4.12-1.el7.centos.x86_64                   4/9
  Verifying  : boost-program-options-1.53.0-27.el7.x86_64                   5/9
  Verifying  : MariaDB-compat-10.4.12-1.el7.centos.x86_64                   6/9
  Verifying  : 1:mariadb-libs-5.5.64-1.el7.x86_64                           7/9
  Verifying  : 1:mariadb-5.5.64-1.el7.x86_64                                8/9
  Verifying  : 1:mariadb-server-5.5.64-1.el7.x86_64                         9/9

Installed:
  MariaDB-client.x86_64 0:10.4.12-1.el7.centos
  MariaDB-compat.x86_64 0:10.4.12-1.el7.centos
  MariaDB-server.x86_64 0:10.4.12-1.el7.centos

Dependency Installed:
  MariaDB-common.x86_64 0:10.4.12-1.el7.centos
  boost-program-options.x86_64 0:1.53.0-27.el7
  galera-4.x86_64 0:26.4.3-1.rhel7.el7.centos

Replaced:
  mariadb.x86_64 1:5.5.64-1.el7           mariadb-libs.x86_64 1:5.5.64-1.el7
  mariadb-server.x86_64 1:5.5.64-1.el7

Complete!
$

We are almost done. Let's restart the MySQL server and check the version again:

$ sudo systemctl restart mariadb
$ sudo mysql -V
mysql  Ver 15.1 Distrib 10.4.12-MariaDB, for Linux (x86_64) using readline 5.1

It looks like the new version is running! The last step is to let the new version upgrade the existing databases:

$ sudo mysql_upgrade
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.tables_priv                                  OK
mysql.time_zone                                    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
Upgrading from a version before MariaDB-10.1
Phase 2/7: Installing used storage engines
Checking for tables with unknown storage engine
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
admin_default
information_schema
performance_schema
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

That completes the upgrade to MariaDB version 10.4. Of course, if you have any software packages dependent on MySQL, you should test them to make sure everything works well with the updated version. Also, don't forget to ensure that MariaDB is set to start at system boot:

$ sudo systemctl enable mariadb
Created symlink from /etc/systemd/system/mysql.service to /usr/lib/systemd/system/mariadb.service.
Created symlink from /etc/systemd/system/mysqld.service to /usr/lib/systemd/system/mariadb.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

That's all for now, happy MySQL-ing!

Read more

Leave a comment

Your email address will not be published. Required fields are marked *

See below before pressing the SUBMIT button!

IMPORTANT! Do not put any URLs in the comment, or it will be rejected. Before submitting, please solve the following simple math problem (so we know that you are not a spambot) :-)

What is eight minus 7 ?
Please enter the answer as a number, 1 through 9:
Please leave these fields as-is: