1.mysql用户的定义
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| rep | 127.0.0.1 |
| root | 127.0.0.1 |
| root | ::1 |
| | db01 |
| root | db01 |
| | localhost |
| root | localhost |
+------+-----------+
7 rows in set (0.01 sec)
#以上是7个用户，在mysql中，用户的定义是 '用户名'@'主机域'
1）用户名写法
mysql> create user lhd@'10.0.0.51';
Query OK, 0 rows affected (0.01 sec)
#数字我们不作为用户创建，如果要创建，加上引号将数字解析成字符串就能添加了
mysql> create user '123'@'10.0.0.51';
Query OK, 0 rows affected (0.00 sec)
2）主机域的写法
127.0.0.1
::1
localhost
db01
10.0.0.51
10.0.0.%
10.0.%.%
10.%.%.%
%
10.0.0.5% （10.0.0.51-10.0.0.59,10.0.0.5）
10.0.0.0/255.255.255.0
10.0.0.0/24 #可以创建但是不生效
#测试：
mysql> grant all on *.* to lhd@'10.0.0.0/255.255.255.0' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to qiudao@'10.0.0.0/24' identified by '123';
Query OK, 0 rows affected (0.00 sec)
2.用户的管理
1）创建用户
mysql> create user test@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
2）查看用户
mysql> select user,host from mysql.user;
+--------+------------------------+
| user | host |
+--------+------------------------+
| lhd | 10.0.0.% |
| test | 10.0.0.% |
| root | localhost |
+--------+------------------------+
6 rows in set (0.00 sec)
3）删除用户
mysql> drop user qiudao@'10.0.0.0/24';
Query OK, 0 rows affected (0.00 sec)
4）修改密码
#1.update修改密码
mysql> update mysql.user set password=password('345') where user='lhd' and host='10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
#2.mysqladmin修改密码
[root@db01 ~]# mysqladmin -uroot -p123 password '345'
为了不暴露密码
[root@db01 ~]# mysqladmin -uroot -p password
Enter password:
New password:
Confirm new password:
#3.grant授权修改密码
mysql> grant all on *.* to lhd@'10.0.0.%' identified by '789';
Query OK, 0 rows affected (0.00 sec)
#4.修改当前登录用户的密码
mysql> set password=password('456');
Query OK, 0 rows affected (0.00 sec)
3.忘记root密码怎么办？
1.停止数据库
[root@db01 ~]# systemctl stop mysql
2.跳过授权表和网络启动
[root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &
3.连接数据
[root@db01 ~]# mysql
4.update修改密码
mysql> update mysql.user set password=password('123') where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
5.刷新授权表
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
6.重启数据库
[root@db01 ~]# mysqladmin shutdown
[root@db01 ~]# systemctl start mysql
7.使用新密码连接测试
[root@db01 ~]# mysql -uroot -p123
