前言
本文系統整理 MySQL 日常操作全流程：連接認證、密碼修改、用戶授權、庫表增刪改查、資料導入導出、備份恢復策略，適合初學者建立完整知識體系，也可作為老手速查手冊。
一、連接 MySQL
1.1 本地連接
# 進入 bin 目錄 (Windows)
cd C:\Program Files\MySQL\MySQL Server 5.7\bin
# 連接
mysql -u root -p
# 輸入密碼 (首次安裝 root 無密碼，直接 Enter)
1.2 遠程連接
mysql -h 110.110.110.110 -u root -p123456
# -h 主機IP，-u 用戶，-p 密碼 (無空格)
1.3 退出
exit
# 或 Ctrl+D
1.4 連接參數速查
| 參數 | 說明 | 範例 |
|---|
-h | 主機地址 | -h 192.168.1.100 |
-u | 用戶名 | -u root |
-p | 密碼 (緊跟無空格) | -p123456 |
-P | 端口 (大寫) | -P 3306 |
-D | 預設資料庫 | -D test |
--protocol | 協議 | --protocol=tcp |
--socket | Unix socket 路徑 | --socket=/tmp/mysql.sock |
二、用戶與權限管理
2.1 修改密碼
# 命令列 (需在 Shell 執行)
mysqladmin -u root -p'oldpass' password 'newpass'
# MySQL 內部 (5.7+)
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpass';
FLUSH PRIVILEGES;
2.2 建立用戶與授權 (MySQL 5.7+ 語法)
-- 1. 建立用戶
CREATE USER 'app'@'10.0.%' IDENTIFIED BY 'StrongPass123!';
-- 2. 授權 (最小權限原則)
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'app'@'10.0.%';
-- 3. 刷新生效
FLUSH PRIVILEGES;
2.3 常見授權模板
| 角色 | 授權語句 |
|---|
| 唯讀帳號 | GRANT SELECT ON db.* TO 'ro'@'%'; |
| 讀寫帳號 | GRANT SELECT,INSERT,UPDATE,DELETE ON db.* TO 'rw'@'%'; |
| 開發帳號 | GRANT ALL PRIVILEGES ON devdb.* TO 'dev'@'%'; |
| 監控帳號 | GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'monitor'@'%'; |
| 備份帳號 | GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'%'; |
2.4 刪除用戶
DROP USER 'app'@'10.0.%';
三、庫表操作核心語法
3.1 資料庫操作
SHOW DATABASES;
CREATE DATABASE school CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE school;
DROP DATABASE school;
3.2 資料表操作
SHOW TABLES;
DESC teacher; -- 或 SHOW COLUMNS FROM teacher;
CREATE TABLE teacher (
id INT(3) AUTO_INCREMENT PRIMARY KEY,
name CHAR(10) NOT NULL,
address VARCHAR(50) DEFAULT '深圳',
year DATE
);
DROP TABLE teacher;
TRUNCATE TABLE teacher; -- 清空資料、重置自增 ID、不記錄 Binlog (快)
3.3 資料增刪改查 (CRUD)
-- 插入
INSERT INTO teacher VALUES (NULL, 'allen', '大連一中', '1976-10-10');
INSERT INTO teacher (name, address) VALUES ('jack', '大連二中');
-- 查詢
SELECT * FROM teacher;
SELECT id, name FROM teacher WHERE address LIKE '%大連%';
-- 更新
UPDATE teacher SET address='北京' WHERE name='allen';
-- 刪除
DELETE FROM teacher WHERE name='jack';
四、文本資料導入
4.1 文件格式要求
- 欄位間用 **Tab (\t)** 分隔
- NULL 值用
\N 表示
- 每行一條記錄，換行符結束
範例 school.txt：
3 rose 大連二中 1976-10-10
4 mike 大連一中 1975-12-23
4.2 導入命令
LOAD DATA LOCAL INFILE 'C:/school.txt' INTO TABLE teacher;
-- 或指定分隔符
LOAD DATA LOCAL INFILE '/path/file.txt' INTO TABLE teacher
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
4.3 導入 SQL 腳本
# Shell 環境
mysql -uroot -p db_name < backup.sql
# MySQL 互動環境
USE db_name;
SOURCE /path/backup.sql;
五、備份與恢復策略
5.1 mysqldump 邏輯備份
| 場景 | 命令 |
|---|
| 整庫備份 (含建庫語句) | mysqldump -uroot -p --databases db1 db2 > full.sql |
| 單表備份 | mysqldump -uroot -p db1 table1 > table1.sql |
| 只備份結構 | mysqldump -uroot -p -d --add-drop-table db1 > struct.sql |
| 指定字元集導出 | mysqldump -uroot -p --default-character-set=latin1 --set-charset=gbk db1 > out.sql |
| 生產環境標準備份 | mysqldump -uroot -p --single-transaction --routines --triggers --events --master-data=2 --databases db1 > prod_backup.sql |
5.2 物理備份 (Percona XtraBackup)
# 全量備份
xtrabackup --backup --target-dir=/backup/full --user=root --password=pass
# 增量備份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full --user=root --password=pass
# 準備/還原
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --copy-back --target-dir=/backup/full
5.3 恢復驗證清單
- 檢查備份檔案大小、行數、Checksum
- 建立測試實例，導入備份
- 核心表
COUNT(*)、關鍵業務欄位抽樣對比
- 驗證存儲過程、觸發器、視圖、事件是否完整
- 記錄 RTO/RPO 實測值
六、實用技巧與避坑
| 技巧 | 說明 |
|---|
| 分行輸入長 SQL | MySQL 客戶端支援多行輸入，以 ; 結束 |
| 歷史命令調用 | ↑/↓ 方向鍵調取上一條命令 |
| 當前資料庫 | SELECT DATABASE(); |
| 當前用戶 | SELECT USER(); |
| 修改 root 密碼 (SQL) | UPDATE mysql.user SET authentication_string=PASSWORD('new') WHERE User='root'; FLUSH PRIVILEGES; |
| 避免 DELETE 全表 | 大表用 TRUNCATE 或 DROP + CREATE |
| 字元集檢查 | SHOW VARIABLES LIKE 'char%'; 確保 utf8mb4 |
七、完整建庫建表腳本範例
DROP DATABASE IF EXISTS school;
CREATE DATABASE school CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE school;
CREATE TABLE teacher (
id INT(3) AUTO_INCREMENT PRIMARY KEY,
name CHAR(10) NOT NULL,
address VARCHAR(50) DEFAULT '深圳',
year DATE
);
INSERT INTO teacher VALUES (NULL, 'allen', '大連一中', '1976-10-10');
INSERT INTO teacher VALUES (NULL, 'jack', '大連二中', '1975-12-23');
-- 執行方式：
-- 1. 存為 school.sql，Shell 執行：mysql -uroot -p < school.sql
-- 2. MySQL 內執行：SOURCE /path/school.sql;
總結
- 連接：掌握本地/遠程參數、退出方式
- 用戶：
CREATE USER → GRANT → FLUSH PRIVILEGES 三步走，最小權限
- 庫表：
CREATE/DROP/DESC、字元集指定 utf8mb4
- CRUD：標準 SQL、避免
SELECT *
- 導入：
LOAD DATA (文本)、SOURCE (SQL)、mysqldump (備份)
- 備份：邏輯備份 (mysqldump) + 物理備份 (XtraBackup) 雙軌制，定期演練恢復
