MySQL open remote access complete solution

Posted by Roger Ramjet on Sat, 04 Jan 2020 14:39:08 +0100

Applicable environment

  • MySQL 5.7
  • Ubuntu 16.04

(applicable but not limited to the above environment)

1, Enable MySQL remote access

Changing the value of field to% means that you can log in to MySQL server on any client machine

mysql> use mysql;
Database changed

mysql> grant all privileges  on *.* to username@'%' identified by "password";
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

How to change the password:

mysql> SET PASSWORD FOR 'username'@'host' = PASSWORD('newpass');

2, Check MySQL configuration

To view the address of MySQL service binding:

ubuntu:~$ netstat -ano | grep 3306
tcp        0      0  *               LISTEN      off (0.00/0/0)

If it is not as shown above (bound in, you need to change the configuration in / etc/my.cnf, and change the following line

bind-address =

3, Check firewall configuration

  1. Turn off windows firewall in control panel \ system and security \ Windows Defender firewall

  2. Check the firewall on the server

    ubuntu:~$ sudo iptables -L -n
    Chain INPUT (policy ACCEPT)
    target     prot opt source               destination         
    ACCEPT     all  --             
    ACCEPT     all  --              state RELATED,ESTABLISHED
    ACCEPT     tcp  --              tcp dpt:22
    ACCEPT     tcp  --              tcp dpt:21
    ACCEPT     tcp  --              tcp dpt:2111
    ACCEPT     tcp  --              tcp dpts:5500:5600
    ACCEPT     tcp  --              tcp dpt:80
    ACCEPT     tcp  --              tcp dpt:443
    ACCEPT     tcp  --              tcp dpt:3306
    ACCEPT     icmp --              icmptype 8
    Chain FORWARD (policy ACCEPT)
    target     prot opt source               destination         
    Chain OUTPUT (policy ACCEPT)
    target     prot opt source               destination    

    If MySQL listening port (default 3306) is set to DROP, it needs to be changed to ACCEPT

    ubuntu:~$ sudo vim /etc/iptables.rules
    ubuntu:~$ sudo iptables-restore < /etc/iptables.rules
  3. Check the security group settings provided by the service provider and open the corresponding ports

Topics: MySQL Ubuntu firewall iptables