Tag Archives: centos

How to reset the root password after upgrading MariaDB

If you've upgraded to MariaDB version 10.4, you may be surprised to learn that it no longer uses the root user the same way it used before. For most situations it should not be a problem, because most software packages should not use the root user anyway. Except that you may have other database management software that uses the root user to manage the MySQL databases, and they would no longer work. For example, if you use Webmin to manage your server that worked well with the old version of MySQL, you may find that it cannot login to MySQL server after the upgrade:

Webmin needs to know your MySQL administration login and password in order to manage your database. Please enter your administration username (usually root) and password below.

The problem is, the latest version of MariaDB changed the handling of the root user quite significantly. There is a way to make it work to keep other software like Webmin happy. We just need to reset the password for the root user. You may find quite a few online tutorials that suggest using the command mysqld_safe --skip-grant-tables to reset the root password, but when you try it, you may be surprised to learn that it no longer works with MariaDB 10.4 (again, because they had changed the handling of the root user.)

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 ACCSEPT THE RISK OF USING THIS INFORMATION, STOP HERE. If you do agree, let's continue.

To reset the root user password for MariaDB 10.4, try sudo mysql instead:

$ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 40
Server version: 10.4.12-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Now enter the following command at the MariaDB command prompt (replace secret with the actual password of the root user that you want to set):

MariaDB [(none)]> ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD("secret");

Now try Webmin to log in to the MySQL server again, using the same root password. Happy MySQL-ing!

Read more

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