一、环境

liurongming@liurongming-VirtualBox:/etc/mysql/mysql.conf.d$ cat /etc/os-release
PRETTY_NAME="Ubuntu 24.04.2 LTS"
NAME="Ubuntu"
VERSION_ID="24.04"
VERSION="24.04.2 LTS (Noble Numbat)"
VERSION_CODENAME=noble
ID=ubuntu
ID_LIKE=debian
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
UBUNTU_CODENAME=noble
LOGO=ubuntu-logo
liurongming@liurongming-VirtualBox:/etc/mysql/mysql.conf.d$

二、安装

# 基础安装
sudo apt install mysql-server
# 查看状态
systemctl status mysql.service
# 开启启动
systemctl enable mysql.service
# 启动运行
systemctl start mysql.service

三、远程访问

/etc/mysql/mysql.conf.d
# cat mysqld.cnf 
# bind-address          = 127.0.0.1
bind-address            = 0.0.0.0

四、创建用户

# 创建
create user 'dev'@'%' indentified by 'password'
# 授权
grant all privileges on *.* to 'dev'@'%' with grant option;
# 改密
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
# 收权
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'dev'@'%';
# 删除
REVOKE ALL PRIVILEGES, GRANT OPTION FROM '用户名'@'主机名';
DROP USER '用户名'@'主机名';
# 刷新
flush privileges;

# 查看
use mysql;
select User,Host,plugin,authentication_string from user;

五、远程软件问题

解决Navicat报错“Authentication plugin ‘caching_sha2_password’ cannot be loaded”的步骤

image.png

这是老版本的软件，不支持caching_sha2_password密码插件。因此，要么换软件要么换数据加密插件。

mysql> select user,host,plugin from user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| dev              | %         | caching_sha2_password |
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | auth_socket           |
+------------------+-----------+-----------------------+
6 rows in set (0.00 sec)

mysql>

# 更换密码插件
alter user 'dev'@'%' identified with mysql_native_password by 'password';

【推荐改软件】通过以上两种方法的任何一种，即可解决。

image.png