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

This article will explain in detail how to use SSH encryption MySQL copy, Xiaobian thinks it is quite practical, so share it with you as a reference, I hope you can gain something after reading this article.

In fact, MySQL is very popular because it is free and open source, and MySQL database has more detailed documentation and built-in support of MySQL replication.

But security administrators are quick to point out one flaw: encryption. The government has strict data privacy requirements, and copying data over a local area network or wide area network MySQL requires encryption.

Although MySQL can be compiled to support SSL, many binary distributions do not enable it. Open an SQL prompt and type the command "show variables like ' %ssl%." If "have_ssl" or "have_openssl" is set to "No," unfortunately this feature is not activated. Fortunately, we have another option to recompile from source code. Secure Shell (SSH) supports data-tunneling, which creates a VPN-like mini-environment to provide transparent encryption. First, we will establish an SSH tunnel using a username/password. We will authenticate the remote by using RSA keys. Once the tunnel is up and running, we will set up data replication.

establish a tunnel

SSH tunneling uses port forwarding techniques to connect to a TCP port on the slave server, which in this example is 7777, which is forwarded via SSH to TCP port 3306 on the master MySQL server. Make sure that SSH tunneling on the MySQL master server side is activated, which is normally activated by default. On the MySQL slave server, execute the following command "ssh -f user@master_ip -L 7777:master_ip:3306 -N." Replace user and master_ip, respectively, with a system user account and IP address from the master server. You may want to use a user who is only used for MySQL replication, with his shell set to/bin/false. Alternatively, you can replace 7777 with any available port on the slave server. On the main database side, you will be prompted for your user ID and password.

Now run " -h 127.0.0.1-P 7777" from MySQL1 slave to connect to MySQL master. Remember not to use localhost because it has a different meaning in MySQL. If desired, append "-u -p" to this command to specify a MySQL account and password. If you get a "permission denied" message, you need to check the authorization statement on the MySQL master server.

The authorization statement should be tied to the host computer's real IP address because it is the real source address of the MySQL1 connection being forwarded. Forwarded connections are not from localhost or 127.0.0.1.

set up replication

Now that the tunnel has been built, it's time to set up the data copy. This process is exactly the same as a typical MySQL replication setup. 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 duplicate account. In MySQL, run the following query:

CREATEUSER’replicationuser’@'master_ip’IDENTIFIEDBY’replicationpassword’; GRANTREPLICATIONSLAVEON*.* TO’replicationuser’@'master_ip’;

On the slave 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 MySQL service on both master and slave servers. For newly created replication environments, you may need to manually copy the database to the slave server. Refer to MySQL Guide (Section 16.1) for details on creating data snapshots and more replication options. When this is done, check to see if MySQL replication works.

Execute a "select" query on both the master and slave servers; the return result should be the same. Perform insert, update, or delete data on the primary server to change the recordset for which "select" returns results. Wait a few seconds, then re-execute the "select" query. if replication is in effect, that result obtained on the mast and slave servers 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 a watchdog shell script to ensure that the SSH channel is active and will automatically restart if the channel fails. Also, consider creating a Cron daemon on the master server to update the data table with the current unix timestamp.

A Cron daemon can be added from the server to check this value. If it lags too far behind the current timestamp, replication may have been broken and administrators should receive an alert email.

Clarify two important things about MySQL replication

There are two important things to remember about MySQL replication. First, its primary purpose is disaster recovery and high availability, not backup. Every data change statement executed on the master server is repeated on the slave server side. So if you inadvertently type "Delete FROM mytables" and forget to use the WHERE clause to qualify the scope, your data will be lost on both the master and slave servers.

The second thing to remember is that you can create, update, and delete data from the server. I have met developers who created applications running on both master and slave servers to achieve high availability and updated a replicated table, each time breaking MySQL replication functionality.

So you need to add checking logic to your application to check if it is running on an inactive slave system and not to write data to copied tables. and then run developer unit tests on it.

About "how to use SSH encryption MySQL copy" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it to let more people see.

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.