데비안 9(Stretch)부터 MariaDB는 디폴트 MySQL 변종이다. 즉, MariaDB가 기존의 MySQL을 완전히 대체한다. 데비안 8(Jessie)의 MySQL 버전은 5.5이다. 내가 사용하는 Confluence 등이 MySQL 5.5를 지원하지 않았기 때문에 MySQL Community의 MySQL 5.6을 설치해야 했다. 최근에 Confluence, Jira, Bitbucket의 MySQL 데이터베이스를 PostgreSQL로 이전하였다. 워드프레스의 데이터베이스만이 MySQL에 남았다. 워드프레스는 현재 공식적으로 MySQL과 MariaDB만을 지원한다. 정교한 데비안 패키지 관리의 이점을 얻기 위해 MariaDB로 돌아갈 것이다. MariaDB 설치 후 처음에 마주칠 수 있는 unix_socket 인증과 튜닝 등의 설정을 정리해 본다.
MariaDB 설치
MySQL Community의 MySQL 패키지를 설치했다면 완전히 삭제하는 것이 필요하다. 패키지의 의존성 문제로 삭제에 애를 먹을 수 있는데 apt-get remove –purge ‘mysql-.*’ 명령어를 사용하여 mysql 관련 패키지를 모두 제거하는 편법을 사용해야 한다. 패키지를 삭제하기 전에 mysqldump로 데이터베이스를 백업하고 /etc/mysql 디렉토리 안의 my.cnf 등 설정 파일을 백업하는 것이 좋을 것이다.
데비안 9(Stretch) 또는 10(Buster)에서 apt install mariadb-server 명령어로 MariaDB를 설치할 수 있다. 데비안 9는 MariaDB 10.1을 데비안 10은 MariaDB 10.3을 설치할 것이다.
# apt update # apt install mariadb-server
데비안 패키지와 관련한 문서는 /usr/share/doc/<패키지 이름> 디렉토리에 위치한다. 패키지 설치 후 가장 먼저 찾아볼 문서이다. 참고로 README는 실제 제작자(upstream)의 문서이고, README.Debian은 데비안 개발자(패키지 메인테이너)가 제공하는 문서이다. MariaDB 서버와 관련하여 볼 만한 문서는 /usr/share/doc/mariadb-server-10.1 또는 /usr/share/doc/mariadb-server-10.3 디렉토리 안의 README.Debian.gz 파일이다. gzip으로 압축이 되어 있으므로 zmore나 zcat 명령어로 보거나 윈도우 운영체제에서는 7-Zip 등으로 압축을 풀어 편집 프로그램을 사용하여 본다.
# zmore /usr/share/doc/mariadb-server-10.3/README.Debian.gz ... # zcat /usr/share/doc/mariadb-server-10.3/README.Debian.gz > ~/mariadb-server.README.Debian
systemd
MariaDB는 버전 10.1.8부터 systemd unit 파일을 제공한다. 데비안 패키지에서 /lib/systemd/system/mariadb.service 파일이 이에 해당한다. mariadb.service는 편리를 위해 mysql.service, mysqld.service 별칭을 가진다. systemctl 사용시 .service 접미사는 생략할 수 있다. 따라서 다음과 같이 systemctl 명령어를 사용하여 MariaDB 서비스의 시작, 종료 등을 할 수 있다.
# systemctl status mariadb # systemctl status mysql.service # systemclt status mysqld # systemctl stop mysqld.service # systemctl start mysql # systemctl restart mariadb.service
mysql_secure_installation을 실행할 필요가 없음
데비안의 MariaDB 패키지는 보안을 적용한 상태이기 때문에 패키지 설치 후 mysql_secure_installation 명령어를 실행할 필요는 없다. 어떤 보안 설정들을 적용하였는지 살펴보자.
# mysql_secure_installation ... In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): ... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] n ... Remove anonymous users? [Y/n] y ... Disallow root login remotely? [Y/n] y ... Remove test database and access to it? [Y/n] y ... Reload privilege tables now? [Y/n] y ...
위의 내용은 mysql_secure_installation 실행 과정의 일부분을 발췌한 것이다. 라인 8에서 현재 root 계정의 암호를 묻고 있다. 처음 설치 시에는 MariaDB에 대한 root 계정의 암호가 없으므로 <Enter> 키를 누른다. 라인 13에서 root의 암호를 설정할 것인지를 묻는다. unix_socket을 사용하여 root 계정을 인증하기 때문에 n을 입력한다. 이후 나머지 설정에서는 모두 y를 입력한다. 라인 15는 anonymous 사용자를 삭제한다. 라인 17은 root의 원격 로그인을 허용하지 않는다. 라인 19는 test 데이터베이스를 삭제한다. 라인 21은 보안 강화를 위해 변경한 내용을 바로 적용한다.
데비안에서 MariaDB 패키지를 설치하면 이미 위와 같이 설정한 상태이다. 데비안에서 라인 13처럼 MariaDB의 root 계정에 대한 암호를 설정하지 않는 데 다음에서 이에 대해 알아볼 것이다.
Unix Socket 인증
데비안은 MariaDB에서 unix_socket 인증 플러그인을 기본적으로 사용한다. unix_socket 인증 플러그인이 나오기 전에 MySQL이나 MariaDB는 데이터베이스를 보호하기 위해 자체적인 인증을 사용하였다. 그래서 시스템에 모든 권한이 있는 root 계정이라도 MySQL 내에 암호를 별도로 가져야 했다. mysql 클라이언트 사용 시 root 계정임에도 불필요해 보이는 -u, -p 옵션을 사용해야만 했다. 데이터베이스의 백업 스크립트에서 암호를 노출해야 하는 문제도 있었다.
unix_socket 인증 플러그인은 시스템의 인증과 MariaDB의 인증을 함께 묶어 준다. 로컬 시스템으로 접속하여 mysql 클라이언트 실행 시 -u, -p 옵션을 사용하지 않을 수 있게 한다. 예제를 통해 좀 더 알아보자. 규모가 큰 조직에서 미숙한 신입 DB 관리자가 들어왔다고 가정해 보자. 시스템 전체의 권한을 주지 않으면서 데이터베이스의 관리 권한만 주고 싶다면 다음과 같이 한다.
# groupadd dbadmin # useradd dbadmin -g dbadmin -m # passwd dbadmin 새 암호: 새 암호 재입력: passwd: 암호를 성공적으로 업데이트했습니다 # mysql -e "GRANT ALL ON *.* TO 'dbadmin'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION"
라인 1에서 3까지 시스템에 dbadmin 계정을 만들고 암호를 설정한다. 라인 7에서 unix_socket 인증을 사용하는 dbadmin 계정에 데이터베이스의 모든 권한을 주는 SQL 문을 실행한다. 다른 ssh 세션에서 dbadmin으로 로그인하여 mysql 클라이언트를 실행해 보자.
$ mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 59 Server version: 10.3.22-MariaDB-0+deb10u1 Debian 10 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)]>
라인 1에서 보듯이 -u, -p 옵션을 사용하지 않았다. 로컬 시스템의 dbadmin 계정과 암호로 로그인하면 unix_socket 인증에 의해 mysql 클라이언트를 사용할 수 있는 것이다.
신입 DB 관리자가 자신의 컴퓨터에서 DBeaver, DataGrip 같은 그래픽 도구로 MariaDB 서버에 접속하여 데이터베이스를 관리하고 싶다고 해보자. 그리고 집에서도 접속하고 싶어 한다. 이럴 때는 unix_socket이 아니라 password 인증을 사용해야 한다. 다음과 같이 작업한다.
MariaDB [(none)]> USE mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mysql]> GRANT ALL ON *.* TO 'dbadmin'@'192.168.10.%' IDENTIFIED BY '원하는 암호 입력' WITH GRANT OPTION; Query OK, 0 rows affected (0.000 sec) MariaDB [mysql]> GRANT ALL ON *.* TO 'dbadmin'@'%' IDENTIFIED BY '원하는 암호 입력' WITH GRANT OPTION; Query OK, 0 rows affected (0.000 sec) MariaDB [mysql]> SELECT user, host, password, plugin FROM user WHERE user='dbadmin'; +---------+--------------+-------------------------------------------+-------------+ | user | host | password | plugin | +---------+--------------+-------------------------------------------+-------------+ | dbadmin | localhost | | unix_socket | | dbadmin | 192.168.10.% | *CB04FE5BBC0DAAE405DB6DD0745827BCF1CF624D | | | dbadmin | % | *CB04FE5BBC0DAAE405DB6DD0745827BCF1CF624D | | +---------+--------------+-------------------------------------------+-------------+ 3 rows in set (0.000 sec) MariaDB [mysql]> DROP USER 'dbadmin'@'%'; Query OK, 0 rows affected (0.000 sec) MariaDB [mysql]>
라인 1에서 라인 12의 SELECT 문을 사용하기 쉽게 mysql 데이터베이스로 이동한다. 라인 6에서 암호 인증을 사용하는 dbadmin 계정에 데이터베이스의 모든 권한을 주었다. ‘dbadmin’@’192.168.10.%’는 dbadmin이 192.168.10의 네트워크에 있는 컴퓨터에서 접속할 수 있음을 의미한다. % 문자는 ‘모든’을 의미한다. 라인 9에서 ‘dbadmin’@’%’는 네트워크 제한을 두지 않는다. dbadmin 계정은 어떤 IP에서든 접속할 수 있다. 당연히 보안에 좋지 않다. 라인 12에서 SELECT 문을 사용하여 dbadmin 사용자의 인증 방법과 접속할 수 있는 네트워크 대역을 확인한다. 라인 22에서 DROP USER 문을 사용하여 ‘dbadmin’@’%’ 사용자를 삭제한다.
원격으로 접속하기 위해서는 추가적인 설정이 필요하다. 이에 대해서는 아래의 ‘원격 접속을 허용하기’에서 다룬다.
워드프레스의 데이터베이스 만들기
응용 프로그램을 위한 데이터베이스 생성은 기존의 방식과 동일하게 암호 인증을 사용한다. 워드프레스를 위한 데이터베이스와 사용자는 다음과 같이 만들 수 있다.
MariaDB [mysql]> CREATE DATABASE wordpress CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Query OK, 1 row affected (0.00 sec) MariaDB [mysql]> GRANT ALL ON wordpress.* TO 'wordpress'@'localhost' IDENTIFIED BY '원하는 암호 입력' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) MariaDB [mysql]>
MariaDB 설정 파일의 위치
/etc/mysql/mariadb.cnf 파일을 보면 MariaDB 설정 파일은 다음과 같다.
- /etc/mysql/mariadb.cnf
- /etc/mysql/conf.d/*.cnf
- /etc/mysql/mariadb.conf.d/*.cnf
- ~/.my.cnf
위의 순서로 설정 파일을 읽기 때문에 같은 옵션이 여러 번 나타난다면 가장 마지막에 있는 설정을 적용할 것이다. MariaDB 서버와 관련한 설정은 /etc/mysql/mariadb.conf.d 디렉토리 안의 50-server.cnf 파일에서 한다.
원격 접속을 허용하기
데비안의 MariaDB는 보안을 위해 3306 포트를 루프백(loop-back) 주소인 127.0.0.1로 바인딩한다. 다음의 명령어로 확인할 수 있다.
# netstat -tlnp | grep mysql tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 12435/mysqld
따라서 시스템 외부에서 접근할 수 없다. 시스템 외부에서 접근할 수 있도록 하려면 50-server.cnf 파일에서 bind-address를 다음과 같이 변경한다.
bind-address = 0.0.0.0
변경한 설정을 반영하기 위해 MariaDB 서버를 다시 시작한다.
# systemctl restart mariadb
공유기(방화벽 역할)의 외부에서 오는 접근을 허용하려면 3306 포트에 대한 포트 포워딩을 해야 한다. 보안에 좋지 않기 때문에 데이터베이스 서버의 포트를 여는 경우는 거의 없을 것이다.
언어 설정
데비안의 MariaDB에서 디폴트 문자 집합(character set)은 utf8mb4이다. 디폴트 데이터 정렬(collation)은 utf8mb4_general_ci이다. 데이터 정렬을 utf8mb4_unicode_ci로 바꾸고 싶다면 50-server.cnf 파일을 다음과 같이 편집한다.
collation-server = utf8mb4_unicode_ci skip-character-set-client-handshake
라인 1의 collation-server의 값을 utf8mb4_general_ci에서 utf8mb4_unicode_ci로 변경한다. 라인 2의 skip-character-set-client-handshake를 추가한다.
MariaDB 서버를 다시 시작하고 MySQL 클라이언트를 다시 실행한 후 다음의 SQL 문으로 변경을 확인한다.
MariaDB [(none)]> SHOW VARIABLES LIKE 'coll%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8mb4_unicode_ci | | collation_database | utf8mb4_unicode_ci | | collation_server | utf8mb4_unicode_ci | +----------------------+--------------------+ 3 rows in set (0.001 sec) MariaDB [(none)]>
MariaDB 서버의 데이터 정렬 설정은 MySQL 클라이언트 외에 다른 응용 프로그램의 데이터 정렬을 강제하지는 않는다. 예를 들면 Adminer는 collation_connection을 utf8mb4_general_ci로 나타낸다. 워드프레스의 일부 플러그인은 utf8mb4_general_ci를 가지는 테이블을 만든다.
튜닝
데이터베이스의 데이터는 ‘하드디스크 > 메모리 > CPU의 레지스터’ 순으로 옮겨간다. 오른쪽으로 갈수록 속도가 빠른 대신 저장 비용은 비싸진다. 데이터베이스 읽기의 최적화는 디스크 입출력(I/O)을 최소화하고 메모리를 최대한 사용하는 것이다. 단순하지만 중요한 개념이다. 데이터베이스 쓰기의 최적화는 메모리의 휘발성으로 인해 간단하지 않다. 커밋(commit)한 데이터를 메모리에서 디스크로 쓰기 전에 시스템 장애가 발생한다면 데이터를 잃을 것이다. 은행이나 증권의 돈과 관련한 데이터라면 커밋 즉시 메모리에서 디스크로 써야 한다. 안정성은 증가하지만, 성능은 준다. 게시판의 글에 대한 데이터라면 메모리를 좀 더 활용하고 디스크에 쓰는 것을 늦출 수 있을 것이다. 데이터베이스 쓰기의 최적화는 데이터의 종류에 따라 안정성과 성능 사이에서 고민해야 할 문제이다.
MariaDB의 디폴트 스토리지 엔진(default_storage_engine)은 InnoDB이다. InnoDB와 관련한 디폴트 설정값은 데스크톱 시스템에 맞게 되어 있다. 데이터베이스 전용의 서버를 사용한다면 최적의 성능을 위해 설정값을 변경하여야 할 것이다. 전용의 서버가 아니더라도 약간의 설정값 변경으로 성능 향상을 꾀할 수 있다. 나는 Configuring MariaDB for Optimal Performance와 10 Database Tuning Tips for Peak Workloads 페이지를 참고하여 3가지 정도의 설정만 추가하였다.
innodb_buffer_pool_size=4096M innodb_log_file_size=1024M innodb_log_buffer_size=64M
라인 1의 innodb_buffer_pool_size는 성능 최적화에 가장 중요한 설정이다. 하나만 설정해야 한다면 이것을 해야 한다. 참고한 페이지에서는 innodb_buffer_pool_size를 데이터베이스 전용의 서버일 때 메모리의 80% 정도를 권장한다. Buffer Pool은 주로 데이터의 읽기와 관련한 것으로 디스크보다는 메모리를 사용하기 위해 가능한 한 크게 설정하는 것을 추천하고 있다. 데이터베이스 전용의 서버가 아니더라도 자신의 시스템 환경에 맞춰 크게 설정하는 것이 좋겠다.
InnoDB는 데이터를 디스크에 쓰기 전에 속도와 안정성을 목적으로 Redo Log를 사용한다. Redo Log는 시스템 장애 발생 시 복구를 위해 변경 기록들을 저장한다. INSERT 문과 같은 데이터 변경이 발생한다면 그 데이터는 ‘Buffer Pool(메모리) > Log Buffer(메모리) > Redo Log(ib_logfile0, ib_logfile1 파일) > 테이블(디스크)’의 순으로 이동한다. Redo Log의 파일 크기인 innodb_log_file_size는 innodb_buffer_pool_size의 1/4에서 1/2까지를 권장한다. Log Buffer의 크기인 innodb_log_buffer_size는 참고한 페이지에서 64M을 추천하고 있다.
innodb_log_file_size의 변경은 다음의 절차로 한다.
- MariaDB 서버를 종료한다.
- 위 라인 2처럼 50-server.cnf 파일에 innodb_log_file_size 설정을 추가한다.
- /var/lib/mysql 디렉토리에서 ib_logfile0, ib_logfile1을 삭제하거나 다른 디렉토리로 옮긴다. (innodb_log_files_in_group의 디폴트 값이 2이기 때문에 Redo Log 파일이 2개가 있다.)
- MariaDB 서버를 시작한다. 새로 설정한 크기의 ib_logfile0, ib_logfile1 파일을 볼 수 있을 것이다.
참고한 페이지에는 데이터 안정성과 관련한 innodb_flush_log_at_trx_commit과 sync_binlog, 동시 접속자 수에 대한 max_connections 등을 다루고 있다. 나는 위 3개의 설정을 제외하고 모두 디폴트 값을 사용할 것이어서 크게 관심을 가지지 않았지만, 여러분에게 필요한 내용이 있을지 모르겠다.