mysql 常用语句

-- 查看死锁

select * from information_schema.innodb_trx;

-- 杀死死锁进程

kill 33291;

-- 查看表情况
show table status like '%base_color%';

-- 查看数据库情况
SELECT table_schema AS "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

-- 刷新统计信息
ANALYZE TABLE sys_mq_consumer_log;

-- 查看表情况
show table status;

-- 收缩表空间
optimize table table_name


-- binlog 时间
select 604800/3600/24;
show variables like '%expire_logs_second%';
show variables like '%expire_logs_second%';
set global binlog_expire_logs_seconds=604800; -- 7天

-- binlog 文件大小
select 1073748124/1024/1024;
show variables like '%max_binlog_size%';
set global max_binlog_size=1073748124; -- 1GB

show variables like '%log_bin%';

-- 查看慢SQL
show variables like '%slow_query_log%';
-- 设置慢SQL
set global slow_query_log=ON;
set global long_query_time=1;
-- 慢SQL数量
show global status like '%slow_queries%'