How to set up MySQL remote access on Ubuntu

Step 1: Check whether MySQL has remote access enabled

Before starting the configuration, we need to ensure that MySQL has remote access enabled. We need to log in to the MySQL server first and confirm whether 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 order to check whether the MySQL shell has remote access enabled, you need to type the following command

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 means MySQL allows it User access from any host. If you see this output, then MySQL has remote access enabled.

If you don't see the output above, or you see a different output than above, then this 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

To enable remote access to MySQL, we need to create a new remote access account first. MySQL only allows the root user to access the server locally by default. Other users need to use public authorization to access, which is why this is done.

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. To allow remote access to the MySQL server, the MySQL port needs to be opened 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 allow TCP traffic through the firewall, opening the port named "3306". Before you perform this step, make sure your firewall is installed and enabled.

Step 4: Update the MySQL configuration file

To allow remote login to the MySQL server, you need to configure 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 administrator and edit a few values ​​as follows:

bind-address = 0.0.0.0

MySQL server will accept connection requests from any IP address, use the following command:. This requires attention because it can make 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 changing 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.

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