How To Configure MySQL Allow Remote Access Connection

1. You must create 
    set bind-address : /etc/my.cnf
    bind-address 0.0.0.0

    It will be set Database can allow from any network on IP4

2. Create user mysql for allowing that user can access remotely

    GRANT ALL PRIVILEGES ON *.* TO db_user @'10.10.151.30' IDENTIFIED BY 'db_passwd';
    [-] GRANT ALL PRIVILAGES : to manage privilage for db_user
    [-] *.*  : to allow db_user access any database and table can. It will be *.* --> [database].[spesific table]
    [-] db_user : user that will be create to have that privilage
    [-] @'10.10.151.30' : Source of Ip address user database. That's mean allowing connection from 10.10.151.30
    [-] IDENTIFIED BY 'db_passwd' : Create Password for db_user

3.  If you want to access from localhost too, it must create user from locahost access also
    ex :
        - GRANT ALL PRIVILEGES ON *.* TO db_user @'127.0.0.1' IDENTIFIED BY 'db_passwd';
        OR
        - GRANT ALL PRIVILEGES ON *.* TO db_user @'localhost' IDENTIFIED BY 'db_passwd';

4. To close the vulnerability, cause this will be allow connection from any host. Create Firewall for Drop anythings come to port Mysql(3306) and than allow specified IP Address to access from remote.
    ex :
               
        - DROP ALL Connection to port 3307 (this case database working with port 3307)
            $sudo /sbin/iptables -A INPUT -p tcp --dport 3307 -j DROP

        - ACCEPT Specified address to acces port 3307 from network
           
            $sudo /sbin/iptables -A INPUT -p tcp -d 10.10.70.3 --dport 3307 -j ACCEPT
            $sudo /sbin/iptables -A INPUT -p tcp -s 10.10.70.3 --dport 3307 -j ACCEPT
            $sudo /sbin/iptables -A INPUT -p tcp -d 10.10.151.30 --dport 3307 -j ACCEPT
            $sudo /sbin/iptables -A INPUT -p tcp -s 10.10.151.30 --dport 3307 -j ACCEPT

    if you not familiar with iptables you can used

    *) another complate set iptables;
        Even you can allow outgoing MySql client request (made via mysql command line client or perl/php script), from firewall host 202.54.1.20 as follows:           
            $iptables -A OUTPUT -p tcp -s 202.54.1.20 --sport 1024:65535 -d 0/0 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
            $iptables -A INPUT -p tcp -s 0/0 --sport 3306 -d 202.54.1.20 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

           
    *) another way to use ufw (Uncomplicated Firewall) command :
            $sudo ufw allow 3306
            ## only allow subnet 192.168.1.0/24 to connect to our mysql server ##
            $sudo ufw allow from 192.168.1.0/24 to any port 3306

       
5. Enjoyed! Just like that


F.A.Q :

How Do I Grant Access To An Existing Database?

Let us assume that you are always making connection from remote IP called 202.54.10.20 for database called webdb for user webadmin, To grant access to this IP address type the following command At mysql> prompt for existing database, enter:
mysql> update db set Host='202.54.10.20' where Db='webdb';
mysql> update user set Host='202.54.10.20' where user='webadmin';


Some Referece :
- https://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html
- https://www.cyberciti.biz/tips/linux-iptables-18-allow-mysql-server-incoming-request.html
- https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/enabling_tcp.html

Author : loliktry

Share this

Related Posts