知识整理 MySQL

本文档持续更新中...

Q1）连接MySQL时提示“Public Key Retrieval is not allowed ”错误

如果是客户端连接，修改驱动属性

连接设置——驱动属性——allowPublicKeyRetrieval改为true即可。
连接数据库后，如果不想把此项开启，那在改为false就行，连接过一次后面都可正常在重新连接使用，但是如果新增连接还是要把此项开启，不然还是会继续报错。

如果用户使用了 sha256_password 认证，密码在传输过程中必须使用 TLS 协议保护，但是如果 RSA 公钥不可用，可以使用服务器提供的公钥；可以在连接中通过 ServerRSAPublicKeyFile 指定服务器的 RSA 公钥，或者AllowPublicKeyRetrieval=True参数以允许客户端从服务器获取公钥；但是需要注意的是 AllowPublicKeyRetrieval=True可能会导致恶意的代理通过中间人攻击(MITM)获取到明文密码，所以默认是关闭的，必须显式开启。

如果是命令行连接

直接在数据库连接字符串上加上参数allowPublicKeyRetrieval=true，这里的useSSL=false被添加是为了避免SSL错误，但是注意的是：在生产环境中，禁用SSL可能会导致数据传输不安全的风险，所以需要配置正确的SSL连接。注意！！！：allowPublicKeyRetrieval=true可能在某些情况下存在安全风险，因为它允许客户端从服务器获取RSA公钥

jdbc:mysql://localhost:3306/mydatabase?allowPublicKeyRetrieval=true&useSSL=false

或者在MySQL服务端修改，用户的认证方式改为mysql_native_password，并刷新权限设置

USE mysql;

ALTER USER 'yourusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword';

FLUSH PRIVILEGES;

执行SQL文件的几种方式：

# 命令行执行
mysql -e "source batch-file"
mysql -h host -u user -p < batch-file

# 先登录mysql客户端，然后运行如下语句：
mysql> source /path/filename;

用户管理

用户可以分为普通用户和root用户，root属于超级管理员

select host, user from user;
create user 'zhang3'@'localhost' identified by 'adb123'; # 默认@'%'
show grants; # 查看权限

update user set user='wang5' where user='li4' and host = '%';
flush privieges;

drop user 'wang5', 'li4'@'localhost'; # 推荐使用
delete from mysql.user where user='zhang3' and host = '%'; # 不推荐

# 修改用户密码
alter user user() identified by 'adbabc';
set password='adbabc';

# 修改其他用户密码, root执行
alter user 'zhang3'@'%' identified by 'hello';
set password for 'zhang3'@'%'='hello123';

密码管理：

• 密码过期，

alter user 'zhang3'@'%' password expire;
set persist default_password_lifetime = 180; # 或者配置文件中添加

create user 'kangshifu'@'%' password expire interval 90 day; # 每90天过期
create user 'kangshifu'@'%' password expire never; # 永不过期

• 密码重用策略

password_history=3            # 不得与最近3个密码相同
password_reuse_interval=365   # 365天内不可相同 

权限管理

show privileges;

grant select, update on dbtest1.* to 'zhang3'@'%';

grant all privileges on *.* to 'li4'@'%';  # 不能给别人赋权，需要with grant option

# 收回权限
revoke all privileges on *.* from joe@'%';

角色管理

逻辑架构