mysql破密和root用户被删除了的恢复

mysql数据库破密

MySQL密码恢复及设置

1.停止MySQL服务程序。

2.跳过授权表启动MySQL服务程序

skip-grant-tables(添加在配置文件)

3.重设root密码（更新user表记录）

4.以正常方式重启MySQL服务程序

[root@host50 ~]#systemctl  stop  mysqld

[root@host50 ~]#vim /etc/my.cnf

[mysqld]

...

skip-grant-tables             //跳过授权表启动MySQL服务程序

...

[root@host50 ~]#systemctl  start  mysqld

[root@host50 ~]#mysql

mysql> update  mysql.user  set  authentication_string=password("新密码") where  user="root"  and host="localhost";

mysql> flush  privileges;     //更新

mysql> quit;

[root@host50 ~]#mysql -uroot -p

Enter password:

root用户被删了的恢复

当删除mysql.user中的root后所有用户均没有权限时可进行跳过授权表启动Mysql服务进程

]#mysql

mysql> flush privileges;

 mysql> grant all privileges on *.* to root@'localhost' identified by '123456' with grant option;  

mysql> flush privileges;

mysql> quit;

设置数据库管理员root的密码

[root@host50 ~]# mysqladmin -hlocalhost -uroot -p password "654321"

Enter password: ——这里输入旧密码（这个必须记住才可以修改）

mysqladmin: [Warning] Using a password on the command line interface can be insecure.

Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

mysqladmin: unable to change password; error: 'Your password does not satisfy the current policy requirements'

报错信息是密码规则不符合密码规则

[root@host50 ~]# mysqladmin  -hlocalhost -p password "123qqq...A"

Enter password: 

mysqladmin: [Warning] Using a password on the command line interface can be insecure.

Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

警告明文登陆不安全

也可以修改配置文件

命令行操作：

systemctl stop mysqld

vim /etc/my.cnf

[mysql]

 validate_password_policy=0

validate_passwo rd_length= 6

:wq

systemctl start mysqld

[root@host50 ~]# mysqladmin  -hlocalhost -uroot -p password "123456"

Enter password: 

mysqladmin: [Warning] Using a password on the command line interface can be insecure.

Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

明文登陆，提示不安全

再次登陆验证：

[root@host50 ~]# mysql -uroot -p

Enter password: 

恢复管理员root密码：

修改配置文件，跳过密码登陆

命令行操作：

systemctl  stop mysqld

vim /etc/my.cnf

[mysql]

skip-grant-tables

:wq

systemctl start mysqld

查看密码库的连接密码：

mysql> select host,user,authentication_string from mysql.user;

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

| host        | user      | authentication_string                     |

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

| localhost   | root      | *F19C699342FA5C91EBCF8E0182FB71470EB2AF30 |

| localhost   | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

| 192.168.4.% | admin     | *F19C699342FA5C91EBCF8E0182FB71470EB2AF30 |

| %           | webuser   | *F19C699342FA5C91EBCF8E0182FB71470EB2AF30 |

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

修改密码（通过更改表记录的方式）

mysql> update mysql.user set authentication_string=password("123456") where user="root" and host="localhost";

Query OK, 0 rows affected, 1 warning (0.03 sec)

Rows matched: 1  Changed: 0  Warnings: 1

刷新记录

mysql> flush privileges;

Query OK, 0 rows affected (0.04 sec)

不叫选项就会把全部的密码都修改

mysql> update mysql.user set authentication_string=password("123456");

Query OK, 4 rows affected, 1 warning (0.04 sec)

Rows matched: 4  Changed: 4  Warnings: 1

[root@host50 ~]# systemctl  stop mysqld

[root@host50 ~]# vim  /etc/my.cnf

[root@host50 ~]# systemctl  restart  mysqld

再一次跳过密码登陆就不会成功

[root@host50 ~]# mysql

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

登陆验证：

[root@host50 ~]# mysql -uroot -p

Enter password: 

补充知识： 是通过加密函数进行加密

mysql> select "123456"

    -> ;

+--------+

| 123456 |

+--------+

| 123456 |

+--------+

1 row in set (0.00 sec)

mysql> select password("123456");

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

| password("123456")                        |

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

| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

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

1 row in set, 1 warning (0.00 sec)