MySQL 實戰操作筆記：安裝、配置、備份、密碼重置與常用指令

MySQL 實戰操作筆記：安裝、配置、備份、密碼重置與常用指令

整理自實際部署維護經驗，涵蓋 Windows/Linux 雙平台的安裝路徑調整、服務管理、Root 密碼重置三大法、客戶端工具選擇與備份策略。

一、客戶端工具推薦

• Navicat Premium：功能最全、介面友善、支援多資料庫、資料同步/轉換/報表強大。首選。
• MySQL Workbench：官方免費、EER 圖設計、SQL 開發、伺服器管理一體化。
• DBeaver：開源免費、跨平台、支援 JDBC 所有資料庫。
• 命令列：自動化腳本、遠端 SSH、輕量環境必備。

二、修改資料目錄

Windows (my.ini)

[mysqld]
datadir="D:/Program Files/MySQL/MySQL Server 5.1/Data/"

Linux (my.cnf)

[mysqld]
datadir=/var/lib/mysql

修改後需停止服務、移動現有資料目錄至新位置、啟動服務。

三、服務管理指令

四、Root 密碼重置三大法

方法 1：mysqladmin（已知舊密碼）

mysqladmin -u root -p'old_password' password 'new_password'

方法 2：UPDATE user 表（需進入 MySQL）

mysql -u root -p
UPDATE mysql.user SET authentication_string=PASSWORD('new_password') WHERE User='root';
FLUSH PRIVILEGES;

MySQL 5.7+ 欄位為 authentication_string，5.6 為 password。

方法 3：SET PASSWORD（需進入 MySQL）

mysql -u root -p
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
FLUSH PRIVILEGES;

方法 4：忘記密碼——跳過授權表（通用終極大法）

1. 停止 MySQL 服務
2. 以 --skip-grant-tables 啟動：
   • Windows：mysqld --skip-grant-tables --console
   • Linux：mysqld_safe --skip-grant-tables &
3. 無密碼登入：mysql -u root
4. 更新密碼：

   USE mysql;
   UPDATE user SET authentication_string=PASSWORD('new_password') WHERE User='root';
   FLUSH PRIVILEGES;
   

5. 停止 --skip-grant-tables 進程，正常啟動 MySQL
6. 用新密碼登入驗證

五、配置檔案位置

六、資料備份與恢復速查

邏輯備份

# 完整備份（含結構+資料）
mysqldump -u root -p --single-transaction --routines --triggers --events db_name > backup.sql

# 僅結構
mysqldump -u root -p --no-data db_name > schema.sql

# 僅資料
mysqldump -u root -p --no-create-info db_name > data.sql

# 壓縮備份
mysqldump -u root -p db_name | gzip > backup.sql.gz

恢復

# 完整恢復
mysql -u root -p db_name < backup.sql

# 壓縮檔恢復
gunzip < backup.sql.gz | mysql -u root -p db_name

# 指定字元集恢復
mysql -u root -p --default-character-set=utf8mb4 db_name < backup.sql

實體備份（需停庫或 LVM 快照）

# 停庫複製資料目錄
systemctl stop mysqld
tar czf /backup/mysql_$(date +%F).tar.gz /var/lib/mysql
systemctl start mysqld

七、建立 UTF-8 資料庫

-- utf8mb4（支援 Emoji，推薦）
CREATE DATABASE `my_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- utf8（舊版相容）
CREATE DATABASE `my_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

八、常用管理指令速查

-- 顯示所有資料庫
SHOW DATABASES;

-- 顯示當前資料庫所有表
SHOW TABLES;

-- 顯示表結構
DESC table_name;
SHOW CREATE TABLE table_name;

-- 顯示索引
SHOW INDEX FROM table_name;

-- 顯示程序列表
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 顯示狀態變數
SHOW GLOBAL STATUS LIKE 'Innodb%';
SHOW GLOBAL VARIABLES LIKE 'innodb%';

-- 殺死進程
KILL process_id;

-- 刷新權限
FLUSH PRIVILEGES;

-- 刷新日誌
FLUSH LOGS;

-- 分析表統計
ANALYZE TABLE table_name;

-- 優化表（整理碎片）
OPTIMIZE TABLE table_name;

-- 檢查表
CHECK TABLE table_name;

-- 修復表
REPAIR TABLE table_name;