1.误删root的解决办法
ubuntu14.04
Server version: 5.7.16 MySQL Community Server (GPL)
不小心删除了mysql的root用户,并且又没有创建其他用户,导致无法连接mysql服务。以下是恢复root用户的尝试步骤,简要记录。其中参考了较多其他网页的内容,全部在最后的参考中。
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
在mysqld下面添加skip-grant-tables,然后重启数据库,直接在命令行下输入mysql
,试图进入到mysql命令提示符界面,报错,无法使用root用户连接。所以证明这种方式行不通。
下面的方法,是可以的,在我本机上验证过,解决了我的问题。
启动mysql,shell
sudo mysqld_safe --user=mysql --skip-grant-tables --skip-networking
–skip-grant-tables:不启动grant-tables(授权表),跳过权限控制。
–skip-networking :跳过TCP/IP协议。
mysql -u root
进入mysql命令提示符之后,添加root用户,并且对root用户进行授权
use mysql;
insert into user set user='root',ssl_cipher='',x509_issuer='',x509_subject='';
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';
grant all privileges on *.* to 'root'@'localhost' identified by '123456';
flush privileges;
注:update语句是修改root用户的权限。
然后,将对的修改恢复即可。
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
最后进行重启mysql即可。
下面是一些其他相关语句,备用。
CREATE USER 'root'@'localhost' IDENTIFIED BY '123456';
grant all on *.* to root @'%' identified by '';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '1234' WITH GRANT OPTION;
CREATE USER 'bugzillauser'@'localhost' IDENTIFIED BY 'SetYourPassword';
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
drop user 'root'@'localhost';
grant select,insert,update,delete,create,drop on vtdc.employee to joe@10.163.225.87 identified by ‘123′;
2.数据库日志、数据等文件的位置
备注:/etc/mysql/mysql.conf.d/mysqld.cnf内容,可以看到mysql日志文件、数据文件位置
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
参考网页
[1]Mysql(Linux服务器)root用户密码忘记重置方法
http://blog.csdn.net/roy_70/article/details/53006405
[2]ubuntu16.04下mysql5.7支持utf-8编码格式配置文件修改步骤
http://blog.csdn.net/liunian_siyu/article/details/53605802
[3]解决误删mysql.user中user:roothost:localhost那一行
http://www.2cto.com/database/201703/607545.html
[4]使用MySQL命令行新建用户并授予权限的方法
http://www.cnblogs.com/penciler/p/4813157.html
[5]mysql不小心删除root恢复
http://blog.csdn.net/guobing965816/article/details/7706574