MySQL实用操作记录

1 启用功能

1.1 启用MySQL慢查询日志

-- 查看是否开启慢查询日志
show variables like '%slow_query_log%';
-- 开启慢查询日志
set global slow_query_log = 'ON';


-- 查看慢查询日志文件
show variables like '%slow_query_log_file%';
-- 设置慢查询日志文件
set global slow_query_log_file = '/var/lib/mysql/slow.log';


-- 查看慢查询的设定时间
show variables like '%long_query_time%';
show global variables like '%long_query_time%';
-- 设置慢查询的设定时间
set long_query_time = 2;
set global long_query_time = 2;


-- 查看慢查询记录是输出到数据表中还是文件中
show variables like 'log_output';
-- 设置慢查询记录输出到数据表中或文件中
set global log_output = 'table/file';
set global log_output = 'table,file';


-- 从数据表中查看慢查询记录
select * from mysql.slow_log;
-- 查看一共有多少条慢查询记录
show global status like '%Slow_queries%';


-- 执行一条慢SQL
select sleep(3);

2 实用操作

2.1 设置数据库最大连接

-- 查看数据库最大连接
show variables like 'max_connections';

-- 设置数据库最大连接
set global max_connections = 200;

2.2 查看线程信息

-- 只能列出前100个线程
show processlist;
-- 列出全部线程
show full processlist;
-- 列出全部线程
select * from information_schema.PROCESSLIST where info is not null;

上述命令用于查看当前数据库运行的所有线程、命令、状态、耗时、SQL等信息。如果是root用户执行，则能看到所有用户的当前的连接状态；如果是普通用户，只能看到当前用户占用的数据库连接。

2.3 查看事务和锁信息

-- 查看当前运行的所有事务
select * from information_schema.INNODB_TRX;

-- 查看当前出现的所有锁
select * from information_schema.INNODB_LOCKS;

-- 查看锁等待的对应关系
select * from information_schema.INNODB_LOCK_WAITS;

-- 查询当前锁性能状态
SHOW STATUS LIKE 'innodb_row_lock_%';

2.4 查看是否存在死锁

SELECT b.trx_state, e.state, e.time, d.state AS block_state, d.time AS block_time, 
a.requesting_trx_id, a.requested_lock_id, b.trx_query, b.trx_mysql_thread_id, a.blocking_trx_id, 
a.blocking_lock_id, c.trx_query AS block_trx_query, c.trx_mysql_thread_id AS block_trx_mysql_tread_id
FROM information_schema.INNODB_LOCK_WAITS a
LEFT JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id = b.trx_id
LEFT JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id = c.trx_id
LEFT JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id = d.id
LEFT JOIN information_schema.PROCESSLIST e ON b.trx_mysql_thread_id = e.id
ORDER BY a.requesting_trx_id;