MySQL semi-synchronous replication based on SSL

MySQL master-slave replication scenario is very many, the default MySQL replication is based on asynchronous and plaintext transmission, that is, fast, but the data from the server will have a certain lag, clear text also means that the data transmission is unsafe. So the author here to build a simple based on encryption and semi-synchronous master MySQL, of course, because of its semi-synchronous characteristics, the primary server write operation speed will be reduced. Exactly how you choose it depends on what the scene needs.

Experimental environment: RHEL5.8 MySQL5.5.28

192.168.88.21 master.mos.com Master

192.168.88.22 slave.mos.com Slave

Note: The author of the two-machine trust, but not written here, and the author of the two MySQL default root user password is null, the author of the experimental environment of the/etc/hosts documents, as above.

First, create the CA private authentication and the required key file, operate on the master server (master)

1, use the script to create the CA internal organization, the author puts the script to the network disk, the link is as follows:
http://pan.baidu.com/share/link?shareid=139821&uk=1678158691

./casetup.sh #输入ca的主机名和email或者两次回车即可

2. Create the key files required by master and salve (internal CA needs the same information as country, province, city, company, Department)

# Mkdir-pv/data/{mysql,ssl}  
# Cd/data/ssl #  
(umask 077 OpenSSL genrsa 1024 > Mysql.key)  
# OpenSSL req- New-key mysql.key-out MYSQL.CSR  
# OpenSSL ca-in mysql.csr-out mysql.crt-days 365  
# CP/ETC/PKI/CA/CACERT.PEM .  
# cd/data  
# CP/ETC/PKI/CA/CACERT.PEM.  
# (Umask 077 OpenSSL genrsa 1024 > Slave.key) # OpenSSL Req-new-key slave.key-out slave.csr  
# OpenSSL CA- In Slave.csr-out slave.crt-days 365  
# ssh slave ' mkdir-pv/data/mysql ' 
# SCP slave.* CACERT.PEM-slave:/data/s sl/  
# RM–RF slave.* Cacert.pem

Second, load semi-synchronous support and provide from replication user (at master server operation)

1, the installation of MySQL process reference to the author before the article can:
http://mos1989.blog.51cto.com/4226977/1039653

2, insert Google provided by the Semisync patch module to support the main module, and create replication required users

mysql> INSTALL PLUGIN rpl_semi_sync_master soname ' semisync_master.so ';

Mysql> GRANT REPLICATION CLIENT, REPLICATION SLAVE on *.* to sroot@192.168.88.22 identified by ' 123 ' REQUIRE SSL;

mysql> FLUSH privileges;

Mysql> \q

Ps: If you need to uninstall the Half sync module, execute the following statement in MySQL:
mysql> UNINSTALL PLUGIN Rpl_semi_sync_master;

Third, configure the main service-side configuration file/etc/my.cnf

[Mysqld]

SSL #开启ssl

Server-id = 1 #主ID和从不一致即可

Rpl_semi_sync_master_enabled=1 #使半同步默认开启

rpl_semi_sync_master_timeout=1000 #超过1秒则回到异步模式

Sync_binlog=1 #将sql语句即时写入二进制日志

Ssl_ca=/data/ssl/cacert.pem

Ssl_cert=/data/ssl/mysql.crt

Ssl_key=/data/ssl/mysql.key

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/