MariaDB root password 모를 때 변경 방법

MariaDB를 설치하고 root password를 설정했는데 오랫만에 접속을 하니 passwor가 생각이 나지 않았다.

 

이럴땐 os에서 mysql 데몬을 정지/구동할 수 있는 os 계정이 있으면 password 변경이 가능하다.

 

 

1. DB 중지

mariadb를 먼저 중지한다.

[root@server1 ~]# systemctl status mysql
● mariadb.service - MariaDB 10.2.24 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: active (running) since 금 2023-11-10 17:11:36 KST; 3h 1min ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 11030 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 10946 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 10944 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 10997 (mysqld)
   Status: "Taking your SQL requests now..."
   CGroup: /system.slice/mariadb.service
           └─10997 /usr/sbin/mysqld

[root@server1 ~]# systemctl stop mysql
[root@server1 ~]#
[root@server1 ~]# systemctl status mysql
● mariadb.service - MariaDB 10.2.24 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: inactive (dead) since 금 2023-11-10 20:13:20 KST; 36s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 11030 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 10997 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 10946 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 10944 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 10997 (code=exited, status=0/SUCCESS)
   Status: "MariaDB server is down"

 

 

2. skip-grant-tables 옵션과 함께 수동으로 mariadb 데몬을 올린다. 

해당 옵션은 grant table을 조회하지 않고 DB를 올리기 때문에 root 계정의 password를 몰라도 로그인 할 수 있다.

[root@server1 ~]# ps -ef | grep mysql
root     13164  9036  0 20:14 pts/1    00:00:00 grep --color=auto mysql
[root@server1 ~]# mysqld_safe --skip-grant-tables &
[1] 13165
[root@server1 ~]# 231110 20:14:53 mysqld_safe Logging to '/var/lib/mysql/server1.err'.
231110 20:14:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@server1 ~]# ps -ef | grep mysql
root     13165  9036  0 20:14 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql    13269 13165  2 20:14 pts/1    00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/lib/mysql/server1.err --pid-file=server1.pid
root     13302  9036  0 20:14 pts/1    00:00:00 grep --color=auto mysql
[root@server1 ~]#

 

 

3. root 계정으로 로그인 하여 DB 버전을 확인하고 계정 정보를 확인한다.

[root@server1 ~]# mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.24-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)]> select @@version ;
+---------------------+
| @@version           |
+---------------------+
| 10.2.24-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> select user, host, password, authentication_string from mysql.user ;
+-------+---------------+-------------------------------------------+-----------------------+
| user  | host          | password                                  | authentication_string |
+-------+---------------+-------------------------------------------+-----------------------+
| root  | localhost     | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |                       |
| root  | %             | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |                       |
| root  | 127.0.0.1     | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |                       |
| root  | ::1           | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |                       |
| test  | %             | *676243218923905CF94CB52A3C9D3EB30CE8E20D |                       |
+-------+---------------+-------------------------------------------+-----------------------+

 

4. mysql.user Table에 있는 password 컬럼을 update를 이용하여 password를 수정한다.

설치 버전은 10.2.24이며 10.2.10 버전부터 mysql.user 테이블에 update 방식으로 변경할 수 있으며 그 이하 버전은 set password 명령으로 변경가능하다.

 

MariaDB [(none)]> UPDATE mysql.user SET authentication_string = PASSWORD('rootroot') where user = 'root' and host = 'localhost' ;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

MariaDB [(none)]> select user, host, password, authentication_string from mysql.user ;
+-------+---------------+-------------------------------------------+-----------------------+
| user  | host          | password                                  | authentication_string |
+-------+---------------+-------------------------------------------+-----------------------+
| root  | localhost     | *6C362347EBEAA7DF44F6D34884615A35095E80EB |                       |
| root  | %             | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |                       |
| root  | 127.0.0.1     | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |                       |
| root  | ::1           | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |                       |
| test  | %             | *676243218923905CF94CB52A3C9D3EB30CE8E20D |                       |
+-------+---------------+-------------------------------------------+-----------------------+

 

10.2.10 이하버전 password 변경

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');
FLUSH PRIVILEGES;

 

5. mysqld_safe로 올렸던 db 데몬을 내리고 정상적으로 DB를 올려서 root로 DB 접속을 한다.

[root@server1 mysql]# mysqladmin -uroot -p shutdown
Enter password:
[1]+  Done                    mysqld_safe --skip-grant-tables
[root@server1 mysql]#
[root@server1 mysql]# sudo systemctl status mysql
● mariadb.service - MariaDB 10.2.24 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: inactive (dead) since 금 2023-11-10 17:08:11 KST; 3min 18s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 10727 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 10693 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 10642 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 10640 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 10693 (code=exited, status=0/SUCCESS)
   Status: "MariaDB server is down"

11월 10 17:08:11 server1 systemd[1]: Stopped MariaDB 10.2.24 database server.
[root@server1 mysql]# sudo systemctl start mysql
[root@server1 mysql]#
[root@server1 mysql]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.24-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)]>