nubbymysql_mysql修改用户密码

修改自己的密码(root用户&＃xff0c;其它用户应该也差不多)

方法一&＃xff1a;

[root&＃64;localhost /]# mysqladmin -u root -p password "root" #修改密码为root

Enter password: #输入旧密码

[root&＃64;localhost /]# mysql -uroot -p #尝试使用旧密码登录 Enter password: ERROR 1045 (28000): Access denied for user &＃39;root&＃39;&＃64;&＃39;localhost&＃39; (using password: YES) [root&＃64;localhost /]# mysql -uroot -p #输入新密码root登录 Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 19 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement. MariaDB [(none)]>

方法二&＃xff1a;

在mysql.user中使用update更新密码

方法三&＃xff1a;

或者进入mysql后&＃xff0c;使用set修改密码

[root&＃64;localhost /]# mysql -uroot -p #使用旧密码root登录

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 19 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement. MariaDB [(none)]> set password&＃61;password("123456"); #修改密码为123456&＃xff0c;我一直很好奇为什么密码必须用password扩起来&＃xff0c;后来知道了&＃xff0c;新密码必须用password来加密 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> quit Bye [root&＃64;localhost /]# mysql -uroot -p #使用新密码123456登录 Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 20 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement. MariaDB [(none)]>

root用户修改指定用户密码

方法一

MariaDB [(none)]> set password for &＃39;bp&＃39;&＃64;&＃39;localhost&＃39;&＃61;password("123456"); Query OK, 0 rows affected (0.01 sec)

方法二&＃xff1a;

MariaDB [(none)]> update mysql.user set password&＃61;password("123") where user&＃61;&＃39;bp&＃39; and host&＃61;&＃39;localhost&＃39;; #使用update修改密码&＃xff0c;修改成功后&＃xff0c;我打开另一个终端使用该用户登录数据库&＃xff0c;发现无法使用新密码登录&＃xff0c;但使用旧密码可以登录

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [(none)]> select user,host from mysql.user; #因为报错信息里面包含localhost&＃xff0c;于是查看用户表信息有没有错&＃xff0c;遗憾的是没有 &＃43;---------&＃43;-----------------------&＃43; | user | host | &＃43;---------&＃43;-----------------------&＃43; | aa | % | | aaa | % | | root | 127.0.0.1 | | root | ::1 | | | localhost | | aa | localhost | | bb | localhost | | bp | localhost | | ggo | localhost | | my | localhost | | mytest | localhost | | newuser | localhost | | nome | localhost | | root | localhost | | | localhost.localdomain | | root | localhost.localdomain | &＃43;---------&＃43;-----------------------&＃43; 16 rows in set (0.00 sec) MariaDB [(none)]> flush privileges; #后来想起来&＃xff0c;是不是还要刷新权限。刷新之后&＃xff0c;使用新密码可以登录 Query OK, 0 rows affected (0.00 sec)

方法三&＃xff1a;grant修改密码

MariaDB [mytest]> grant select on mytest.test to &＃39;bp&＃39;&＃64;&＃39;localhost&＃39; identified by &＃39;linux&＃39;; Query OK, 0 rows affected (0.05 sec) #这个不需要刷新权限。。

mysql5.7修改密码

cat /var/log/mysqld.log|grep &＃39;temporary password&＃39;

alter user &＃39;root&＃39;&＃64;&＃39;localhost&＃39; identified by &＃39;root&＃39;;

忘记密码(需要重启服务器)

在/etc/my.cnf的mysqld里面增加skip-grant-tables (5.7以前的应该是skip-grant)

重启mysqld

mysql> update mysql.user set authentication_string&＃61;password(&＃39;,,,abc123...&＃39;) where user&＃61;&＃39;root&＃39;;  (旧版的应该是update mysql.user set password&＃61;password(&＃39;,,,abc123...&＃39;) where user&＃61;&＃39;root&＃39;;)

Query OK, 1 row affected, 1 warning (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 1

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> quit

重启服务器