When configuring MySQL server access, many administrators face a peculiar situation where they can't maintain both remote and localhost connections simultaneously. The core issue stems from MySQL's bind-address directive in my.cnf, which typically only accepts a single IP address.

# Current problematic configuration examples:
bind-address = 192.168.2.20  # Only allows remote connections
# OR
bind-address = 0.0.0.0       # Allows all connections but raises security concerns

MySQL's network binding operates at a lower level than many developers realize. When you specify an IP in bind-address, you're telling MySQL which network interface to listen on - not just which IPs to accept.

Modern MySQL versions (5.6+) support listening on multiple interfaces through separate configuration:

[mysqld]
bind-address = 127.0.0.1     # Localhost
local-infile = 0            # Security best practice
skip-networking = 0         # Ensure networking is enabled

# For remote access, use MySQL's built-in networking
# No need for additional bind-address entries

Then configure your remote access through MySQL's user privileges:

CREATE USER 'remote_user'@'%' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

For systems where you need absolute local performance and remote access:

[mysqld]
socket = /var/run/mysqld/mysqld.sock
port = 3306
bind-address = 0.0.0.0

Then connect locally using:

mysql -u user -p --protocol=socket --socket=/var/run/mysqld/mysqld.sock

While remotely using standard TCP:

mysql -h server_ip -P 3306 -u remote_user -p

When enabling remote access, always implement these security measures:

• Use SSH tunneling for sensitive operations
• Implement firewall rules (UFW/iptables)
• Configure MySQL's secure connections
• Regularly audit user privileges

# Example iptables rule for MySQL access
iptables -A INPUT -p tcp --dport 3306 -s trusted_ip -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP

If connections still fail, check these diagnostic commands:

netstat -tulnp | grep mysql
ss -tulnp | grep mysql
mysqladmin variables | grep bind-address

Remember to restart MySQL after configuration changes:

systemctl restart mysql
# Or for older systems
service mysql restart

---

Many developers face this common MySQL configuration dilemma: setting up a database server that's accessible from both local applications and remote clients. The standard bind-address parameter in my.cnf seems to force us to choose between these two access methods.

# Current problematic configuration examples:
bind-address = 192.168.2.20  # Only allows remote connections
# OR
bind-address = 0.0.0.0       # Allows all connections but creates security concerns

Here's how to properly configure MySQL for dual access without compromising security:

# /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
bind-address = 127.0.0.1      # Localhost only binding
skip-networking = 0           # Ensure networking is enabled

Then create specific user privileges for remote access:

# MySQL command line:
CREATE USER 'remote_user'@'192.168.2.%' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@'192.168.2.%';
FLUSH PRIVILEGES;

For more complex scenarios, consider these methods:

1. Using SSH Tunneling

ssh -L 3306:localhost:3306 user@your-server.com
# Then connect locally using 127.0.0.1:3306

2. Multiple MySQL Instances

Run separate instances with different ports:

[mysqld_multi]
mysqld1 = /etc/mysql/local.cnf  # Binds to 127.0.0.1
mysqld2 = /etc/mysql/remote.cnf # Binds to external IP

• Always use firewall rules (iptables/ufw) to restrict access
• Implement TLS/SSL encryption for remote connections
• Regularly audit user privileges
• Consider using MySQL enterprise firewall for production systems

Common diagnostic commands:

# Check MySQL listening ports
sudo netstat -tulnp | grep mysql

# Test local connection
mysql -u root -p -h 127.0.0.1

# Test remote connection (from another machine)
mysql -u remote_user -p -h 192.168.2.20