Filed under:

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

Was this answer helpful?

Add to Favourites Add to Favourites    Print this Article Print this Article

Also Read