浅谈Mysql用户信息的操作

于 2019-11-23 11:37:26 发布
阅读量139 收藏
点赞数
分类专栏： Mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lpb2019/article/details/103212338
版权

浅谈Mysql用户信息的操作

修改远程用户登录

MariaDB [mysql]> SELECT HOST,USER,PASSWORD FROM user;
+-----------+------+-------------------------------------------+
| HOST      | USER | PASSWORD                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *293C17197B3150952CF285A2AD56017E97CA787C |
| 127.0.0.1 | root | *293C17197B3150952CF285A2AD56017E97CA787C |
| ::1       | root | *293C17197B3150952CF285A2AD56017E97CA787C |
+-----------+------+-------------------------------------------+
MariaDB [mysql]> UPDATE user SET HOST='172.20.54.1' WHERE HOST='::1';
MariaDB [mysql]> SELECT HOST,USER,PASSWORD FROM user;
+-------------+------+-------------------------------------------+
| HOST        | USER | PASSWORD                                  |
+-------------+------+-------------------------------------------+
| localhost   | root | *293C17197B3150952CF285A2AD56017E97CA787C |
| 127.0.0.1   | root | *293C17197B3150952CF285A2AD56017E97CA787C |
| 172.20.54.1 | root | *293C17197B3150952CF285A2AD56017E97CA787C |
+-------------+------+-------------------------------------------+
3 rows in set (0.00 sec)
# 使修改立即生效
MariaDB [mysql]> FLUSH PRIVLIEGES
# 在172.20.54.1上登录
[root@Centos7 ~]# mysql -h 172.20.54.2 -uroot -pyes
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.5.5-10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# 查看自己的登录状态
mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.6.34, for linux-glibc2.5 (x86_64) using  EditLine wrapper

Connection id:		49
Current database:	
Current user:		root@172.20.54.1
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.5-10.2.29-MariaDB MariaDB Server
Protocol version:	10
Connection:		172.20.54.2 via TCP/IP
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			3 days 21 hours 53 min 58 sec

Threads: 8  Questions: 862  Slow queries: 0  Opens: 83  Flush tables: 1  Open tables: 48  Queries per second avg: 0.002

创建新用户

  • 用INSERT创建新用户空密码
MariaDB [mysql]> INSERT INTO user (HOST,USER,PASSWORD,SSL_CIPHER,X509_ISSUER,x509_subject,authentication_string)VALUES('172.20.54.1','yijie','','','','','');
Query OK, 1 row affected (0.00 sec)
MariaDB [mysql]> FLUSH PRIVILEGES;
[root@Centos7 ~]# mysql -h 172.20.54.2 -u yijie
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 55
Server version: 5.5.5-10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  • 用CREATE USER创建新用户空密码
  • CREATE USER创建的用户直接生效不需要用FLUSH PRIVILGES来生效它
MariaDB [mysql]> CREATE USER mary@'172.20.54.1' IDENTIFIED BY '';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> SELECT HOST,USER,PASSWORD FROM user;
+-------------+-------+-------------------------------------------+
| HOST        | USER  | PASSWORD                                  |
+-------------+-------+-------------------------------------------+
| localhost   | root  | *293C17197B3150952CF285A2AD56017E97CA787C |
| 127.0.0.1   | root  | *293C17197B3150952CF285A2AD56017E97CA787C |
| 172.20.54.1 | root  | *293C17197B3150952CF285A2AD56017E97CA787C |
| 172.20.54.1 | mary  |                                           |
| 172.20.54.1 | yijie |                                           |
+-------------+-------+-------------------------------------------+
5 rows in set (0.00 sec)
[root@Centos7 ~]# mysql -h 172.20.54.2 -umary

mysql> SELECT USER()
    -> ;
+------------------+
| USER()           |
+------------------+
| mary@172.20.54.1 |
+------------------+
1 row in set (0.00 sec)

删除指定用户

DELETE USER

  • 用DELETE删除用户，但是一定要用FLUSH PRIVILEGES生效
MariaDB [mysql]> SELECT HOST,USER,PASSWORD FROM user;
+-------------+-------+-------------------------------------------+
| HOST        | USER  | PASSWORD                                  |
+-------------+-------+-------------------------------------------+
| localhost   | root  | *293C17197B3150952CF285A2AD56017E97CA787C |
| 127.0.0.1   | root  | *293C17197B3150952CF285A2AD56017E97CA787C |
| 172.20.54.1 | root  | *293C17197B3150952CF285A2AD56017E97CA787C |
| 172.20.54.1 | mary  |                                           |
| 172.20.54.1 | yijie |                                           |
+-------------+-------+-------------------------------------------+
5 rows in set (0.00 sec)
MariaDB [mysql]> DELETE FROM user WHERE USER='mary';
Query OK, 1 row affected (0.00 sec)
MariaDB [mysql]> FLUSH PRIVILEGES;
[root@Centos7 ~]# mysql -h 172.20.54.2 -umary
ERROR 1045 (28000): Access denied for user 'mary'@'172.20.54.1' (using password: NO)

