创建用户
语法:CREATE USER user_specification [, user_specification] ...
CREATE USER 'user1'@'192.168.30.74' IDENTIFIED BY 'centos';
user_specification:IDENTIFIED BY [PASSWORD] 'password'
MySQL 的用户信息存放在 mysql 库的 user 表中
SELECT user, host, password FROM user;
+-------+------------------+-------------------------------------------+
| user | host | password |
+-------+------------------+-------------------------------------------+
| root | localhost | *6F3CAE7C3BBB2A5B5D933738682953BC21AEBEE7 |
| root | centos75.miariam | *6F3CAE7C3BBB2A5B5D933738682953BC21AEBEE7 |
| root | 127.0.0.1 | *6F3CAE7C3BBB2A5B5D933738682953BC21AEBEE7 |
| root | ::1 | *6F3CAE7C3BBB2A5B5D933738682953BC21AEBEE7 |
| user1 | 192.168.30.74 | *128977E278358FF80A246B5046F51043A2B1FCED |
+-------+------------------+-------------------------------------------+
5 rows in set (0.00 sec)
在192.168.30.74的主机上远程登录 MySQL 数据库
mysql -uuser1 -pcentos -h192.168.30.75
查看登录状态
使用 SHOW processlist 命令查看用户的进程状态
SHOW processlist;
+----+-------------+---------------------+-------+---------+------+--------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+---------------------+-------+---------+------+--------------------------+------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 10 | root | localhost | mysql | Query | 0 | init | SHOW processlist | 0.000 |
| 11 | user1 | 192.168.30.74:41820 | NULL | Sleep | 58 | | NULL | 0.000 |
+----+-------------+---------------------+-------+---------+------+--------------------------+------------------+----------+
7 rows in set (0.00 sec)
在远程主机上查看登录状态
删除、改名
-
删除
DROP USER 'user1'@'192.168.30.74';
也可以使用 DELETE 子句对 user 表中的记录进行删除,在删除后使用 FLUSH PRIVILEGES 对数据库的策略进行更新
-
改名
RENAME USER 'user1'@'192.168.30.74' TO 'testuser1'@'192.168.30.74';
修改密码
语法:SET PASSWORD FOR 'user'@'host' = PASSWORD(‘password');
SET PASSWORD FOR 'testuser1'@'192.168.30.74' = PASSWORD('linux');
同样也可以使用 UPDATE 子句对 user 表中的 password 字段进行修改
UPDATE user SET password = password('centos') WHERE user = 'testuser1';
-
破解密码
- 启动 mysql 前修改 my.cnf 配置文件或者在启动 mysql 进程时使用 --skip-grant-tables 选项来忽略策略表
- 启动 mysql 进程,使用 UPDATE 子句对 mysql 库中的 user 表的 password 字段进行修改(SET PASSWORD不能使用)
- 移除步骤一中的选项,重启服务