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;
配置完成。