创建用户并授权
创建用户
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;