How to enable Remote access to your MariaDB/MySQL database on Ubuntu Bionic or MariaDB < v10.6
In this article we show how you can easily enable remote access to a new database in Webdock or alternatively how to perform the process manually if you need fine-grained access control
webdock.io
일단 내가 안되던 부분은 위의 링크에서 도움을 받아 해결했다.
우분투 20.04 에 MariaDB 설치하기
python의 웹 프레임워크인 Flask를 테스트하면서 DB를 연동해야할 일이 생겼다. MySQL을 설치할지 아니면 MariaDB를 설치할지를 고민하다 MariaDB를 설치하기로 했다. [Flask 웹 프레임워크] Blueprint를 이용
blogger.pe.kr
내 서버 환경은 Digital Ocean 클라우드 서버를 이용한 Ubuntu서버였기에 위의 링크를 참고로 기본적인 MariaDB 설치를 했다.
자, 설치까지 완료했다면 이제 유저를 등록해야 한다.
>> mariadb
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 34
Server version: 10.1.48-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04
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)]>일단 먼저 mariadb에 접속하기 위해 터미널에 mariadb를 입력해준다.
MariaDB [(none)]> SELECT user, host, password FROM mysql.user;
+-------------+-----------+-------------------------------------------+
| user | host | password |
+-------------+-----------+-------------------------------------------+
| root | localhost | |
+-------------+-----------+-------------------------------------------+먼저 db에 생성된 user리스트를 보기위해 SELECT user, host, password FROM mysql.user; 를 입력한다.
그러면 현재 등록되어 있는 user의 정보가 나온다.
현재 유저는 root 하나로 host가 localhost만 배정되어 있다.
이러면 이 db는 내부망에서만 root계정을 이용해 접속이 가능한 상태이다.
그렇다면, 외부에서도 허용하는 계정을 새로 만들어 줘야 한다.
MariaDB [(none)]> CREATE USER 'user1'@'%' IDENTIFIED BY 'password1';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT user, host, password FROM mysql.user;
+-------------+-----------+-------------------------------------------+
| user | host | password |
+-------------+-----------+-------------------------------------------+
| root | localhost | |
| user1 | % | *668425423DB5193AF921380129F465A6425216D0 |
+-------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)먼저 CREATE USER '유저명'@'호스트' IDENTIFIED BY '비밀번호';를 입력한다.
- 유저명 : MARIADB에 로그인할때 사용할 USER ID이다.
- 호스트 : MARIADB에 접속 가능한 호스트를 지정해준다. 특정 IP가 아닌 모든 아이피에서 접근 가능하게 하기 위해서는 '%'를 입력해주면 된다.
- 비밀번호 : MARIADB에 로그인할 때 사용할 USER의 PASSWORD이다.
다음으로 유저가 만들어졌는지 확인하기 위해 다시 SELECT user, host, password FROM mysql.user; 를 입력해 확인해본다.
확인해 보면 알겠지만 비밀번호가 이상한 값으로 저장되어있을 것이다. 해당 값은 MariaDB에서 자동으로 SHA-1 방식으로 암호화한 뒤 앞에 *을 붙여서 암호화시킨 값이니 넘어가면 된다. (참고 링크)
Hashing Algorithm in MySQL PASSWORD()
Recently we had a question from a customer: what is the hashing algorithm implemented in PASSWORD() ? The manual doesn't give a straight answer in any of
blog.pythian.com
user가 정상적으로 생성됐음을 확인했다면, 다음 할 일은 user에게 database의 설정 권한을 부여해주어야 한다.
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' IDENTIFIED BY 'password1';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GRANTS FOR 'user1'@'%';
+---------------------------------------------------------------------------------------------------------------+
| Grants for user1@% |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' IDENTIFIED BY PASSWORD '*668425423DB5193AF921380129F465A6425216D0' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' IDENTIFIED BY 'password1'; 를 입력하여 모든 DB에 대한 권한은 부여할 수 있다.
다음으로 FLUSH PRIVILEGES; 를 입력하여 권한을 새로고침 해준다.
권한 부여가 어떻게 됐는지는 SHOW GRANTS FOR 'user1'@'%'; 를 입력하여 확인할 수 있다.
MariaDB [(none)]> CREATE DATABASE TEST_DB;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| TEST_DB |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
이제 user 세팅은 끝났다. 다음으로 CREATE DATABASE 'DB명'; 을 입력해 DB를 생성해준다.
마찬가지로 SHOW DATABASES; 명령어를 통해 생성된 DB를 확인할 수 있다.
이제 외부 db접속 프로그램에서 접근하기 위해서는 mariadb가 배포된 포트를 개방해줘야 한다. ctrl+c를 통해 mariadb의 접속을 풀고 다시 ubuntu 터미널로 나간다.
MariaDB [(none)]> Ctrl-C -- exit!
Aborted
>> ufw allow 3306
Rule added
Rule added (v6)
>> systemctl restart ufw
>> netstat -tnlp
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:80 0.0.0.0:* LISTEN 831/nginx: master p
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 827/sshd
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 3156/mysqld
tcp6 0 0 :::80 :::* LISTEN 831/nginx: master p
tcp6 0 0 :::22 :::* LISTEN 827/sshd이 ubuntu 서버는 ufw로 방화벽이 관리되고 있기 때문에 ufw allow 3306 커맨드로 포트를 개방해준 뒤, systemctl retstart ufw 커맨드로 ufw를 재시작해준다.
보통의 MySQL의 DB는 기본 포트로 3306을 사용한다.
혹시 모르니 포트 확인 방법은 netstat -tnlp 커맨드를 날려보면 배포 중인 프로그램들의 정보들이 나열된다.
자 이제 포트도 개방됐으니 DBEaver프로그램으로 접속해보자.
위의 사진처럼 세팅한 뒤 왼쪽 하단의 Test Connection ... 버튼을 눌러본다.
키야~ 언제나 서버는 나에게 재밌는 걸 던져준다.
이는 일단 mariadb가 배포된 ip의 3306 포트로 연결이 거절당하고 있는 상태이다.
방화벽도 열었는데 왜 일까???????
사실 여기서 한참을 헤매다가 최상단 링크를 보고 문제점을 찾아 고칠 수 있었다.
일단 문제는 간단하다 mysql의 bind address 값이 localhost인 127.0.0.1로 설정되어 있어서이다.
이는 설정 파일에 저장되어있는데, vi /etc/mysql/mariadb.conf.d/50-server.cnf 커맨드로 mysql 배포 설정 파일을 수정해야 한다.
.
.
.
# this is only for the mysqld standalone daemon
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
.
.
.
.# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
bind-address의 부분의 값을 0.0.0.0으로 바꿔주고 저장한 다음, systemctl restart mariadb 커맨드를 통해 mariadb를 재시작해줘야 설정이 적용된다.
이렇게 한 다음 다시 DBEaver를 통해 다시 접속 테스트를 해보자.
이제 생성한 DB에 외부 접속이 가능해졌다!!
이제 이 DB를 맘껏 사용해 봐야겠다!!