MySQL 運維知識庫：SQL 技巧、管理命令、權限模型與監控指標

一、高頻 SQL 技巧

1.1 生日計算周歲

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS('1788-11-26')), '%Y') + 0;

原理：日期差轉天數 → FROM_DAYS 得基準年 → 取年份 → 隱式轉數值。

1.2 多表關聯刪除

DELETE t1, t2
FROM class t1
INNER JOIN stuscore t2 ON t1.classid = t2.classid
WHERE t1.classid IN (1, 2);

1.3 指定位置新增列

ALTER TABLE stuscore ADD COLUMN classid INT NOT NULL AFTER course;

1.4 狀態變量重置

FLUSH STATUS;  -- 將 Threads_created 等計數器歸零
SHOW STATUS LIKE '%thread%';

關鍵指標：

• Threads_connected：當前連接數；
• Threads_created：歷史創建線程數，過大說明 thread_cache_size 過小；
• Threads_running：活躍線程數。

二、管理命令實戰

2.1 mysqladmin 修改密碼（指定主機）

/usr/mysql/bin/mysqladmin -h 192.168.0.3 -u yangsq -p password

注意：-h 不支持通配符 %，需具體 IP。

2.2 多實例關閉指定端口

/usr/mysql/bin/mysqladmin --port 3306 shutdown

三、權限模型細節

3.1 USAGE 權限

• 創建賬號但不授權：CREATE USER 'u'@'%' IDENTIFIED BY 'pwd'; 等價於 GRANT USAGE ON *.* TO 'u'@'%';；
• 僅能登錄、查看 information_schema 和 test 庫；
• 不可回收：REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'root'@'127.0.0.1'; 後仍保留 GRANT USAGE ON *.* TO 'root'@'127.0.0.1';。

3.2 Shell 變量判空最佳實踐

# -n 非空為真，但必須雙引號包裹變量防止語法錯誤
if [ -n "$var" ]; then echo "non-empty"; fi

四、運維檢查清單

五、常用調優參數速查

[mysqld]
innodb_buffer_pool_size = 1G        # 物理內存 60-70%
innodb_log_file_size = 128M         # 大事務支撐
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 1  # 數據安全
max_connections = 1500
thread_cache_size = 100
join_buffer_size = 128M
sort_buffer_size = 10M
read_rnd_buffer_size = 2M
event_scheduler = ON
explicit_defaults_for_timestamp = ON
lower_case_table_names = 1