Ubuntu下配置并远程连接MySQL

1、安装mysql-server

apt update
apt install mysql-server

2、修改配置文件/etc/mysql/mysql.conf.d/mysqld.cnf

bind-address            = 0.0.0.0
mysqlx-bind-address     = 0.0.0.0

3、启动并设置服务为开机自启动

systemctl enable mysql.service --now

4、查看服务状态

systemctl status mysql.service

5、配置远程连接

（1）配置mysql_native_password插件，以允许用口令连接

本地登录数据库，执行以下命令

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '自行设置连接密码';
 
FLUSH PRIVILEGES;

（2）修改mysql数据库的host主机，以允许远程连接

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed


mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;

（3）初始化mysql

mysql_secure_installation 



Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Please set the password for root here.

New password: 

Re-enter new password: 

……

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 

6、重启mysql服务

systemctl restart mysql

7、远程连接

使用navicat等连接工具就可以远程连接了。