04-mysql用户密码管理

1.管理mysql用户密码

1.命令行模式设置mysql密码

# 设置mysql密码，有密码的情况是不行的，必须是密码为空的情况下才可以
[root@mysql support-files]# mysqladmin password '123'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

# 登录测试
[root@mysql support-files]# mysql -uroot -p123

2.修改管理员root密码

1.Linux命令行修改法

#oldboy123为原密码，新密码为oldboy。
[root@mysql ~]# mysqladmin -uroot -p123 password ''
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
# 登录测试
[root@mysql ~]# mysql -uroot

2.SQL语法修改法

# 查询需要登陆的用户
mysql> select user,host,account_locked  from mysql.user;
+------------------+-----------+----------------+
| user             | host      | account_locked |
+------------------+-----------+----------------+
| mysql.infoschema | localhost | Y              |
| mysql.session    | localhost | Y              |
| mysql.sys        | localhost | Y              |
| root             | localhost | N              |
+------------------+-----------+----------------+
4 rows in set (0.00 sec)

#修改root用户的密码
mysql> alter user root@'localhost' identified by '123';
mysql> flush privileges; #<==刷新权限使得修改密码生效。
#交互登录
[root@mysql ~]# mysql -uroot -p123

3.找回mysql root用户密码

1.首先停止MySQL服务。
[root@mysql ~]# systemctl stop mysqld
[root@mysql ~]# netstat -lntup|grep 330
2.启动mysql服务。
[root@mysql ~]# mysqld_safe --skip-grant-tables --skip-networking &
###--skip-grant-tables（忽略授权登录验证）和--skip-networking（忽略网络）&后台运行
3.无需密码登录MySQL。
[root@mysql ~]# mysql
4.修改root密码为新密码。
mysql> flush privileges; #<==此命令必须先执行。
mysql> alter user root@'localhost' identified by 'oldboy123';
mysql> quit
5.重启mysql
[root@mysql ~]# killall mysqld
[root@mysql ~]# systemctl start mysqld
[root@mysql ~]# netstat -lntup|grep 330
6.登录验证
[root@mysql ~]# mysql -uroot -poldboy123

4.创建的用户无法登录

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| oldboy           | 10.0.0.%  |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
mysql> quit
Bye
[root@mysql ~]# mysql -uoldboy -p123;
ERROR 1045 (280): Access denied for user 'oldboy'@'localhost' (usingpassword: YES)
# 以上是使用oldboy@localhost登录
[root@mysql ~]# mysql -uoldboy -p123 -hlocalhost
#而实际用户是:oldboy@10.0.0.%，所以无法登录
#正确登录
[root@mysql ~]# mysql -uoldboy -p123 -h10.0.0.51