MySQL 常用建表、授權、備份、還原 SQL 命令速查表

整理高頻 MySQL 運維指令，適合貼在工位牆上隨時查閱。

連線與基礎

mysql -h host -u user -p'pass'   # 連線（-p 後直接跟密碼無空格）
exit                              # 退出

資料庫操作

SHOW DATABASES;
CREATE DATABASE `db_name` DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
DROP DATABASE `db_name`;
USE `db_name`;

資料表操作

SHOW TABLES;
DESC `table_name`;          -- 結構
SHOW CREATE TABLE `tbl`;    -- 建表 SQL

CREATE TABLE `t` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `t` ADD COLUMN `age` TINYINT UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE `t` MODIFY `name` VARCHAR(100) NOT NULL;
ALTER TABLE `t` DROP COLUMN `age`;
ALTER TABLE `t` RENAME TO `t2`;
DROP TABLE `t`;
TRUNCATE TABLE `t`;         -- 清空表（比 DELETE 快、不記錄 Binlog）

索引操作

ALTER TABLE `t` ADD INDEX `idx_name` (`name`);
ALTER TABLE `t` ADD UNIQUE `uk_email` (`email`);
ALTER TABLE `t` DROP INDEX `idx_name`;

授權與使用者

CREATE USER 'app'@'%' IDENTIFIED BY 'strong_pass';
GRANT SELECT, INSERT, UPDATE, DELETE ON `db`.* TO 'app'@'%';
GRANT ALL PRIVILEGES ON `db`.* TO 'admin'@'10.0.0.%' WITH GRANT OPTION;
REVOKE INSERT ON `db`.* FROM 'app'@'%';
FLUSH PRIVILEGES;
DROP USER 'app'@'%';

備份與還原

# 單庫備份（含結構+資料）
mysqldump -h host -u user -p'pass' --single-transaction --routines --triggers `db` > db.sql

# 僅結構
mysqldump -d `db` > db_struct.sql

# 壓縮備份
mysqldump `db` | gzip > db_$(date +%F).sql.gz

# 還原
mysql -h host -u user -p'pass' `db` < db.sql
gunzip < db.sql.gz | mysql -u user -p'pass' `db`

# 跨主機直接同步
mysqldump -h src_host -u u -p'p' `db` | mysql -h dst_host -u u -p'p' `db`

資料匯入匯出

# CSV 匯出
SELECT * INTO OUTFILE '/tmp/t.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' FROM `t`;

# CSV 匯入
LOAD DATA INFILE '/tmp/t.csv' INTO TABLE `t` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;

維護指令

ANALYZE TABLE `t`;          -- 更新統計資訊
OPTIMIZE TABLE `t`;         -- 整理碎片（MyISAM/InnoDB 皆可）
CHECK TABLE `t`;            -- 檢查損壞
REPAIR TABLE `t`;           -- 修復（主要 MyISAM）
mysqlcheck -A -o -u root -p  -- 全庫優化

修改密碼

# 5.7+
SET PASSWORD FOR 'user'@'host' = PASSWORD('newpass');
# 8.0
ALTER USER 'user'@'host' IDENTIFIED BY 'newpass';

建議將常用指令存為 ~/.mysql_cheatsheet，配合 alias myc='cat ~/.mysql_cheatsheet' 隨時查閱。