mysql 8.0 常用操作

创建用户并授权

创建用户

CREATE USER 'custom'@'localhost' IDENTIFIED BY 'password'; 

MySQL 8.0 默认身份验证插件为caching_sha2_password，导致很多使用mysql_native_password身份验证的旧客户端无法建立连接

如果使用旧版本的navicat，则执行下面的语句

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourPassword';
FLUSH PRIVILEGES;

授权

GRANT ALL ON bankaccount.* TO 'custom'@'localhost';

root密码重置

在/etc/my.cnf文件中加入下面内容

skip-grant-tables

重启mysqld服务

systemctl restart mysqld

登录mysql，不用输入密码，直接回车

# mysql -uroot
 mysql> use mysql
 mysql> select host, user, authentication_string, plugin from user;

#如果authentication_string自动查询出来有内容，执行下面命令清空

 mysql> update user set authentication_string='' where user='root';

删除/etc/my.cnf中skip-grant-tables字段，重启mysqld服务

systemctl restart mysqld

登录mysql（此时密码为空，回车即可），修改密码

# mysql -uroot -p
 mysql> alter user 'root'@'localhost' identified by 'MhSqls@123';

允许root远程登录

登录mysql执行下面命令

create user 'root'@'%' identified by 'MhSqls@123';
grant all on *.* to 'root'@'%' with grant option;
flush privileges;

设置密码过期时间

设置90天过期

CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

设置永不过期

CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;