How to access MySQL through SSH Channel

Many times, when using Mysql, you may encounter the following situations: due to the security of the database, the database administrator adds an umbrella for the database When configuring the database, to protect the security and information of users when they connect to the database, the common practice is to configure SSH, that is, to add a security protocol to the database, which also causes users to remotely connect to the database.

Many times, when using Mysql, you may encounter the following situations: due to the security of the database, the database administrator adds an umbrella for the database When configuring the database, to protect the security and information of users when they connect to the database, the common practice is to configure SSH, that is, to add a security protocol to the database, which also causes users to remotely connect to the database.

In many cases, you may encounter the following situations when using Mysql:

For the sake of database security, the database administrator adds an "umbrella" to the database When configuring the database to protect the user's security and information during database connection, the common practice is to configure SSH, that is, to add a security protocol to the database, which also makes it difficult for users to connect remotely.
1. Information is important, and communication is expected to be encrypted.
2. Some ports, such as port 3306, are disabled by the router.

A Direct Solution to the first problem is to change the mysql code or use some certificates. However, this method is obviously not very simple.

Another method is to connect to a remote Mysql through the SSH channel, which is quite simple.

1. Create an SSH Channel

For example:

Remote Mysql Server ip Address: 172.21.20.203

Mysql port: 3307 Username: test password: 123456

SSH channel IP Address: 117.123.520.1 port: 79

Username: admin

Using the navicat for mysql client tool,

The common connection is as follows:

Click connection test: the error box is displayed because the SSH protocol is added! So we need to use the SSH channel, such:

In this case, click connection Test to connect to Test Success.

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

For example, enter:

Ssh-fNg-p 79-L 8989: 172.21.20.203: 3307 admin@117.123.52.161

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

Click Connect and Test Success

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.

You can also use Mysql Query Brower to access port 3307 of the Client.

Similarly, use PHP to access:

$ 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 shoshould 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.