How do I Allow Remote Access to MySQL for Specific Users to My Virtual/Dedicated/Cloud Server

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.

1. Connect to your Server over SSH
2. 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

3. 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

4. Configure MySQL to Allow Remote Connections

   nano -w /etc/my.cnf
   add 'bind-address={server-ip-address}'

5. 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.

Add to Favourites    Print this Article

Where are the User and Group details stored on my server ? (Views: 2498)

How do I see what is running / listening / responding on a port ? (Views: 2021)

Why do I get a cPanel / WHM Invalid License File Error Message ? (Views: 6400)

Wrong Mime-Type Error when serving WAP / WML Pages ... (Views: 1339)

What are the exact specs of your hardware? (Views: 2561)