MySql 備份登入的帳號與存取 table 的權限

要備份 MySQL 的登入帳號、密碼以及權限（如對 Table 的存取權），最直接的方式是備份系統內建的 mysql 資料庫。這個資料庫專門存儲使用者資訊與權限表（如 user, db, tables_priv 等）。

以下提供三種主要的備份與匯出方式：

1. 使用 mysqldump 備份權限表 (指令列)

這是最常用的方法，直接將系統權限相關的資料表匯出成 SQL 檔。 [2]

備份所有使用者與權限：

mysqldump -u root -p mysql user db tables_priv columns_priv procs_priv > mysql_privileges.sql

這會匯出包含帳號密碼 (user)、資料庫權限 (db) 以及資料表權限 (tables_priv) 的 SQL 檔。

備份整個系統資料庫 (最完整)：

mysqldump -u root -p mysql > full_mysql_system_backup.sql

注意：若還原到不同版本的 MySQL，直接覆蓋 mysql 資料庫可能導致相容性問題。

2. 匯出權限指令 (推薦用於移機)

如果你想產出純粹的 CREATE USER 和 GRANT 指令（方便在另一台主機執行），可以使用 mysqlpump 特定參數：

使用 mysqldump (最新版支援)：

mysqldump -u root -p --system=users --no-data --no-create-info > user_grants.sql

–system=users 會將帳號與權限轉換為 GRANT 語法，而非單純的資料表內容。

3. 使用 phpMyAdmin (圖形介面)

適合不習慣指令的使用者：

  1. 登入 phpMyAdmin。
  2. 在左側面板點選名為 mysql 的資料庫。
  3. 勾選 user (帳號)、db (庫權限)、tables_priv (表權限) 等資料表。
  4. 點擊上方的 「匯出」(Export)，選擇 SQL 格式並執行下載。

還原與生效

還原後，記得在 MySQL 指令列執行以下指令，權限才會立即生效： 

FLUSH PRIVILEGES;

安全提醒： 備份檔中包含帳號的密碼雜湊值（Hash），請妥善保管此檔案，避免外流。

還原 MySQL 資料庫的方法主要取決於你手邊有的備份檔案類型。最常見的情況是擁有一個 .sql 格式的備份檔。

以下是三種最常用的還原教學：

1. 使用命令列 (Command Line)

這是最穩定且支援大檔案的方法。請開啟終端機（Linux/Mac）或命令提示字元（Windows），並確保已安裝 MySQL 指令列工具。

還原特定資料庫：

mysql -u [帳號] -p [資料庫名稱] < [備份檔路徑].sql

範例：

mysql -u root -p my_database < /path/to/backup.sql

注意事項： 在執行指令前，請先手動建立一個空的資料庫，名稱需與指令中的一致。

2. 使用 phpMyAdmin (圖形化網頁介面)

適合不熟悉指令的使用者，常用於虛擬主機。

  1. 登入 phpMyAdmin。
  2. 在左側選單中，點選你要還原進去的目標資料庫。
  3. 點擊頂端選單的「匯入」 (Import)。
  4. 在「檔案匯入」區塊點選「選擇檔案」，挑選你的 .sql 或 .sql.gz 檔案。
  5. 捲動到頁面最下方，點擊「執行」 (Go)。

3. 使用 MySQL Workbench (官方工具)

如果你是開發者並使用電腦上的管理工具：

  1. 開啟 MySQL Workbench 並連線至資料庫伺服器。
  2. 在左側選單點擊 「Data Import/Restore」。
  3. 選擇 「Import from Self-Contained File」，並選取你的備份檔。
  4. 在 「Default Target Schema」 下拉選單選擇目標資料庫。
  5. 點擊右下角的 「Start Import」 開始執行。

常見問題處理

  • 檔案過大： 若使用 phpMyAdmin 匯入大檔案失敗，建議改用「方法 1」指令還原。
  • 外鍵約束錯誤： 若還原時出現 foreign key constraints 錯誤，有時需要在 SQL 檔開頭暫時停用外鍵檢查。
  • InnoDB 損毀： 如果資料庫是因為檔案損毀（如 XAMPP 崩潰），則需要進行 InnoDB 損毀修復 程序。

要還原整個 MySQL 系統資料庫（包含所有使用者帳號、密碼、權限及全域設定），通常是將之前備份好的 mysql 資料庫 SQL 檔匯入回去。

請注意，還原系統資料庫具有高風險，建議在執行前先將目前的 mysql 資料庫備份一份，以防版本不相容導致資料庫無法啟動。

以下是還原步驟：

1. 使用命令列還原 (最推薦)

這是最完整且不會被網頁逾時限制影響的方法。

執行指令：

