MySQL误删了所有用户_mysql03--误删除了所有用户解决办法

误删除了所有用户解决办法

第一种方法(企业常用)

1.将数据库down掉

[root@db03 mysql]# /etc/init.d/mysqld stop

Shutting down MySQL.. SUCCESS!

[root@db03 mysql]# mysql

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/opt/mysql-5.6.44/tmp/mysql.sock' (2)

2.给/opt/目录下所有受mysql用户权限

[root@db03 mysql]# chown -R mysql.mysql /opt/*

3.启动数据库

[root@db03 ~]# mysqld_safe --skip-grant-tables --skip-networking &

4.进入数据库

[root@db03 ~]# mysql

5.初始化

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

6.设置数据库用户权限和密码

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

Query OK, 0 rows affected (0.00 sec)

7.退出重启数据库

mysql> \q

[root@db03 mysql]# /etc/init.d/mysqld restar

8.进入数据库查看

mysql> select user,host from mysql.user;

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

| user | host |

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

| root | localhost |

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

1 row in set (0.01 sec)

第二种方法

1.down掉数据库

[root@db04 scripts]# /etc/init.d/mysqld stop

Shutting down MySQL.. SUCCESS!

2.启动数据库

[root@db04 scripts]# mysqld_safe --skip-grant-tables --skip-networking & [1] 25934

3.进入数据库

[root@db04 scripts]# mysql

4.使用数据库

mysql> use mysql

Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed

5.创建root用户

mysql> insert into mysql.user values ('localhost','root',PASSWORD('123'),

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> 'Y',

-> '',

-> '',

-> '',

-> '',0,0,0,0,'mysql_native_password','','N');

6.查看用户

mysql> select user,host from mysql.user;

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

| user | host |

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

| root | localhost |

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

1 row in set (0.00 sec)

第三种方法(企业不可用,数据会丢失)

1.down掉数据库

[root@db04 ~]# /etc/init.d/mysqld stop

Shutting down MySQL.. SUCCESS!

2.删除或改名/opt/mysql/data目录

[root@db04 opt]# cd mysql

[root@db04 mysql]# mv data date

[root@db04 mysql]# rm -fr data

3.初始化

[root@db04 scripts]# ./mysql_install_db --datadir=/opt/mysql/data --basedir=/opt/mysql -- user=mysql

[root@db04 scripts]# echo $?

0

4.授权

[root@db04 scripts]# chown -R mysql.mysql /opt/*

5.启动数据库

[root@db04 scripts]# /etc/init.d/mysqld start

Starting MySQL.Logging to '/opt/mysql/data/db04.err'. SUCCESS!

6.进入数据库查看

[root@db04 scripts]# mysql

mysql> select user,host from mysql.user;

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

| user | host |

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

| root | % |

| root | 127.0.0.1 |

| root | ::1 |

| | db02 |

| root | db02 |

| | localhost |

| root | localhost |

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

7 rows in set (0.00 sec)