Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to establish SSH encrypted MySQL replication". In daily operation, I believe many people have doubts about how to establish SSH encrypted MySQL replication. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "how to establish SSH encrypted MySQL replication". Next, please follow the editor to study!

For administrators, MySQL has many attractions, such as its free and open source, detailed documentation and built-in support for data replication. But security administrators are quick to point out one flaw: encryption. The government has extremely strict requirements for data privacy protection, and data replication through the local area network or wide area network needs to be encrypted.

Although you can compile MySQL to support SSL, many binary distributions do not activate this feature. Open a SQL prompt and type the command "show variables like% ssl%". If "have_ssl" or "have_openssl" is set to "No", unfortunately the feature is not activated. Fortunately, we have another option to recompile from the source code. Secure shell (ssh) supports data tunneling (data-tunneling), which can create a VPN-like mini environment to provide transparent encryption. First, we will establish a ssh tunnel with a username / password. We will authenticate the remote end by using the RSA key. Once the tunnel is running properly, we will set up data replication.

Build a tunnel

The ssh tunnel uses port forwarding technology to connect to a TCP port on the secondary server, which in this example is 7777 and is forwarded to TCP port 3306 on the primary MySQL server via ssh. Make sure that the ssh tunnel on the MySQL primary server is activated, which is generally active by default. On the MySQL secondary server, execute the following command "ssh-f-L 7777:master_ip:3306-N". Replace user and master_ip with a system user account and IP address of the primary server, respectively. You may want to use a user for data replication only and set their shell to / bin/false. In addition, you can replace 7777 with any available port on the server. On the main database side, you will be prompted to enter the user account number and password.

Now run "MySQL-h 127.0.0.1-P 7777" from the MySQL1 slave to connect to the MySQL master server. Remember that you can't use localhost, because it means something else in MySQL. If necessary, you can specify a MySQL account and password by appending "- u-p" to the command. If you get a "permission denied" message, you need to check the authorization statement on the MySQL master server. The authorization statement should be bundled to the real IP address of the host computer because it is the real source address of the forwarded MySQL1 connection. The forwarded connection is not from localhost or 127.0.0.1.

Set up replication

Now that the tunnel has been built, it's time to set up data replication. This process is exactly the same as typical MySQL replication settings. Edit the my.cnf file on the primary server and add the following two lines of code:

Log-bin=MySQL-bin

Server-id=1

Next, create a copy account. In MySQL, run the following query statement:

CREATE USER IDENTIFIED BY replicationpassword

GRANT REPLICATION SLAVE ON *. * TO

On the secondary server side, add the following code to the my.cnf file:

Server-id = 2

Master-host = 127.0.0.1

Master-user = replicationuser

Master-password = replicationpassword

Master-port = 7777

Restart the MySQL service on the master and slave servers. For the newly created replication environment, you may need to manually copy the database to the slave server. Refer to the MySQL Guide (Section 16.1) for more details on creating data snapshots and more replication options. When all this is done, check to see if the replication is valid. Execute a "select" query on the master server and the slave server respectively; the results should be the same. Execute insert, update, or delete data on the primary server, changing the recordset of the results returned by "select". After waiting a few seconds, re-execute the "select" query. If the replication function is already in effect, the result from the master from the server should still be the same.

You may want to use a pre-shared RSA key instead of having to type a password. By using the key, you can set up the watchdog shell script to ensure that the ssh channel is active and will automatically restart if the channel fails. In addition, consider creating a Cron daemon on the primary server to update the data table with the current unix timestamp. A Cron daemon that checks this value can be added from the server. If it lags behind the current timestamp is too large, the replication function may have been compromised, and the administrator should receive an alarm email.

Identify two important points related to MySQL replication

There are two important things to keep in mind about MySQL replication. First, its main purpose is disaster recovery and high availability, not backup. Each data change statement executed on the master server is repeated on the slave server. So if you inadvertently type in the "DELETE FROM mytables" statement and forget to use the WHERE clause to limit the scope, your data will be lost on both the master and slave servers.

The second thing to keep in mind is that you can create, update, and delete data from the server. I have met developers who, in order to achieve high availability, create applications that run on both master and slave servers, and update a replicated table, destroying replication each time. So you need to add check logic to your application to see if it is running on an inactive slave system, and do not write data to the replicated table. Then do a developer unit test on it.

At this point, the study on "how to establish SSH encrypted MySQL replication" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.