mysql -u root -p mysql < full_mysql_system_backup.sql

這會將 SQL 內的資料表直接覆蓋回現有的 mysql 系統資料庫中。

2. 令權限立即生效

還原完成後，MySQL 伺服器並不會自動讀取新的權限表，你必須手動登入 MySQL 並執行以下指令：

FLUSH PRIVILEGES;

⚠️ 重要注意事項 (必讀)

  1. 版本相容性：
    • 嚴禁將舊版 MySQL (如 5.7) 的系統資料庫直接還原到新版 (如 8.0)，這會導致伺服器崩潰或無法啟動。
    • 如果是因為升級需求，還原後通常需要執行 mysql_upgrade (在 MySQL 8.0.16 之前的版本) 來修復系統表結構。
  2. 連線中斷風險：
    • 如果你在還原過程中更改了 root 密碼或權限，還原完成後你當前的連線可能會失效，下次登入請使用「備份檔中」的舊密碼。
  3. 僅針對特定使用者還原：
    • 如果你只是想在「新主機」上重建帳號，而不打算覆蓋整個系統設定，建議從 SQL 檔中提取 CREATE USER 和 GRANT 語句，而不是還原整個 mysql 資料庫。

這是一個非常典型的 MariaDB/MySQL 恢復衝突。錯誤訊息 ERROR 1050 (42S01): Table 'user' already exists 意思很直白：資料庫裡已經有一個叫做 user 的資料表了，所以執行 CREATE TABLE 時就撞牆了。

這通常發生在還原 mysql 系統資料庫時，因為 MariaDB 啟動後會自動生成這些系統表。

為什麼會發生這個問題？

當你執行 mariadb-dump (或 mysqldump) 時，如果備份指令沒有包含 DROP TABLE 的語句，還原程式就不會主動刪除現有的資料表。而 mysql.user 是系統核心表，它永遠存在於運行中的資料庫中。

解決方案

方法一：在還原指令中加入強制覆蓋（推薦）

如果你目前是手動匯入 SQL 檔，請檢查你的備份檔（dump file）。通常建議在備份時就加上 --add-drop-table，但如果你現在只有這份檔案，可以嘗試在還原指令中加上 -f (force) 參數，強制跳過錯誤繼續執行：

Bash

mariadb -u root -p -f < your_backup_file.sql

方法二：手動修改 SQL 備份檔

如果你希望還原過程乾淨俐落，可以編輯你的 .sql 檔，在 CREATE TABLE "user" 語句之前，手動加入一行：

