How to set up remote access in mysql

MySQL is a popular relational database management system, and many applications require the use of MySQL for data storage and processing. In some scenarios, we need to use remote access to MySQL databases, such as sharing data between different servers, or accessing MySQL databases located outside the local network. This article will introduce how to set up remote access to MySQL.

MySQL does not allow remote access by default, so we need to perform the following three steps to achieve it:

1. Configure MySQL permissions

We need to open the configuration of MySQL File my.cnf, this file is usually located in the /etc/mysql/ directory. In this file, we need to change the value of the bind-address option to 0.0.0.0:

bind-address = 0.0.0.0

This will allow MySQL to listen on all available IP addresses, i.e. allow remote access. After saving and closing the my.cnf file, you need to restart the MySQL service:

sudo service mysql restart

2. Create a MySQL user

In MySQL, each user needs to have a username and password for identity verify. We can use the following statement to create a MySQL user named "remoteuser" and set the password to "password":

CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'password';

Among them, % means that any host with an IP address is allowed to connect to the MySQL server. If only hosts with specific IP addresses are allowed to connect, you can replace % with a specific IP address or domain name.

3. Grant MySQL permissions

We need to use the GRANT statement to grant the remoteuser user the corresponding permissions to remotely access the MySQL database. For example, we can grant the remoteuser user full permissions on the test database using the following statement:

GRANT ALL ON test.* TO 'remoteuser'@'%';

This will allow the remoteuser user to connect to the MySQL server from any IP address and have full permissions on the database named test.

After completing the above three steps, we can use the remote client to connect to the MySQL server. In the client, we need to enter the IP address (or domain name) of the MySQL server, and the username and password of the remote user. For example, use the following command in a Linux terminal to connect to the MySQL server:

mysql -u remoteuser -h 192.168.1.100 -p

The -u parameter specifies the username, the -h parameter specifies the IP address of the MySQL server, and the -p parameter indicates the password. After entering the password, we will get the MySQL command line prompt.

Summary

Setting up remote access to MySQL requires three steps: configuring MySQL permissions, creating a MySQL user, and granting MySQL permissions. In addition to the above methods, you can also use web applications like phpMyAdmin to remotely manage MySQL databases. No matter which method is used, we need to pay close attention to security issues, such as configuring strong passwords for users, restricting user permissions, etc.

The above is the detailed content of How to set up remote access in mysql. For more information, please follow other related articles on the PHP Chinese website!