[Linux] Ubuntu 22.04 MariaDB 설치하기

1. 패키지 저장소에 mariadb 업데이트

설치 버전은 mariadb10.10이며, 명령어에서 버전을 변경하여 버전별 선택설치가 가능하다.

 

# 10.10 버전 설치
root@database1:~# curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.10"

# 10.6 버전 선택 설치
root@database1:~# curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.6"
# [info] Checking for script prerequisites.
# [info] MariaDB Server version 10.10 is valid
# [info] Repository file successfully written to /etc/apt/sources.list.d/mariadb.list
# [info] Adding trusted package signing keys...
# [info] Running apt-get update...
# [info] Done adding trusted package signing keys

 

2. 레포지토리에서 mariadb 설치

업데이트된 레포지토리에서 설치한다. (mariadb-server, mariadb-client)

root@database1:~# apt install mariadb-server mariadb-client
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  galera-4 libcgi-fast-perl libcgi-pm-perl libclone-perl libconfig-inifiles-perl libdaxctl1 libdbd-mysql-perl
  libdbi-perl libencode-locale-perl libfcgi-bin libfcgi-perl libfcgi0ldbl libhtml-parser-perl libhtml-tagset-perl
  libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libmariadb3
  libmysqlclient21 libndctl6 libpmem1 libtimedate-perl liburi-perl liburing2 lsof mariadb-client-core mariadb-common
  mariadb-server-core mysql-common psmisc pv rsync socat
Suggested packages:
  libmldbm-perl libnet-daemon-perl libsql-statement-perl libdata-dump-perl libipc-sharedcache-perl
  libbusiness-isbn-perl libwww-perl mailx mariadb-test netcat-openbsd doc-base
The following NEW packages will be installed:
  galera-4 libcgi-fast-perl libcgi-pm-perl libclone-perl libconfig-inifiles-perl libdaxctl1 libdbd-mysql-perl
  libdbi-perl libencode-locale-perl libfcgi-bin libfcgi-perl libfcgi0ldbl libhtml-parser-perl libhtml-tagset-perl
  libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libmariadb3
  libmysqlclient21 libndctl6 libpmem1 libtimedate-perl liburi-perl liburing2 lsof mariadb-client mariadb-client-core
  mariadb-common mariadb-server mariadb-server-core mysql-common psmisc pv rsync socat
