Solution to the failure of remote connection to MySQL database in Linux configuration

Today I have a question about configuring MySQL database under Linux for remote access. Baidu has a lot of information on this, but the methods are the same. I have tried them all but failed to solve them. I will record them here.

Step 1: Find bind-address = 127.0.0.1 in /etc/mysql/my.cnf

Add a "#" before this line to comment it out, or change it to: bind-address = 0.0.0.0

Allow any IP address to access; or specify the IP address that you need to access remotely.

Then restart mysql: sudo /etc/init.d/mysql restart

Step 2: Authorize users to connect remotely

grant all privileges on *.* to root@"%" identified by "password" with grant option;
 flush privileges;

The first line of command is explained as follows: *.*: the first * represents the database name; the second * represents the table name. This means that all tables in all databases are authorized to the user. If you only want to authorize a certain database or a table under a database, replace * with the database name and table you need. root: Grant the root account. “%”: Indicates that the authorized user IP can be specified, which means that any IP address can access the MySQL database. "password": the password corresponding to the assigned account. Replace the password with your root account password.

The second line of command refreshes the permission information, allowing the permissions we set to take effect immediately.

But in the end, it still cannot be accessed. At this time, it may be a problem with the firewall and MySQL port 3306.

I used the command netstar -a to query all ports but did not see port 3306. I used the command nerstat -an|grep 3306 but nothing was displayed. This means that port 3306 does not exist.

Log in to the Alibaba Cloud console, add a new rule in the cloud service security group, clone a rule and change the port number to 3306 of MySQL. The remote connection still fails again.

At this time, the console needs to restart the system to make the newly added rules take effect. After the restart, the connection still fails. At this time, the local navicat software connection returns an access denied error instead of the previous unable to connect error.

At this time, execute the second step above to authorize the user command again, and after restarting Apache MySQL, the connection is finally successful.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.