Windows 伺服器中使用 mysqldump 命令匯出資料，解決中文亂碼問題

天下沒有收費的bug發表於2022-12-01
Windows伺服器MySql

起因

環境：阿里雲伺服器(windows server)、mysql(8.0.11)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.11    |
+-----------+
1 row in set
  • 在windows伺服器中每天備份一次資料庫資料，防止出現各種意外。這裡就使用了mysql的 mysqldump 服務來匯出備份，備份初始指令碼如下
@echo off
set hour=%time:~0,2%
if "%time:~0,1%"==" " set hour=0%time:~1,1%
set now=%Date:~0,4%%Date:~5,2%%Date:~8,2%%hour%%Time:~3,2%%Time:~6,2%
echo %now%
set host=127.0.0.1
set port=3306
set user=root
set pass=馬賽克
# 要備份的資料庫，這裡我專案的資料庫是 xxx_project
set dbname=xxx_project
# 備份檔案所在的位置,這裡我固定放在 C:\MysqlDataBackups\ 下面，%dbname%-%now%.sql 是要生成的檔名稱
set backupfile=C:\MysqlDataBackups\%dbname%-%now%.sql
# 執行備份命令 C:\mysql-8.0.11-winx64\bin\mysqldump 是我 mysql 安裝目錄下 mysqldump 所在的位置
"C:\mysql-8.0.11-winx64\bin\mysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname% > %backupfile%
echo delete files before 10 days
forfiles /p E:\MysqlDataBackups /s /m *.sql /d -60 /c "cmd /c del @file /f"
@echo on

命令儲存為 bat 檔案，雙擊執行，能成功在 C:\MysqlDataBackups\ 下面生成一個檔案

但是，當我們開啟檔案看的時候，發現裡面的備註，中文相關的都是亂碼(PS：Notepad++ 軟體開啟不會，可能是因為這個軟體比較智慧)

解決辦法

檢查編碼

  • 這裡為了防止我們資料庫編碼不對，我們先用命令檢視資料庫編碼，這裡可以看到編碼都是正常的, 主要欄位是 character_set_results 的編碼
mysql> show variables like '%char%';
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | utf8                                   |
| character_set_connection | utf8                                   |
| character_set_database   | utf8mb4                                |
| character_set_filesystem | binary                                 |
| character_set_results    | utf8                                   |
| character_set_server     | utf8mb4                                |
| character_set_system     | utf8                                   |
| character_sets_dir       | C:\mysql-8.0.11-winx64\share\charsets\ |
+--------------------------+----------------------------------------+
8 rows in set

上面幾個變數說明：

  • character_set_client: 設定客戶端使用的字符集。
  • character_set_connection: 連線資料庫的字符集設定型別，如果程式沒有指明連線資料庫使用的字符集型別則按照伺服器端預設的字符集設定。
  • character_set_database: 設定資料庫伺服器中某個庫的字符集。
  • character_set_filesystem: 設定檔案系統的字符集。
  • character_set_results: 設定服務端返回給客戶端結果顯示使用的字符集。
  • character_set_server: 設定伺服器安裝時指定的預設字符集。
  • character_set_system: 設定資料庫系統使用的字符集。

如果上面編碼不對的話，想快速設定的話執行：set character_set_results = utf8;

上面這個只在當前終端有效，如果想永久儲存的話還是需要修改my.ini的配置檔案。

解決方案一(不適用)

  • 網上很多說法是 Windows PowerShell 輸出重定向 (“>”) 檔案編碼預設為UTF-16(LE)問題，解決辦法都是把命令拿去 cmd 中執行 ,但是我們這裡是寫成指令碼了，在cmd中執行是手動執行，雖然不是亂碼，但是我們放在指令碼中執行就出現亂碼了，這個辦法顯然對於我來說是不可行的。

mysqldump -uroot -p --add-drop-table xxx_project > D:\xxx.sql

解決方案二(不可行)

  • 還有的文章說在匯出的命令上加上各種編碼引數，如：--default-character-set=utf8

mysqldump -uroot -proot --add-drop-table --default-character-set=utf8 xxx_project > D:\xxx.sql

  • 在網上發現另一個引數--hex-blob, 這個引數主要是為了把BINARY, VARBINARY, BLOB, BIT等型別匯出為十六進位制，因為這些型別比較容易亂碼。再次嘗試：

mysqldump -uroot -proot --add-drop-table --default-character-set=utf8 --hex-blob xxx_project > D:\xxx.sql

開啟檔案後依然亂碼

最終方案

  • 這段話翻譯過來就是：

說明：

在Windows上使用帶有輸出重定向的PowerShell生成的轉儲檔案將建立一個具有UTF-16編碼的檔案:

mysqldump [options] > dump.sql

但是，UTF-16不允許作為連線字符集(請參閱不允許的客戶端字符集)，因此無法正確載入轉儲檔案。要解決這個問題，使用——result-file選項，它會建立ASCII格式的輸出:


mysqldump [options] --result-file=dump.sql
  • 最後執行的命令 --result-file 不會建立檔案。所以我們要提前建立 xxx.sql 檔案，每次執行都會清空 xxx.sql 裡面的檔案內容，然後重新寫入資料到檔案中

mysqldump -uroot -proot --add-drop-table --default-character-set=utf8 --hex-blob xxx_project --result-file=C:\MysqlDataBackups\xxx.sql

  • 解決辦法找到了，但是光是這樣執行，還達不到我們備份的目的，於是結合指令碼，最終修改為：
@echo off
set hour=%time:~0,2%
if "%time:~0,1%"==" " set hour=0%time:~1,1%
set now=%Date:~0,4%%Date:~5,2%%Date:~8,2%%hour%%Time:~3,2%%Time:~6,2%
echo %now%
set host=127.0.0.1
set port=3306
set user=root
set pass=馬賽克
# 要備份的資料庫，這裡我專案的資料庫是 xxx_project
set dbname=xxx_project
# 備份檔案所在的位置,這裡我固定放在 C:\MysqlDataBackups\ 下面，%dbname%-%now%.sql 是要生成的檔名稱
set backupfile=C:\MysqlDataBackups\%dbname%-%now%.sql
#用 bat 命令先建立檔案 backupfile，然後在用 mysqldump 往這個檔案中 寫入資料
echo=>%backupfile%
"C:\mysql-8.0.11-winx64\bin\mysqldump" -h%host% -P%port% -u%user% -p%pass% --default-character-set=utf8 --hex-blob -c --add-drop-table %dbname% --result-file=%backupfile%
echo delete files before 10 days
forfiles /p E:\MysqlDataBackups /s /m *.sql /d -60 /c "cmd /c del @file /f"
@echo on
  • 最後看看效果
  • 最後我們用 windows 的定時任務在每天凌晨三點的時候執行這個 bat 指令碼，就能做到一天備份一次資料庫拉。

相關文章