Python - - MySQL数据库 - - 操作锦集

• 注：环境配置信息，系统环境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;

# 更改密码策略为LOW

set 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.cnf
sql_mode='NO_ENGINE_SUBSTITUTION'
# 重启mysql
service mysql restart

5，查死锁

show engine innodb status \G;

select * from information_schema.INNODB_TRX;