欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL修改密码和忘记ROOT密码

程序员文章站 2022-05-26 08:33:44
...

前言:本文档介绍MYSQL修改用户密码的方法和忘记root密码后的处理方法。 一、三种修改密码的方式 1、修改当前用户的登录密码 脚本

前言:本文档介绍MYSQL修改用户密码的方法和忘记root密码后的处理方法。

一、三种修改密码的方式

1、修改当前用户的登录密码

脚本:SET PASSWORD=PASSWORD('netpassword');

例:

mysql> set password=password('root');

Query OK, 0 rows affected (0.00 sec)

2、使用SET修改其他用户的密码,,通过ROOT修改其他账号的密码

脚本:SET PASSWORD FOR

'user' @'host'=PASSWORD('newpassword')

例:

mysql> select host,user from mysql.user;

+----------------+--------+

| host | user |

+----------------+--------+

| | test |

| % | john1 |

| % | mytest |

| 127.0.0.1 | root |

| 192.168.189.71 | john |

| 192.168.189.71 | john1 |

| ::1 | john |

| ::1 | root |

| localhost | john |

| localhost | root |

| mysql | root |

+----------------+--------+

11 rows in set (0.00 sec)

mysql> SET PASSWORD FOR

-> 'john1' @'%'=PASSWORD('john');

Query OK, 0 rows affected (0.00 sec)

3、使用UPDATE语句修改其他用户的密码,需要有修改MYSQL权限的用户,一般用ROOT用户

脚本:

UPDATE mysql.user SET

PASSWORD=PASSWORD('newpassword')

WHERE

User='username'

AND

Host='host';

例:

mysql> UPDATE mysql.user SET

-> PASSWORD=PASSWORD('root')

-> where

-> User='root' AND Host='127.0.0.1';

Query OK, 0 rows affected (0.02 sec)

Rows matched: 0 Changed: 0 Warnings: 0

二、忘记ROOT密码的处理方法

1、关闭数据库

脚本:[root@mysql etc]# service mysql stop

2、使用脚本: mysqld_safe --skip-grant-tables 启动数据库

使用/usr/bin/mysqld_safe --skip-grant-tables&启动数据库

3、使用空密码进入数据库(mysql命令后直接回车)

[root@mysql ~]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.6.20-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, 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>

4、使用UPDATE语句修改ROOT密码

mysql> UPDATE mysql.user SET

-> PASSWORD=PASSWORD('root')

-> where

-> User='root' AND Host='localhost';

5、关闭数据库并重新以正常方式启动数据库

[root@mysql ~]# service mysql restart;

Shutting down MySQL.. SUCCESS!

Starting MySQL.. SUCCESS!

[root@mysql ~]# mysql

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@mysql ~]# mysql -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.20-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, 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>

显示可以成功登录,整个过程很简单。大功告成!!!

--------------------------------------分割线 --------------------------------------

Ubuntu 14.04下安装MySQL

《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF

Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主从服务器

Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群

Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二进制安装

--------------------------------------分割线 --------------------------------------

本文永久更新链接地址:

MySQL修改密码和忘记ROOT密码