How to Allow Remote Access to MySQL Server for a User/IP/Database
For your application/requirements you may want to selectively allow MySQL Access from Remote (off-server) Locations.- Connect to your Server over SSH
- Login to MySQL as the MySQL-Root user:
mysql -uroot -p
>mysql GRANT ALL PRIVILEGES ON *.* TO [username]@[ip-address] IDENTIFIED BY "[password]";
(replace [username] [ip-address] [password] as required - you can use % instead of an IP to allow from any IP)
e.g. >mysql GRANT ALL PRIVILEGES ON *.* TO MySQLExternalUser@% IDENTIFIED BY "SecurePassw0rdz!";
>mysql FLUSH PRIVILEGES;
>mysql quit - Allow MySQL through FireWall (iptables) where installed
iptables -I INPUT -s (yourip)/(yoursubnet) -j ACCEPT
iptables -A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
iptables-save - Configure MySQL to Allow Remote Connections
nano -w /etc/my.cnf
add 'bind-address={server-ip-address}' - Switch off SELinux Enforcing Mode
echo 0 > /selinux/enforce
nano -w /etc/selinux/config
change 'SELINUX=enforcing' to your choice of disabled or permissive
After a reboot your server will allow the defined users/ips to remotely connect to the MySQL Server.
