메뉴 건너뛰기

?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄 첨부
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄 첨부

MySQL을 로컬에서만 사용하다 외부접속으로 해서 뭔가 쿼리를 날리다보면 패킷레벨에서 다 오픈되니까 불안하다.

mysql도 기타 다른 서버들 처럼 ssl 위에서 접속하게 하는 방법이다.

 

아래 설명은 우분투 20.04의 MariaDB 10.3.22 버전 기준인데.. 오래된 라즈베리파이의 10.1.44 에서도 똑같이 적용되는거보니

10.X버전에선 별 차이 없을거같다.

 

1. 현재 DB상태파악

 

root@server:/etc/mysql/certs# mysql -uroot -p
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 691
Server version: 10.3.22-MariaDB-1ubuntu1-log Ubuntu 20.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)]> select version();
+------------------------------+
| version()                    |
+------------------------------+
| 10.3.22-MariaDB-1ubuntu1-log |
+------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show variables like '%ssl%';
+---------------------+-------------+
| Variable_name       | Value       |
+---------------------+-------------+
| have_openssl        | NO          |
| have_ssl            | DISABLED    |
| ssl_ca              |             |
| ssl_capath          |             |
| ssl_cert            |             |
| ssl_cipher          |             |
| ssl_crl             |             |
| ssl_crlpath         |             |
| ssl_key             |             |
| version_ssl_library | YaSSL 2.4.4 |
+---------------------+-------------+
10 rows in set (0.002 sec)
MariaDB [(none)]> 

 

위처럼 아무 설정을 안했다면 have_ssl 로 disabled 로 되어 있다. 이는 ssl이 지원되지만 enable 되지 않았다는 뜻.

version_ssl_library 는 openssl 이 아닌 yaSSL 을 쓰는걸로 되어 있는데 저걸 써도 상관없다.

 

2. DB에 SSL 지원준비

 

/etc/mysql/mariadb.conf.d/50-server.cnf
을 열어 #ssl-ca=... 부분을 찾아 그 위에 ssl을 추가하고 저장한다

 

#
# For generating SSL certificates you can use for example the GUI tool "tinyca".
#
ssl
#ssl-ca=...

 

mariadb 재시작

 

service mariadb restart

 

mysql 에 재접속해서 다시 variables 를 조회했을때 have_ssl 에 YES로 바뀌어 있으면 

 SSL 지원준비는 완료되었다.

 

MariaDB [(none)]> show variables like '%ssl%';
+---------------------+-------------+
| Variable_name       | Value       |
+---------------------+-------------+
| have_openssl        | NO          |
| have_ssl            | YES         |
| ssl_ca              |             |
| ssl_capath          |             |
| ssl_cert            |             |
| ssl_cipher          |             |
| ssl_crl             |             |
| ssl_crlpath         |             |
| ssl_key             |             |
| version_ssl_library | YaSSL 2.4.4 |
+---------------------+-------------+
10 rows in set (0.01 sec)

MariaDB [(none)]> 

 

3. 인증서 생성

 

인증서를 관리용, 서버용, 클라이언트용 총 3가지를 만든다.
인증서 만들때 과정에서 물어보는 Common Name을 3가지 모두 다르게 해야 검증오류가 안난다.
 
 
3-1) 관리용 인증서 생성
 
$ mkdir /etc/mysql/certs
$ cd /etc/mysql/certs
$ openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus (2 primes)
.........+++++
.................................+++++
e is 65537 (0x010001)
$ openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.pem
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:MariaDB Admin
Email Address []:

 

위의 밑에서 2번째줄 Common Name 에 주의

 

3-2) 서버용 인증서 생성

 

$ openssl req -newkey rsa:2048 -days 365000 -nodes -keyout server-key.pem -out server-req.pem
Ignoring -days; not generating a certificate
Generating a RSA private key
..................+++++
..+++++
writing new private key to 'server-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:MairaDB Server
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
$ openssl rsa -in server-key.pem -out server-key.pem
writing RSA key
$ openssl x509 -req -in server-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok
subject=C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = MairaDB Server
Getting CA Private Key
$

 

