Join the community today
Become a Member

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

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by eva2000, Dec 20, 2016.

Thread Status:
Not open for further replies.
  1. eva2000

    eva2000 Administrator Staff Member

    29,035
    6,589
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,784
    Local Time:
    8:46 AM
    Nginx 1.13.x
    MariaDB 5.5

    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
    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
     
    Last edited: Apr 23, 2017
    • Informative Informative x 4
Thread Status:
Not open for further replies.