SQL

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` ( ...

注意： 刪除 mysql.user 表具有高度風險。如果你正在還原的是 mysql 這個系統資料庫，建議先備份目前的 /var/lib/mysql 目錄。

方法三：使用 --replace 或 --insert-ignore

如果你的目的是「更新」權限而非「重建」表結構，且備份檔中包含 INSERT 語句，你可以考慮將 INSERT INTO 改成 REPLACE INTO 或 INSERT IGNORE INTO。不過這需要大範圍修改備份檔，較不建議。

💡 專業建議：處理系統權限表的正確姿勢

還原 mysql.user 表後，MariaDB 可能不會立即套用新的權限。請務必執行以下指令來刷新權限緩存：

SQL

FLUSH PRIVILEGES;

此外，如果你是跨版本還原（例如從舊版 MySQL 搬到新版 MariaDB），直接還原 mysql.user 結構可能會導致系統崩潰，因為不同版本的欄位數量和定義不同。在這種情況下，建議：

  1. 只匯入資料列（Insert）。
  2. 匯入後執行 mariadb-upgrade 進行修復。

請問您是從舊機器搬移到新機器，還是單純的資料誤刪回滾呢？ 知道情境後我可以提供更精確的步驟。

這是一個非常關鍵的轉折點。你遇到的 ERROR 1356 是因為 MariaDB 10.x 與 11.x 之間對權限系統進行了重大重構。

在 MariaDB 10.4 之後（包含你現在用的 11.8），mysql.user 已經不再是一個單純的資料表（Table），而是一個視圖（View）。它真正的資料儲存在 mysql.global_priv 之中。

為什麼會報錯？

你正試圖用舊版（10.0）的 CREATE TABLE 語法去覆蓋新版（11.8）的系統架構：

  1. 結構衝突：新版的 mysql.user 是 View，你強行用 CREATE TABLE 去還原它，導致資料庫內部邏輯錯亂。
  2. 欄位不相容：10.0 的 user 表有 Password 欄位，而 11.x 使用 JSON 格式儲存在 global_priv 中。

正確的還原步驟（針對跨大版本升級）

在跨越這麼大的版本號（10.0 -> 11.8）時，絕對不要直接還原整份 mysql 系統資料庫。請依照以下邏輯操作：

1. 只還原業務資料（資料庫）

先將你的 App 資料還原，跳過系統表：

Bash

# 假設你的備份檔叫 backup.sql
# 排除 mysql, information_schema, performance_schema 等系統庫
mariadb -u root -p --force < backup.sql

2. 手動遷移用戶（建議做法）

由於 mysql.user 的底層結構完全改變，最安全的方法是在 10.0 環境使用工具匯出「帳號權限語句」（GRANT 語法），而非直接匯出資料表。

如果你手邊只有該 SQL 檔，請嘗試搜尋檔案中的 INSERT INTO mysql.user 語句，並手動將其轉化為：

SQL

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'host';

3. 強制執行並修復（高風險但快速）

如果你必須匯入該系統表，匯入後資料庫可能會無法正常啟動或權限鎖死，此時必須執行修復工具：

  1. 執行匯入（加上 -f 強制跳過錯誤）。
  2. 執行修復指令：Bashmariadb-upgrade -u root -p --force 這個工具會掃描舊版的系統表，並自動將其轉換為 11.8 所需的格式（例如將 user 表轉換為 global_priv 並重新建立 user View）。

總結建議

版本mysql.user 本質存儲方式
MariaDB 10.0實體資料表 (Table)傳統欄位 (Password, Select_priv…)
MariaDB 11.8視圖 (View)存儲於 mysql.global_priv (JSON 格式)

最穩妥的方案：

不要還原備份檔中的 mysql 資料庫部分。先還原你的資料（例如 my_app_db），然後在新主機上手動重新 CREATE USER。

既然您有大量的帳號需要遷移，且從 MariaDB 10.0 直接匯入到 11.8 會因為底層架構從「實體表」變成「JSON 視圖」而崩潰，最專業且安全的做法是：從 SQL 備份檔中提取 GRANT 語句，在新環境重新執行。

我為您準備了一個 Bash 腳本，它可以幫您從舊的 .sql 檔案中過濾出建立使用者與權限的語法，避開直接操作系統表（System Tables）。

🛠️ 解決方案：提取並遷移權限

請在您的 Linux 環境下執行以下指令（假設您的備份檔名為 old_backup.sql）：

Bash

# 1. 提取所有的 CREATE USER 與 GRANT 語句
grep -E "CREATE USER|GRANT" old_backup.sql > migrate_users.sql

# 2. 檢查一下產出的檔案內容是否正確
cat migrate_users.sql

💡 如果您的備份檔只有 INSERT INTO mysql.user…

這是最麻煩的情況，因為 10.0 的 INSERT 語法與 11.8 完全不相容。若發生這種情況，請嘗試以下「強制升級法」：

  1. 先備份目前的環境（以防萬一）。
  2. 強制匯入系統表：使用 --force 忽略所有 View 或 Table already exists 的錯誤。Bashmariadb -u root -p -f mysql < old_backup.sql
  3. 關鍵步驟：執行系統升級工具。這個工具會偵測到您的 mysql.user 格式過舊，並自動將其轉換為 11.8 所需的 global_priv 格式。Bashmariadb-upgrade -u root -p --force
  4. 重啟服務：Bashsystemctl restart mariadb

⚠️ 重要提醒

  • 密碼相容性：MariaDB 11.8 預設可能使用更強的加密外掛（如 ed25519）。如果 10.0 的帳號使用的是舊式的 mysql_native_password，請確保新環境的 my.cnf 設定中允許舊式認證，否則使用者會無法登入。
  • 不要覆蓋 View：在 11.8 中，mysql.user 是一個方便開發者查看的 View。如果您強行用 CREATE TABLE 把他變成實體表，MariaDB 的權限系統會發生不可預期的錯誤。

在 Windows 環境下，處理 SQL 備份檔的大量文字過濾不像 Linux 那樣可以直接用 grep，但我們可以利用 PowerShell 或是 Notepad++ 來達成同樣的目的。

由於 MariaDB 11.8 將 mysql.user 視為「視圖（View）」，而你的 10.0 備份檔嘗試將其建立為「資料表（Table）」，這就是報錯的主因。

以下是針對 Windows 使用者的三種解決方案：

方法一：使用 PowerShell 提取權限語句（推薦）

如果你想從幾百 MB 甚至 GB 等級的 SQL 檔中只抓出帳號設定，請開啟 PowerShell 並執行：

PowerShell

# 尋找包含 CREATE USER 或 GRANT 的行，並存到新檔案
Select-String -Path ".\your_backup.sql" -Pattern "CREATE USER|GRANT" | Select-Object -ExpandProperty Line > migrate_users.sql

執行後，你只需要將產出的 migrate_users.sql 匯入新資料庫即可。

方法二：手動修正 SQL 檔案（適用於小型備份檔）

如果你習慣使用編輯器（如 Notepad++ 或 VS Code），請開啟備份檔並搜尋：

  1. 刪除 CREATE TABLE user 區塊：從 DROP TABLE IF EXISTS "user"; 到 PRIMARY KEY ... ) ENGINE=MyISAM ...; 的這整段程式碼都刪除。
  2. 保留 INSERT 語句：如果你的備份檔是用 INSERT INTO mysql.user 寫入資料，請保留它們，但執行還原時要加上 --force。

方法三：Windows 命令列「強制還原 + 修復」

如果你懶得改檔案，想直接硬衝（這是在 Windows 最常見的做法）：

  1. 開啟 CMD 或 PowerShell 並進入 MariaDB 的 bin 目錄（例如 C:\Program Files\MariaDB 11.8\bin）。
  2. 強制執行還原（忽略錯誤繼續跑）：
    mariadb.exe -u root -p --force < "C:\路徑\你的備份檔.sql"
  3. 執行關鍵修復工具：這是將 10.0 結構轉換為 11.8 結構的核心步驟。
    mariadb-upgrade.exe -u root -p --force 這個工具會看到 mysql.user 表格不對勁，自動幫你把它修復成 11.8 規格的 global_priv 並重新建立正確的 View。

⚠️ Windows 環境的特別注意事項

  • 路徑空格：如果你的備份路徑有空格（例如 C:\My Backups\db.sql），請務必加上雙引號。
  • 編碼問題：Windows 預設可能是 UTF-16 或 Big5，但 MariaDB 備份通常是 UTF-8。若執行 PowerShell 提取後出現亂碼，請在指令後加上 -Encoding utf8。
  • 服務重啟：執行完 mariadb-upgrade 後，建議到「服務 (services.msc)」重啟 MariaDB 服務。

看到這串錯誤訊息，問題的核心已經非常明確了：

在 MariaDB 10.11 中，mysql.user 不再是一個實體資料表，而是一個 View。你的備份檔（來自 10.0）試圖：

  1. CREATE TABLE user：這會失敗，因為系統中已經有一個叫 user 的 View（ERROR 1050）。
  2. INSERT INTO user：這也會失敗，因為你不能直接對這個特殊的系統 View 執行傳統的 INSERT 語法（ERROR 1356）。

🛠️ Windows 環境下的最快解決方案

手動改寫成 CREATE USER 太慢了。請按照以下步驟執行「強制覆蓋與結構修復」：

第一步：強制匯入（跳過錯誤）

打開 CMD (命令提示字元)，切換到 MariaDB 的 bin 目錄，執行還原指令並加上 -f (force) 參數。這會讓程式忽略 user 表建立失敗的錯誤，繼續把其他資料還原。

cd /d "C:\Program Files\MariaDB 10.11\bin"
mariadb.exe -u root -p -f < "C:\你的路徑\備份檔.sql"

注意：匯入過程中畫面上會持續出現錯誤訊息，這是正常的，請讓它跑完。

第二步：執行結構轉換（最關鍵）

這是修復 mysql.user 的核心動作。mariadb-upgrade 會偵測到你的系統表是 10.0 的舊格式，並自動將那些 INSERT 進去的舊資料轉換成 10.11 所需的 global_priv 格式，並重新生成正確的 user View。

mariadb-upgrade.exe -u root -p --force

第三步：刷新權限與重啟

在 CMD 中登入 MariaDB 執行刷新：

mariadb -u root -p -e "FLUSH PRIVILEGES;"

然後去 Windows 的「服務」(services.msc) 重新啟動 MariaDB 服務。

💡 為什麼會有 ERROR 1100 (LOCK TABLES)?

這是因為備份檔開頭通常有 LOCK TABLES user WRITE;，但因為前面的 CREATE TABLE 失敗了，導致資料庫認為 user 表根本沒被鎖定，後面的 INSERT 和 UNLOCK TABLES 就會連續報錯。只要執行完上面的 mariadb-upgrade，這些錯誤都會被自動修正。

📝 如果 mariadb-upgrade 失敗了怎麼辦？

如果 upgrade 工具無法修復，代表 10.0 到 10.11 的跨度太大導致結構鎖死。這時你最後的手段是：

  1. 只還原業務資料庫：不要還原 mysql 這個資料庫。
  2. 手動提取帳號：如果你需要我幫你寫一段可以在 Windows 跑的 PowerShell 腳本，將你的 INSERT INTO user VALUES (...); 轉換成 CREATE USER 語法，請告訴我，我立刻寫給你。

請問您執行完 mariadb-upgrade 後，帳號可以正常登入嗎？

Related Posts

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *