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

   Copy after login

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';

   Copy after login

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'@'%';

   Copy after login

4. Flush Privileges and Open Port:

   Flush the privileges to apply the changes:

   FLUSH PRIVILEGES;

   Copy after login

   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!