Detailed tutorial on MySQL master-slave Replication Based on the SSL protocol,

Detailed tutorial on MySQL master-slave Replication Based on the SSL protocol,

When mysql is replicated across the Internet, others can steal the mysql replication information, which is in plain text and therefore insecure. Here, the copied information is encrypted through ssl. When a customer wants to access the server without a fixed ip address, mysql must allow access from any address. The server and client can use certificate verification to prevent brute-force cracking.

Before starting, let's review the installation process of the SSL protocol client OpenSSL:
Install openssl

mkdir /test/setupcd /test/setuptar zxvf openssl-0.9.8b.tar.gzcd openssl-0.9.8b./configmake && make install

Enable ssl in mysql
Log on to Mysql to view

mysql> show variables like '%ssl%'; 

+---------------+----------+ | Variable_name | Value  | +---------------+----------+ | have_openssl | DISABLED | | have_ssl   | DISABLED | | ssl_ca    |     | | ssl_capath  |     | | ssl_cert   |     | | ssl_cipher  |     | | ssl_key    |     | +---------------+----------+

If the mysql output is as described above, continue to enable ssl. If not, recompile and install mysql. Note that the parameters are correct when makefile is generated.
Exit mysql and edit/etc/my. cnf.
Add the following configuration information between [mysqld] and [mysqldump:

ssl

Save and restart mysql. log on to mysql again.

mysql -uroot -pmysql> show variables like '%ssl%'; 

+---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_openssl | YES  | | have_ssl   | YES  | | ssl_ca    |    | | ssl_capath  |    | | ssl_cert   |    | | ssl_cipher  |    | | ssl_key    |    | +---------------+-------+

Now, let's go to the topic:
Ssl-based mysql Replication
1. Create a certificate Center
Create a certificate center on the master server

cd /etc/pki/CA

Generate Private Key

(umask 077;openssl genrsa -out /etc/pki/CA/private/cakey.pem 2048)

Because a large amount of user information is required to generate a self-signed certificate, edit the certificate configuration file and create a certificate on a private CA. Note that all user information must be consistent with that in the CA, the certificate must be the same from country to department; otherwise, the certificate cannot be used.

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

Self-generated visa document

openssl req -new -x509 -key /etc/pki/CA/private/cakey.pem -out /etc/pki/CA/cacert.pem -days 3650

-X509 is a required parameter for creating a self-signed certificate. You cannot add this parameter when creating other certificates.

Because it is a self-signed certificate, you need to modify the certificate path

vim /etc/pki/tls/openssl.cnf

[CA_defalut] dir =/etc/pki/CA certs = $ dir/certs # directory for storing the certificate generated crl_dir = $ dir/crl # directory for storing the Certificate Revocation database = $ dir/ index.txt # certificate index file new_certs_dir = $ dir_newcerts # New certificate directory serial = $ dir/serial # serial number crl = $ dir/crl. pem private_key = $ dir/private/cakey. pem # certificate center Private Key File

Create certificate ID

mkdir certs crl newcerts touch index.txt echo 00 > serial

2. Create a certificate for the master server
The server name must be fixed. When applying for a certificate, enter the server name. The certificate corresponds to the server name.

Create Private Key

mkdir /usr/local/mysql/ssl cd /usr/local/mysql/ssl (umask 077;openssl genrsa -out /usr/local/mysql/ssl/master.key 2048)

Generate Certificate Application

openssl req -new -key master.key -out master.csr

Issue the master certificate on the Certificate Server

openssl ca -in master.csr -out master.crt -days 365

3. Create a slave server certificate

(umask 077;openssl genrsa -out /usr/local/mysql/ssl/slave.key 2048) openssl req -new -key slave.key -out slave.csr

Copy the certificate application file from the server to the Certificate Server for issuance

opessl ca -in slave.csr -out slave.crt -days 356

4. Modify the certificate permission and mysql configuration file
Copy the certificate's public key cacert. pem to the directory of the Master/Slave server.

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

Modify slave server configuration

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. Create a copy user on the master server

grant replication slave on *.* to slave@'192.168.216.133' identified by 'slave' requere ssl; flush privileges;

View the current binary location of the master server

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. Start copying on the slave server

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;

View status

Error 1:

If you want to ensure that the certificate is correct, you can establish a test user to connect to the master server with ssl and open a user with a high permission to perform the ssl login test.

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

This is because the virtual time is incorrect.
If you do not use ssl for connection at this time, an error will be reported.

[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)

Error 2:

Add the certificate configuration in the configuration file and execute show variables like '% ssl %' to display

This is because the owner of the certificate is not changed to mysql. You can know from the log that you are not authorized to obtain the private key.

Articles you may be interested in:

• How to generate a key using ssl in mysql
• Smooth and seamless installation of Apache, SSL, MySQL, and PHP
• Full installation of apache + mysql + php + ssl servers
• In-depth analysis of semi-synchronous and asynchronous MySQL master-slave replication configurations