How to perform remote connection and access control in MySQL?

MySQL is a commonly used relational database management system. In order to improve efficiency and convenience, we often need to remotely connect and access MySQL in different environments. This article will introduce how to perform remote connection and access control in MySQL and provide corresponding code examples.

1. Modify the MySQL configuration file

First, we need to modify the MySQL configuration file to allow remote connections. Find the my.cnf file in the MySQL installation directory and open it with a text editor.

sudo nano /etc/mysql/my.cnf

Find the bind-address attribute in the file, comment it out or change its value to 0.0.0.0. This way, the MySQL server will accept remote connections from any IP address.

#bind-address = 127.0.0.1

Save and close the file, and then restart the MySQL service for the changes to take effect.

sudo service mysql restart

2. Authorize remote access permissions

Next, we need to create a user in MySQL that allows remote access and grant it the corresponding permissions. Open the MySQL command line client.

mysql -u root -p

Enter the password of MySQL super user root and enter the MySQL command line interface.

Create a new user and set a password.

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

Where, 'remote_user' is the username of the new user, 'password' is the password, and % means allowing connections from any IP address. If you only want to allow connections from a specific IP address, you can replace % with the specific IP address.

Give this user access to all databases.

GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@'%';

Refresh the system permission table.

FLUSH PRIVILEGES;

Exit the MySQL command line client.

EXIT;

3. Configuring the firewall

If the firewall is enabled on your server, you need to ensure that the default port of MySQL (usually 3306) is open. You can use the following command to view the current firewall rules.

sudo ufw status

If the default port of MySQL is not open, you can use the following command to open the port.

sudo ufw allow 3306

4. Connect remotely

Now, we can use the following code example to connect to the database remotely.

import mysql.connector

config = {
  'user': 'remote_user',
  'password': 'password',
  'host': 'your_server_ip',
  'database': 'your_database',
  'raise_on_warnings': True
}

cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()

# 这里可以编写具体的数据库操作代码

cursor.close()
cnx.close()

In the code, you need to replace 'remote_user' with the username of the previously created remote user and 'password' with the corresponding password. 'your_server_ip' and 'your_database' are replaced with the IP address of the remote MySQL server and the name of the database to be connected according to the actual situation.

Through the above steps and code examples, we can implement remote connection and access control in MySQL. In this way, we can easily operate the MySQL database in different environments and improve work efficiency.

The above is the detailed content of How to perform remote connection and access control in MySQL?. For more information, please follow other related articles on the PHP Chinese website!