3-3) 클라이언트용 인증서 생성

 

$  openssl req -newkey rsa:2048 -days 365000 -nodes -keyout client-key.pem -out client-req.pem
Ignoring -days; not generating a certificate
Generating a RSA private key
......+++++
...............................................................................................................................+++++
writing new private key to 'client-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:MairaDB Client
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
$ openssl rsa -in client-key.pem -out client-key.pem
writing RSA key
$ openssl x509 -req -in client-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Signature ok
subject=C = AU, ST = Some-State, O = Internet Widgits Pty Ltd, CN = MairaDB Client
Getting CA Private Key
$

 

3-4) 생성된 인증서 검증

 

$ ls -al
total 40
drwxr-xr-x 2 root root 4096  7월 11 18:02 .
drwxr-xr-x 6 root root 4096  7월 11 17:46 ..
-rw-r--r-- 1 root root 1314  7월 11 17:51 ca-cert.pem
-rw-r--r-- 1 root root 1675  7월 11 17:49 ca-key.pem
-rw-r--r-- 1 root root 1168  7월 11 18:02 client-cert.pem
-rw------- 1 root root 1679  7월 11 18:01 client-key.pem
-rw-r--r-- 1 root root  993  7월 11 18:01 client-req.pem
-rw-r--r-- 1 root root 1168  7월 11 17:59 server-cert.pem
-rw------- 1 root root 1675  7월 11 17:58 server-key.pem
-rw-r--r-- 1 root root  993  7월 11 17:58 server-req.pem
$ openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK
$

 

3-5) mysql 서버가 접근가능하게 인증서 파일 소유를 mysql:mysql로 변경
 

$ chown -R mysql:mysql /etc/mysql/certs

 

4. 서버 설정

 

/etc/mysql/mariadb.conf.d/50-server.cnf

 

#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#

ssl
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem
 

 

을 열어 #ssl- 로 주석처리된 부분에서 주석을 지우고 위처럼 변경하고 mariadb 재시작

$ service mariadb restart

 

5. 서버 SSL 상태 확인

 

$ service mariadb restart
$ mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 214
Server version: 10.3.22-MariaDB-1ubuntu1-log Ubuntu 20.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)]> show variables like '%ssl%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| have_openssl        | NO                               |
| have_ssl            | YES                              |
| ssl_ca              | /etc/mysql/certs/ca-cert.pem     |
| ssl_capath          |                                  |
| ssl_cert            | /etc/mysql/certs/server-cert.pem |
| ssl_cipher          |                                  |
| ssl_crl             |                                  |
| ssl_crlpath         |                                  |
| ssl_key             | /etc/mysql/certs/server-key.pem  |
| version_ssl_library | YaSSL 2.4.4                      |
+---------------------+----------------------------------+
10 rows in set (0.002 sec)

MariaDB [(none)]> 

 

이렇게 표시되면 서버측에서 SSL 지원 준비는 끝났다.

 

6. 서버에 SSL 외부접속 사용자 추가

SSL 로만 외부접속을 허용하는 사용자를 추가해서 설정이 제대로 동작하는지 테스트 해보자.

 

MariaDB [(none)]> CREATE USER 'ssluser'@'%' IDENTIFIED BY '패스워드';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON DB이름.* TO 'ssluser'@'%' REQUIRE SSL;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> 
 

 

7. 외부컴퓨터에서 서버에 SSL접속 확인하기

 

7-1) 일반 접속 실패 확인

 

C:\Users\사용자>mysql -h서버주소 -ussluser -p
Enter password: **********
ERROR 1045 (28000): Access denied for user 'ssluser'@'_gateway' (using password: YES)

 

7-2) SSL 접속 성공 확인

 

C:\Users\사용자>mysql --ssl -h서버주소 -ussluser -p
Enter password: **********
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 506
Server version: 10.3.22-MariaDB-1ubuntu1-log Ubuntu 20.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)]> status;
--------------
mysql  Ver 15.1 Distrib 10.5.4-MariaDB, for Win64 (AMD64), source revision 5018b998a76b99a30efe96ca007a95a5a1c83bb5

