Master-Slave replication of MySql based on ssl secure connection

Master-Slave replication of MySql based on ssl secure connection

I. equipment environment

Two centos7.2 sets

MySQL 5.7

MySQL 5.7 main features:

Native support for Systemd

Better performance: better optimization for multicore CPU, solid state drives, and locks

Better InnoDB storage engine

More robust replication: replication brings a solution that does not lose data at all, and traditional financial customers can also choose to use MySQL databases.

New sys library: this will be the most frequently accessed library by DBA in the future

Better optimizer: the meaning of optimizer code refactoring will bring huge improvements in this and later versions, and Oracle officials are solving the biggest problem before MySQL: native JSON type support (JavaScript Object Notation).

Note: JSON (JavaScript Object Notation) is a lightweight data exchange format. JSON uses a completely language-independent text format, but also uses conventions similar to the C language family (including C, C++, C #, Java, JavaScript, Perl, Python, and so on). These features make JSON an ideal data exchange language. It is easy for people to read and write, and it is also easy to be parsed and generated by machines (generally used to increase the network transmission rate).

The JSON syntax is a subset of the syntax represented by JavaScript objects.

L data in key-value pair

L data is separated by commas

L curly braces save object

L square brackets save array

Second, implement master-slave replication based on ssl secure connection.

1) create a SSL/RSA file in the master MySQL

Give server-key.pem permission

Log in to mysql and execute mysql > show variables like'%ssl%'

After the operation on the main mysql is completed, a copy account is generated: REQUIRE SSL

Enable binary logging and restart the mysql service on the primary mysql

View the status of the primary mysql

Note: to keep in mind the values of file and position shown in the figure above, the configuration is required from the server

Firewall allows 3306/tcp communication

2) then go to configure from above

From the / etc/my.cnf file contents of mysql

The certificate generated by the master mysql is given to the slave server

View certificates copied from mysql

Continue to configure SSL on the slave: modify the / etc/my.cnf file to add the following

Restart the mysqld service

Check to see if SSL is supported:

Try connecting to the master server with SSL on the slave mysql before configuring master-slave replication:

SSL test connection is successful, and the SSL protocol logged in is: Cipher in use is DHE-RSA-AES256-SHA

Finally, configure master-slave replicate and log in to slave mysql

Change master to from the top

Test:

From the server

The above synchronization is successful.

Summary:

SSL (Secure Sockets Layer secure Sockets layer) and its successor Transport layer Security (Transport Layer Security,TLS) are security protocols that provide security and data integrity for network communications. Replication is transmitted in clear text by default, and data security can be greatly improved by SSL encryption.