The 30-Second Fix
If you need to grant access to a specific IP right now, run these commands on the server where MySQL is running. This example assumes your client machine's IP is 192.168.1.100.
# Login locally on the server
mysql -u root -p
# Grant access (MySQL 8.0+ syntax)
CREATE USER 'root'@'192.168.1.100' IDENTIFIED BY 'your_secure_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.100' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Swap 192.168.1.100 for your actual IP address. You could use '%' to allow connections from any host, but doing that for a root account is like leaving your front door wide open in a busy city.
Why Is This Happening?
You’ve probably confirmed your password is correct and your server is online. Yet, the moment you try to connect from DBeaver, TablePlus, or a remote terminal, MySQL shuts the door. Here's the deal: MySQL identifies users by a combination of Username + Host.
In the mysql.user table, 'admin'@'localhost' is a completely different entity than 'admin'@'192.168.1.100'. By default, MySQL is paranoid. It usually only ships with a root user allowed to connect from localhost (the server itself). If your IP isn't on the guest list, MySQL rejects the connection before it even checks your password.
Three Ways to Fix the Connection
Method 1: Create a Dedicated Remote User (Best Practice)
Don't expose your root account. Instead, create a specific user for your application or your dev machine. This keeps your database secure while giving you the access you need.
-- 1. Create the user for your specific IP
CREATE USER 'dev_user'@'192.168.1.100' IDENTIFIED BY 'AppPassword_2024!';
-- 2. Grant permissions only to the database you need
GRANT ALL PRIVILEGES ON ecommerce_db.* TO 'dev_user'@'192.168.1.100';
-- 3. Finalize changes
FLUSH PRIVILEGES;
Method 2: Update an Existing User for Any Host
If you're in a safe development environment and want a user to connect regardless of their IP, use the % wildcard. On MySQL 8.0 or newer, use RENAME USER instead of manually updating the system tables.
-- Switch the host from 'localhost' to the wildcard '%'
RENAME USER 'root'@'localhost' TO 'root'@'%';
-- Or create a new global root if the local one must stay
CREATE USER 'root'@'%' IDENTIFIED BY 'YourPassword';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Security Tip: If you allow %, ensure your server's cloud firewall (like AWS Security Groups) restricts port 3306 to only your known IP addresses.
Method 3: Check the bind-address Trap
Sometimes the permissions are perfect, but the server isn't even "listening" for outside calls. Open your MySQL config file—usually found at /etc/mysql/mysql.conf.d/mysqld.cnf on Ubuntu or /etc/my.cnf on CentOS.
Find this line:
bind-address = 127.0.0.1
This tells MySQL to ignore everything except local traffic. Change it to 0.0.0.0 to listen on all network interfaces, or set it to your server's internal VPC IP.
bind-address = 0.0.0.0
Save the file and restart the service to apply the change:
sudo systemctl restart mysql
Testing the Connection
Before you wrestle with complex SQL again, check if the network path is actually open. Run this from your local client machine:
# Check if port 3306 is reachable
nc -zv your_server_ip 3306
If you see "Connection to [IP] 3306 port [tcp/mysql] succeeded!", your network is fine. If it times out, you likely have a firewall blocking the way.
Quick Troubleshooting Checklist
- **Cloud Firewalls:** Did you open port 3306 in your AWS, Azure, or DigitalOcean dashboard?
- **Local Firewall:** Check `ufw status` or `iptables`. On Windows, check the Advanced Firewall rules.
- **Dynamic IP:** If you're working from home, your IP might change daily. Check `SELECT USER();` while connected locally to see exactly how MySQL perceives your current host.
- **Docker:** For containers, ensure you've mapped `3306:3306` and that your user is allowed to connect from the Docker bridge subnet (often `172.17.0.0/16`).