ubuntu安装mysql并远程连接

环境

阿里云轻量级云服务器 ubuntu20.04
MySQL 8.0.27-0ubuntu0.20.04.1

MySQL安装

1.更新源

sudo apt update

sudo apt upgrade

2.安装服务器客户端

sudo apt install mysql-server mysql-client

3.进入MySQL并设置密码
进入MySQL环境

mysql

修改root密码并保存

use mysql;
alter user 'root'@'localhost' identified with mysql_native_password by '123456';
flush privileges;

如果修改失败可以尝试

use mysql;
alter user 'root'@'%' identified with mysql_native_password by '123456';
flush privileges;

quit退出后尝试

mysql -uroot -p123456

成功进入MySQL
4.MySQL服务启动、关闭、重启

sudo service mysql start
sudo service mysql restart
sudo service mysql stop

连接MySQL

1.添加允许访问的host
查询用户和允许访问的host

use mysql;
select user,host from user; 

查询结果

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

root用户的host中仅有localhost可以访问，进行修改

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

也可以

RENAME USER 'root'@'localhost' TO 'root'@'%';
flush privileges;

再次查询

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+

修改成功

但是尝试连接仍然失败
2.修改配置文件
(没有vim的用cat也行)

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

将文件中的bind_address修改为0.0.0.0

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

重启服务端

sudo service mysql restart

用navicat连接成功

一些坑

MySQL的较低版本修改密码方式与本文不同
认证方式也有修改，可使用

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

进行修改
更加详细的配置过程可见
《如何远程连接 MySQL 数据库，阿里云腾讯云外网连接教程》

参考文章
Linux中mysql忘记密码怎么解决