0 upgraded, 37 newly installed, 0 to remove and 36 not upgraded.
Need to get 30.6 MB of archives.
After this operation, 233 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 https://dlm.mariadb.com/repo/mariadb-server/10.10/repo/ubuntu jammy/main amd64 mysql-common all 1:10.10.2+maria~ubu2204 [2764 B]
Get:2 http://ap-seoul-1-ad-1.clouds.archive.ubuntu.com/ubuntu jammy-updates/main amd64 rsync amd64 3.2.3-8ubuntu3.1 [404 kB]
Get:3 https://dlm.mariadb.com/repo/mariadb-server/10.10/repo/ubuntu jammy/main amd64 mariadb-common all 1:10.10.2+maria~ubu2204 [3918 B]
Get:4 https://dlm.mariadb.com/repo/mariadb-server/10.10/repo/ubuntu jammy/main amd64 galera-4 amd64 26.4.13-jammy [10.4 MB]
Get:5 https://dlm.mariadb.com/repo/mariadb-server/10.10/repo/ubuntu jammy/main amd64 libmariadb3 amd64 1:10.10.2+maria~ubu2204 [168 kB]
Get:6 http://ap-seoul-1-ad-1.clouds.archive.ubuntu.com/ubuntu jammy/main amd64 libdbi-perl amd64 1.643-3build3 [741 kB]
Get:7 http://ap-seoul-1-ad-1.clouds.archive.ubuntu.com/ubuntu jammy/main amd64 lsof amd64 4.93.2+dfsg-1.1build2 [253 kB]Get:8 https://dlm.mariadb.com/repo/mariadb-server/10.10/repo/ubuntu jammy/main amd64 mariadb-client-core amd64 1:10.10.2+maria~ubu2204 [1239 kB]
Get:9 http://ap-seoul-1-ad-1.clouds.archive.ubuntu.com/ubuntu jammy/main amd64 libconfig-inifiles-perl all 3.000003-1 [40.5 kB]
Get:10 http://ap-seoul-1-ad-1.clouds.archive.ubuntu.com/ubuntu jammy/main amd64 libdaxctl1 amd64 72.1-1 [19.8 kB]
Get:11 http://ap-seoul-1-ad-1.clouds.archive.ubuntu.com/ubuntu jammy/main amd64 libndctl6 amd64 72.1-1 [57.7 kB]
Get:12 http://ap-seoul-1-ad-1.clouds.archive.ubuntu.com/ubuntu jammy/main amd64 libpmem1 amd64 1.11.1-3build1 [81.4 kB]
Get:13 http://ap-seoul-1-ad-1.clouds.archive.ubuntu.com/ubuntu jammy/main amd64 liburing2 amd64 2.1-2build1 [10.3 kB]
Get:14 http://ap-seoul-1-ad-1.clouds.archive.ubuntu.com/ubuntu jammy/main amd64 psmisc amd64 23.4-2build3 [119 kB]
Get:15 http://ap-seoul-1-ad-1.clouds.archive.ubuntu.com/ubuntu jammy/main amd64 socat amd64 1.7.4.1-3ubuntu4 [349 kB]
Get:16 http://ap-seoul-1-ad-1.clouds.archive.ubuntu.com/ubuntu jammy/main amd64 libhtml-tagset-perl all 3.20-4 [12.5 kB]Get:17 http://ap-seoul-1-ad-1.clouds.archive.ubuntu.com/ubuntu jammy/main amd64 liburi-perl all 5.10-1 [78.8 kB]
Setting up libencode-locale-perl (1.05-1.1) ...
Setting up socat (1.7.4.1-3ubuntu4) ...
Setting up libio-html-perl (1.004-2) ...
Setting up libmariadb3:amd64 (1:10.10.2+maria~ubu2204) ...
Setting up libdaxctl1:amd64 (72.1-1) ...
Setting up lsof (4.93.2+dfsg-1.1build2) ...
Setting up libtimedate-perl (2.3300-2) ...
Setting up pv (1.6.6-1build2) ...
Setting up libndctl6:amd64 (72.1-1) ...
Setting up libfcgi-perl:amd64 (0.82+ds-1build1) ...
Setting up liburing2:amd64 (2.1-2build1) ...
Setting up libpmem1:amd64 (1.11.1-3build1) ...
Setting up liburi-perl (5.10-1) ...
Setting up libdbi-perl:amd64 (1.643-3build3) ...
Setting up rsync (3.2.3-8ubuntu3.1) ...
rsync.service is a disabled or a static unit, not starting it.
Setting up libhttp-date-perl (6.05-1) ...
Setting up mariadb-client-core (1:10.10.2+maria~ubu2204) ...
Setting up libdbd-mysql-perl:amd64 (4.050-5) ...
Setting up libhtml-parser-perl:amd64 (3.76-1build2) ...
Setting up mariadb-server-core (1:10.10.2+maria~ubu2204) ...
Setting up libhttp-message-perl (6.36-1) ...
Setting up mariadb-client (1:10.10.2+maria~ubu2204) ...
Setting up libcgi-pm-perl (4.54-1) ...
Setting up libhtml-template-perl (2.97-1.1) ...
Setting up mariadb-server (1:10.10.2+maria~ubu2204) ...
debconf: unable to initialize frontend: Dialog
debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 78.)
debconf: falling back to frontend: Readline
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /lib/systemd/system/mariadb.service.
mariadb-extra.socket is a disabled or a static unit, not starting it.
mariadb-extra.socket is a disabled or a static unit, not starting it.
Setting up libcgi-fast-perl (1:2.15-1) ...
Processing triggers for libc-bin (2.35-0ubuntu3.1) ...
debconf: unable to initialize frontend: Dialog
debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 78.)
debconf: falling back to frontend: Readline
Scanning processes...
Scanning candidates...
Scanning linux images...

 

3. 설치된 mariadb 버전 확인 및 통신 포트 확인

설치 완료된 mariadb 버전을 확인한다.

root@database1:~# mariadb --version
mariadb  Ver 15.1 Distrib 10.10.2-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

root@database1:~# netstat -ntpl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN      1/init
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      646/sshd: /usr/sbin
tcp        0      0 127.0.0.53:53           0.0.0.0:*               LISTEN      535/systemd-resolve
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      978/mariadbd
tcp6       0      0 :::111                  :::*                    LISTEN      1/init
tcp6       0      0 :::22                   :::*                    LISTEN      646/sshd: /usr/sbin

 

4. mariadb 보안 및 초기 설정 (mariadb-secure-installtion)

root@database1:~# mariadb-secure-installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

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
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n
 ... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] Y 
New password: # 비밀번호 입력
Re-enter new password: # 비밀번호 재입력
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] Y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n # 원격 접근 여부
 ... skipping.

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y
 ... skipping.

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

