Tutorial on how to remotely connect to MySQL database under Linux system

Preface

I recently encountered this requirement at work, and it took me more than an hour to get the remote connection established. A local computer and a cloud server, both are Linux systems. Let's take a look at the detailed introduction:

step

1. Enable remote access on the server

First enter the mysql database, and then enter the following two commands:

grant all privileges on *.* to 'root'@'%' identified by 'password';
flush privileges;

The first * is the database, which can be changed to the name of the database that is allowed to be accessed

The second is the database table name, which means that access to any table is allowed.

root represents the user name used for remote login, which can be customized

% means that any IP is allowed to log in. If you want to specify a specific IP, you can replace it with %.

Password represents the password used for remote login, which can be customized

flush privileges; This makes the privileges take effect immediately

2. Modify the my.cnf configuration file

This is the mysql configuration file. If you can't find it, you can enter find /* -name my.cnf to find it.

Edit the file using vim, find the line bind-address = 127.0.0.1 , add a # in front of it to comment it out, save the file and exit.

3. Restart the service

service mysql restart

4. Remote connection locally

Type in the terminal:

mysql -h server ip address -P 3306 -u root -p

Then enter your password.

root is the username set in point 1, and the password is also the password set in point 1

Some details

I found a lot of articles online, saying that I need to open port 3306 to connect, but I still can't connect after I opened it. Later, I found some articles saying that I need to change my.cnf, which is the second point above. After changing it, restart the server.

I just tried it on another server. The port was not configured. After following the above three steps, I was able to connect quickly.

So the second point is very important. Basically everyone will configure that file when installing MySQL because the character set needs to be configured. So the file must exist. Just use the find command to find it.

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.