Mysql 误把root删除处理

最新推荐文章于 2024-10-17 17:45:58 发布

原创最新推荐文章于 2024-10-17 17:45:58 发布 · 1.7k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

大数据专栏收录该内容

15 篇文章

订阅专栏

本文介绍在Mysql 5.7.6版本下，当root用户被误删后的详细恢复步骤，包括通过配置skip-grant-tables绕过权限限制，创建新的root用户并赋予所有权限，以及如何配置远程访问权限。若常规方法失败，还提供了彻底删除并重新安装Mysql的方法。

Mysql 误把root删除处理

未测试成功，仅供参考

mysql版本5.7.6

配置允许非用户密码进入

mysql.cnf文件中[mysqld]段加入skip-grant-tables 并保存

重启mysql

terry@terry-VirtualBox:~$ sudo service mysql restart

进入到数据库中并创建root用户以及权限

terry@terry-VirtualBox:~$ mysql
mysql> use mysql;
mysql> insert into user set user='root',ssl_cipher='',x509_issuer='',x509_subject='';
 
mysql> update user set Host='localhost',select_priv='y', insert_priv='y',update_priv='y', Alter_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',create_user_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' where user='root';
 
mysql> update user set authentication_string=password('123456') where user='root';

刷新退出mysql

flush privileges;
quit

注释掉skip-grant-tables参数，重启数据库

添加root远程访问权限

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

解决办法：
配置grant权限

mysql> use mysql;
mysql> update user set grant_priv='Y' where user='root'；

配置root密码

mysql> alter table user add column password varchar(64);
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> update user set password=password('123456')where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

如果以上方法不成功，则删除mysql，重新安装

删除mysql

sudo apt-get autoremove --purge mysql-server-5.7 
#sudo apt-get remove mysql-server         # 没用到,已经没有mysql-server
#sudo apt-get autoremove mysql-server  # 没用到,已经没有mysql-server
sudo apt-get remove mysql-common
sudo rm -rf /etc/mysql/  /var/lib/mysql    #很重要
#清理残留数据
dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P  
sudo apt autoremove
sudo apt autoreclean

安装 mysql5.7

包下载地址：https://dev.mysql.com/downloads/mysql/5.7.html#downloads
解压到/opt/tool/mysql目录下面
安装mysql

sudo dpkg -i mysql-common_5.7.25-1ubuntu16.04_amd64.deb 
sudo dpkg -i libmysql*
sudo dpkg -i mysql-community-client_5.7.25-1ubuntu16.04_amd64.deb 
sudo dpkg -i mysql-community-server_5.7.25-1ubuntu16.04_amd64.deb 
sudo dpkg -i mysql-client_5.7.25-1ubuntu16.04_amd64.deb 
sudo dpkg -i mysql-community-server_5.7.25-1ubuntu16.04_amd64.deb 
sudo dpkg -i mysql-server_5.7.25-1ubuntu16.04_amd64.deb

允许远程访问

修改/etc/mysql/mysql.conf.d/mysqld.cnf文件，

terry@terry-VirtualBox$sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
#bind-address   = 127.0.0.1
bind-address    = 0.0.0.0

重启mysql服务

给root用户远程访问授权

登录mysql ，

mysql -uroot -p123456
mysql>use mysql;
mysql>CREATE USER 'root'@'%'  IDENTIFIED BY '123456';
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
mysql>FLUSH PRIVILEGES;
mysql>EXIT;