MariaDB – 忘記 root 密碼

測試環境 CentOS 8 , MariaDB 10.3.28

忘記 root 密碼了.

[root@localhost ~]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

重新設定 root 密碼方式,參考文章 – https://www.a2hosting.com/kb/developer-corner/mysql/reset-mysql-root-password

停止 mariadb .

[root@localhost ~]# systemctl stop mariadb

使用參數來啟動 mysql.

[root@localhost ~]# mysqld_safe --skip-grant-tables &
[1] 2525
221117 10:12:51 mysqld_safe Logging to '/var/log/mariadb/error.log'.
221117 10:12:51 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql               

–skip-grant-tables
causes the server not to read the grant tables in the mysql system schema, and thus to start without using the privilege system at all. This gives anyone with access to the server unrestricted access to all databases.

這時候就可以直接進到 mysql 更改密碼了.

[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log 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)]> UPDATE mysql.user SET Password=PASSWORD('your_password') WHERE User='root';
Query OK, 4 rows affected (0.001 sec)
Rows matched: 4  Changed: 4  Warnings: 0

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

MariaDB [(none)]> exit;
Bye

透過 mysqladmin 關閉剛剛透過 mysqld_safe 啟動的 mysql.

[root@localhost ~]# mysqladmin -u root -p shutdown
Enter password:
[1]+  Done                    mysqld_safe --skip-grant-tables

重啟服務.

[root@localhost ~]# systemctl start mariadb
Job for mariadb.service failed because the control process exited with error code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.
[root@localhost ~]# systemctl status mariadb
● mariadb.service - MariaDB 10.3 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Wed 2022-11-16 16:41:01 CST; 11s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 38439 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)
  Process: 38406 ExecStart=/usr/libexec/mysqld --basedir=/usr $MYSQLD_OPTS $_WSREP_NEW_CLUSTER (code=exited, status=0/S>
  Process: 38369 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mariadb.service (code=exited, status=0/SUCCESS)
  Process: 39120 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=1/FAILURE)
 Main PID: 38406 (code=exited, status=0/SUCCESS)

Nov 16 16:41:01 localhost.localdomain systemd[1]: Starting MariaDB 10.3 database server...
Nov 16 16:41:01 localhost.localdomain mysql-check-socket[39120]: Socket file /var/lib/mysql/mysql.sock exists.
Nov 16 16:41:01 localhost.localdomain mysql-check-socket[39120]: Is another MySQL daemon already running with the same >
Nov 16 16:41:01 localhost.localdomain mysql-check-socket[39120]: Please, stop the process using the socket /var/lib/mys>
Nov 16 16:41:01 localhost.localdomain systemd[1]: mariadb.service: Control process exited, code=exited status=1
Nov 16 16:41:01 localhost.localdomain systemd[1]: mariadb.service: Failed with result 'exit-code'.
Nov 16 16:41:01 localhost.localdomain systemd[1]: Failed to start MariaDB 10.3 database server.

接下來就可以用剛剛的密碼登入你的 mysql 了.

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log 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)]> exit
Bye