MySQL · 05/14/2019 0

mysql日常操作及错误解决

1、查看mysql默认读取my.cnf的目录:

mysql --help|grep 'my.cnf'

输出:

                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

靠前为优先级最高。

2、查询mysql运行配置信息:

SHOW VARIABLES LIKE '%查询字符%'

3、5.7查询group by后的其他字段报错:

错误信息:which is not functionally dependent on columns in GROUP BY clause

解决:5.7后默认开启了sql_mode:ONLY_FULL_GROUP_BY,只能查询group by中的列,my.cnf配置中删除此参数即可。

4、默认日期0000-00-00 00:00:00 Invalid date错误:

解决:sql_mode设置了:NO_ZERO_IN_DATE, NO_ZERO_DATE。这两个选项禁止了0000的日期和时间,删除此参数即可。

5、查看当前连接:

SHOW processlist;
#默认展示100条,展示全部:
SHOW FULL processlist;
#注意区分帐号,root帐号可查询全部

#查询表:
select * from information_schema.PROCESSLIST

6、查看状态值:

SHOW STATUS;#当前运行状态
SHOW GLOBAL STATUS;#全局

SHOW STATUS LIKE '%查询字符%';
#eg:
show variables like '%slow%';

7、ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction:

原因:执行sql,未commit,超时结束。

解决:

#临时解决,通过一下查看:
show engine innodb status;
show full processlist;

#当前运行的所有事务
SELECT * FROM information_schema.INNODB_TRX;
#当前出现的锁
SELECT * FROM information_schema.INNODB_LOCKs;
#锁等待的对应关系
SELECT * FROM information_schema.INNODB_LOCK_waits;

看事务表INNODB_TRX,是否有正在锁定的事务线程,查询ID是否在processlist表中的sleep线程中,如果有就说明这个sleep的线程事务一直没有commit或者rollback卡住了,需要手动kill掉。

示例:

经过:使用navicat某版本导入sql后,事务锁未提交,更改无效也不报错,命令行执行显示超时。 

查询show full processlis和select * from information_schema.PROCESSLIST结果STATE不一致(原因未知,表中状态正确),最终查询innodb_trx有一条数据,查询processlist ID,kill ID,杀掉进程。

#innodb_trx表中的trx_mysql_thread_id:MySQL中的进程ID,与show processlist中的ID值相对应

常用列介绍:

trx_id:InnoDB存储引擎内部唯一的事物ID
trx_status:当前事务的状态
trx_status:事务的开始时间
trx_requested_lock_id:等待事务的锁ID
trx_weight:事务的权重,反应一个事务修改和锁定的行数,当发现死锁需要回滚时,权重越小的值被回滚

trx_wait_started:事务等待的开始时间

trx_mysql_thread_id:MySQL中的进程ID,与show processlist中的ID值相对应
trx_query:事务运行的SQL语句