How to install Mysql in Ubuntu to enable remote connections

Background

The cloud server used is Baidu Cloud (the same applies to other cloud servers). The system is Ubuntu 20.04 LTS, Mysql version 8.0. The requirement is to develop on Windows, and you can remotely connect to the read and write server at any time. Mysql on the server

Establish a remote connection to the server

You can use the ssh client or the web console provided by the cloud server manufacturer, as long as you can connect to the server

By the way, I would like to recommend a good-looking and easy-to-use ssh client: NextSSH

Use apt-get to install mysql

Update the apt warehouse first:

sudo apt-get update

By the way, because I don’t know what accounts you are using, and I can’t figure out which commands require higher permissions, so I added sudo to all commands, so no matter what Anyone who can copy and paste can use it directly without any permission issues.
Then install mysql-server:

sudo apt-get install mysql-server -y

At this step, mysql has been installed and started automatically. You can take a look:

sudo service mysql status

Set the root password

At this time, the mysql root account does not have a password set. You can log in directly with the mysql command:

Set the root Password (change the mynewpassword part to the password you want to set):

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'mynewpassword';

Exit, enter the mysql command and find that it cannot Logged in directly:

#So far, I can use mysql directly on the server.

Edit the configuration file to monitor remote connections

By default, the MySQL database only monitors local connections. If you want the external network to connect to the database remotely, we need to modify the configuration file so that MySQL can monitor remote connections. Fixed IP or listen on all remote IPs.
You need to use a command line text editor here. I use vim, so I will teach you vim. If you know nano or other things, you will know how to do it. If you don’t understand, just follow my instructions. Install vim:

sudo apt-get install vim -y

Then use vim to open the mysqld.cnf configuration file:

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Find the line bind-address = 127.0.0.1:

When this value is 127.0.0.1, it only monitors local connections. Change it to 0.0.0.0 to monitor all connections. Or you can change it to only allow connections from specified IPs.
Now vim is in reading mode. Click i to enter editing mode, and then use the up, down, left and right keys to locate this line (when INSERT is displayed at the bottom, it indicates that it is in editing mode. Press Esc to exit and return to reading. Mode):

After making changes, press Esc to exit the editing mode, then enter :wq to save and exit. (If you enter :q, you will exit without saving)
Restart the mysql service to make the changes take effect:

sudo service mysql restart

Allow the root account to use remote connections

mysql default Only the root account is allowed to be used locally. You need to modify it to allow the root account to be used remotely (I have not tried other accounts, but the principle is the same). First log in to mysql:

mysql -u root -p

Enter the password and log in.
Then select mysql Database:

use mysql;

View the account’s host permissions:

select user, host from user;

# When

##host is localhost, only local use is allowed. Change it to % for remote use:

update user set host='%' where user='root';

Exit mysql.

Check the firewall status that comes with ubuntu

sudo ufw status

If it is

inactive, it means the firewall is not turned on, then don’t worry about it. What is the purpose of the firewall? My own understanding is that if the firewall is turned on, all ports on the server are prohibited from connecting by default. Only the ports you allow are allowed to connect, similar to this:

所以如果防火墙开了，那要么把防火墙直接关了：

sudo ufw disable

要么添加一条规则让防火墙放行3306端口(mysql的默认端口)：

sudo ufw allow 3306

检查云服务器厂商的防火墙状态

打开云服务器的后台管理页面，找到防火墙：

云服务器厂商默认只开启几个最常用的端口，其他端口都是默认关闭的，所以也要在这里添加一条规则放行3306端口：

测试连接

随便找个数据库管理的软件测试一下：

The above is the detailed content of How to install Mysql in Ubuntu to enable remote connections. For more information, please follow other related articles on the PHP Chinese website!