Featured MariaDB How to reset MySQL root user password for CentOS 7 / MariaDB 10.1.19+

How To Reset MySQL Root Password For MariaDB 10.1.19+

Centmin Mod 123.09beta01's centmin.sh menu option 11 submenu option 0 allows resetting MySQL root user password when the known existing MySQL root password is contained in /root/.my.cnf. But when you do not have a valid working MySQL root password in /root/.my.cnf and/or you don't know the existing MySQL root password, you will need to reset it via official documented methods outlined here. However, for MariaDB 10.1.19+ seems the official method isn't working !

So I figured out how to reset mysql root user password for MariaDB 10.1.19+ server. It seems not all the official mysql documentations have the correct method or it could be a MariaDB specific bug similar to these 2 issues MDEV-7656 and MDEV-9835 and/or specific to CentOS 7 and systemd method/protections.

The method used to reset mysql root user password in MariaDB 10.1.19+ that works is as follows on CentOS 7:

Stop MariaDB MySQL server

Code (Text):

systemctl stop mariadb

Start manually with skip grants tables and skip networking in background

Code (Text):

mysqld_safe --skip-grant-tables --skip-networking &

log into mysql client as root user and you will see the official documentated methods resulting in ERROR 1290 and ERROR 1131 and checking with select to see existing user = root passwords hashed

Code (Text):

mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.19-MariaDB MariaDB Server

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

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

MariaDB [(none)]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Code (Text):

MariaDB [(none)]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
ERROR 1131 (42000): You are using MariaDB as an anonymous user and anonymous users are not allowed to modify user settings

Code (Text):

MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user='root';
+------+---------------------+-------------------------------------------+--------+-----------------------+
| user | host                | password                                  | plugin | authentication_string |
+------+---------------------+-------------------------------------------+--------+-----------------------+
| root | localhost           | *A59082D43302731DFF45380446E4650F5B165CDE |        |                       |
| root | centos7.localdomain | *A59082D43302731DFF45380446E4650F5B165CDE |        |                       |
| root | 127.0.0.1           | *A59082D43302731DFF45380446E4650F5B165CDE |        |                       |
| root | ::1                 | *A59082D43302731DFF45380446E4650F5B165CDE |        |                       |
+------+---------------------+-------------------------------------------+--------+-----------------------+
4 rows in set (0.00 sec)

Only working method is these additional steps still within same mysql client interactive session

Code (Text):

MariaDB [(none)]> update mysql.user set password=password('MyNewPass') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

check via select where user = root shows new hashed password listed

Code (Text):

MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user='root';
+------+---------------------+-------------------------------------------+--------+-----------------------+
| user | host                | password                                  | plugin | authentication_string |
+------+---------------------+-------------------------------------------+--------+-----------------------+
| root | localhost           | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |        |                       |
| root | centos7.localdomain | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |        |                       |
| root | 127.0.0.1           | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |        |                       |
| root | ::1                 | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |        |                       |
+------+---------------------+-------------------------------------------+--------+-----------------------+
4 rows in set (0.00 sec)

Try again

Code (Text):

MariaDB [(none)]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');                          
ERROR 1131 (42000): You are using MariaDB as an anonymous user and anonymous users are not allowed to modify user settings

but you still get ERROR 1131. Why ? because it seems you need to quit the existing mysql client session and start a new mysql client session but using the new mysql root password and then use the official mysql documented commands via SET PASSWORD FOR and then flush privileges

Code (Text):

MariaDB [(none)]> quit
Bye

Code (Text):

mysql -u root -pMyNewPass
Your MariaDB connection id is 3
Server version: 10.1.19-MariaDB MariaDB Server

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

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

MariaDB [(none)]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Code (Text):

MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user='root';
+------+---------------------+-------------------------------------------+--------+-----------------------+
| user | host                | password                                  | plugin | authentication_string |
+------+---------------------+-------------------------------------------+--------+-----------------------+
| root | localhost           | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |        |                       |
| root | centos7.localdomain | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |        |                       |
| root | 127.0.0.1           | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |        |                       |
| root | ::1                 | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |        |                       |
+------+---------------------+-------------------------------------------+--------+-----------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> quit
Bye

Then after you quit, manually kill the manually started mysql server process ids for mysqld_safe and mysqld which in this case from ps aufx command are process ids = 2747 and 3105

Code (Text):

ps aufx | grep mysql | grep -v grep
root      2747  0.0  0.0 113264  1680 pts/0    S    17:48   0:00          \_ /bin/sh /bin/mysqld_safe --skip-grant-tables --skip-networking
mysql     3105  0.3  5.0 515644 95948 pts/0    Sl   17:48   0:00          |   \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --skip-networking --log-error=/var/log/mysqld.log --open-files-limit=8192 --pid-file=centos7.localdomain.pid --socket=/var/lib/mysql/mysql.sock

kill -9 2747 3105

then start MariaDB MySQL server

Code (Text):

systemctl start mariadb

Then edit /root/.my.cnf with new mysql root password wrapped in single quotes - if you have problems some passwords require removing the single quotes

Code (Text):

cat /root/.my.cnf
[client]
user=root
password='MyNewPass'

Then check it all works... done !

Code (Text):

mysqladmin ver
mysqladmin  Ver 9.1 Distrib 10.1.19-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Server version          10.1.19-MariaDB
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 32 sec

Threads: 1  Questions: 1  Slow queries: 0  Opens: 18  Flush tables: 1  Open tables: 11  Queries per second avg: 0.031