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)