Mysql(Mariadb)マスタからSSL暗号化方式への変更
15209 ワード
最近半年忙しくて、あまり更新していないので、今日Mysql主従のSSL暗号化アクセス方式をテストしました.テストノートをここに列挙します.深層原理は詳しく研究されていません.間違いがあれば、指摘してください.
一.概要:1.スキーマ:2マスター2スレーブ、同時に1マスターしか接続せず、この2つのマスターライブラリ間を自由に切り替えることができます.
2.仮に2マスタ2スレーブが、既にセットされており、GTIDの接続方式を使用しているとする.ここではこれをSSL接続に変更するだけです
3.具体的な情報は以下の通り.
master1:
hostname: db01 ip: 10.100.31.141
master2:
hostname: db02 ip: 10.100.31.142
slave1:
hostname: db11 ip: 10.100.31.151
slave2:
hostname: db12 ip: 10.100.31.152
4.2つのマスターマシンでslave userを再作成し、require sslを追加する必要がある
grant replication slave,replication client on *.* to slave@'%' identified by 'slave' require ssl;
5.バージョン情報:
[root@db01 ~]# mysql --version
mysql Ver 15.1 Distrib 10.1.8-MariaDB, for Linux (x86_64) using readline 5.1
[root@db01 ~]# cat/etc/issue
CentOS release 6.6 (Final)
Kernel\r on an\m
二.OSでのSSLの構成
1.MASTER 1のSSL 1.1を配置する.マスター1でCAサーバを作成する
cd/etc/pki/CA
rm -rf *
mkdir private newcerts certs crl
(umask 077;openssl genrsa -out private/cakey.pem 2048)
openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 36500
touch index.txt serial crlnumber
echo 01 > serial
==============================================================================
1.2. master 1(db 01)自体に証明書を発行する
# mkdir/data01/mysql/ssl
# cd/data01/mysql/ssl/
(umask 077;openssl genrsa -out master_db01.key 2048)
openssl req -new -key master_db01.key -out master_db01.csr -days 36500
openssl ca -in master_db01.csr -out master_db01.crt -days 36500
==============================================================================
==============================================================================
1.3マスター1にCA証明書を提供する
cp/etc/pki/CA/cacert.pem/data01/mysql/ssl/cacert_db01.pem
2.MASTER 2のSSL 2.1を配合する.マスター2でCAサーバを作成する
cd/etc/pki/CA
rm -rf *
mkdir private newcerts certs crl
(umask 077;openssl genrsa -out private/cakey.pem 2048)
openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 36500
touch index.txt serial crlnumber
echo 01 > serial
2.2. master 1(db 02)自体に証明書を発行する
# mkdir/data01/mysql/ssl
# cd/data01/mysql/ssl/
(umask 077;openssl genrsa -out master_db02.key 2048)
openssl req -new -key master_db02.key -out master_db02.csr -days 36500
openssl ca -in master_db02.csr -out master_db02.crt -days 36500
2.3マスター2のCA証明書の自己提供
cp/etc/pki/CA/cacert.pem/data01/mysql/ssl/cacert_db02.pem
3.SLAVE 1のSSL 3.1を配置する.slave 1(db 11)で証明書リクエストを生成する
# mkdir/data01/mysql/ssl
# cd/data01/mysql/ssl/
(umask 077;openssl genrsa -out slave_db11.key 2048)
openssl req -new -key slave_db11.key -out slave_db11.csr -days 36500
scp slave_db11.csr 10.100.31.141:/root
scp slave_db11.csr 10.100.31.142:/root
==============================================================================
3.2 master 1はslave 1のために証明書を発行する
#master 1(db 01)での操作
cd/root/
openssl ca -in slave_db11.csr -out db01-slave_db11.crt -days 36500
scp db01-slave_db11.crt 10.100.31.151:/data01/mysql/ssl/
==============================================================================
3.3 master 1 slave 1(db 11)にCAの証明書を提供する
scp/etc/pki/CA/cacert.pem 10.100.31.151:/data01/mysql/ssl/cacert_db01.pem
3.4 master 2はslave 1のために証明書を発行する
#master 2(db 02)での操作
cd/root/
openssl ca -in slave_db11.csr -out db02-slave_db11.crt -days 36500
scp db02-slave_db11.crt 10.100.31.151:/data01/mysql/ssl/
3.5 master 2 slave 1(db 11)にCAの証明書を提供する
scp/etc/pki/CA/cacert.pem 10.100.31.151:/data01/mysql/ssl/cacert_db02.pem
4.SLAVE 2のSSL 4.1を配置する.slave 2(db 12)で証明書リクエストを生成する
# mkdir/data01/mysql/ssl
# cd/data01/mysql/ssl/
(umask 077;openssl genrsa -out slave_db12.key 2048)
openssl req -new -key slave_db12.key -out slave_db12.csr -days 36500
scp slave_db12.csr 10.100.31.141:/root
scp slave_db12.csr 10.100.31.142:/root
4.2 master 1はslave 2のために証明書を発行する
#master 1(db 01)での操作
cd/root/
openssl ca -in slave_db12.csr -out db01-slave_db12.crt -days 36500
scp db01-slave_db12.crt 10.100.31.152:/data01/mysql/ssl/
4.3 slave 1(db 12)にCAの証明書を提供する
scp/etc/pki/CA/cacert.pem 10.100.31.152:/data01/mysql/ssl/cacert_db01.pem
4.4 master 2 slave 2の証明書発行
#master 2(db 02)での操作
cd/root/
openssl ca -in slave_db12.csr -out db02-slave_db12.crt -days 36500
scp db02-slave_db12.crt 10.100.31.152:/data01/mysql/ssl/
4.5 master 2 slave 2(db 12)にCAの証明書を提供する
scp/etc/pki/CA/cacert.pem 10.100.31.152:/data01/mysql/ssl/cacert_db02.pem
5.4台のマシンを修正する以下のディレクトリ権限#はそれぞれ4台のマシンで実行
chown -R mysql:mysql/data01/mysql/ssl/
三.mysqlマスターからのSSLの構成
1.master 1で次のパラメータを追加してデータベース#SSLを再起動
ssl
ssl-ca=/data01/mysql/ssl/cacert_db01.pem
ssl-cert=/data01/mysql/ssl/master_db01.crt
ssl-key=/data01/mysql/ssl/master_db01.key
ssl_cipher = DHE-RSA-AES256-SHA
2.master 2中同上#SSL
ssl
ssl-ca=/data01/mysql/ssl/cacert_db02.pem
ssl-cert=/data01/mysql/ssl/master_db02.crt
ssl-key=/data01/mysql/ssl/master_db02.key
ssl_cipher = DHE-RSA-AES256-SHA
3.手動接続試験mysql-uslave-p-h 10.100.31.141 --ssl=1 --ssl_ca='/data01/mysql/ssl/cacert_db01.pem'
4.主従構成SSL slave 1の設定:
CHANGE MASTER TO
MASTER_HOST='10.100.31.141',
MASTER_USER='slave',
MASTER_PASSWORD='slave',
MASTER_USE_GTID=slave_pos,
master_ssl=1,
master_ssl_ca='/data01/mysql/ssl/cacert_db01.pem',
master_ssl_cert='/data01/mysql/ssl/db01-slave_db11.crt',
master_ssl_key='/data01/mysql/ssl/slave_db11.key';
start slave;
マスター2に切り替えてみます.
stop slave;
CHANGE MASTER TO
MASTER_HOST='10.100.31.142',
MASTER_USER='slave',
MASTER_PASSWORD='slave',
MASTER_USE_GTID=slave_pos,
master_ssl=1,
master_ssl_ca='/data01/mysql/ssl/cacert_db02.pem',
master_ssl_cert='/data01/mysql/ssl/db02-slave_db11.crt',
master_ssl_key='/data01/mysql/ssl/slave_db11.key';
start slave;
確認検査の結果、同様に問題ありませんでした.
四.その他の説明1.SLAVE 2も同様の方法で接続テストを行っていますが、問題ありません.
2.本文は1つの内容を無視して、2つのmasterの間は互いに主備の関係で、現在の基礎の上でSSLを加えることができて、実際にテストして、問題がありません.記事の読み取りに影響し、実際にシーンを使用していない場合に限ってリストされません.
一.概要:1.スキーマ:2マスター2スレーブ、同時に1マスターしか接続せず、この2つのマスターライブラリ間を自由に切り替えることができます.
2.仮に2マスタ2スレーブが、既にセットされており、GTIDの接続方式を使用しているとする.ここではこれをSSL接続に変更するだけです
3.具体的な情報は以下の通り.
master1:
hostname: db01 ip: 10.100.31.141
master2:
hostname: db02 ip: 10.100.31.142
slave1:
hostname: db11 ip: 10.100.31.151
slave2:
hostname: db12 ip: 10.100.31.152
4.2つのマスターマシンでslave userを再作成し、require sslを追加する必要がある
grant replication slave,replication client on *.* to slave@'%' identified by 'slave' require ssl;
5.バージョン情報:
[root@db01 ~]# mysql --version
mysql Ver 15.1 Distrib 10.1.8-MariaDB, for Linux (x86_64) using readline 5.1
[root@db01 ~]# cat/etc/issue
CentOS release 6.6 (Final)
Kernel\r on an\m
二.OSでのSSLの構成
1.MASTER 1のSSL 1.1を配置する.マスター1でCAサーバを作成する
cd/etc/pki/CA
rm -rf *
mkdir private newcerts certs crl
(umask 077;openssl genrsa -out private/cakey.pem 2048)
openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 36500
touch index.txt serial crlnumber
echo 01 > serial
==============================================================================
[root@db01 CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 36500
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) [XX]:CN
State or Province Name (full name) []:Beijing
Locality Name (eg, city) [Default City]:Beijing
Organization Name (eg, company) [Default Company Ltd]:exiao
Organizational Unit Name (eg, section) []:mysql
Common Name (eg, your name or your server's hostname) []:db01
Email Address []:1.2. master 1(db 01)自体に証明書を発行する
# mkdir/data01/mysql/ssl
# cd/data01/mysql/ssl/
(umask 077;openssl genrsa -out master_db01.key 2048)
openssl req -new -key master_db01.key -out master_db01.csr -days 36500
openssl ca -in master_db01.csr -out master_db01.crt -days 36500
==============================================================================
[root@db01 ssl]# openssl req -new -key master_db01.key -out master_db01.csr -days 36500
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) [XX]:CN
State or Province Name (full name) []:Beijing
Locality Name (eg, city) [Default City]:Beijing
Organization Name (eg, company) [Default Company Ltd]:exiao
Organizational Unit Name (eg, section) []:mysql
Common Name (eg, your name or your server's hostname) []:db01
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
-----------
[root@db01 ssl]# openssl ca -in master_db01.csr -out master_db01.crt -days 36500
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 1 (0x1)
Validity
Not Before: Dec 18 02:36:24 2015 GMT
Not After : Nov 24 02:36:24 2115 GMT
Subject:
countryName = CN
stateOrProvinceName = Beijing
organizationName = exiao
organizationalUnitName = mysql
commonName = db01
X509v3 extensions:
X509v3 Basic Constraints:
CA:TRUE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
8A:18:2B:84:E6:B8:8E:D9:AB:07:90:D5:5D:63:8C:9B:A3:DB:6E:00
X509v3 Authority Key Identifier:
keyid:B5:4F:20:EC:C7:CA:B4:A1:AA:1B:F1:B9:91:0F:85:12:2D:59:68:29
Certificate is to be certified until Nov 24 02:36:24 2115 GMT (36500 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated==============================================================================
1.3マスター1にCA証明書を提供する
cp/etc/pki/CA/cacert.pem/data01/mysql/ssl/cacert_db01.pem
2.MASTER 2のSSL 2.1を配合する.マスター2でCAサーバを作成する
cd/etc/pki/CA
rm -rf *
mkdir private newcerts certs crl
(umask 077;openssl genrsa -out private/cakey.pem 2048)
openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 36500
touch index.txt serial crlnumber
echo 01 > serial
2.2. master 1(db 02)自体に証明書を発行する
# mkdir/data01/mysql/ssl
# cd/data01/mysql/ssl/
(umask 077;openssl genrsa -out master_db02.key 2048)
openssl req -new -key master_db02.key -out master_db02.csr -days 36500
openssl ca -in master_db02.csr -out master_db02.crt -days 36500
2.3マスター2のCA証明書の自己提供
cp/etc/pki/CA/cacert.pem/data01/mysql/ssl/cacert_db02.pem
3.SLAVE 1のSSL 3.1を配置する.slave 1(db 11)で証明書リクエストを生成する
# mkdir/data01/mysql/ssl
# cd/data01/mysql/ssl/
(umask 077;openssl genrsa -out slave_db11.key 2048)
openssl req -new -key slave_db11.key -out slave_db11.csr -days 36500
scp slave_db11.csr 10.100.31.141:/root
scp slave_db11.csr 10.100.31.142:/root
==============================================================================
[root@db11 ssl]# openssl req -new -key slave_db11.key -out slave_db11.csr -days 36500
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) [XX]:CN
State or Province Name (full name) []:Beijing
Locality Name (eg, city) [Default City]:Beijing
Organization Name (eg, company) [Default Company Ltd]:exiao
Organizational Unit Name (eg, section) []:mysql
Common Name (eg, your name or your server's hostname) []:db11
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request3.2 master 1はslave 1のために証明書を発行する
#master 1(db 01)での操作
cd/root/
openssl ca -in slave_db11.csr -out db01-slave_db11.crt -days 36500
scp db01-slave_db11.crt 10.100.31.151:/data01/mysql/ssl/
==============================================================================
[root@db01 ~]# openssl ca -in slave_db11.csr -out db01-slave_db11.crt -days 36500
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 2 (0x2)
Validity
Not Before: Dec 18 03:25:35 2015 GMT
Not After : Nov 24 03:25:35 2115 GMT
Subject:
countryName = CN
stateOrProvinceName = Beijing
organizationName = exiao
organizationalUnitName = mysql
commonName = db11
X509v3 extensions:
X509v3 Basic Constraints:
CA:TRUE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
FD:CB:77:04:C5:54:47:24:06:C0:3E:AC:5D:CC:6C:F2:3F:1E:EA:C6
X509v3 Authority Key Identifier:
keyid:B5:4F:20:EC:C7:CA:B4:A1:AA:1B:F1:B9:91:0F:85:12:2D:59:68:29
Certificate is to be certified until Nov 24 03:25:35 2115 GMT (36500 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated3.3 master 1 slave 1(db 11)にCAの証明書を提供する
scp/etc/pki/CA/cacert.pem 10.100.31.151:/data01/mysql/ssl/cacert_db01.pem
3.4 master 2はslave 1のために証明書を発行する
#master 2(db 02)での操作
cd/root/
openssl ca -in slave_db11.csr -out db02-slave_db11.crt -days 36500
scp db02-slave_db11.crt 10.100.31.151:/data01/mysql/ssl/
3.5 master 2 slave 1(db 11)にCAの証明書を提供する
scp/etc/pki/CA/cacert.pem 10.100.31.151:/data01/mysql/ssl/cacert_db02.pem
4.SLAVE 2のSSL 4.1を配置する.slave 2(db 12)で証明書リクエストを生成する
# mkdir/data01/mysql/ssl
# cd/data01/mysql/ssl/
(umask 077;openssl genrsa -out slave_db12.key 2048)
openssl req -new -key slave_db12.key -out slave_db12.csr -days 36500
scp slave_db12.csr 10.100.31.141:/root
scp slave_db12.csr 10.100.31.142:/root
4.2 master 1はslave 2のために証明書を発行する
#master 1(db 01)での操作
cd/root/
openssl ca -in slave_db12.csr -out db01-slave_db12.crt -days 36500
scp db01-slave_db12.crt 10.100.31.152:/data01/mysql/ssl/
4.3 slave 1(db 12)にCAの証明書を提供する
scp/etc/pki/CA/cacert.pem 10.100.31.152:/data01/mysql/ssl/cacert_db01.pem
4.4 master 2 slave 2の証明書発行
#master 2(db 02)での操作
cd/root/
openssl ca -in slave_db12.csr -out db02-slave_db12.crt -days 36500
scp db02-slave_db12.crt 10.100.31.152:/data01/mysql/ssl/
4.5 master 2 slave 2(db 12)にCAの証明書を提供する
scp/etc/pki/CA/cacert.pem 10.100.31.152:/data01/mysql/ssl/cacert_db02.pem
5.4台のマシンを修正する以下のディレクトリ権限#はそれぞれ4台のマシンで実行
chown -R mysql:mysql/data01/mysql/ssl/
三.mysqlマスターからのSSLの構成
1.master 1で次のパラメータを追加してデータベース#SSLを再起動
ssl
ssl-ca=/data01/mysql/ssl/cacert_db01.pem
ssl-cert=/data01/mysql/ssl/master_db01.crt
ssl-key=/data01/mysql/ssl/master_db01.key
ssl_cipher = DHE-RSA-AES256-SHA
2.master 2中同上#SSL
ssl
ssl-ca=/data01/mysql/ssl/cacert_db02.pem
ssl-cert=/data01/mysql/ssl/master_db02.crt
ssl-key=/data01/mysql/ssl/master_db02.key
ssl_cipher = DHE-RSA-AES256-SHA
3.手動接続試験mysql-uslave-p-h 10.100.31.141 --ssl=1 --ssl_ca='/data01/mysql/ssl/cacert_db01.pem'
MariaDB [(none)]> status;
--------------
mysql Ver 15.1 Distrib 10.1.8-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 108
Current database:
Current user: [email protected]
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.1.8-MariaDB-log Source distribution
Protocol version: 10
Connection: 10.100.31.141 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 30 min 8 sec
MariaDB [(none)]> show variables like '%ssl%';
+---------------------+------------------------------+
| Variable_name | Value |
+---------------------+------------------------------+
| have_openssl | NO |
| have_ssl | YES |
| ssl_ca | /data01/mysql/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /data01/mysql/ssl/master.crt |
| ssl_cipher | DHE-RSA-AES256-SHA |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /data01/mysql/ssl/master.key |
| version_ssl_library | YaSSL 2.3.8 |4.主従構成SSL slave 1の設定:
CHANGE MASTER TO
MASTER_HOST='10.100.31.141',
MASTER_USER='slave',
MASTER_PASSWORD='slave',
MASTER_USE_GTID=slave_pos,
master_ssl=1,
master_ssl_ca='/data01/mysql/ssl/cacert_db01.pem',
master_ssl_cert='/data01/mysql/ssl/db01-slave_db11.crt',
master_ssl_key='/data01/mysql/ssl/slave_db11.key';
start slave;
MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.100.31.141
Master_User: slave
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000030
Read_Master_Log_Pos: 829
Relay_Log_File: relay-bin.000003
Relay_Log_Pos: 19067946
Relay_Master_Log_File: mysql-bin.000015
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: 19067654
Relay_Log_Space: 26107784
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /data01/mysql/ssl/cacert_db01.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /data01/mysql/ssl/db01-slave_db11.crt
Master_SSL_Cipher:
Master_SSL_Key: /data01/mysql/ssl/slave_db11.key
Seconds_Behind_Master: 768062
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: 11
Master_SSL_Crl: /data01/mysql/ssl/cacert_db01.pem
Master_SSL_Crlpath:
Using_Gtid: Slave_Pos
Gtid_IO_Pos: 0-11-131063
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
1 row in set (0.00 sec)マスター2に切り替えてみます.
stop slave;
CHANGE MASTER TO
MASTER_HOST='10.100.31.142',
MASTER_USER='slave',
MASTER_PASSWORD='slave',
MASTER_USE_GTID=slave_pos,
master_ssl=1,
master_ssl_ca='/data01/mysql/ssl/cacert_db02.pem',
master_ssl_cert='/data01/mysql/ssl/db02-slave_db11.crt',
master_ssl_key='/data01/mysql/ssl/slave_db11.key';
start slave;
確認検査の結果、同様に問題ありませんでした.
四.その他の説明1.SLAVE 2も同様の方法で接続テストを行っていますが、問題ありません.
2.本文は1つの内容を無視して、2つのmasterの間は互いに主備の関係で、現在の基礎の上でSSLを加えることができて、実際にテストして、問題がありません.記事の読み取りに影響し、実際にシーンを使用していない場合に限ってリストされません.