Connection id:          506
Current database:
Current user:           ssluser@_gateway
SSL:                    Cipher in use is AES256-SHA
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.3.22-MariaDB-1ubuntu1-log Ubuntu 20.04
Protocol version:       10
Connection:             ddart.net via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    euckr
Conn.  characterset:    euckr
TCP port:               3306
Uptime:                 16 min 28 sec

Threads: 12  Questions: 9629  Slow queries: 0  Opens: 239  Flush tables: 1  Open tables: 233  Queries per second avg: 9.745
--------------

MariaDB [(none)]>

 

mysql 접속후  status 에서

SSL: Cipher in use is AES256-SHA

을 보면 현재 SSL 접속상태인지 아닌지 알수 있다. SSL이 아니면 'Not in use' 라고 표시된다.

 

 

7-3) mysql client 설정

 

3번과정에서 만든 ca-cert.pem, client-cert.pem, client-key.pem 을 외부접속할 클라이언트 PC로 다운받는다.

mysql client 설정에서 이 인증키들을 설정해주면 그냥 접속해도 ssl로 접속된다.

 

클라이언트 PC가 윈도우일 경우

C:\Program Files\MariaDB 10.5\data\my.ini

 

[client]
port=3306
plugin-dir=C:/Program Files/MariaDB 10.5/lib/plugin
ssl-ca=C:\Program Files\MariaDB 10.5\certs\ca-cert.pem
ssl-cert=C:\Program Files\MariaDB 10.5\certs\client-cert.pem
ssl-key=C:\Program Files\MariaDB 10.5\certs\client-key.pem

 

에 추가해준다.

클라이언트 PC가 리눅스일경우

 

/etc/mysql/mariadb.conf.d/50-mysql-clients.cnf 

 

[mysql]
# Default is Latin1, if you need UTF-8 set this (also in server section)
default-character-set = utf8mb4
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/client-cert.pem
ssl-key=/etc/mysql/certs/client-key.pem
 

 

로 변경해준다.

 

이제 그냥 ssl 옵션없이

mysql -h서버주소 -u사용자 -p 로 접속해도 접속후 status 정보보면

 

MariaDB [(none)]> status;
--------------
mysql  Ver 15.1 Distrib 10.1.44-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2

Connection id: 1918
Current database:
Current user: ddartssl@61.98.230.101
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.3.22-MariaDB-1ubuntu1-log Ubuntu 20.04
Protocol version: 10
Connection: ddart.net via TCP/IP
Server characterset: utf8mb4
Db     characterset: utf8mb4
Client characterset: utf8mb4
Conn.  characterset: utf8mb4
TCP port: 3306
Uptime: 1 hour 57 min 30 sec

Threads: 14  Questions: 93126  Slow queries: 0  Opens: 251  Flush tables: 1  Open tables: 245  Queries per second avg: 13.209

 

처럼 SSL 접속이 됨을 알수 있다.

 

MariaDB 윈도우용 GUI클라이언트 HeidiSQL 에서도

heidisql_setting.jpg

heidisql_adv.jpg

 

 

와 같이 설정한다.

 

7-4) libmariadb.dll 라이브러리에서 SSL 사용하는 C++ 콘솔 예제

 

mysql_real_connect 하기전에 mysql_ssl_set 나 mysql_options 로 인증키 설정해줘야한다.

 

#include <iostream>
#include "mysql.h"
#pragma comment(lib, "libmariadb")
int main()
{
  MYSQL *connection, mysql;
  mysql_init(&mysql);
  mysql_ssl_set(&mysql, "certs/client-key.pem", "certs/client-cert.pem", "certs/ca-cert.pem", NULL, NULL);
  connection = mysql_real_connect(&mysql, "ddart.net", "ssluser", "password", "dbname", 0, 0, 0);
  if (connection == NULL)
    cout << mysql_error(&mysql) << endl;
  else
    cout << mysql_get_ssl_cipher(&mysql) << endl;
}

 

 

8. 서버간 replication 에 ssl 적용

 

 

https://ddart.net/xe/board/12179 

