MySQL - 備份、還原資料庫(匯出、匯入)

5 月 2, 2019

--

--

匯出資料庫 - 一個資料庫

--

匯出資料庫 - 從資料庫挑選資料表

有時候匯出資料庫並不需要全部的資料表,可以手動或是根據條件式來挑選

備份 1 2 3 資料表

使用 like 過濾,挑選 user 開頭資料表,--login-path=root 是身份驗證方式請根據 MySQL 或 MariaDB 自行替換

使用 not like 過濾,不想備份 log 又剛好資料表都是 _log 結尾就可以使用

--

匯出不包含視圖 view

雖然可以使用 --ignore-table 來排除 "指定" 資料表,可是當有多個資料庫並且都有多個資料表需要排除時,沒有人會人工輸入。這時可以到 information_schema 資料庫的 TABLES 資料表來查詢

查詢所有 TABLE_TYPE 為 BASE TABLE 的資料表,view 的 TYPE 是 VIEW

應用

--

備份所有資料庫,並且一個資料庫一個檔案

--

匯出不鎖定資料庫

預設備份資料庫會鎖住資料庫,這時就無法做任何操作,如果不需要一致性的備份資料庫,可以設定不鎖定

網路上說可以加上 --quick

--

只匯出結構(包含預存程序、事件、觸發器)

--

只匯出資料

--

正確的備份、復原資料庫方式

你可以直接整個把資料庫匯出,但是卻無法正確的匯入!

因為資料建立是有順序的,為什麼可以建立 view 是因為資料表結構已經存在,routine 和 trigger 也是,所以按照預設的 mysqldump 會根據字母順序匯出,只要 view 有使用到後面的資料表那匯入就會出錯,重複匯入可以解決不過你不會想浪費好幾倍的時間。

我們需要分開備份

  1. 結構
  2. 視圖
  3. 預存程序、觸發器、事件
  4. 資料

匯入的時候再按照順序就不會看到一個錯誤

--

使用 .sql 匯入資料庫

加上 -f 可以跳過錯誤繼續匯入,例如重複建立資料表

使用 mysql shell

--

使用 gz 匯入資料庫

--

匯入出現 MySql server has gone away

--

使用 phpMyAdmin 匯入有 foreign key 的資料表處理方案

匯入 fk 資料表時,就算所有關聯資料表一起匯入也會因為順序不一致而發生

這個錯誤

從網站上可以查詢到必須設定 SET FOREIGN_KEY_CHECKS=0; ,但是使用 SQL 指令執行後再使用 SELECT @@FOREIGN_KEY_CHECKS; 查看會發現還是 1 ,也就是沒有被關閉

解決的方法是修改匯入 .sql 檔案,在最開頭加上 SET FOREIGN_KEY_CHECKS=0; 以及結尾加上 SET FOREIGN_KEY_CHECKS=1;

類似像這樣

--

使用 LOAD DATA 匯入 CSV 檔案

直接匯入 CSV 檔案到 MySQL 資料庫

--

實際範例

需要注意指令順序

--

效能

如果可以使用 LOAD DATA 一定會比使用程式拆解後逐筆插入快,五萬筆的匯入在小白 MacBook 上也只需不到 3 秒

--

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet'

--

Using a password on the command line interface can be insecure.

MySQL 在 cli 要自動執行必須使用 mysql_config_editor 設定

--

ERROR 1045 (28000): Access denied for user 'XXXXX'@'%' (using password: YES)

將 LOAD DATA INFILE 改成 LOAD DATA LOCAL INFILE

--

 11,756 total views,  14 views today

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。