DROP USER

  • 用DROP删除指定用户，直接生效
MariaDB [mysql]> DROP USER yijie@'172.20.54.1';
MariaDB [mysql]> SELECT HOST,USER,PASSWORD FROM user;
+-------------+------+-------------------------------------------+
| HOST        | USER | PASSWORD                                  |
+-------------+------+-------------------------------------------+
| localhost   | root | *293C17197B3150952CF285A2AD56017E97CA787C |
| 127.0.0.1   | root | *293C17197B3150952CF285A2AD56017E97CA787C |
| 172.20.54.1 | root | *293C17197B3150952CF285A2AD56017E97CA787C |
+-------------+------+-------------------------------------------+
3 rows in set (0.00 sec)
[root@Centos7 ~]# mysql -h 172.20.54.2 -uyijie
ERROR 1045 (28000): Access denied for user 'yijie'@'172.20.54.1' (using password: NO)

修改用户密码

  • 准备工作
MariaDB [mysql]> CREATE USER yijie@'172.20.54.1' IDENTIFIED BY 'centos';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> INSERT INTO user (HOST,USER,PASSWORD,ssl_cipher, x509_issuer, x509_subject, authentication_string)VALUES('172.20.54.1','mary',password('centos'),'','','','');
Query OK, 1 row affected (0.00 sec)

MariaDB [mysql]> SELECT HOST,USER,PASSWORD FROM user;
+-------------+-------+-------------------------------------------+
| HOST        | USER  | PASSWORD                                  |
+-------------+-------+-------------------------------------------+
| localhost   | root  | *293C17197B3150952CF285A2AD56017E97CA787C |
| 127.0.0.1   | root  | *293C17197B3150952CF285A2AD56017E97CA787C |
| 172.20.54.1 | root  | *293C17197B3150952CF285A2AD56017E97CA787C |
| 172.20.54.1 | yijie | *128977E278358FF80A246B5046F51043A2B1FCED |
| 172.20.54.1 | mary  | *128977E278358FF80A246B5046F51043A2B1FCED                                  |
+-------------+-------+-------------------------------------------+
5 rows in set (0.00 sec)
MariaDB [mysql]>FLUSH PRIVILEGES;

UPDATE PASSWROD

  • 使用UPDATE修改密码，但是一定要配合FLUSH PRIVILEGES 生效
MariaDB [mysql]> UPDATE user SET PASSWORD=PASSWORD('123456') WHERE USER='yijie';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
[root@Centos7 ~]# mysql -h 172.20.54.2 -uyijie -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 66
Server version: 5.5.5-10.2.29-MariaDB MariaDB Server

SET PASSWORD

  • 使用SET PASSWORD修改密码直接生效
MariaDB [mysql]> SET PASSWORD FOR mary@'172.20.54.1'=password('12345');
Query OK, 0 rows affected (0.00 sec)
[root@Centos7 ~]# mysql -h 172.20.54.2 -umary -p12345;
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 70
Server version: 5.5.5-10.2.29-MariaDB MariaDB Server

MYSQLAMIN 修改ROOT PASSWORD

mysqladmin -uroot -pyes password 'centos'

ALTER修改密码

ALTER USER 'root'@'localhost' INDENTIFIED BY'123456'

破解root口令

  • 实现步骤如下
# 修改配置文件
vim /etc/my.cnf
[mysqld]
skip-networking        # 禁止远程连接
skip-grant-tables      # 忽略授权表信息
# 重启mysql服务
[root@Centos7 ~]# systemctl restart mysqld
[root@Centos7 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# 修改mysql密码
MariaDB [mysql]> SET PASSWORD FOR root@'localhost' =password('centos') ;
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement
# 给出提示正在运行忽略授权表选项，不允许这么修改
# 使用UPDATE修改
MariaDB [mysql]> UPDATE user SET PASSWORD=password('caiji') WHERE USER='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec）
# 注释掉服务器选项并重启mysql服务发现还是失败
# 因为mysql5.7以后密码已经存储在user表里面authentication_string这个字段里面
[root@Centos7 ~]# mysql -uroot -pcaiji
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
# 再来一次
MariaDB [(none)]> UPDATE mysql.user set authentication_string=password('caiji') where user='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
[root@Centos7 ~]# mysql -uroot -pcaiji
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.29-MariaDB MariaDB Server     # 成功
lpb2019
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值