Basic steps to use Mysql SSH tunnel connection

Preface

For security reasons, the root user of MySQL can only log in locally and does not have authorized access to the external network. At this time, you can connect to the MySQL database through the SSH tunnel. The following are the basic steps to configure a Mysql SSH tunnel connection.

Let's take a look at the detailed steps.

Deleting Remote Login

Log in to mysql:

 mysql -uroot -p

View the user's open access permissions:

select user, host from mysql.user;

Remove unnecessary access permissions, such as:

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

Query again to verify whether the deletion is successful.

Establishing a Tunnel

The server can be logged in by username and password or RSA key. It is recommended to use RAS key and place the local id_rsa.pub content in ~/.ssh/authorized_keys of the server. How to generate RSA key is not described here.

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

ssh -NCPf [email protected] -L 3388:127.0.0.1:3306

Parameter explanation:

• C Use compression, which is optional and speeds up.
• P Use a non-privileged port for outgoing connections.
• f After SSH completes authentication and establishes port forwarding, it goes into the background.
• N Do not execute the remote command. This parameter is useful when only forwarding ports are opened (supported by SSH V2)

[email protected] is the SSH username and IP address for logging into the MySQL server. -L 3388:127.0.0.1:3306 means opening a mapping from port 3388 on the local machine to port 127.0.0.1:3306 of the MySQL server. 127.0.0.1 can also be the intranet or extranet IP address of the server where MySQL is located.

Then, use the mysql tool to enter the corresponding username and password to log in. Note that when logging in, the host address selected is localhost or 127.0.0.1. At the same time, open the access rights of the corresponding IP in the MySQL database.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.