secure-installtion에서 원격 접근을 허용한다고 해서 별도로 설정하지 않고 접근을 할 수 있는 것은 아니다.

외부로 접근하기 위해서는 bind-address 설정이 반드시 필요하다.

 

 

5. bind-address 설정

bind-address 옵션은 /etc/mysql/my.cnf 에서 설정한다.

bind-address가 기본값으로 127.0.0.1로 설정되어 있다.

0.0.0.0으로 변경하여 외부 접근을 허용해야 한다.

root@database1: vi /etc/mysql/my.cnf

# my.cnf 내용
[client-server]
# Port or socket location where to connect
port = 3306
socket = /run/mysqld/mysqld.sock

#추가한 내용
[mysqld]
bind-address = 0.0.0.0

# 내용 수정이 완료되었으면
:wq 로 저장 후 systemctl restart mariadb 명령어로 서비스를 재시작한다.

 

6. iptables 방화벽 보안 정책 추가

mariadb 기본 포트인 3306에 대해 들어오는 패킷을 허용해야 외부에서 접근할 수 있다.

root@database1: iptables -A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
root@database1: iptables-save

... 생략
COMMIT
# Completed on Mon Dec 19 08:26:57 2022

 

7. mariadb 콘솔 접속

mariadb만 입력하여도 localhost에서는 사용자 또는 비밀번호 없이 접근할 수 있다.

root@database1:~# mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.10.2-MariaDB-1:10.10.2+maria~ubu2204 mariadb.org binary distribution

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)]>

 

7. 사용자 추가 및 권한 부여

기본적으로 mysql.user 정보를 조회해보면 root는 localhost로만 접근할 수 있었다. update Query를 사용하여

host 컬럼 데이터 변경을 시도했으나 result OK로 정상적으로 업데이트 되었다고 하지만

SELECT QUERY로 조회해보면 변경되지 않는 것을 확인할 수 있었다.

 

해결책은 새로운 사용자(admin)를 만들고 admin에게 모든 권한을 부여하는 방식을 취했다.

@'localhost'는 로컬에서만 접근 가능 @'%' 는 외부 어디서든지 접근 가능하단 의미이다.

아래 명령어로 admin 계정을 추가했다.

MariaDB [(none)]> create user '사용자'@'%' identified by '비밀번호';
MariaDB [(none)]> select host, user, password, plugin from mysql.user;
+-----------+-------------+-------------------------------------------+-----------------------+
| Host      | User        | Password                                  | plugin                |
+-----------+-------------+-------------------------------------------+-----------------------+
| localhost | mariadb.sys |                                           | mysql_native_password |
| %         | root        | *9BE02DCF8FEE75750C9262B0875091F94C620EF2 | mysql_native_password |
| localhost | mysql       | invalid                                   | mysql_native_password |
| %         | admin       | *EA4D1BD3DF1ECD21535EFB1DC7E451DFC160456E | mysql_native_password |
+-----------+-------------+-------------------------------------------+-----------------------+
4 rows in set (0.001 sec)

# 모든 데이터베이스 권한 부여
grant all privileges on *.* to '사용자'@'%';

# 특정 데이터베이스 및 테이블 권한 부여
grant all privileges on <데이터베이스명>.<테이블명> to '사용자'@'%';

# 권한 새로고침
MariaDB [(none)]> flush privileges;

 

8. 사용자 권한 확인 및 서비스 등록

MariaDB [(none)]> show grants for 'admin'@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for admin@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON `admin`.* TO `admin`@`%`                                                     |
+------------------------------------------------------------------------------------------------------+

# 자동 서비스 등록
root@database1:~# systemctl enable mariadb
Synchronizing state of mariadb.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install enable mariadb


# 서비스 상태 확인
root@database1:~# systemctl status mariadb
● mariadb.service - MariaDB 10.10.2 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
    Drop-In: /etc/systemd/system/mariadb.service.d
             └─migrated-from-my.cnf-settings.conf
     Active: active (running) since Mon 2022-12-19 02:51:04 UTC; 5h 47min ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
   Main PID: 1444 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 9 (limit: 1090)
     Memory: 110.5M
        CPU: 6.174s
     CGroup: /system.slice/mariadb.service
             └─1444 /usr/sbin/mariadbd

 

사용자를 추가하는데 있어서 조금 지연됬지만 이제 MariaDB를 새롭게 구축했으니

외부에서 자유롭게 접근할 수 있다.

 

참고: https://mariadb.com/kb/en/mariadb-package-repository-setup-and-usage/

MariaDB Package Repository Setup and Usage