MySQL基於SSL協定停止主從復制的具體操作教程。本站提示廣大學習愛好者：（MySQL基於SSL協定停止主從復制的具體操作教程）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL基於SSL協定停止主從復制的具體操作教程正文
當mysql逾越互聯網停止復制時他人可以盜取到mysql的復制信息，這些信息是明文的，是以存在不平安性，這裡經由過程ssl對復制的信息停止加密。當在客戶沒有固定ip而要拜訪辦事器時，mysql要許可隨意率性地址的拜訪，辦事端和客戶端經由過程證書驗證可以避免暴力破解。
開端之前讓我們先往返顧一下SSL協定客戶端OpenSSL的裝置進程：
裝置openssl
mkdir /test/setup cd /test/setup tar zxvf openssl-0.9.8b.tar.gz cd openssl-0.9.8b ./config make && make install
開啟mysql中ssl功效
登錄Mysql檢查
mysql> show variables like '%ssl%';
+---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | +---------------+----------+
假如mysql輸入如上所述，那末持續操作開啟ssl；假如不是，從新編譯裝置mysql，留意生成makefile時填寫參數准確。
加入mysql，編纂/etc/my.cnf
在[mysqld]和[mysqldump]之間，參加以下設置裝備擺設信息：
ssl
保留後從新啟動mysql，再次登錄mysql
mysql -uroot -p mysql> show variables like '%ssl%';
+---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | +---------------+-------+
好了，上面進入正題：
mysql基於ssl復制
1、創立證書中間
在主辦事器上創立證書中間
cd /etc/pki/CA
生成私鑰
(umask 077;openssl genrsa -out /etc/pki/CA/private/cakey.pem 2048)
生成自簽證書,因為須要輸出年夜量用戶信息，是以編纂證書的設置裝備擺設文件，在公有的CA上創立證書要留意一切的用戶信息要和CA中的分歧，從國度到部分都要雷同，不然會形成證書沒法應用
vim /etc/pki/tls/openssh.cnf
[ req_distinguished_name ] countryName = Country Name (2 letter code) countryName_default = CN countryName_min = 2 countryName_max = 2 stateOrProvinceName = State or Province Name (full name) stateOrpovinceName_default = FJ localityName = Locality Name (eg,city) localityName = FZ O.organizationName = Organization Name (eg,company) O.organizationName_default = zdz organizationalUnitName = Organizational Unit Name (eg,section) organizationalUnitName_default = zdz
生成自簽證書
openssl req -new -x509 -key /etc/pki/CA/private/cakey.pem -out /etc/pki/CA/cacert.pem -days 3650
-x509是創立自簽證書是須要的參數，在創立其他證書時不克不及加該參數
因為是自簽證書是以要修正證書途徑
vim /etc/pki/tls/openssl.cnf
[ CA_defalut ] dir = /etc/pki/CA certs = $dir/certs #寄存生成證書的目次 crl_dir = $dir/crl #寄存撤消證書的目次 database = $dir/index.txt #證書的索引文件 new_certs_dir = $dir_newcerts #新簽的證書目次 serial = $dir/serial #序列號 crl = $dir/crl.pem private_key = $dir/private/cakey.pem #證書中間私鑰文件
創立證書編號
mkdir certs crl newcerts touch index.txt echo 00 > serial
2、為主辦事器創立證書
辦事器的稱號必需固定，在請求證書時要輸出辦事器稱號，證書和辦事器稱號對應
創立私鑰
mkdir /usr/local/mysql/ssl cd /usr/local/mysql/ssl (umask 077;openssl genrsa -out /usr/local/mysql/ssl/master.key 2048)
生成證書請求
openssl req -new -key master.key -out master.csr
在證書辦事器上對master的證書停止簽發
openssl ca -in master.csr -out master.crt -days 365
3、創立從辦事器證書
(umask 077;openssl genrsa -out /usr/local/mysql/ssl/slave.key 2048) openssl req -new -key slave.key -out slave.csr
將從辦事器的證書請求文件復制到證書辦事器長進行簽發
opessl ca -in slave.csr -out slave.crt -days 356
4、修正證書權限和mysql設置裝備擺設文件
將證書的公鑰cacert.pem復制到主從辦事器的目次下
cd /usr/local/mysql/ssl cp /etc/pki/CA/cacert.pem ./ chown -R mysql:mysql master.crt master.key cacert.pem chmod 600 master.crt master.key cacert.pem vim /usr/local/mysql/my.cnf ssl ssl_ca = /usr/local/mysql/ssl/cacrt.pem ssl_cert = /usr/local/mysql/ssl/master.crt ssl_key = /usr/local/mysql/ssl/master.key
修正從辦事器設置裝備擺設
cd /usr/local/mysql/ssl cp /etc/pki/CA/cacert.pem ./ chown -R mysql:mysql slave.crt slave.key cacert.pem chmod 600 slave.crt slave.key cacert.pem vim /usr/local/mysql/my.cnf ssl ssl_ca = /usr/local/mysql/ssl/cacrt.pem ssl_cert = /usr/local/mysql/ssl/slave.crt ssl_key = /usr/local/mysql/ssl/slave.key
5、在主辦事器上創立復制用戶
grant replication slave on *.* to slave@'192.168.216.133' identified by 'slave' requere ssl; flush privileges;
檢查主辦事器以後二進制地位
mysql> show master status ;
+-------------------------+------------+---------------------+--------------------------+--------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------------+------------+---------------------+--------------------------+--------------------------+ | mysql-bin.000007 | 1015 | | | | +-------------------------+------------+---------------------+--------------------------+---------------------------+ 1 row in set (0.00 sec)
6、在從辦事器上開端復制
change master to master_host='192.168.216.132', master_user='slave', master_password='slave', master_log_file='mysql-bin.000007', master_log_pos=1015, master_ssl=1, master_ssl_ca=' /usr/local/mysql/ssl/cacrt.pem', master_ssl_cert='/usr/local/mysql/ssl/slave.crt', master_ssl_key='/usr/local/mysql/ssl/slave.key'; start slave;
檢查狀況
毛病1：
假如要確包管書沒有成績可以經由過程樹立測試的用戶同ssl停止銜接在主辦事器上開一個權限很年夜的用戶，停止ssl的登錄測試
grant all privileges on *.* to root@'192.168.216.133′ identified by ‘root' require ssl; [root@slave ssl]# mysql -uroot -proot -h192.168.216.133 –ssl-ca=cacrt.pem –ssl-cert=slave.crt –ssl-key=slave.key
Warning: Using a password on the command line interface can be insecure. ERROR 2026 (HY000): SSL connection error: ASN: before date in the future
這是因為虛擬的時光不准確招致
假如這時候候不應用ssl方法停止銜接則會報失足誤
[root@slave ssl]# mysql -uroot -proot -h192.168.216.133;
Warning: Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user ‘root'@'192.168.216.132′ (using password: YES)
毛病2：
在設置裝備擺設文件中添加證書設置裝備擺設後履行 show variables like ‘%ssl%'顯示
這是因為未將證書的屬主改成mysql，可以從日記中得知是無權限獲得私鑰
