Why Can't I Connect to My MySQL Server Remotely Even with a Wildcard Hostname?

Connecting to MySQL Remotely: Troubleshooting Access Denied

Problem Statement:

You've created a user with a wildcard hostname ('%'), but you're unable to connect remotely using that user.

Explanation:

While creating a user with a wildcard hostname allows connections from any host, it requires additional configuration on the MySQL server.

Solution:

1. Configure MySQL Binding:

   Edit your MySQL configuration file (my.cnf on Linux, my.ini on Windows) and add the following line to bind port 3306 to your machine's IP address:

   bind-address = xxx.xxx.xxx.xxx

2. Create Users with both 'localhost' and '%' Hosts:

   Create the user with both 'localhost' and '%' hostnames:

   CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
   CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

3. Grant Permissions:

   Grant permissions on all databases for both localhost and wildcard hostnames:

   GRANT ALL ON *.* TO 'myuser'@'localhost';
   GRANT ALL ON *.* TO 'myuser'@'%';

4. Flush Privileges and Open Port:

   Flush the privileges to apply the changes:

   FLUSH PRIVILEGES;

   Depending on your operating system, you may need to open port 3306 in your firewall to allow remote connections.

The above is the detailed content of Why Can't I Connect to My MySQL Server Remotely Even with a Wildcard Hostname?. For more information, please follow other related articles on the PHP Chinese website!