When your MySQL server refuses remote connections, you'll typically encounter these telltale signs:
mysql -h10.0.0.2 -uroot -pmypassword
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.2' (111)
telnet 10.0.0.2 3306
Trying 10.0.0.2...
telnet: Unable to connect to remote host: Connection refused
The first diagnostic step should always be checking how MySQL is binding to network interfaces:
sudo netstat -ntlup | grep mysql
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 127018/mysqld
This output shows MySQL is only listening on localhost (127.0.0.1), which explains why remote connections fail.
Even when skip_networking and bind-address appear correctly set, other factors can cause this behavior:
mysql> show variables like '%skip_networking%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| skip_networking | OFF |
+-----------------+-------+
mysql> show variables like '%bind%';
Empty set (0.00 sec)
Many administrators overlook the mysql.cnf or mysqld.conf files where bind-address might be set without appearing in runtime variables:
# Check all possible config file locations
sudo grep -r "bind-address" /etc/mysql/
Here's how to properly enable remote connections:
# 1. Edit MySQL configuration
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# 2. Comment out or modify the bind-address line
# bind-address = 127.0.0.1
# becomes
bind-address = 0.0.0.0
# 3. Restart MySQL service
sudo systemctl restart mysql
Even with correct MySQL configuration, firewall rules can block access:
# For UFW (Ubuntu)
sudo ufw allow 3306/tcp
# For iptables
sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT
After fixing network access, ensure your user has proper remote access privileges:
-- Grant access to specific IP
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.0.100' IDENTIFIED BY 'password';
-- Or grant access from any host (less secure)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
For temporary remote access or testing, consider SSH tunneling:
ssh -L 3306:localhost:3306 username@10.0.0.2
Then connect to localhost:3306 from your client machine.
After making all changes, verify MySQL is now listening on all interfaces:
sudo netstat -ntlup | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2431/mysqld
When setting up a new Percona 5.5 server on Ubuntu, you might encounter MySQL stubbornly refusing remote connections while working perfectly for local access. Let's examine the complete solution pathway:
# First verify current listening ports
sudo netstat -ntlup | grep mysql
# Expected output for remote access:
# tcp6 0 0 :::3306 :::* LISTEN 2431/mysqld
If you see 127.0.0.1:3306 instead of 0.0.0.0:3306, MySQL is binding to localhost only.
Edit MySQL's configuration file (location varies by OS/distribution):
# For Ubuntu/Debian:
sudo nano /etc/mysql/my.cnf
# For CentOS/RHEL:
sudo nano /etc/my.cnf
Add or modify these directives under the [mysqld] section:
[mysqld]
bind-address = 0.0.0.0
skip-networking = OFF
For Ubuntu's UFW firewall:
sudo ufw allow 3306/tcp
sudo ufw reload
For systems running iptables:
sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT
sudo service iptables save
Even with network access, MySQL users need proper remote access privileges:
mysql> CREATE USER 'remote_user'@'%' IDENTIFIED BY 'secure_password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
For existing users:
mysql> UPDATE mysql.user SET host='%' WHERE user='remote_user';
mysql> FLUSH PRIVILEGES;
For temporary or secure access without opening ports:
ssh -L 3306:localhost:3306 user@mysql-server
# Then connect locally:
mysql -h 127.0.0.1 -u remote_user -p
After making changes, always restart MySQL:
sudo service mysql restart
# Or for systemd:
sudo systemctl restart mysqld
Verify remote connectivity:
nc -zv your_server_ip 3306
# Or with MySQL client:
mysql -h your_server_ip -u remote_user -p -e "SHOW DATABASES;"