How to Set Up Remote Access for MySQL on Ubuntu

MySQL is a widely used database management system that can be accessed locally or remotely on a server and is often used to develop web applications. However, sometimes we need remote access to MySQL on different machines, so we need to configure MySQL correctly. In this article, we will introduce how to set up remote access to MySQL on Ubuntu 18.04 operating system.

Step 1: Check whether MySQL has remote access enabled

Before starting the configuration, we need to ensure that MySQL has remote access enabled. First, we need to log into the MySQL server and see if remote access is enabled. Open a terminal and enter the following command:

sudo mysql -u root -p

This command will log in to MySQL as the root user. After entering this command, you will be asked for your password. If you enter the correct password, you will enter the MySQL shell, which allows you to access the MySQL server's command line interface. In the MySQL shell, we need to enter the following command to see if remote access is enabled:

SELECT user,authentication_string,host FROM mysql.user;

If remote access is enabled, you should see the following output:

+------------------+-------------------------------+-----------+
| user             | authentication_string         | host      |
+------------------+-------------------------------+-----------+
| root             | *E0AAECBBB187B27AAF667AEC86667 | localhost |
| root             | *E0AAECBBB187B27AAF667AEC86667 | %         |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHAT | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHAT | localhost |
| debian-sys-maint | *456D0C7680DF288F66F7401EABC4B | localhost |
+------------------+-------------------------------+-----------+

The "% here" "Indicates that MySQL allows access by users on any host. If you see this output, then MySQL has remote access enabled.

If you do not see the above output or if you see a different output than the above, it means that MySQL does not have remote access enabled. In this case, you need to configure MySQL to allow remote access.

Step 2: Set up a new remote access account for MySQL

Before enabling remote access, we need to create a new remote access account for MySQL. The reason for this is that MySQL only allows the root user to access the server locally by default, and other users need to use open authorization to access.

In order to set up a new remote access account, we need to execute the following commands in the MySQL shell:

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

These commands will create a new user named "newuser" and assign all access rights . "%" means that the user can access the MySQL server from any host. You can also specify allowed hosts using specific IP addresses or hostnames. For example, "newuser"@"10.0.0.2" means that access to the MySQL server is only allowed from the host with the IP address "10.0.0.2".

Step 3: Configure the firewall to allow MySQL traffic

Normally, the MySQL server may be protected by a firewall. In order to allow remote access to the MySQL server, you need to open the MySQL port on the firewall (default is 3306). In order to do this, we need to enter the following command:

sudo ufw allow 3306/tcp

This command will open a port named "3306", allowing TCP traffic to pass through the firewall. However, before proceeding with this step, you need to make sure that your firewall is installed and enabled.

Step 4: Update the MySQL configuration file

In order to allow remote login to the MySQL server, you need to set it in the MySQL configuration file. By default, the MySQL configuration file is located at "/etc/mysql/mysql.conf.d/mysqld.cnf". You need to open the file as an administrator and edit a few values ​​as follows:

bind-address = 0.0.0.0

The above command will allow the MySQL server to accept connection requests from any IP address. Note that this may be unsafe as it leaves the MySQL server vulnerable to unauthorized access. If you want to make remote access to MySQL more secure, you should specify the IP addresses or CIDR ranges that allow access.

Step 5: Restart the MySQL server

After modifying the MySQL configuration file, you need to restart the MySQL server for the changes to take effect. To restart MySQL, you can use the following command:

sudo systemctl restart mysql

If you see the following output, it means that the MySQL server has been successfully restarted:

mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2019-01-21 15:40:22 EST; 17s ago

At this point, you have successfully set up the MySQL server remote access.

Conclusion

MySQL is a powerful database management system that allows you to access it locally or remotely on the server. In this article, we explain how to set up remote access to MySQL on Ubuntu 18.04 operating system. By following the above steps, you can easily perform MySQL remote access between different machines. But please note that you need to pay attention to security and protect your MySQL server from unauthorized access.

The above is the detailed content of How to Set Up Remote Access for MySQL on Ubuntu. For more information, please follow other related articles on the PHP Chinese website!