今天突然有了修改密码的需求,然后试着在网上搜了下方法,好像都不能成功,后来看了下官方文档才发现,是因为我用的mysql版本太高了,已经改变了修改密码的方法。
查看mysql版本
如果还没连接到mysql服务器,那么采用以下三种方法:
-- 方法一
C:\Users\root>mysql -V
mysql Ver 14.14 Distrib 5.7.20, for Win64 (x86_64)
-- 方法二
C:\Users\root>mysql --help | find "Distrib"
mysql Ver 14.14 Distrib 5.7.20, for Win64 (x86_64)
-- 方法三,可以看到登录的时候会显示mysql版本信息
C:\Users\root>mysql -uroot -p
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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数据库,采用以下三种方法:
-- 第一种方法
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.20-log |
+------------+
1 row in set (0.00 sec)
-- 第二种方法
mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.20, for Win64 (x86_64)
Connection id: 21
Current database:
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.7.20-log MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: gbk
Conn. characterset: gbk
TCP port: 3306
Uptime: 6 days 46 min 10 sec
Threads: 1 Questions: 158 Slow queries: 0 Opens: 135 Flush tables: 1 Open tables: 125 Queries per second avg: 0.000
--------------
-- 第三种方法
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.20, for Win64 (x86_64)
Connection id: 21
Current database:
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.7.20-log MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: gbk
Conn. characterset: gbk
TCP port: 3306
Uptime: 6 days 48 min 28 sec
Threads: 1 Questions: 161 Slow queries: 0 Opens: 135 Flush tables: 1 Open tables: 125 Queries per second avg: 0.000
--------------
总结
因此,由以上查看版本的方法,可以知道,我用的mysql版本为5.7.20-log MySQL Community Server (GPL)
,已经不支持以前修改密码的方式了。
查看系统配置
-- 数据库系统配置存储在mysql数据库中,切换到mysql数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| menageries |
| mysql |
| performance_schema |
| sakila |
| sys |
| test |
| world |
+--------------------+
8 rows in set (0.00 sec)
mysql> use mysql
Database changed
-- 可以看出,用户配置信息存储在user表中
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.27 sec)
-- 查看表格属性有哪些,由于表格太长,此处不一一列出,分析知道其中有一行名为authentication_string,这就是我们找的属性
mysql> describe user;
……
authentication_string | text
……
-- 果然这里面保存的是用户密码,但是并非明文显示
mysql> select user,authentication_string from user;
+---------------+-------------------------------------------+
| user | authentication_string |
+---------------+-------------------------------------------+
| root | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| test | *676243218923905CF94CB52A3C9D3EB30CE8E20D |
+---------------+-------------------------------------------+
4 rows in set (0.00 sec)
创建用户
mysql> create user '2b'@'localhost' identified by '2b';
Query OK, 0 rows affected (1.06 sec)
mysql> select user,authentication_string from user;
+---------------+-------------------------------------------+
| user | authentication_string |
+---------------+-------------------------------------------+
| root | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| test | *676243218923905CF94CB52A3C9D3EB30CE8E20D |
| 2b | *3382AC02521A1462D80B82F33C8F09029CD69A42 |
+---------------+-------------------------------------------+
5 rows in set (0.00 sec)
可以看到,我们成功的创建了一个用户2b,我们给他设置了密码2b,但是是加密显示的。
删除用户
mysql> delete from user where user='2b';
Query OK, 1 row affected (0.45 sec)
mysql> select user,authentication_string from user;
+---------------+-------------------------------------------+
| user | authentication_string |
+---------------+-------------------------------------------+
| root | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| test | *676243218923905CF94CB52A3C9D3EB30CE8E20D |
+---------------+-------------------------------------------+
4 rows in set (0.00 sec)
执行删除命令的时候,千万要再三确认再回车,不然误删了就麻烦了。
授权
mysql> grant all on test.* to 'test'@'localhost';
Query OK, 0 rows affected (0.05 sec)
命令:grant privileges on databasename.tablename to ‘username’@’host’
privileges为用户操作权限,如select、insert等,如果要授予所有的权限,则用all;
databasename为数据库名,table那么为相对应的数据库中的列表,如果授予全部列表,则用星号表示。
查看用户授权
使用show grants for user@host
命令查看
mysql> show grants for test@localhost;
+--------------------------------------------------------+
| Grants for test@localhost |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'test'@'localhost' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)
撤销用户权限
使用REVOKE
命令来收回分配出去的权限。
mysql> revoke all on test.* from 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
flush privileges
创建了用户,删除了用户,进行了授权以后,尽量进行flush privileges;
操作,否则可能相应的操作并不能立即生效。
更改用户密码
我又实验了一下,用之前的版本的修改方法修改密码:
-- 看起来好像不成功
mysql> set password for '2b'@'localhost'=password('123');
Query OK, 0 rows affected, 1 warning (0.03 sec)
当我执行完上面命令以后,提示我有一个警示,没有数据受到影响。但是当我用123这个密码登陆2b用户的时候,竟然登上去了!!!这骗得我太辛苦了……^;^
然后我又换了一种方法,用新加的authentication_string属性去修改密码,居然也成功了!
mysql> update mysql.user set authentication_string=password('123') where user='2b';
Query OK, 1 row affected, 1 warning (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 1
-- 下面这一行必须要执行,否则不能立即生效
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
但是从这第二种方法明显可以看出,修改成功了,因为它显示一行受到影响。