mysql.user被删除了,MariaDB的mysql.user表被误删除

误删除mysql.user表或者表中的数据

解决方法：

1、停掉所有mysql或mariadb服务：

~]#service mysqld stop(CentOS6)

~]#systemctl stop mysqld.service(CentOS7)

或者

~]#pkill mysql

2、命令行启动：

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

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

MariaDB [none]>use mysql;

4、查看user表：

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

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

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用户数据：

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

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

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

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

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、查看是否授权成功：

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

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

MariaDB [mysql]>commit;

MariaDB [mysql]>flush privileges;

11、重新启动Mariadb并登录：

~]#systemctl restart mariadb.service

~]#mysql -uroot -hlocalhost -p

输入设置的密码(此处是pa$$word)。

登陆成功

12、验证:

MariaDB [none]>show grants;

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