MariaDB的mysql.user表被误删除

yutrew

误删除mysql.user表或者表中的数据
解决方法：
1、停掉所有mysql或mariadb服务：

1 2 3 4

~]#service mysqld stop（CentOS6） ~]#systemctl  stop mysqld.service（CentOS7） 或者 ~]#pkill mysql

2、命令行启动：

1	~]#/bin/mysqld_safe --skip-grant-tables &

3、进入mysql库，用户信息存放在mysql库下user表：

1	MariaDB [none]>use mysql;

4、查看user表：

1	MariaDB [mysql]>select Host,User,Password from mysql.user;

5、(注意：本步骤在无mysql.user表时执行)因为误删除user表，所以现在需要在mysql库下新创建user表：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

MariaDB [mysql]>create table `user` (   `Host` char(60) collate utf8_bin NOT NULL default '',   `User` char(16) collate utf8_bin NOT NULL default '',   `Password` char(41) character set latin1 collate latin1_bin NOT NULL default ' ',   `Select_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Insert_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Update_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Delete_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Create_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Drop_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Reload_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Shutdown_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Process_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `File_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Grant_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `References_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Index_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Alter_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Show_db_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Super_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Create_tmp_table_priv` enum('N','Y') character set utf8 NOT NULL default 'N',      `Lock_tables_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Execute_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Repl_slave_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Repl_client_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Create_view_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Show_view_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Create_routine_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Alter_routine_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `Create_user_priv` enum('N','Y') character set utf8 NOT NULL default 'N',   `ssl_type` enum('','ANY','X509','SPECIFIED') character set utf8 NOT NULL defau lt '',   `ssl_cipher` blob NOT NULL,   `x509_issuer` blob NOT NULL,   `x509_subject` blob NOT NULL,   `max_questions` int(11) unsigned NOT NULL default '0',   `max_updates` int(11) unsigned NOT NULL default '0',   `max_connections` int(11) unsigned NOT NULL default '0',   `max_user_connections` int(11) unsigned NOT NULL default '0',   PRIMARY KEY  (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';

6、插入root用户数据：

1	MariaDB [mysql]>insert into mysql.user (Host,User,Password) values(‘localhost’,’root’,password(’pa$$word’));

7、查看添加root用户的权限：

1	MariaDB [mysql]>select * from mysql.user where User=’root’ \G

8、添加属于root用户的管理权限：

1

MariaDB [mysql]>update mysql.user set  Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',Create_user_priv='Y',Event_priv='Y',Trigger_priv='Y',Create_tablespace_priv='Y' where User='root' and Host='localhost';

9、查看是否授权成功：

1	MariaDB [mysql]>select * from mysql.user where User=’root’;

10、完成提交并刷新内存数据：

1 2	MariaDB [mysql]>commit; MariaDB [mysql]>flush privileges;

11、重新启动Mariadb并登录：

1 2	~]#systemctl restart mariadb.service ~]#mysql -uroot -hlocalhost -p

输入设置的密码（此处是pa$$word）。
登陆成功
12、验证:

1 2	MariaDB [none]>show grants; MariaDB [none]>show grants for root@localhost;