Solution for adding iptables firewall policy to MySQL service

If your MySQL database is installed on a centos7 system and your operating system has a firewall enabled. If your application wants to access a MySQL database, you have 2 solutions.

Solution 1: Stop the firewall service Solution 2: Add a policy in the firewall to allow the application to access the MySQL service port normally

Stop Centos7 firewall

Check the firewall operation status

[root@mysql ~]# firewall-cmd --state
running

Stop the firewall service

[root@mysql ~]# systemctl stop firewalld.service

Start Centos7 firewall

Check the firewall operation status

[root@mysql ~]# firewall-cmd --state
not running

Start the firewall service

[root@mysql ~]# systemctl start firewalld.service

Configure the firewall to start at boot

[root@mysql ~]# systemctl enable firewalld.service

Access the MySQL service to test the connection to the MySQL service

[mysql@mysql ~]$ mysql -utony -ptony -h 192.168.112.131 -P 3306
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.112.131' (113)

Master-slave replication connection test [root@localhost] 15:23:46 [(none)]>show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.112.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000034
          Read_Master_Log_Pos: 194
               Relay_Log_File:mysql-relay-bin.000007
                Relay_Log_Pos: 401
        Relay_Master_Log_File: binlog.000034
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
           .....
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 1
               Last_SQL_Errno: 0

The IO threads of the master and slave are disconnected, and a 2003 error is reported. This indicates that the network is unavailable and the services of the master database cannot be accessed.

Add MySQL service access policy in the firewall

View Firewall Policy

[root@mysql ~]# iptables -L -n --line-number|grep 3306

Since no access policy for port 3306 is added to the firewall, external applications cannot access the MySQL service.

[mysql@mysql ~]$ mysql -utony -ptony -h 192.168.112.131 -P 3306
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.112.131' (113)

Add access policy for port 3306

# iptables -I INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
[root@mysql ~]# iptables -L -n --line-number|grep 3306
1 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:3306

You can see that the access policy for port 3306 has been added. External applications can access port 3306 through the TCP protocol.

Deleting a Firewall Policy

[root@mysql ~]# iptables -D INPUT 1
[root@mysql ~]# iptables -L -n --line-number|grep 3306

This is the end of this article about adding iptables firewall policy to MySQL service. For more relevant content about adding iptables firewall to MySQL service, please search previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: