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

Basic Exim troubleshooting (Views: 6226)

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

What sort of performance can I really expect from my Cloud Server? (Views: 2105)

What ports should be open to run cPanel / WHM behind a firewall ? (Views: 14679)

How can I BACKUP a MySQL database from the command line? (Views: 3156)