MySQL replication에 SSL 설정

mysql replication data에도 당연히 보안은 적용되어야 한다. 그러므로 replication 채널간의 SSL 설정이 필요하다.

다음은 SSL 설정을 했던 과정과 트러블 슈팅을 기록한 노트이다.

########################################################

# 인증서 발급 

########################################################

mkdir /etc/mysql/ssl

cd /etc/mysql/ssl/

-- create own CA (있으면 안해도 됨!!)

openssl req -x509 -new -days 999999 -newkey rsa:2048 -nodes -keyout ca-key.pem -out ca-cert.pem

-- Create the server certificate request

openssl req -new -newkey rsa:2048 -nodes -keyout server-key.pem -out server-csr.pem

-- (optional) Remove the passphrase from the key

openssl rsa -in server-key.pem -out server-key.pem

-- Sign this server request with the CA key to make a proper server certificate.

openssl x509 -req -days 999999 -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial -CAserial ca-srl.txt -in server-csr.pem -out server-cert.pem

-- 인증서 분류 관리

mkdir server-cert 

mv server-* server-cert/ 

-- 클라이언트 인증서 생성 - 하나 만들어서 모든 서버 사용

-- Create the client certificate request

openssl req -new -newkey rsa:2048 -nodes -keyout client-key.pem -out client-csr.pem

-- (OPTIONAL) Remove a passphrase from the key

openssl rsa -in client-key.pem -out client-key.pem

-- Sign this server request with the CA key to make a proper server certificate

openssl x509 -req -days 999999 -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial -CAserial ca-srl.txt -in client-csr.pem -out client-cert.pem

-- copy to client

mkdir client-cert

mv client-*.pem client-cert

-- transfer to server

scp -r  server-cert/ client-cert/ ca-cert.pem [host address]

########################################################

# 인증서 전송 받은 후 서버 설정

########################################################

-- 인증서 파일 이동 

mkdir -p /etc/mysql/ssl

cp /home/ubuntu/server-cert/* /etc/mysql/ssl

cp /home/ubuntu/ca-cert.pem /etc/mysql/ssl

chown mysql:mysql /etc/mysql/ssl/*

chmod 400 /etc/mysql/ssl/*

--/etc/my.cnf 수정

[mysqld]

ssl-key=/etc/mysql/ssl/server-key.pem

ssl-cert=/etc/mysql/ssl/server-cert.pem

ssl-ca=/etc/mysql/ssl/ca-cert.pem

--repl_user에게 권한 주기

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl_user IDENTIFIED BY 'password' require SSL;

--권한 확인

show grants for repl_user;

FLUSH PRIVILEGES;

change master to master_ssl_ca='/etc/mysql/ssl/ca-cert.pem',master_ssl_cert='/etc/mysql/ssl/server-cert.pem',master_ssl_key='/etc/mysql/ssl/server-key.pem',master_ssl=1;

########################################################

# 인증서 전송 받은 후 클라이언트 설정

########################################################

-- ca-cert.pem과 client-cert, client-key를 적당한 위치에 카피, mysql에 권한 주기 

cp /home/ubuntu/client-cert/* /etc/mysql/ssl

chown mysql:mysql /etc/mysql/ssl/*

chmod 400 /etc/mysql/ssl/*

-- /etc/my.cnf 수정

[client]

ssl-ca=/etc/mysql/ssl/ca-cert.pem

ssl-key=/etc/mysql/ssl/client-key.pem

ssl-cert=/etc/mysql/ssl/client-cert.pem

########################################################

# test

########################################################

mysql --ssl -h MySQL_SERVER -u SSL_CLIENT -p

SHOW VARIABLES LIKE 'have_openssl';

########################################################

# troubleshooting

########################################################

- 특정 패스워드일땐 안되다가 repl_user로 바꾸니까 된다. 위키에도 패스워드 해시 관련된 얘기가 있네...

- 키 파일 권한이 mysql 계정이 read할 수 있어야 함