1) MySQL配置文件 （my.cnf 或 my.ini）
2) MySQL完整的錯誤日誌文件 （error log file）.（如果文件太大，可以壓縮後上傳）
3) MySQL的 slow query log 文件 （如果已經配置收集的話）.
4) 生成下面的mysql_output.txt文本文件（請在查詢性能低、響應慢時運行）：
（請使用具有SUPER權限的MySQL用戶（如root）登錄MySQL命令行客戶端並運行）
TEE mysql_output0416.txt;
select now(),@@version,@@version_comment,@@hostname,@@port,@@basedir,@@datadir,@@tmpdir,@@log_error,
@@slow_query_log_file,user(),current_user(),/*!50600 @@server_uuid,*/@@server_id\G
SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS;
SHOW ENGINES\G
SHOW PLUGINS\G
select benchmark(50000000,(1234*5678/37485-1298+8596^2)); #should take less than 20 seconds
SELECT ENGINE, COUNT(*), SUM(DATA_LENGTH), SUM(INDEX_LENGTH) FROM information_schema.TABLES GROUP BY ENGINE;
SHOW ENGINE INNODB STATUS;
/*!50503 SHOW ENGINE performance_schema STATUS */;
/*!50503 SELECT * FROM performance_schema.setup_instruments WHERE name LIKE 'wait/sync%' AND (enabled='yes' OR timed='yes')*/;
-- Info on transactions and locks
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query,
b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query,
bl.lock_id blocking_lock_id, bl.lock_mode blocking_lock_mode, bl.lock_type blocking_lock_type,
bl.lock_table blocking_lock_table, bl.lock_index blocking_lock_index,
rl.lock_id waiting_lock_id, rl.lock_mode waiting_lock_mode, rl.lock_type waiting_lock_type,
rl.lock_table waiting_lock_table, rl.lock_index waiting_lock_index
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.INNODB_LOCKS bl ON bl.lock_id = w.blocking_lock_id
INNER JOIN information_schema.INNODB_LOCKS rl ON rl.lock_id = w.requested_lock_id\G
SHOW FULL PROCESSLIST;
/*!50503 SELECT * FROM information_schema.innodb_trx */;
/*!50503 SELECT * FROM performance_schema.threads */;
/*!50708 SELECT * FROM sys.session */;
SHOW OPEN TABLES;
SHOW MASTER STATUS\G
SHOW SLAVE STATUS\G
/*!50602 SELECT * FROM MYSQL.SLAVE_MASTER_INFO */;
/*!50602 SELECT * FROM MYSQL.SLAVE_RELAY_LOG_INFO */;
/*!50602 SELECT * FROM MYSQL.SLAVE_WORKER_INFO */;
SHOW MASTER LOGS;
SELECT SLEEP(300);
SHOW GLOBAL STATUS;
SHOW ENGINE INNODB STATUS;
/*!50503 SHOW ENGINE performance_schema STATUS */;
/*!50503 SELECT * FROM performance_schema.setup_instruments WHERE name LIKE 'wait/sync%' AND (enabled='yes' OR timed='yes')*/;
-- Info on transactions and locks
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query,
b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query,
bl.lock_id blocking_lock_id, bl.lock_mode blocking_lock_mode, bl.lock_type blocking_lock_type,
bl.lock_table blocking_lock_table, bl.lock_index blocking_lock_index,
rl.lock_id waiting_lock_id, rl.lock_mode waiting_lock_mode, rl.lock_type waiting_lock_type,
rl.lock_table waiting_lock_table, rl.lock_index waiting_lock_index
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.INNODB_LOCKS bl ON bl.lock_id = w.blocking_lock_id
INNER JOIN information_schema.INNODB_LOCKS rl ON rl.lock_id = w.requested_lock_id\G
SHOW FULL PROCESSLIST;
/*!50503 SELECT * FROM information_schema.innodb_trx */;
/*!50503 SELECT * FROM performance_schema.threads */;
/*!50708 SELECT * FROM sys.session */;
SHOW OPEN TABLES;
SHOW MASTER STATUS\G
SHOW SLAVE STATUS\G
/*!50602 SELECT * FROM MYSQL.SLAVE_MASTER_INFO */;
/*!50602 SELECT * FROM MYSQL.SLAVE_RELAY_LOG_INFO */;
/*!50602 SELECT * FROM MYSQL.SLAVE_WORKER_INFO */;
select * from information_schema.innodb_trx;
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;
select * from performance_schema.events_waits_history;
SHOW MASTER LOGS;
SELECT
t.TABLE_SCHEMA, t.TABLE_NAME, s.TABLE_NAME
FROM
information_schema.tables t
LEFT OUTER JOIN
information_schema.statistics s ON t.TABLE_SCHEMA = s.TABLE_SCHEMA
AND t.TABLE_NAME = s.TABLE_NAME
AND s.INDEX_NAME = 'PRIMARY'
WHERE
s.TABLE_NAME IS NULL
AND t.TABLE_SCHEMA not in ('information_schema','mysql','performance_schema')
AND t.TABLE_TYPE = 'BASE TABLE';
\s
NOTEE;
（注意：SELECT SLEEP(300)會休眠300秒，請勿中斷運行！）
5) 生成下面的query.txt文本文件（請在查詢性能低、響應慢時運行）：
TEE query.txt;
EXPLAIN EXTENDED select count(1) from t_intg_dm_0863;
SHOW WARNINGS;
SHOW CREATE TABLE t_intg_dm_0863\G
SHOW INDEXES FROM t_intg_dm_0863;
SHOW TABLE STATUS LIKE 't_intg_dm_0863'\G
SET PROFILING=1;
SHOW SESSION STATUS;
select count(1) from t_intg_dm_0863;
select sleep(1);
select count(1) from t_intg_dm_0863;
SHOW SESSION STATUS;
SHOW PROFILE ALL FOR QUERY 2;
SHOW PROFILE ALL FOR QUERY 4;
SELECT *
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 2 OR QUERY_ID = 4 ORDER BY SEQ;
SET PROFILING=0;
SET optimizer_trace="enabled=on";
select count(1) from t_intg_dm_0863;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";
NOTEE;
6) OS的狀態信息，生成文件linuxdiags.txt（使用root用戶運行，請在查詢性能低、響應慢時運行）： (注意：先單獨運行 “script"命令，然後再運行其他命令）
script /tmp/linuxdiags.txt
set -x
id
uptime
uname -a
free -m
cat /proc/cpuinfo
cat /proc/mounts
mount
ls -lrt /dev/mapper
pvdisplay
vgdisplay
lvdisplay
df -h
df -i
top -b -d 10 -n 6
iostat -x 10 6
vmstat 10 6
numactl -H
numastat -m
numastat -n
ps -ef | grep -i mysql
ls -al /etc/init.d/ | grep -i mysql
for PID in `ps -ef | awk '/mysqld[^_[]/{print $2}'`; do
echo "PID=$PID";
cat /proc/$PID/limits;
done
ps auxfww | grep mysql
dmesg
egrep -i "err|fault|mysql|oom|kill|warn|fail" /var/log/*
exit
