• 注：环境配置信息，系统环境CentOS 7.4，数据库版本 mysql-5.7.24

1，跳过授权表

# 在命令行跳过授权表命令mysqld_safe --skip-grant-tables &# 在 my.cnf 文件配置跳过授权表命令[mysqld]skip-grant-tables

2，更改root密码

# 方法一：update mysql.user set authentication_string=password('root') where user='root' and host='localhost';flush privileges;# 报错如下：使用方法二ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.# 方法二：alter user user() identified by "root";flush privileges;

# 更改密码策略为LOWset global validate_password_policy=0;

3，创建新用户并授权

# 授权本地登录grant all privileges on *.* to test@localhost identified by 'test';# 授权远程登录grant all privileges on *.* to test@'%' identified by 'test';

4， 通过 Navicat 创建数据库报错

# 报错如下[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by# 查询select version(), @@sql_mode;# 临时生效修改SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); # 永久生效改 my.cnfsql_mode='NO_ENGINE_SUBSTITUTION'# 重启mysqlservice mysql restart

5，查死锁

show engine innodb status \G;select * from information_schema.INNODB_TRX;