공식 문서 참고 : https://mariadb.com/kb/en/authentication-from-mariadb-104/
하여 작성을 하였으며, MariaDB 10.4 이상부터는 작업을 보다 쉽고 직관적으로 만들기 위해 인증 프로세스에 여러 변경 사항을 도입했다고 합니다. 그중 가장 이슈가 되는 부분은 바로 ROOT 패스워드 입력 없이 Unix_socket 방식을 이용하여 바로 DB에 접속하는 방식을 말합니다. 먼저 이것을 설명하기 위해 바로 테스트를 진행해보도록 하겠습니다.

[테스트 환경]
□ OS : Rocky Linux release 8.5 (Green Obsidian)
□ DB : 10.3.28-MariaDB
 DB : 10.4.24-MariaDB [10.4.X 이상]

 DB : 10.6.7-MariaDB


◇ 먼저 설치 직 후에는 패스워드가 설정되어 있지 않기 때문에 플러그인 상관없이 바로 접근이 됩니다.

◇ 보안 및 패스워드 설정을 위해 [# mysql(mariadb)-secure-installation] 실행의 차이점

이후 아래와 같이 각 버전에 루트 인증을 시도해보면 다음과 같은 결과가 나옵니다.

 

■ MariaDB 10.3 버전에서 루트(Root)로그인 인증

[root@Rocky85 ~]# mysql -u root -p
Enter password: [패스워드 없이 엔터만 누를 경우]

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [인증 불가]

■ MariaDB 10.4 이상 버전에서 루트(Root)로그인 인증[Unix_socket 방식]

[root@Rocky85 ~]# mysql -u root -p

Enter password: [패스워드 없이 엔터만 누를 경우]

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.4.24-MariaDB MariaDB Server [패스워드 인증 없이 바로 DB 접속]

 

◇ 아래는 로그인 인증 플러그인 비교

여기서 위 결과값을 비교를 해보았을 때 이상한 부분이 보이지 않나요? 네.. 바로 10.4.X 버전의 Plugin 부분을 보면 [mysql_native_password]으로 패스워드 인증 방식으로 되어 있지만 막상 [# mysql -u root -p]해보면 패스워드 없이 엔터만 눌러도 바로 DB로 접속되는 것을 확인할 수 있습니다.

 

문서를 자세히 보면 왜 이렇게 되는지 이유를 알 수 있는데요.

※(중요) 모든 사용자 계정, 암호, 인증방식 권한은 이제 [ mysql.global_priv ] 테이블에 저장됩니다.

[ mysql.user ]테이블은 여전히 존재하며, 이전과 정확히 동일한 컬럼 집합을 가지고 있지만 이제는

[ mysql.global_priv ]테이블을 참조하는 뷰(View)를 하는 역할로 바뀌게 됩니다.

[설명]

원래 이 기술은 Otto Kekäläinen개발자 분께서 Debian MariaDB 패키지에서 개척했으며 이미 MariaDB 10.0부터 리눅스 데비안[Debian]에서 사용되어 왔다고 합니다. CentOS/RedHat 배포판에서는 10.4 버전부터 적용을 하기 시작했습니다. [ Unix_socket ]을 사용하는 것은 시스템 ROOT 사용자인 경우 암호 없이 root@localhost로 로그인할 수 있음을 의미합니다. 그럼 왜 이렇게 적용을 했을까요?

 

① 콘솔에서 DB에 접속하는 것은 시스템 ROOT 권한이 있는 사용자가 내부(Localhost)에만 접속을 하므로 패스워드에는 의미가 없다는 뜻

② 패스워드가 없다는 뜻은 MariaDB ROOT 암호 재설정에 대한 설명서가 필요하지 않음, 즉 잊어버릴 염가 없습니다.

③ 암호가 없으니 일반 텍스트로 암호를 저장할 필요가 없어 노출될 일이 없다.

④ 접속하기 편하다. [ # sudo mysql ] 명령어를 통해 바로 접속 할 수 있다.

 

가 개발자의 뜻이지만.. 과연 보안담당자들은 어떻게 볼까요. 우리가 데이터베이스에 접근을 할 때 서버가 내부 환경이면 문제가 없겠지만 웹이나 DB서버가 분리가 되어 있을 경우는 어쩔 수 없이 외부 아이피에서 접근을 해야 하는 경우가 있습니다. 보안에 취약하여 서버의 ROOT가 탈취를 당하면 데이터베이스도 자유롭게 접근이 가능하므로 이것 자체를 취약점로 봅니다.


■ 이전 패스워드 인증 밥벙으로 설정하기

이미 MariaDB를 설치 하고 root@localhost 사용자 계정이 이미 unix_socket 인증을 사용하는 경우

MariaDB [mysql]> ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD("패스워드");

※ 또하나의 방법은 [ mysql_install_db (mariadb-install-db) ]를 이용한 방법입니다. 하지만 MariaDB 설치 직후 바로 실행하면 자동으로 계정을 생성하기 때문에 제외하였습니다.

문서 자료 : https://mariadb.com/kb/en/mysql_install_db/


■ 다시 [ Unix_socket ] 인증 방식으로 설정하기

MariaDB [mysql]> ALTER USER root@localhost IDENTIFIED VIA unix_socket;


■ 원격 서버[클라이언트 프로그램]에서 MariaDB접속 시 반응

① 원격 호스트 IP를 추가하고 Unix_socket 설정했을 때 암호 없이 접근이 가능한지 테스트

MariaDB [mysql]> grant all privileges on *.* to 'root'@'원격지IP' identified by '패스워드';

MariaDB [mysql]> ALTER USER root@원격지IP IDENTIFIED VIA unix_socket;

 

② 패스워드 인증으로 변경 후 접속 테스트

MariaDB [mysql]> ALTER USER root@192.168.150.1 IDENTIFIED VIA mysql_native_password USING PASSWORD("패스워드");

[결론]

원격은 무조건 패스워드 인증으로만 접근이 가능하다.


■ 실수로 ROOT 권한에 문제가 생기거나 패스워드를 잊어버렸을 경우

◇ ROOT 계정을 삭제했을 경우[예시]

MariaDB [mysql]> drop user root@localhost;

만약 다른 계정 중에 모든 권한이 있는 일반 계정이 있다면 우회 접속하여 다시 생성하면 되겠지만, ROOT계정만으로 관리를 했다면 지금과 같은 곤란한 상황이 발생할 수 있습니다.

복원을 하기 위해서는 MariaDB를 실행을 할 때 [ --skip-grant-tables ] 이용합니다. 리눅스 싱글 부팅하는 것과 비슷한 원리입니다. 다만 우리는 대부분 RPM으로 설치하면 기본적으로 [ systemctl ]을 이용하여 MariaDB를 중지 실행을 하기 때문에 실제 실행 위치를 모를 수 있습니다.

[root@Rocky85 mysql]# systemctl status mariadb  [스테이터스를 이용하여 스크립트 실행 로드 확인]

Loaded : /usr/lib/systemd/system/mariadb.service

[root@Rocky85 mysql]# vi /usr/lib/systemd/system/mariadb.service  [스크립트 파일 열기]

에서 ExecStart 부분을 확인해보면 어떤 경로에서 Mariadb를 실행하는지 알 수 있습니다.

예외상황 발생①

[root@Rocky85 mysql]# /usr/sbin/mysqld --skip-grant-tables & [실행 오류]
2022-03-23 22:39:53 0 [Note] /usr/sbin/mysqld (mysqld 10.4.24-MariaDB) starting as process 1889 ...
/usr/sbin/mysqld: Please consult the Knowledge Base to find out how to run mysqld as root!
2022-03-23 22:39:53 0 [ERROR] Aborting

아.. 이렇게 하면 권한 때문에 실행이 안됩니다. 기본 설치 data경로가 [/var/lib/mysql/] mysql 권한으로 실행되므로 다음과 같이 시스템 권한을 mysql 유저 권한으로 변경합니다. 혹시나 Passwd를 확인하여 mysql 계정이 로그인이 가능한지 확인합니다.

[root@Rocky85 ~]# cat /etc/passwd | grep mysql
mysql:x:27:27:MySQL Server:/var/lib/mysql:/sbin/nologin  [nologin을 /bin/bash로 변경]

 

[root@Rocky85 ~]# vi /etc/passwd

[root@Rocky85 ~]# su - mysql  [시스템 권한 유저(mysql)로 로그인]
[mysql@Rocky85 ~]$ /usr/sbin/mysqld --skip-grant-tables &  [실행 오류]

예외상황 발생②

2022-03-24  0:13:28 0 [ERROR] InnoDB: Missing MLOG_CHECKPOINT at 61021 between the checkpoint 61021 and the end 61030.
2022-03-24  0:13:28 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error

테스트한다고 강제로 죽였다가 살렸다가 반복했더니 ib_logfile 데이터가 충돌 나는 문제가 발생했습니다.

그렇게 심각한 문제는 아니므로 다음과 같이 조치를 합니다.
데이터 파일이 있는 경로에 ib_logfile을 이름을 변경(권고), 또는 삭제를 합니다.

[mysql@Rocky85 ~]$ cd /var/lib/mysql/

[mysql@Rocky85 ~]$ mv ib_logfile0 ib_logfile0.bak

[mysql@Rocky85 ~]$ mv ib_logfile1 ib_logfile1.bak

 

DB실행

[mysql@Rocky85 ~]$ /usr/sbin/mysqld --skip-grant-tables &  [MariaDB 실행]

아래와 같이 정상적으로 Mysql이 실행되신 것을 확인할 수 있습니다.

※ 세팅 방법에 따라 실행 권한이나 구동 방법이 다를 수 있습니다!

이제 다시 MariaDB 접속을 시도합니다.

[mysql@Rocky85 ~]$ mysql -u root -p

Enter password: [패스워드 미입력(엔터)]

 

MariaDB [(none)]> FLUSH PRIVILEGES; [접속 후 바로 해당 명령어 입력]

MariaDB [(none)]> grant all privileges on *.* to 'root'@'localhost' identified by '패스워드';

이제 종료를 하고 기존에 실행했던 MariaDB를 중지하고 시스템 ROOT권한으로 돌아온 후에 정상적인 방법으로 다시 구동하여 root@localhost 접속이 되는지 확인합니다. 혹시나 DB가 중지가 되지 않으면 강제 종료[ kill ] 한 후에 시도를 하면 됩니다.

※ 10.6.X 버전에서도 동일하게 되는 것을 확인하였습니다.


MariaDB가 아무리 Mysql기반으로 개발이 되었지만, 계속 업데이트하면서 변하는 부분이 생기네요. 덕분에 일주일 동안 많은 시행착오를 겪으면서 좋은 경험을 하게 되었습니다. 계정을 변경하거나 삭제할 경우는 꼭 다른 계정을 생성 또는 기존 다른 유저에게 모든 권한을 부여한 후에 진행하시기 바랍니다.

서버 버전 확인

실행 중인 MySQL 또는 MariaDB 서버 버전에 따라 다른 명령어로 루트 암호를 복구해야 합니다.

다음 명령을 실행하여 서버 버전을 확인합니다.

mysql --version

시스템에 MySQL이 설치되어 있는 경우 출력은 다음과 같습니다.

mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper

또는 MariaDB에 대해 다음과 같이 출력합니다.

mysql  Ver 15.1 Distrib 10.3.32-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

 

MySQL 또는 MariaDB 루트 비밀번호를 재설정하는 방법

MySQL/MariaDB 루트 비밀번호를 재설정하려면 다음 단계를 수행합니다.

 

1. MySQL/MariaDB 서비스 중지

만일 mysql이나 mariaDB가 기동 중에 있을 경우, 루트 암호를 변경하려면 먼저 MySQL 서버를 중지해야 합니다. 

 

sudo systemctl stop mysql

 

2. 그랜트 테이블을 로드하지 않고 MySQL/MariaDB 서버를 시작합니다.

그랜트 테이블을 로드하지 않고 데이터베이스 서버를 시작합니다:

sudo mysqld_safe --skip-grant-tables &

위의 명령 끝에 있는 &(앰퍼샌드)는 프로그램이 백그라운드에서 실행 되도록 하므로 쉘을 계속 사용할 수 있습니다.

--skip-grant-tables옵션을 사용하면 누구나 암호 없이 데이터베이스 서버에 연결할 수 있습니다.

 

3. MySQL 셸에 로그인

이제 루트 사용자로 데이터베이스 서버에 연결할 수 있습니다.

mysql -u root

 

4. 새 루트 비밀번호 설정

MySQL 5.7.5 및 이전 버전 또는 MariaDB 10.1.20 및 이전 버전인 경우

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

 

MySQL 5.7.6 이상 또는 MariaDB 10.1.20 이상인 경우

MariaDB> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW_PASSWORD';
MariaDB> FLUSH PRIVILEGES;

 

ALTER USER문이 수행되지 않고 아래와 같이 오류가 발생하면 UPDATE 문을 수행합니다.

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement

 

오류 발생 시 아래 SQL문을 수행합니다.

MariaDB> UPDATE mysql.user SET authentication_string = PASSWORD('NEW_PASSWORD')
WHERE User = 'root' AND Host = 'localhost';

MariaDB> FLUSH PRIVILEGES;

 

5. 데이터베이스 서버를 정상적으로 중지 및 시작

이제 루트 암호가 설정되었으므로 데이터베이스 서버를 중지하고 정상적으로 시작합니다.

$ mysqladmin -u root -p shutdown

새 루트 암호를 입력하라는 메시지가 표시됩니다. 암호를 입력하면 데이터베이스 서버를 정상적으로 중지합니다.

 

데이터베이스 기동

MySQL의 경우 다음을 입력합니다.

sudo systemctl start mysql

MariaDB의 경우 다음을 입력합니다.

sudo systemctl start mariadb

 

***  참고 ***

WSL 에서  sudo systemctl start mysql, sudo systemctl start mariadb 명령어로 DB 기동 시 아래 오류가 발생하면

 

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

System has not been booted with systemd as init system (PID 1). Can't operate.
Failed to connect to bus: Host is down

 

아래 명령어로 DB를 기동한다.

sudo /etc/init.d/mysql start

 

 

6. 비밀번호 확인

새 루트 암호가 올바르게 적용되었는지 확인하려면 다음을 입력합니다.

sudo mysql -u root -p

새 루트 암호를 입력하라는 메시지가 표시됩니다. 입력하면 데이터베이스 서버에 로그인 합니다.

SELECT TIME_TO_SEC('05:15:40');

 

Sample Output:

mysql> SELECT TIME_TO_SEC('05:15:40');

+-------------------------+

| TIME_TO_SEC('05:15:40') |

+-------------------------+

|                       18940 |

+-------------------------+

1 row in set (0.02 sec)

 

mysql> SELECT SEC_TO_TIME(18940);

+-------------------------+

|  SEC_TO_TIME(18940)  |

+-------------------------+

|                    05:15:40 |

+-------------------------+

1 row in set (0.02 sec)

 

 

 

참고: https://www.w3resource.com/mysql/date-and-time-functions/mysql-time_to_sec-function.php

+ Recent posts