Configuring MySQL Remote Root Access to Create Databases

A remote client cannot create a database on a MySQL server because the account it logs in with lacks the global CREATE privilege, and the account that has it (root) exists only as 'root'@'localhost'. Fix it in two places: create a root-equivalent account whose host part is '%' (or the admin PC's address) with GRANT ALL PRIVILEGES ON *.*, and remove or widen bind-address in the server configuration so the listener accepts connections from that address. Per-schema grants on individual databases will never let a client create a new one, because CREATE DATABASE is evaluated against global privileges.

What do the symptoms tell you before you change anything?

Look at the trend first. The signal chain for a MySQL login is: the client sends a user name plus the address it connects from; the server matches that pair against the Host and User columns of mysql.user; the matched row decides which global privileges apply, and mysql.db rows add per-schema privileges on top. Every symptom in this class maps to one stage of that chain. Read them in order and you avoid changing grants when the fault is a closed port, or opening ports when the fault is a grant.

The SQLyog-versus-MySQL Administrator difference is diagnostic on its own. SQLyog only runs the statements you ask for, so a per-schema account is enough for table work. MySQL Administrator reads server status, the process list and the grant tables as soon as it connects, and an account with schema-only privileges cannot serve those queries. That is a privilege-level symptom, not a network one, so widening bind-address will not change it.

Why does MySQL treat root@localhost and root@% as different accounts?

An account in MySQL is the pair 'user'@'host', not the user name alone. A default installation creates 'root'@'localhost' and nothing else, which is deliberate: it keeps the superuser unreachable from the network until an administrator explicitly decides otherwise. When a client on another machine presents the user name root, the server searches for a row whose Host pattern matches the client's address. localhost does not match an IP address, so the login is rejected before the password is even considered. The % wildcard in the host column matches any address; a specific address such as the admin PC's IP restricts the match to that one machine.

The second stage is independent of the first. bind-address tells the daemon which interface to listen on. If it is set to 127.0.0.1, the server never sees the remote SYN, and no grant can help. The two controls are in series: the listener must accept the connection, then the grant table must match the account.

How do you open remote root access?

Do the grant work through the SQL command line on the server, not through a GUI privilege editor. The GUI hides the host part of the account and makes it easy to edit 'root'@'localhost' while believing you have created a remote account.

1. On the server, log in locally as root and confirm which accounts exist:

   mysql -u root -p
   SELECT User, Host FROM mysql.user;

   You will typically see only root | localhost.
2. Create the remote superuser. Use the admin PC's IP address instead of % if the client's address is fixed; use % if it will later connect from anywhere:

   CREATE USER 'root'@'%' IDENTIFIED BY 'strong-password';
   GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
   FLUSH PRIVILEGES;

   Older servers accept IDENTIFIED BY directly inside the GRANT statement; newer ones require the CREATE USER step first. If one form errors, use the other.
3. Open the listener. Edit my.cnf (Linux) or my.ini (Windows) under the [mysqld] section. Either comment out bind-address or set it to the interface address the remote PC reaches. Also remove skip-networking if present.
4. Restart the MySQL service so the daemon rebinds.
5. Open the server port (3306 unless changed with port=) in the host firewall for the admin PC's address or subnet.
6. From the remote PC, connect with the new account and immediately run CREATE DATABASE test_remote; to prove the global privilege is live.

If the goal is only to create databases rather than full superuser control, grant less: GRANT CREATE, DROP, ALTER ON *.* TO 'dbadmin'@'%' covers schema creation without SUPER or FILE. Tuning does not fix wiring, and a wide grant does not fix a firewall; keep each control doing its own job.

How do you verify the fix instead of assuming it?

Measure each stage of the chain separately, in the same order the server evaluates it.

1. On the server, confirm the listener is bound to a reachable interface: netstat -an (Windows) or ss -ltn (Linux) should show port 3306 on 0.0.0.0 or the LAN address, not 127.0.0.1.
2. From the remote PC, test raw TCP reachability to port 3306 before involving any SQL client. A connection that opens and immediately receives the server greeting proves bind-address and firewall are correct.
3. Log in and ask the server who it thinks you are: must return root@% (or the specific host you granted). If it returns an unexpected host pattern, another row matched first; MySQL prefers the most specific host match.
4. Run SHOW GRANTS; and confirm ALL PRIVILEGES ON *.* (or at least CREATE) is present for that identity.
5. Create and drop a throwaway database. Then reconnect with MySQL Administrator; its status and user-administration views should now populate, since the account can read the grant tables.

Which pitfalls recur on remote MySQL administration?

Forgetting FLUSH PRIVILEGES after editing the grant tables directly with INSERT or UPDATE leaves the in-memory privilege cache unchanged; GRANT and CREATE USER statements reload automatically, direct table edits do not. Editing bind-address in a configuration file the service does not read is common on Windows where several my.ini candidates exist; check the service's command line for the --defaults-file path. An anonymous account ''@'%' or ''@'localhost' left over from installation can out-match your new row for some connections and produce an access-denied error with an empty user name; drop it. The % wildcard does not match localhost connections through the Unix socket, so local tools keep using 'root'@'localhost' and the two accounts can drift to different passwords.

Exposing a 'root'@'%' account on a port reachable from the internet is a brute-force target. When the admin PC moves off the LAN, prefer an SSH tunnel or VPN terminating on the server and keep bind-address local, rather than granting root from every address. Both SQLyog and MySQL Administrator support connecting through a tunnel, so nothing in the workflow changes.

FAQ

Stop and escalate when the server greeting is received on port 3306, returns the expected root@% identity, SHOW GRANTS lists ALL PRIVILEGES ON *.*, and the client tool still refuses to create a database or populate its administrative views. At that point the fault is in the client software or a server-side setting outside the grant tables, and the MySQL documentation and support channels, or the client tool vendor's support, are the correct next step.