Detailed explanation of how to access MySQL through SSH channel

Many times when using Mysql, you will encounter the following situations:

1. The information is important and you want the communication to be encrypted.

2. Some ports, such as port 3306, are disabled by the router.

A more direct solution to the first problem is to change the mysql code, or use some certificates, but this method is obviously not very simple.

Recommended related learning video tutorials: mysql video tutorial

Here we will introduce another method, which is to use the SSH channel to connect to the remote Mysql. The method is quite simple.

1. Establish SSH channel

Just type the following command locally:

ssh -fNg -L 3307:127.0.0.1:3306 myuser@remotehost.com
The command tells ssh to log in to remotehost.com as myuser, go into the background (-f) and not execute any remote command (-N), and set up port-forwarding (-L localport:localhost:remoteport ). In this case, we forward port 3307 on localhost to port 3306 on remotehost.com.

2. Connect to Mysql

Now, you can connect to the remote database locally, just like accessing the local database.

mysql -h 127.0.0.1 -P 3307 -u dbuser -p db
The command tells the local MySQL client to connect to localhost port 3307 (which is forwarded via ssh to remotehost.com:3306). The exchange of data between client and server is now sent over the encrypted ssh connection.

Or use Mysql Query Brower to access the Client's 3307 port.

Similarly, use PHP to access:

<?php
$smysql = mysql_connect( "127.0.0.1:3307", "dbuser", "PASS" );
mysql_select_db( "db", $smysql );
?>
Making It A Daemon
A quick and dirty way to make sure the connection runs on startup and respawns on failure is to add it to /etc/inittab and have the init process (the, uh, kernel) keep it going.
Add the following to /etc/inittab on each client:
sm:345:respawn:/usr/bin/ssh -Ng -L 3307:127.0.0.1:3306 myuser@remotehost.com
And that should be all you need to do. Send init the HUP signal ( kill -HUP 1 ) to make it reload the configuration. To turn it off, comment out the line and HUP init again.

The above is the detailed content of Detailed explanation of how to access MySQL through SSH channel. For more information, please follow other related articles on the PHP Chinese website!