Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the methods and steps of allowing remote connection to the MySQL database server". In the daily operation, I believe that many people have doubts about the methods and steps of allowing remote connection to the MySQL database server. The editor has consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about the methods and steps of allowing remote connection to the MySQL database server. Next, please follow the editor to study!

Configure the MySQL server

The first step is to set up the MySQL server to listen for a specific IP address or all IP addresses on the machine.

If the MySQL server and client can communicate with each other over a private network, the best option is to set up a MySQL server that listens only for private IP. Otherwise, if you want to connect to the server through the public network, set up the MySQL server and listen for all IP addresses on the machine.

To do this, you need to edit the MySQL configuration file and add or change the value of the bind-address option. You can set a single IP address and IP range. If the address is 0.0.0.0 MySQL server accepts connections on all host IPv4 interfaces. If you configure IPv6 on your system, use:: instead of 0.0.0.0.

The location of the MySQL configuration file varies from release to release. In Ubuntu and Debian, the file is located in / etc/mysql/mysql.conf.d/mysqld.cnf, while in Red Hat-based distributions, such as CentOS, the file is located in / etc/my.cnf.

Open the file using a text editor:

Sudo nano / etc/mysql/mysql.conf.d/mysqld.cnf

Search for lines that start with bind-address and set the value to the IP address that the MySQL server should listen to.

By default, this value is set to 127.0.0.1 (listening only in localhost).

In this example, we change the value to 0.0.0.0 to set up the MySQL server and listen on all IPv4 interfaces:

Mysqld.cnfbind-address = 0.0.0.13 skip-networking

If a line contains skip-networking, delete the line or comment it out by adding # at the beginning of the line.

In MySQL 8.0 and later, the bind-address directive may not exist. In this case, add it under the [mysqld] section.

When you are finished, restart the MySQL service for the changes to take effect. Only root users or users with sudo privileges can restart the service.

To restart the MySQL service on Debian or Ubuntu, enter:

Sudo systemctl restart mysqld

On RedHat-based distributions such as CentOS, to restart the service, run:

Sudo systemctl restart mysql

Authorized users to access from remote machines

The next step is to allow remote users to access the database.

Log in to the MySQL server as root by entering the following:

$sudo mysql

If you log in as root using the old native MySQL authentication plug-in, run the following command and enter your password when prompted:

$mysql-uroot-p

From within MySQL shell, use the Grant statement to grant access to remote users.

Mysql > GRANT ALL ON database_name.* TO user_name@'ip_address' IDENTIFIED BY 'user_password'

Where:

Database_name is the name of the database to which the user will connect.

User_name is the name of the MySQL user.

Ip_address is the IP address to which the user will connect. Use% to allow users to connect from any IP address.

User_password is the user password.

For example, to grant a user named foo who uses the password my_password to access the database dbname from a client machine with IP 10.8.0.5, you run:

Mysql > GRANT ALL ON dbname.* TO foo@'10.8.0.5' BY 'my_passwd'

Configure the firewall

The final step is to configure the firewall configuration to allow inbound traffic from remote machines through port 3306 (the default port for MySQL).

Iptables

If you use iptables as your firewall, the following command will allow access to the MySQL port from any IP address on the Internet. It's not safe.

$sudo iptables-An INPUT-p tcp-- destination-port 3306-j ACCEPT

Allow access from a specific IP address:

Sudo iptables-An INPUT-s 10.8.0.5-p tcp-- destination-port 3306-j ACCEPT

UFW

UFW is the default firewall tool in Ubuntu. To allow access from any IP address on the Internet (very insecure), run:

$sudo ufw allow 3306 / tcp

Allow access from a specific IP address:

Sudo ufw allow from 10.8.0.5 to any port 3306

FirewallD

FirewallD is the default firewall management tool in CentOS. To allow access from any IP address on the Internet (very insecure), enter:

Sudo firewall-cmd-- permanent-- zone = public-- add-port = 3306 / tcp $sudo firewall-cmd-- reload

To allow access from a specific IP address on a specific port, you can create a new FirewallD area or use rich rules. You might as well create a new zone called mysqlzone:

$sudo firewall-cmd-new-zone=mysqlzone-- permanent $sudo firewall-cmd-- reload $sudo firewall-cmd-- permanent-- zone=mysqlzone-- add-source=10.8.0.5/32$ sudo firewall-cmd-- permanent-- zone=mysqlzone-- add-port=3306/tcp $sudo firewall-cmd-- reload

Validate changes

To verify that remote users can connect to the MySQL server, run the following command:

Mysql-u user_name-h mysql_server_ip-p

Where user_name is the name of the user you granted access to, and mysql_server_ip is the IP address of the host running the MySQL server.

If everything is set up correctly, you can log in to the remote MySQL server.

If you receive the following error, port 3306 is not open, or the MySQL server is not listening for IP addresses.

ERROR 2003 (HY000): Can't connect to MySQL server on '10.8.0.5'

The following error indicates that the user you are trying to log in does not have access to the remote MySQL server.

"ERROR 1130 (HY000): Host '10.8.0.5' is not allowed to connect to this MySQL server"

MySQL is the default popular open source database server that listens only for inbound connections from localhost.

To allow remote connections to the MySQL server, you need to perform the following steps:

1. Configure the MySQL server to listen on all interfaces or specific interfaces.

two。 Grant access to remote users.

3. Open the MySQL port in the firewall.

At this point, the study on the "method steps to allow remote connection to the MySQL database server" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.