How to open a remote connection in MySQL?

The basic syntax for opening MySQL is grant authorization. There is generally no problem with this. The main reason is that you may still be unable to connect after opening it.

GRANT ALL PRIVILEGES ON 库名.表名 TO 用户名@'IP地址' IDENTIFIED BY '密码' WITH GRANT OPTION;
FLUSH RIVILEGES;
例：GRANT ALL PRIVILEGES ON *.* TO temp@'%' IDENTIFIED BY 'temp123' WITH GRANT OPTION;
授权所有的服务器使用用户名temp密码temp123来连接所有库的所有表执行增产查改的所有操作

It may occur The following error message

　

After confirming the successful authorization (the specific check method is at the bottom of the article), there may be two reasons for the following problem, one is a firewall problem, the other is It is the MySQL configuration that binds the local 127.0.0.1.

1. Firewall problem

The firewall does not open port 3306, causing the remote connection to fail

 1 开放防火墙端口 2     添加需要监听的端口 3     /sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT 4  5     保存设置 6     /etc/init.d/iptables save 7  8     查看状态 9     /etc/init.d/iptables status10 11     临时关闭防火墙服务12     service iptables stop13 14     开启防火墙服务15     service iptables start16 17     开机不再启动防火墙服务18     chkconfig iptables off

2. Binding Local IP problem

MySQL is bound to the local IP of 127.0.0.1

This can be viewed using the command, as shown below

Modify the my.cnf configuration file and add it at line:43 #Comment the statement

Restart MySQL to take effect

　

Check whether the authorized user is created successfully

Successfully authorized users are saved in the user table of MySQL's mysql library. View the table data

use mysql;select Host,User from user\G;

Host % means all servers

127.0 .0.1, localhost is the local server.

You can also use select * to view other specific permissions including addition, deletion, modification, and query permissions.

The above is the detailed content of How to open a remote connection in MySQL?. For more information, please follow other related articles on the PHP Chinese website!