主從狀態
# 主庫 binlog 位置
mysql -uroot -p -e "SHOW MASTER STATUS\G"
# 從庫狀態
SHOW SLAVE STATUS\G
表資訊與結構
SHOW TABLE STATUS\G;
SHOW CREATE TABLE tbl_name;
DESCRIBE tbl_name;
慢查詢配置
log = /tmp/mysqld.log
slow_query_log_file = /data/mysql/slowquery.log
long_query_time = 2
進程監控
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
系統狀態與變數
SHOW STATUS;
SHOW VARIABLES;
SHOW WARNINGS;
SHOW ERRORS;
負載查看
\s
密碼重置 (跳過權限)
service mysql stop
mysqld_safe --skip-grant-tables &
mysql -u root
UPDATE mysql.user SET authentication_string=PASSWORD('test') WHERE User='root';
FLUSH PRIVILEGES;
字符集修改
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4;
SET NAMES utf8mb4;
SHOW VARIABLES LIKE '%char%';
資料量統計
-- 全庫
SELECT CONCAT(ROUND(SUM(DATA_LENGTH)/1024/1024,2), 'MB') FROM information_schema.TABLES;
-- 指定庫
SELECT ... WHERE TABLE_SCHEMA='forexpert';
-- 指定表
SELECT ... WHERE TABLE_SCHEMA='forexpert' AND TABLE_NAME='member';
版本與執行計畫
SELECT VERSION();
EXPLAIN SELECT ...;
索引失效修復
ANALYZE TABLE tbl_name;
SHOW INDEX FROM tbl_name;
表修復
REPAIR TABLE tbl_name;
清理 Binlog
PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2024-01-01 00:00:00';
RESET MASTER; -- 僅限從庫已同步
分區表查詢
SELECT PARTITION_NAME, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='test';
ALTER TABLE emp CHECK PARTITION p1,p2;
ALTER TABLE emp REPAIR PARTITION p1;
主從不一致修復
pt-table-sync --execute --sync-to-master h=10.232.31.109 --databases test -uroot -p
徹底卸載 MySQL (RPM)
rpm -qa | grep -i mysql | xargs rpm -e
rm -f /etc/my.cnf
rm -rf /var/lib/mysql
rm -f /etc/init.d/mysql
chkconfig --del mysql
whereis mysql | xargs rm -rf
