MySQL can be connected locally, and the remote connection fails.

Source: Internet
Author: User

MySQL can be connected locally, and the remote connection fails.

I have encountered the problem that MySQL can be connected locally but cannot be connected remotely. I have not recorded the problem. I encountered this problem on the new server applied for on ucloud today. I will record the solution process.

1. Eliminate network or firewall Problems

Check whether you can ping the remote server and ping 192.168.1.211. Then, check whether the port is blocked by the firewall. telnet 192.168.1.211 3306. If the connection fails, configure the firewall.

Configure the firewall to enable port 3306
Vi/etc/sysconfig/iptables
-A input-m state-state NEW-m tcp-p tcp-dport 3306-j ACCEPT (allow port 3306 to pass the firewall)
/Etc/init. d/iptables restart (restart the firewall to make the configuration take effect)

2. Check MySQL Configuration

If the firewall is enabled, telnet still fails. Use netstat to check the port status of port 3306:
Netstat-apn | grep 3360
Tcp6 0 0 127.0.0.1: 3306: * LISTEN 13524/mysqld

Pay attention to the red area, which means that 3306 is bound to the local device. Check the configuration of my. cnf. You can bind an IP address here.
Bind-address = addr
If this parameter is not set or the IP address is 0.0.0.0, all client connections are monitored.

Ps: After I enable port 3306 and check the MySQL configuration, telent still fails, but the local telnet is OK, and I have repeatedly confirmed that the configuration is correct. Later I mentioned it to our ucloud account administrator to know that port 3306 needs to be enabled in the ucloud management background, so I should pay attention to this with ECS.

3. Check User Access Permissions

When a MySQL user is created, a host is specified. The default value is 127.0.0.1/localhost. Therefore, this user can only access the machine. If other machines use this user account to access the database, the system prompts that the user has no permission and the host is changed to %, allow access from all machines.
# Mysql-u root-p
Mysql> use mysql;
Database changed
Mysql> update user set host = '%' where user = 'root ';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

Mysql> select host, user from user;
+ ---- + --- +
| Host | user |
+ ---- + --- +
| 127.0.0.1 | root |
| % | Night |
+ ---- + --- +
2 rows in set (0.00 sec)

Finally, do not forget to restart mysql to make the configuration take effect.
#/Etc/init. d/mysql restart

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.