MySQL数据库没有root用户和权限（附加：数据库忘记密码）

mysql没有root用户权限

1. 安装数据库的时候是否添加。
2. 数据库被误删。
3. 数据库被攻击。

修改mysql的配置文件：

 vim /etc/my.cnf
 skip-grant-tables     # 在[mysqld]下面添加这一行，忽略权限表

重启mysql

service mysql restart

或

/etc/init.d/mysqld restart

1.创建root用户：

create user 'root'@'localhost' identified by '123456';

localhost表示本地，mysql登入的时候，不用指定ip登入

此步骤可能会报以下错误，没报错的跳过（直接到权限那一步），用一下方法解决：

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

刷新权限:

flush privileges;

再重新创建用户：

create user 'root'@'localhost' identified by '123456';

如果还报错，如果没报错的直接跳到赋予权限那一步，报错的话以下操作：

drop user 'root'@'localhost';

没有再报错，root用户创建成功。
下面赋予root权限：

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; #赋予所有库所有表操作权限
mysql> flush privileges;
mysql> exit;

到这一步没有报错，表明已经成功了，不过要把最开始的配置文件恢复：

vim /etc/my.cnf

删除配置文件中的：

skip-grant-tables

退出，重启mysql：

service mysql restart

或

/etc/init.d/mysqld restart

忘记密码

1. vim /etc/my.cnf，在[mysqld]中添加

skip-grant-tables  # 忽略验证

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]
skip-grant-tables
max_connections = 10000
max_user_connections=2000
default-time-zone = '+08:00'
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION

2. 重启mysql

service mysql restart

3. 使用用户无密码登录

mysql -uroot -p (直接点击回车，密码为空)

4. 选择数据库

use mysql;

5. 修改root密码

update user set authentication_string=password('123456') where user='root';

6 .刷新权限

 flush privileges;

7 .退出

exit;

8 .删除第1部增加的配置信息

skip-grant-tables

9 .重启mysql

service mysql restart