【資料庫】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                  // 設定備份條件
        
    

備份

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

        
            //備份全部資料庫
            $ 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
        
    

還原

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

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

使用設定檔

之前將資料庫升級到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指令還原。

Facebook 留言