MYSQL 서버 SSL 설정 문제
Client 에서 SSL로 접속하기 위해서는 서버에서 SSL 관련 셋팅을 해 주어야 합니다.
apprmon
설정환경
OS : Ubuntu 10.04.4 LTS
Mysql version : 5.1.63-0ubuntu0.10.04.1
기본 설정은 하단 (MySQL with SSL Encryption) 참조
설정 이후, CA 관련 에러가 발생하는 경우(log 확인)...
[Warning] SSL error: Unable to get certificate
apparmor 설정을 변경해야 한다.
/etc/apparmor.d/usr.sbin.mysqld 파일에서 참조한 ca 파일들에 대한 read 설정을 해 주어야 한다.
SSL 설정 참조 : http://www.vmadmin.co.uk/linux/44-redhat/145-linuxmysqlencryption
-----------------------------------------------------------
MySQL with SSL Encryption
Check MySQL has SSL Support
mysql --ssl --help
ssl TRUE
show variables like ‘%ssl%’;
have_openssl YES
have_ssl YES
Generate SSL Certificates
mkdir /etc/mysql-ssl
cd /etc/mysql-ssl
CA
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 9000 -key ca-key.pem > ca-cert.pem
Server
openssl req -newkey rsa:2048 -days 9000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 9000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
Client
openssl req -newkey rsa:2048 -days 9000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 9000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
Configure the MySQL Server to use SSL Encryption
[mysqld]
ssl-ca=/etc/mysql-ssl/ca-cert.pem
ssl-cert=/etc/mysql-ssl/server-cert.pem
ssl-key=/etc/mysql-ssl/server-key.pem
Configure the MySQL Clients to use SSL Encryption
[client]
ssl-ca=/etc/mysql-ssl/ca-cert.pem
ssl-cert=/etc/mysql-ssl/client-cert.pem
ssl-key=/etc/mysql-ssl/client-key.pem
Create MySQL User that is Required to use SSL
GRANT SELECT, INSERT, UPDATE, DELETE on mydb.* to ‘ssluser’@’host’ IDENTIFIED BY ‘secretpass’ REQUIRE SSL;
FLUSH PRIVILEGES;
Login to MySQL using SSL Encryption
Using command line parameters without /etc/my.cnf [client] section
mysql--ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem –ussluser –p
Using the [client] section in /etc/my.cnf
mysql –ussluser –p
Configure PHP MySQL Connections to use SSL
NOTE: Uses /etc/my.cnf [client] section
mysql_pconnect(‘serverhost’, ‘ssluser’, ‘secretpass’, MYSQL_CLIENT_SSL);
Using SSL in MySQL Query Browser and MySQL Administrator
Add the following parameters under the advanced parameters tab:
USE_SSL YES
SSL_CA ca-cert.pem
SSL_CERT client-cert.pem
SSL_KEY client-key.pem
Press the down arrow to add a new parameter
MySQL Replication
stop slave;
change master to
master_ssl=1,
master_ssl_ca=’/etc/mysql-ssl/ca-cert.pem’,
master_ssl_cert=’/etc/mysql/ssl/client-cert.pem’,
master_ssl_key=’/etc/mysql/ssl/client-key.pem’;
start slave;
show slave status;
Check SSL Encryption is Working
At a MySQL prompt type:
show status like ‘%ssl%’;
Ssl_cipher DHE-RSA-AES256-SHA
\s
Cipher in use is DHE-RSA-AES256-SHA
-----------------------------------------------------------
