MariaDB數據庫基于SSL實現遠程訪問和主從復制
實驗環境
系統環境：Centos6.5
數據庫版本：5.5.36-MariaDB-log MariaDB Server
虛機數量：2
方案實施：
1，配置CA；并為node1和node2生成key和證書
2，在node1和node2上安裝Mariadb
3，配置節點1為MariaDB主節點
4，配置節點2為MariaDB從節點
5，配置節點1和節點2主從復制模式
6，配置節點1和節點2支持ssl
7，在node1上新建ssl模式的用戶
8，node2已ssl的方式訪問數據庫node1
9，node2以ssl的方式連接數據庫，并設置支持主從復制默認
10，Windows下通過“Navicat for MySQL”以ssl的方式連接訪問node1
1，配置CA；并為node1和node2生成key和證書
確認系統已安裝openssl組件，如果未安裝，通過以下方式安裝并查看
# yum install openssl -y
# rpm -ql openssl
/etc/pki/CA
/etc/pki/CA/certs
/etc/pki/CA/crl
/etc/pki/CA/newcerts
/etc/pki/CA/private
/etc/pki/tls
/etc/pki/tls/certs
/etc/pki/tls/certs/Makefile
/etc/pki/tls/certs/make-dummy-cert
/etc/pki/tls/certs/renew-dummy-cert
/etc/pki/tls/misc
/etc/pki/tls/misc/CA
/etc/pki/tls/misc/c_hash
/etc/pki/tls/misc/c_info
/etc/pki/tls/misc/c_issuer
/etc/pki/tls/misc/c_name
/etc/pki/tls/openssl.cnf
/etc/pki/tls/private
/usr/bin/openssl
......
修改openssl.cnf的配置文件，修改幾個地方
# vim /etc/pki/tls/openssl.cnf
countryName_default = CN 國家
stateOrProvinceName_default = BEIJING 城市
localityName_default= Tongzhou 區縣
0.organizationName_default = TEST 公司
organizationalUnitName_default = IT 部門
node1以CAserver的身份自建CA證書
# cd /etc/pki/CA
# (umask 077;openssl genrsa -out private/cakey.pem 2048)
# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650
# touch {index.txt,serial}
# echo 01 > serial
給node1生成key，證書請求和證書
# cd /etc/pki/CA
# (umask 077;openssl genrsa -out master.key 2048)
一路回車即可
# openssl req -new -key master.key -out master.csr
需要點兩次y確認
# openssl ca -in master.csr -out master.crt -days 3650
給node2生成key，證書請求和證書
# cd /etc/pki/CA
# (umask 077;openssl genrsa -out slave.key 2048)
一路回車即可
# openssl req -new -key slave.key -out slave.csr
需要點兩次y確認
# openssl ca -in slave.csr -out slave.crt -days 3650
2，在node1和node2上安裝Mariadb
使用軟件安裝包為mariadb-5.5.36-linux-x86_64.tar.gz，上傳至root目錄下，安裝配置Mairadb，并設置mydata目錄為數據和日志存放目錄；以下步驟在node1和node2上一致
# cd /root
# tar -xzf mariadb-5.5.36-linux-x86_64.tar.gz -C /usr/local/
# ln -sv mariadb-5.5.36-linux-x86_64 mysql
# groupadd=mysql
# useradd=mysql -g mysql
# mkdir /mydata/{data,binlog,relaylog} -pv
# chown -R mysql.mysql /mydata/*
# mkdir /etc/mysql
# cp /usr/local/mysql/support-files/my-large.cnf /etc/mysql/my.cnf
修改/etc/mysql/my.cnf
# vim /etc/mysql/my.cnf 最后一行增加以下語句
datadir=/mydata
# chown -R mysql.mysql /etc/mysql/*
# echo "export PATH=$PATH:/usr/local/mysql/bin" > /etc/profile.d/mysql.sh
# source /etc/profile.d/mysql.sh
# mkdir /var/lib/mysql/
# chown -R mysql.mysql /var/lib/mysql/
# cd /usr/local/mysql
# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
# chmod +x /etc/init.d/mysqld
# scripts/mysql_install_db --user=mysql --datadir=/mydata/data
# service mysqld restart
3，配置節點1為MariaDB主節點
在node1部署為MariaDB的master節點，修改node1的配置文件，增加以下內容
# vim /etc/mysql/my.cnf server-id=1 設置server-id號為1 log-bin=/mydata/binlog/master-binlog 是此服務器支持binlog
4，配置節點2為MariaDB從節點
# vim /etc/mysql/my.cnf server-id=1 設置server-id號為2 relay-log=/mydata/relaylog/relay-bin 是此服務器支持relaylog
5，配置節點1和節點2主從復制模式
在node1節點上，新建一個用戶nossl@'%.%.%.%'，密碼nossl，并授權所有訪問權限 # mysql MariaDB [(none)]> grant all on *.* to nossl@'%.%.%.%' identified by "nossl"; Query OK, 0 rows affected (0.06 sec) MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 2682 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.02 sec) 在node2節點上，使用此賬戶登錄，并設置同node1實現主從復制 # mysql -unossl -hnode1 -pnossl mysql> CHANGE MASTER TO MASTER_HOST='nod1',MASTER_USER='nossl',MASTER_PASSWORD='nossl',,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=2682; Query OK, 0 rows affected (0.25 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: node1 Master_User: nossl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 2682 Relay_Log_File: zabbix-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ..... Master_Server_Id: 1 1 row in set (0.00 sec)
6，配置節點1和節點2支持ssl
在node1上，新建文件夾用于存放ca證書和node1自己的key+證書 # mkdir /etc/mysql/ssl # cp /etc/pki/CA/private/cakey.pem /etc/mysql/ssl # cp /etc/pki/CA/master.key /etc/mysql/ssl # cp /etc/pki/CA/master.crt /etc/mysql/ssl # chown -R mysql.mysql /etc/mysql* 修改mysql的配置文件，使mysql支持ssl模式 # vim /etc/mysql/my.cnf ssl 開啟SSL功能 ssl-ca = /etc/mysql/ssl/cacert.pem 指定CA文件位置 ssl-cert = /etc/mysql/ssl/master.crt 指定證書文件位置 ssl-key = /etc/mysql/ssl/master.key 指定密鑰所在位置 在node2上，新建文件夾用于存放ca證書和node2自己的key+證書 # mkdir /etc/mysql/ssl # scp node1:/etc/pki/CA/private/cakey.pem /etc/mysql/ssl # cp node1:/etc/pki/CA/slave.key /etc/mysql/ssl # cp node1:/etc/pki/CA/slave.crt /etc/mysql/ssl # chown -R mysql.mysql /etc/mysql* # vim /etc/mysql/my.cnf ssl 開啟SSL功能 ssl-ca = /etc/mysql/ssl/cacert.pem 指定CA文件位置 ssl-cert = /etc/mysql/ssl/slave.crt 指定證書文件位置 ssl-key = /etc/mysql/ssl/slave.key 指定密鑰所在位置
在node1和node2上經過以上配置后，需要重啟mysqld服務，并通過以下方式查看
# service mysqld restart # mysql mysql> show variables like "%ssl%"; +---------------+---------------------------+ | Variable_name | Value | +---------------+---------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca| /etc/mysql/ssl/cacert.pem | | ssl_capath| | | ssl_cert | /etc/mysql/ssl/mysql.crt | | ssl_cipher| | | ssl_key | /etc/mysql/ssl/mysql.key | +---------------+---------------------------+ 7 rows in set (0.00 sec)
7，在node1上新建ssl模式的用戶
MariaDB [(none)]> grant all on *.* to 'ssluser'@'%.%.%.%' identified by 'sslpass' require ssl;
8，在node2以ssl的方式訪問數據庫node1
# mysql -ussluser -psslpass -hnode1 --ssl-ca=/etc/mysql/ssl/cacert.pem --ssl-cert=/etc/mysql/ssl/slave.crt --ssl-key=/etc/mysql/ssl/slave.key
9，node2以ssl的方式連接數據庫，并設置支持主從復制默認
在正式環境里，為了安全期間，可以刪除nossl用戶，僅保留ssluser用戶用來遠程連接mysql；
# mysql -ussluser -psslpass -hnode1 --ssl-ca=/etc/mysql/ssl/cacert.pem --ssl-cert=/etc/mysql/ssl/slave.crt --ssl-key=/etc/mysql/ssl/slave.key mysql> CHANGE MASTER TO MASTER_HOST='node1',MASTER_USER='ssluser',MASTER_PASSWORD='sslpass',master_ssl=1,master_ssl_ca='/etc/mysql/ssl/cacert.pem',master_ssl_cert='/etc/mysql/ssl/mysqls.crt',master_ssl_key='/etc/mysql/ssl/mysqls.key',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=26822; Query OK, 0 rows affected (0.23 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.97.47 Master_User: ssluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 44904037 Relay_Log_File: zabbix-relay-bin.00000 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.00000 Slave_IO_Running: Connecting 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: 44904037 Relay_Log_Space: 245 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/mysql/ssl/cacert.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/mysql/ssl/mysqls.crt Master_SSL_Cipher: Master_SSL_Key: /etc/mysql/ssl/mysqls.key Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec) 通過狀態查看，slave已經以ssl的工作模式正常執行同步
10，Windows下通過“Navicat for MySQL”以ssl的方式連接訪問數據庫node1
將node1/etc/mysql/ssl下的幾個文件存放到windows上，此處我放到我的D盤下ssl目錄
注意，在ssl里選擇證書和key的時候，需要先點擊“使用驗證”，等選擇完畢后再勾除“使用驗證”
原創文章，作者：Tuolei，如若轉載，請注明出處：http://www.www58058.com/16374
