MysqlHow-ToBeginner · 3 min read

How to Create User with Remote Access in MySQL

To create a MySQL user with remote access, use CREATE USER 'username'@'host' specifying the remote host or '%' for any host, then grant privileges with GRANT. Finally, run FLUSH PRIVILEGES to apply changes.

📐

Syntax

The basic syntax to create a user with remote access in MySQL involves specifying the username and the host from which the user can connect. Use 'username'@'host' where host can be an IP address, domain name, or '%' to allow any host.

After creating the user, you grant privileges to define what the user can do.

Finally, FLUSH PRIVILEGES reloads the grant tables to apply changes immediately.

sql

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO 'username'@'host';
FLUSH PRIVILEGES;

💻

Example

This example creates a user named remote_user who can connect from any IP address, grants all privileges on the mydb database, and applies the changes immediately.

sql

CREATE USER 'remote_user'@'%' IDENTIFIED BY 'StrongPass123';
GRANT ALL PRIVILEGES ON mydb.* TO 'remote_user'@'%';
FLUSH PRIVILEGES;

Output

Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec)

⚠️

Common Pitfalls

Using 'localhost' instead of '%': This restricts access to local machine only, blocking remote connections.
Not running FLUSH PRIVILEGES: Changes may not take effect until the server reloads privileges.
Firewall or MySQL bind-address settings: Even with correct user setup, remote access can be blocked by firewall or MySQL configured to listen only on localhost.

sql

/* Wrong: user can only connect from localhost */
CREATE USER 'user'@'localhost' IDENTIFIED BY 'pass';

/* Right: user can connect from any host */
CREATE USER 'user'@'%' IDENTIFIED BY 'pass';

📊

Quick Reference

Remember these key points when creating a remote user in MySQL:

Use 'username'@'host' with '%' for any remote host.
Grant only necessary privileges for security.
Run FLUSH PRIVILEGES after changes.
Check MySQL server's bind-address and firewall settings to allow remote connections.

✅

Key Takeaways

Use 'username'@'%' to allow remote access from any host in MySQL.

Always grant only the privileges the user needs for security.

Run FLUSH PRIVILEGES to apply user and permission changes immediately.

Check MySQL server bind-address and firewall to enable remote connections.

Avoid using 'localhost' if remote access is required.