mysql删除本机用户_mysql 不小心把root用户删除了，重建本地用户及远程用户

一、模拟删除破坏用户：

laojiang:~ # mysql -uroot -proot

mysql> use mysql;

mysql> select user,host from user;

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

| user     | host       |

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

| laojiang | %          |

| laojiang | 127.0.0.1  |

| root     | 127.0.0.1  |

| root     | ::1        |

| root     | laojiang |

| laojiang | localhost  |

| root     | localhost  |

| laojiang | laojiang  |

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

8 rows in set (0.00 sec)

mysql> delete from user where user='root';

Query OK, 4 rows affected (0.00 sec)

mysql> select user,host from user;

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

| user     | host      |

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

| laojiang | %         |

| laojiang | 127.0.0.1 |

| laojiang | localhost |

| laojiang | laojiang |

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

4 rows in set (0.00 sec)

mysql>

laojiang:~ # service mysql restart

Shutting down MySQL..                                                                                                                                                 done

Starting MySQL.                                                                                                                                                       done

laojiang:~ # mysql -uroot -proot

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

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

laojiang:~ #

二、重建本地用户：

laojiang:/etc # vi my.cnf

[mysqld]

skip-grant-tables

laojiang:~ # service mysql restart

Shutting down MySQL..                                                                                                                                                 done

Starting MySQL.

laojiang:~ # mysql -uroot -p

Enter password:

mysql> insert into mysql.user (host, user, password,ssl_cipher,x509_issuer,x509_subject) values ('localhost', 'root', password('root'),'','','');

Query OK, 1 row affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

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

Query OK, 0 rows affected (0.00 sec)

mysql>

laojiang:/etc # vi my.cnf

[mysqld]

#skip-grant-tables

laojiang:~ # service mysql restart

Shutting down MySQL..                                                                                                                                                 done

Starting MySQL.                                                                                                                                                       done

三、通过本地用户重建远程用户：

laojiang:~ # mysql -uroot -proot

mysql> select user,host from mysql.user;

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

| user     | host      |

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

| laojiang | %         |

| laojiang | 127.0.0.1 |

| laojiang | localhost |

| root     | localhost |

| laojiang | laojiang |

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

5 rows in set (0.00 sec)

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

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;

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

| user     | host      |

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

| laojiang | %         |

| root     | %         |

| laojiang | 127.0.0.1 |

| laojiang | localhost |

| root     | localhost |

| laojiang | laojiang |

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

6 rows in set (0.00 sec)

mysql>