一、记住密码的情况下,修改MySQL数据库登录密码
方法1、使用 mysqladmin 管理工具修改密码,将原密码 123123 修改为 abcabc
[root@svr5 ~]# mysqladmin -u root -p password
Enter password: # 这里输入的是原密码
New password: # 输入新密码
Confirm new password: # 输入新密码
[root@svr5 ~]#
方法2、 登录到 mysql 中,使用 set password 修改密码, 将原密码 abcabc 修改为 666
[root@svr5 ~]# mysql -uroot -pabcabc # 登录 mysql
.......
mysql> set password for root@localhost= password("666"); # 修改密码
Query OK, 0 rows affected (0.00 sec)
方法3、 登录到 mysql 中, 以 grant 授权的方式修改密码, 将原密码 666 修改为 234
[root@svr5 ~]# mysql -uroot -p666 # 登录 mysql
..............
mysql> grant all on *.* to root@localhost identified by '234'; # 将密码修改为 234
Query OK, 0 rows affected (0.00 sec)
方式4、 登录到 mysql 中, 通过 update mysql.user 表的方式修改密码, 将原密码 234 修改为 999
[root@svr5 ~]# mysql -u root -p234 # 登录 mysql
...............
mysql> update mysql.user set password=password('999') where user='root' and host='localhost'; # 修改密码为 999
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> FLUSH PRIVILEGES; # 更新密码后要刷新一下授权表 或 重启mysql服务
二、忘记密码的情况下,重置MySQL数据库登录密码
方式1、启动mysql服务时加参数 --skip-grant-tables, 将密码重置为 321
[root@svr5 ~]# service mysql stop # 1. 停止 mysql 服务
Shutting down MySQL. SUCCESS!
[root@svr5 ~]#
[root@svr5 ~]# service mysql start --skip-grant-tables # 2. 开启 mysql 服务, 并跳过授权表
Starting MySQL.. SUCCESS!
[root@svr5 ~]#
[root@svr5 ~]# mysql -uroot # 3. 登录 mysql, 无需密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.48-log MySQL Community Server (GPL)
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>
mysql> update mysql.user set password=password("321") where user='root' and host='localhost'; # 4. 将 root 用户密码 改为 321
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> exit; # 5. 退出 mysql
Bye
[root@svr5 ~]# service mysql stop # 6. 停止 mysql 服务, 让修改密码生效
Shutting down MySQL. SUCCESS!
[root@svr5 ~]#
[root@svr5 ~]# service mysql start # 7. 启动 mysql 服务
Starting MySQL.. SUCCESS!
[root@svr5 ~]#
[root@svr5 ~]# mysql -uroot -p321; # 8. 使用 新密码 321 登录
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.48-log MySQL Community Server (GPL)
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>
方式2、修改 mysql 主配置文件 /etc/my.cnf ,在[mysqld] 下添加 skip_grant_tables=1 ,然后将启动mysql 将密码重置为 aaa
[root@svr5 ~]# service mysql stop; # 1. 停止 mysql 服务
Shutting down MySQL. SUCCESS!
[root@svr5 ~]#
[root@svr5 ~]# vim /etc/my.cnf # 2. 编辑主配置文件, 在 [mysql]下添加 skip_grant_tables=1
[root@svr5 ~]# service mysql start; # 3. 启动 mysql 服务
Starting MySQL.. SUCCESS!
[root@svr5 ~]#
[root@svr5 ~]# mysql -uroot # 4. 登录 mysql 无需密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.48-log MySQL Community Server (GPL)
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>
mysql> update mysql.user set password=password('aaa') where user='root' and host='localhost'; # 5. 将密码修改为 aaa
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> exit;
Bye
[root@svr5 ~]# vim /etc/my.cnf # 6. 编辑主配置文件, 将之前添加的 skip_grant_tables=1去掉
[root@svr5 ~]# service mysql restart; # 7. 重启 mysql 服务
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[root@svr5 ~]#
[root@svr5 ~]# mysql -uroot -paaa; # 8. 使用新密码 aaa 登录
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.48-log MySQL Community Server (GPL)
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>