Why Doesn't MySQL's Wildcard Host '%' Automatically Grant Remote Access?

Remote Access for MySQL Users

When attempting to establish a remote connection to a MySQL database using the user user@'%' and the password password, the connection fails despite the wildcard character % denoting access from any host.

Reason for Failure

The wildcard character % in a MySQL user's host field does not automatically grant remote access. To connect remotely, the following steps are necessary:

1. Bind MySQL to Machine's IP Address: Configure MySQL to bind port 3306 to your machine's IP address in my.cnf (or my.ini on Windows) as follows:

   bind-address        = xxx.xxx.xxx.xxx

2. Create User in Localhost and Wildcard: Create the user in both localhost and % wildcard hosts using the following commands:

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

3. Grant Permissions: Grant full permissions on all databases to the user from both localhost and wildcard:

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

Additional Considerations

• Depending on your operating system, you may need to open port 3306 to allow remote connections.
• Ensure that the MySQL configuration includes the skip-name-resolve option to prevent DNS lookups.

• Test the remote connection with the following command:

  mysql -h <hostname> -u myuser -pmypass

The above is the detailed content of Why Doesn't MySQL's Wildcard Host '%' Automatically Grant Remote Access?. For more information, please follow other related articles on the PHP Chinese website!