MySQL 用户管理

创建用户和修改已存在用户的密码

> CREATE USER 'netdata'@'10.0.0.31';
> SET PASSWORD FOR 'netdata'@'10.0.0.31' = PASSWORD('R3GrLknS245v');
> FLUSH PRIVILEGES;

创建数据库

mysql> CREATE DATABASE `iot` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

创建用户，并设置密码

mysql> create user 'test-user1'@'%'  identified by '123456';

给已存在用户授所有权限

mysql> grant all on *.* to 'test-user1'@'%';

给已存在用户授某一个库的所有权限

mysql> GRANT ALL PRIVILEGES ON `helmchart`.* TO 'devops-helmchart'@'%';

查看授权

mysql> show grants for 'test-user1'@'%;

给已存在用户授mydb库中update权限

mysql> GRANT UPDATE ON mydb.* TO user2@'%';
mysql> FLUSH PRIVILEGES;

给表添加字段

# 添加字段
mysql> ALTER TABLE tb_1 ADD test_4 TINYINT UNSIGNED;

# 给表添加字段
mysql> ALTER TABLE tb_1 ADD test_3 varchar(30);

# 授权用户对某个字段（test_3)的更新权限
mysql> GRANT UPDATE(test_3) ON user1.tb_1 TO 'user1'@'%';

# 查看用户权限
mysql> SHOW GRANTS FOR 'user1'@'%';

# 更新表中的某个字段
mysql> UPDATE tb_1 SET test_3=1 WHERE test_1=2;

# 重名用户名称
mysql> RENAME USER user1 to xikang;

删除用户权限

mysql> show GRANTS FOR xikang@'%';
# 删除SELECT权限
mysql> REVOKE SELECT ON user1.* FROM 'xikang'@'%';
mysql> show GRANTS FOR xikang@'%';

mysql 删除匿名用户

mysql> use mysql
mysql> select host,user,password from user;
mysql> DROP USER ''@localhost;

更改root密码

1、mysql> UPDATE user SET Password=PASSWORD('redhat') WHERE user='root';
2、mysql> SET PASSWORD FOR root@127.0.0.1 = PASSWORD('redhat');
3、[root@rsync1 ~]# mysqladmin -uroot password xikang -p
-p 输入老的密码
password xikang 设置新密码为xikang
# mysqladmin -uroot -p flush-privileges
4、mysql>  alter user 'root'@'localhost' identified by '123456'; # 修改现有用户密码
5、执行修改密码报错 “ ERROR 1054(42S22) Unknown column 'password' in 'field list'”
    update mysql.user set authentication_string=password('123456') where user='root';
6、刷新权限
mysql> FLUSH PRIVILEGES;

# 通配符：%
172.24.%.%
%.center.test

# 跳过主机名解析，以提高用户连接速度
--skip-name-resolve