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
- Solved: How to add Group Policy and Local Security Policy to Windows 10 Home
- File too large for USB drive? Here is how to fix it.
- Forget your WI-FI password? Find it in Windows 10 settings
- What is FAT32 maximum file size limit?
- How to create a secret folder with Folder Guard for Windows 10
- How to easily password-protect Windows 10 Linux folders with Folder Guard
- How to reset the root password after upgrading MariaDB
- How to upgrade MySQL or MariaDB properly
- Force DISKPART to delete EFI system partition on Windows 10
- How to make Windows 10 recognize a cloned hard drive again
- How to stop Windows 10 from using thumbnail preview icons for folders
- How to hide pictures from the Photos app in Windows 10
- How to save Windows Spotlight photos to your computer
- How to move the OneDrive folder to an encrypted drive
- Windows 10 fails to upgrade? Here is how to fix it.
- How to stop Microsoft Edge from hijacking PDF files
- Preventing installations of specific programs with Folder Guard
- Folder Guard licensing explained
- Speed up the updates of the network folders
- Make your Windows laptop work as a Wi-Fi access point
- How to stop automatic updates on Windows 10
- Windows cannot connect to the printer. Access is denied.
- Migrating encrypted data from TrueCrypt to USBCrypt
- Enhancing a photo CD collection with PhotoLauncher
- “The Microsoft account service is unavailable right now. Try again later.”
- Using DiffMerge as the external tool of AB Commander to compare plain text files
- How to erase the icon cache and/or thumbnail cache in Windows 10
- Transferring images between your PC and an Android device: Part 2
- Transferring images between your PC and an Android device: Part 1
- Case study: Using SoftDetective to suppress Corel Guide sign-in prompt
- Using junction points to change the iTunes backup folder location
- How to tell if my Windows is 32- or 64-bit?
- How do I stop Windows from rearranging my desktop icons?
- Organize your photo library with the Rename tool of AB Commander
- Windows does not offer the NTFS format option? Here is how to bring it back.
- Encrypt Firefox profile, bookmarks, and cookies with USBCrypt
- Restarting Windows 10 and Windows 8 in the safe mode
- Integrating AB Commander with Universal Viewer
- How to delete a protected EFI system partition with Windows 10, 8, or 7
- Using Folder Guard to protect from the social engineering attacks
- How to erase Windows login password if you forget it
- How to unhide a folder hidden with Folder Guard
- How to repair Windows desktop icons with AB Commander
- Slow network in Windows 7 Virtual PC? Speed it up!
- How to show drive letters first in AB Commander and Windows Explorer
- What is my IP address?
- Why can’t I copy large files over 4GB to my USB flash drive or SD card?
- Test the strength of your password with USBCrypt
- How to set up an external text editor for AB Commander
- How to restrict Internet Explorer from downloading programs from the Internet
- Personal vs business license for USBCrypt
- Use Folder Guard to restrict access to Control Panel
- Compare MySecretFolder and Folder Guard
- Hide folders and make files invisible with Folder Guard
- WINEXIT vs ActiveExit: automatically log off users from Windows 10
- Password protect folders, lock files and folders with passwords
- How to restrict access and lock external drives with Folder Guard
- How to password-protect Dropbox folder with USBCrypt
- How to set up Folder Guard to stop downloading from the Internet
- Is [Wipe the content] the same as [Secure Delete]?
- How to encrypt and protect the system C: drive with USBCrypt
- Make it easier to return your lost encrypted drive
- USBCrypt for users of Microsoft® Office
- How to start programs elevated from a batch file
- How to make elevated programs recognize network drives
- How to disable hibernation with AB Commander
- Using names and labels to organize USBCrypt drives
- How to password-protect a USB flash drive
- Always have a backup of your important files