Reasons and solutions for being unable to remotely connect to MySQL database under CentOS7

Preface

I recently encountered a problem at work. I found that I could not remotely connect to MySQL under the Centos7 system. I finally solved it by searching for relevant information. The following method is what I tried after I encountered the problem of not being able to connect to the Mysql database remotely, and it finally solved the problem. So I summarize and share it for reference and learning for friends who encounter the same problem. I won’t say much below, let’s take a look at the detailed introduction.

There are two reasons

• The database is not authorized
• The server firewall does not open port 3306

1. The database is not authorized

There is no authorization for the MySQL database, so just one command is enough.

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;


//When connecting to the database remotely, you need to enter the username and password Username: root
Password:123456
Point to ip:% to represent all IPs. You can also enter IP here to specify IP.

To make the changes take effect, you also need the following statement mysql>FLUSH PRIVILEGES;

2. The server firewall does not open port 3306

CentOS has two firewalls: FirewallD and iptables firewall

centos7 uses the FirewallD firewall.

FirewallD is a front-end controller for iptables that implements persistent network traffic rules. It provides both command line and graphical interfaces and is available in the repositories of most Linux distributions. There are two main differences when using FirewallD compared to controlling iptables directly:

1. FirewallD uses zones and services instead of chained rules.

2. It dynamically manages rule sets, allowing rules to be updated without disrupting existing sessions and connections.

FirewallD is a wrapper around iptables that allows you to manage iptables rules more easily - it is not a replacement for iptables. Although iptables commands can still be used with FirewallD, it is recommended that you use only FirewallD commands when using FirewallD.

1. Open port 3306 on FirewallD

 firewall-cmd --zone=public --add-port=3306/tcp --permanent

Command meaning:

--zone #scope --add-port=3306/tcp #Add port, format: port/communication protocol --permanent #Permanent, without this parameter, it will be invalid after restart

Restart the firewall

systemctl restart firewalld.service

2.iptables development port 3306

/sbin/iptables -I INPUT -p tcp -dport 3306 -j ACCEPT

/etc/rc.d/init.d/iptables save

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.