에서 설명한 mysql master 서버와 slave 서버사이 동기화할때도 모든게 노출되는 일반접속보다 ssl 설정을 하는게 좋다.

 

8-1. 마스터 서버

 위에서 설명한 서버에서 설정과 동일하다.

 

8-2. 슬레이브 서버

 클라이언트 PC처럼 동작하므로 

마스터 서버에서 위3번 과정에서 만든 ca-cert.pem, client-cert.pem, client-key.pem 을

슬레이브 서버의 /etc/mysql/certs 디렉토리에 복사하고 소유권을 mysql:mysql로 바꿔준다.

그런후 슬레이브 서버에 

mysql -uroot -p 로 루트 접속후 

 

 

 

 

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.06 sec)

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='서버주소', MASTER_USER='replication_user', MASTER_PASSWORD='비밀번호',  \
MASTER_LOG_FILE='master1-bin.000020', MASTER_LOG_POS=1010814, \
MASTER_SSL=1, \
MASTER_SSL_CA = '/etc/mysql/certs/ca-cert.pem', \
MASTER_SSL_CERT = '/etc/mysql/certs/client-cert.pem', \
MASTER_SSL_KEY = '/etc/mysql/certs/client-key.pem';

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: ddart.net
                  Master_User: replication_user
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master1-bin.000020
          Read_Master_Log_Pos: 1697766
               Relay_Log_File: master1-relay-bin.000009
                Relay_Log_Pos: 11044
        Relay_Master_Log_File: master1-bin.000020
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1697766
              Relay_Log_Space: 23829
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql/certs/ca-cert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /etc/mysql/certs/client-cert.pem
            Master_SSL_Cipher: 
               Master_SSL_Key: /etc/mysql/certs/client-key.pem
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
               Master_SSL_Crl: /etc/mysql/certs/ca-cert.pem
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
1 row in set (0.00 sec)

 

 

 


List of Articles
번호 제목 글쓴이 날짜 조회 수
62 윈도우에서 우분투 22.04 MariaDB 서버 SSL접속시 SEC_E_ALGORITHM_MISMATCH 오류 DDART 2024.01.20 466
61 윈도우10 아이콘이 제대로 표시 안될때 DDART 2023.11.08 482
60 윈도우용 아파치 php 버전 동시에 2개이상 띄우기 DDART 2023.05.26 695
59 윈도우용 아파치에서 localhost 를 https로 띄우기 file DDART 2023.06.20 815
58 mysql/mariadb 손상된 inno db 복구 DDART 2023.05.03 852
57 우분투 22.04 메일서버 설정 DDART 2023.06.13 878
56 아파치서버에서 웹소켓 특정포트 프락시설정방법 DDART 2023.07.19 1000
55 원격데스크톱 연결후 화면 사라짐현상, 특정 IP만 접속 허용 DDART 2023.05.04 1123
54 윈도우에서 우분투 20.04 MariaDB 서버 SSL접속시 SEC_E_ALGORITHM_MISMATCH 오류 DDART 2023.05.06 1142
53 원격데스크톱 마이크로소프트 계정 자격증명 실패할때 DDART 2023.04.28 1192
52 우분투 서버 자동업데이트 DDART 2021.07.02 1382
51 우분투 19.04, 19.10, 20.04 으로 업그레이드 하기 DDART 2019.12.21 1577
50 윈도우 10 마이크로소프트 계정 PIN 없이 자동로그인 DDART 2020.09.16 1712
49 우분투 서버 업데이트 후 자동 전원 대기모드 방지 DDART 2021.07.22 1747
48 postfix 에서 mysql 오류 DDART 2020.08.01 1760
47 터미널에서 backspace 키가 안눌러질때 DDART 2020.07.10 1895
46 svn 서버 사이 동기화 DDART 2020.07.29 1932
45 우분투 16.04 에서 18.04 로 업그레이드하기 DDART 2018.10.15 1943
44 윈도우 10에서 구글 어시스턴트 명령 DDART 2019.09.08 2102
43 vsftpd 설정 DDART 2017.12.11 2103
Board Pagination Prev 1 2 3 ... 4 Next
/ 4