MySQL用户及权限知识梳理 _MySQL

一、账号

用户账号

MySQL用户账号组成: 'USER'@'HOST'
USER: 用户名。
HOST: 来源主机地址， IP、主机名、通配符(%和_) 。

创建用户账号:

mysql> CREATE USER 'USER'@'HOST';mysql> CREATE USER 'USER'@'HOST' IDENTIFIED BY 'PWD';

用户重命名:

mysql> RENAME USER old_username TO new_username;

删除用户:

mysql> DROP USER 'USER'@'HOST';mysql> DELETE FROM mysql.user WHERE user=xxx AND host=xxx;

修改密码:

方式一:mysql> SET PASSword FOR 'USER'@'HOST'=password('PWD');方式二:mysql> ALTER USER 'USER'@'HOST' IDENTIFIED BY 'PWD';mysql> ALTER USER 'USER'@'HOST' IDENTIFIED WITH mysql_native_password BY 'PWD';方式三:mysql> UPDATE mysql.user SET password=PASSWORD('PWD') WHERE user=xxx AND host=xxx;方式四:mysqladmin -uxxx -pxxx -hxxx password 'PWD'

忘记root密码找回步骤:

(1)、重新启动mysqld进程, 添加选项 --skip-grant-tables# mysqld_safe --defaults-file=/data/mysql/mysql3306/my.cnf --skip-grant-tables &> /dev/null &(2)、修改管理员密码(3)、关闭mysqld进程, 移除上述选项, 重启mysqld# mysqld_safe --defaults-file=/data/mysql/mysql3306/my.cnf &> /dev/null &

清理无用的MySQL用户:

mysql> SELECT user,host FROM mysql.user;mysql> DROP USER "root"@"::1"mysql> DROP USER ""@"localhost"......mysql> flush privileges;

二、权限

添加授权:

方法一：mysql> CREATE USER 'USER'@'HOST' IDENTIFIED BY 'PWD';mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'USER'@'HOST';方法二：mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'USER'@'HOST' IDENTIFIED BY 'PWD';

查看指定用户授权:

mysql> SHOW GRANTS FOR CURRENT_USER;mysql> SHOW GRANTS FOR 'USER'@'HOST';

回收权限:

mysql> REVOKE priv_type,... ON dbname.* FROM 'USER'@'HOST';

线上环境用户授权建议:

权限控制:主库权限: select,insert,update,delete从库权限: selectmysql> GRANT select,insert,update,delete ON db_name.* TO 'USER'@'HOST' IDENTIFIED BY 'PWD';mysql> GRANT select ON db_name.* TO 'USER'@'HOST' IDENTIFIED BY 'PWD';

【MySQL用户及权限知识梳理】

用户设置:方案一: 用户名密码都一样 ， 仅有服务器HOST不一样主库: 用户名username, 密码pwd, 端口3306, 服务器HOST host1从库: 用户名username, 密码pwd, 端口3306, 服务器HOST host2方案一: 用户名密码都不一样 ， 服务器HOST不一样主库: 用户名username_rw,密码pwd1, 端口3306, 服务器HOST host1从库: 用户名username_r,密码pwd2, 端口3306, 服务器HOST host2