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 mysql.host 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 0.0.0.0:3306            0.0.0.0:*               LISTEN      off (0.00/0/0)

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

bind-address = 0.0.0.0

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  --  0.0.0.0/0            0.0.0.0/0           
   ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0            state RELATED,ESTABLISHED
   ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            tcp dpt:22
   ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            tcp dpt:21
   ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            tcp dpt:2111
   ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            tcp dpts:5500:5600
   ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            tcp dpt:80
   ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            tcp dpt:443
   ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            tcp dpt:3306
   ACCEPT     icmp --  0.0.0.0/0            0.0.0.0/0            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