环境

阿里云轻量级云服务器 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;

进行修改 更加详细的配置过程可见