MySQL 備份與還原資料庫

MySQL 備份與還原資料庫 - 封面圖

資料庫備份最簡單的方式就是直接去 MySQL 目錄下把 data 資料夾複製起來,但此種方式會受限於資料庫版本,而且資料量大的話,這個資料夾也會非常的大,並不建議這樣做,本文也不打算說明這個方式。本文要介紹的是使用 MySQL 提供的 mysqldump 工具來做資料轉移,由於它是命令列的指令,很適合寫成腳本讓系統排程自動備份。

1指令說明

mysqldump 預設路徑為:

作業系統 路徑
Windows C:\Program Files\MySQL\MySQL Server 5.6\bin\
Linux /usr/bin/

詳細參數請參考官網說明,以下僅列出常用的參數:

            
                指令:mysqldump [options] db_name [tbl_name ...]
                --host, -h               // 指定資料庫位置
                --user, -u               // 指定登入帳號
                --password, -p           // 指定登入密碼
                --add-drop-database      // 建立資料庫前先刪除資料庫
                --add-drop-table         // 建立資料表前先刪除資料表
                --all-databases, -A      // 備份全部資料庫
                --default-character-set  // 設定字元編碼
                --defaults-extra-file    // 讀取外部設定檔
                --where                  // 設定備份條件
            
        

2備份

以下備份直接使用範例說明:

            
                // 備份全部資料庫
                $ mysqldump -uroot -p1234 -hlocalhost --default-character-set=utf8 -A > mysql.sql

                // 備份單一資料庫
                $ mysqldump -uroot -p1234 -hlocalhost --default-character-set=utf8 db1 > mysql.sql

                // 備份單一資料表
                $ mysqldump -uroot -p1234 -hlocalhost --default-character-set=utf8 db1 tb1 > mysql.sql
            
        

3還原

還原可以使用 mysql 指令來完成,以下直接使用範例說明:

            
                $ mysql -uroot -p1234 > mysql.sql         // 直接執行備份檔案
                $ mysql -uroot -p1234 db1 > mysql.sql     // 指定資料庫
            
        

4使用設定檔

之前將資料庫升級到 5.6.21 之後,再執行上面指令時都會出現 Warning: Using a password on the command line interface can be insecure. 的警告訊息,雖然指令還是可以執行,不過看到警告訊息就是覺得不是很放心,因此接下來也說明一下如何避免出現這些警告。

第一種方法:打開 MySQL 設定檔 (Windows:C:\Windows\my.ini、Linux:/etc/my.cnf),Windows 作業系統如果 C:\Windows 底下沒有 my.ini 檔案的話,請到 C:\ProgramData\MySQL\MySQL Server 5.6\my.ini 複製,打開設定檔後,在最下面新增以下文字。

            
                [mysqldump]
                user = 資料庫帳號
                password = 資料庫密碼
                host = 資料庫位置
                default-character-set = 字元編碼
            
        

之後你在執行 mysqldump 指令的時候就不需要再指定帳號密碼了,意即:

            
                // 原本下的指令
                $ mysqldump -uroot -p1234 -hlocalhost --default-character-set=utf8 -A > mysql.sql

                // 設定檔修改後
                $ mysqldump -A > mysql.sql
            
        

第二種方法:首先建立一個檔案 config.cnf 內容如下

            
                [client]
                user = 資料庫帳號
                password = 資料庫密碼
                host = 資料庫位置
                default-character-set = 字元編碼
            
        

之後您在執行 mysqldump 指令時可使用以下方式:

            
                $ mysqldump --defaults-extra-file=config.cnf 路徑 -A > mysql.sql
            
        

兩種方式都不會出現警告訊息,至於哪一種比較好,就根據您的需求決定吧,不過我是比較常用第二種。另外,mysqldump 雖然提供 --where 參數可以設定備份條件,但是卻沒有提供欄位的選擇,我之前遇過備份來源的欄位名稱與目的端欄位名稱不一樣,且兩邊的資料欄位都不能修改,後來解決的方式還是自己寫程式產生 sql 檔,再用 mysql 指令還原。


0 則留言