Teach you how to solve the problem of being unable to access Mysql remotely

Nowadays, the use of mysql database has become an unstoppable trend in many Internet companies, so what we often do at the beginning of the project is to find a Linux server, install mysql on it, and then When we start importing our data tables, we cannot always occupy the remote connection to the server, so we often use remote tools to operate the database, but we will also encounter the following situations:

MYSQL CONNECT ERROR – 1130:Host ’202.43.**.**’ is not allowed to connect to this MySQL server“

The root cause of the above connection rejection is that remote login of the connection account is restricted when creating a mysql account. That is to say: except for the current installation server where mysql is located, other IPs (hosts) are Access is not allowed, even though your username and password are correct; in fact, it is not that MySQL does not support remote access by default, but that MySQL's default user root does not support remote access by default. To put it bluntly, it is a permissions issue. The root user's Host is 127.0 by default. 0.1 (localhost) The simplest method is to create a new user to support remote access without modifying root permissions. This is recommended, but if you really need to use root remote operation, there is no way. Here are two methods: Here are some solutions:

① Comment out the [mysqld] section in /etc/mysql/my.cnf and comment out bind-address = 127.0.0.1

② Use mysql -uroot -p to log in mysql, and then use the following method to enable remote access permissions;

#mysql -u root -p
#*******(密码，默认的密码是空)
mysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mysql>flush privileges;

After we execute the code, we can see that the host corresponding to our root user is %, which means that all IP connections are allowed, as shown below ：

Due to the above explanation process, I found that some mysql users do not care much about the default password of mysql being empty, so here is a guide for everyone. The reminder is that mysql has just been installed. After we complete the installation, remember to clear the empty users and change the password in time. The following is a brief record of these two processes, as follows:

Source: MySQL database release version knowledge explanation

mysql -u root -p
mysql>select user,host,password from mysql.user;
mysql>drop user ''@localhost;
mysql>update mysql.user set password = PASSWORD('*********') where user='root';
mysql>flush privileges;

The execution process is as follows:

The above is the detailed content of Teach you how to solve the problem of being unable to access Mysql remotely. For more information, please follow other related articles on the PHP Chinese website!