MySQL 基礎操作完整指南：從連線到權限管理

MySQL 基礎操作完整指南：從連線到權限管理

本文彙總 MySQL 日常運維與開發高頻命令，涵蓋連線認證、庫表管理、數據增刪改查、用戶授權、參數調整、進程監控等核心場景，適合作為速查手冊。

連線與基礎導航

# 登入（生產環境建議使用 --socket 或 --host 顯式指定）
mysql -u root -p
# 進入後常用導航
SHOW DATABASES;
USE db_name;
SHOW TABLES;
DESC table_name;          -- 或 SHOW COLUMNS FROM table_name
SELECT USER();            -- 當前登入用戶
SELECT DATABASE();        -- 當前庫

庫表結構管理 (DDL)

-- 建庫（指定字符集/排序規則）
CREATE DATABASE db1 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 建表（欄位名用反引號）
CREATE TABLE t1 (
    `id` INT(4) NOT NULL,
    `name` CHAR(40)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 清空表（保留結構、重置自增、不可回滾、速度快）
TRUNCATE TABLE t1;

-- 刪除表/庫
DROP TABLE t1;
DROP DATABASE db1;

數據操作 (DML)

-- 插入
INSERT INTO t1 VALUES (1, 'abc');
INSERT INTO t1 (`name`) VALUES ('test');  -- id 自增

-- 更新
UPDATE t1 SET `name`='aaa' WHERE `id` = 1;

-- 查詢
SELECT * FROM t1 WHERE `id` = 1;

用戶與權限體系

-- 建用戶並授權（% 表示任意主機，建議生產限制具體 IP）
CREATE USER 'user1'@'%' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- 僅授權特定庫/表
GRANT SELECT, INSERT, UPDATE ON db1.* TO 'app_user'@'10.0.%';

-- 撤銷權限
REVOKE ALL PRIVILEGES ON *.* FROM 'user1'@'%';
DROP USER 'user1'@'%';

注意：MySQL 5.7+ 已棄用 GRANT ... IDENTIFIED BY 隱式建用戶語法，請分步執行 CREATE USER 再 GRANT。

服務狀態與參數調優

-- 查看運行狀態/變量
SHOW STATUS;
SHOW VARIABLES;
SHOW VARIABLES LIKE 'max_connect%';  -- 模糊匹配

-- 線上臨時調整（重啟失效，持久化需改 my.cnf）
SET GLOBAL max_connect_errors = 1000;
SET GLOBAL innodb_buffer_pool_size = 1073741824;  -- 需 SUPER 權限

-- 進程監控
SHOW PROCESSLIST;          -- 或 SHOW FULL PROCESSLIST 查看完整 SQL
KILL 12345;                -- 終止阻塞/異常進程

常用維護腳本片段

-- 忘記 root 密碼重置流程
# 1. 停服務
systemctl stop mysqld
# 2. 跳過授權表啟動
mysqld_safe --skip-grant-tables --skip-networking &
# 3. 免密登入修改
mysql -u root
UPDATE mysql.user SET authentication_string=PASSWORD('new_pass') WHERE User='root';
FLUSH PRIVILEGES;
# 4. 正常重啟
systemctl restart mysqld

最佳實踐提醒

• 生產環境禁用 root 遠程登入，建立專用運維/應用賬號並最小權限原則授權。
• 字符集統一使用 utf8mb4，避免 Emoji/生僻字存儲截斷。
• 表結構變更（DDL）大表請使用 pt-online-schema-change 或 MySQL 8.0+ 瞬時 DDL，避免鎖表阻塞業務。
• 定期 ANALYZE TABLE 更新統計信息，配合 EXPLAIN 驗證執行計劃。