Basic steps for Mysql SSH tunnel connection

Preface

For security, root users of mysql only log in locally, and do not authorize access to the external network. At this time, mysql database can be connected through SSH tunnel. The following are the basic steps for configuring an Mysql SSH tunnel connection.

Let's not say much below. Let's take a look at the detailed steps

Delete Remote Login

Login to mysql:

 mysql -uroot -p

View the user's open access rights:

select user, host from mysql.user;

Remove unwanted access rights, such as:

delete from mysql.user where user='root' and host='%';
flush privileges;

Query again to verify whether the deletion was successful.

Build a tunnel

The login of the server can be done in two ways: user name and password and RSA secret key. It is recommended to use RAS secret key form, and place the contents of id_rsa. pub of this machine in the ~/. ssh/authorized_keys of the server. How to generate RSA secret key is not described here.

Open a separate window, modify the ip and port corresponding to the following command and execute it:

ssh -NCPf root@192.168.99.52 -L 3388:127.0.0.1:3306

Parameter interpretation:

C uses compression function, which is optional and speeds up. P uses an unprivileged port for outgoing connections. After f SSH is authenticated and port forwarding is established, it is transferred to background operation. N does not execute remote commands. This parameter is useful when only forwarding ports are open (V2 version SSH support)

root @ 192.168. 99.52 is the SSH username and IP address that logs in to the mysql server. -L 3388: 127.0. 0.1: 3306 indicates that port 3388 is mapped locally to port 127.0. 0.1: 3306 of the mysql server. Where 127.0. 0.1 can also be the intranet ip or the extranet ip of the server where mysql is located.

Then, through mysql tool, enter the corresponding user name and password to log in. Note that when logging in, the selected host address is localhost or 127.0. 0.1. At the same time, the access rights corresponding to ip are opened in mysql database.

Summarize