Mysql備份與恢復(2)---邏輯備份

逆月翎發表於2020-01-09
MySql

資料庫及時備份可以幫助我們在資料庫出現異常當機時及時的使用備份資料進行恢復工作，將因為資料庫當機產生的影響降低到最小。上一篇針對使用xtrabackup工具進行物理備份和資料恢復做了一個詳細講解，本篇主要談談如何使用mysql自帶的備份工具mysqldump進行邏輯備份和資料恢復。如果還圍觀看過上一篇文章的可以先行查詢上一篇文章關於使用xtrabackup進行資料備份與恢復：Mysql備份與恢復(1)---物理備份。

前言上一篇可能存在一個問題，最後一步恢復資料到/var/lib/mysql目錄成功，但是最後重啟資料庫啟動不起來，資料庫日誌一般存放路徑為：/var/log/mysqld.log，經過定位日誌可以檢視到錯誤日誌原因：

  • 2019-10-14T07:43:22.351861Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
  • 2019-10-14T07:43:22.351885Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
  • 2019-10-14T07:43:22.351892Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
  • 2019-10-14T07:43:22.952508Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
  • 2019-10-14T07:43:22.952596Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
  • 2019-10-14T07:43:22.952606Z 0 [ERROR] Failed to initialize builtin plugins.
  • 2019-10-14T07:43:22.952613Z 0 [ERROR] Aborting

很明顯是因為共享表檔案ibdata1不可寫，所以解決方案很簡單：將/var/lib/mysql下的檔案許可權改成可寫，使用命令：

  • chmod -R 777 /var/lib/mysql

然後重啟mysql服務，可以發現mysql服務已經成功啟動，而且備份資料也成功進行恢復了。file

物理備份和邏輯備份的區別

邏輯備份

優點：

  • 備份成sql檔案，恢復資料很簡單。
  • 邏輯備份與儲存引擎無關，所以可以通用備份。
  • 有助於避免資料損壞。

缺點：

  • 資料恢復速度較慢。
  • 需要資料庫服務正常時安排cpu去進行還原操作。

物理備份

優點：

  • 容易跨平臺，基於檔案的物理備份。
  • 恢復資料很快，實際上就是將備份檔案拷貝回原路徑。

缺點：

  • 資料量大的話檔案大。
  • 備份檔案儲存在磁碟本地，如果磁碟出問題容易造成備份檔案丟失。

mysqldump邏輯備份

mysqldump是mysql自帶的一個簡單的備份工具，由於操作簡單而且對不同的儲存引擎通用，所以比較受歡迎。邏輯備份實際上是將資料庫的資料被分成一個sql檔案，然後恢復資料時只需要用圖形化工具匯入這個sql檔案或者dos下使用source命令引入就可恢復資料。首先我們先看下mysqldump的基本語法：

  • mysqldump -uroot -proot -h127.0.0.1 -P3306 test test> test.sql

命令還有幾個可傳引數：

  • --all-databases：備份全部資料庫。
  • -t：不匯出建立表的語句。
  • -d：不匯出資料。
  • -R：匯出儲存過程和函式。
  • --default-character-set：指定編碼
  • --result-file：指定匯出檔案路徑及檔名。
  • --skip-triggers：不匯出觸發器。

1. 備份前踩坑

如果系統中存在兩個mysql版本，執行mysqldump命令就會報錯，報錯資訊如下：

  • mysqldump: Couldn't execute 'SET OPTION SQLQUOTESHOWCREATE=1': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQLQUOTESHOWCREATE=1' at line 1 (1064)

首先使用mysqldump --version命令檢視mysqldump當前版本，看是否和mysql版本一致：

  • mysqldump Ver 10.13 Distrib 5.5.29, for Win64 (x86)

再使用rpm -qa | grep -i mysql命令檢視mysql版本

  • mysql-community-libs-5.7.27-1.el7.x86_64
  • mysql-community-server-5.7.27-1.el7.x86_64
  • mysql57-community-release-el7-8.noarch
  • mysql-community-common-5.7.27-1.el7.x86_64
  • mysql-community-libs-compat-5.7.27-1.el7.x86_64
  • perl-DBD-MySQL-4.023-6.el7.x86_64
  • mysql-community-client-5.7.27-1.el7.x86_64

可以看到mysql版本與mysqldump版本不一致造成的報錯原因，使用whereis mysqld檢視mysql安裝路徑：

  • /usr/local/mysql/bin/mysqld

然後將同處於bin目錄下的mysqldump拷貝到/usr/bin/目錄下覆蓋舊版本的mysqldump即可解決版本不一致的問題：

  • cp /usr/local/mysql/bin/mysqldump /usr/bin/mysqldump

2. 資料備份

剛才說過了命令，我們使用mysqldump是可以進行多種不同方案的備份的。接下來我們簡單看幾種備案方案：

(1) 全庫備份：使用全庫備份會將所有資料庫中的資料全部備份到同一個sql檔案中。

  • mysqldump -uroot -proot -h49.235.28.88 -P3306 --all-databases> /usr/local/all.sql

(2) 備份單個資料庫：將lxy資料庫中的資料備份到sql檔案lxy.sql中。

  • mysqldump -uroot -proot -h49.235.28.88 -P3306 lxy> /usr/local/lxy.sql

(3) 備份lxy資料庫中的users表到user_test.sql檔案。

  • mysqldump -uroot -proot -h49.235.28.88 -P3306 lxy users> /usr/local/user_test.sql

(4) 只備份lxy資料庫的表結構，不備份資料到lxy1.sql檔案中。

  • mysqldump -uroot -proot -h49.235.28.88 -P3306 lxy -d> /usr/local/lxy1.sql

(5) 備份lxy資料庫的表結構和儲存過程，不備份資料到lxy2.sql中。

  • mysqldump -uroot -proot -h49.235.28.88 -P3306 lxy -d -R> /usr/local/lxy2.sql

(6) 不備份表結構，只備份資料到lxy3.sql。

  • mysqldump -uroot -proot -h49.235.28.88 -P3306 lxy -t> /usr/local/lxy3.sql

資料恢復

表級恢復

(1) 登入mysql，選擇對應的資料庫，然後設定編碼。

(2) 使用source命令匯入備份資料。

  • source /usr/local/all.sql

單庫恢復

(1) 登入Mysql，刪除要恢復的舊資料庫。

(2) 使用source命令匯入備份資料。

全庫恢復

(1) 退出mysql，使用mysql命令恢復資料。

  • mysql < /usr/local/all.sql -p

然後在控制檯輸入密碼，密碼不會顯示別以為沒有輸入成功。

mysqldump全量備份 + mysqlbinlog增量備份

剛才已經將mysql全量備份及資料恢復的方式講完了，可以發現mysqldump的使用方式很簡單，但是使用mysqldump進行資料備份會存在一個問題：恢復資料時會丟失掉從備份點開始的更新資料，所以我們需要結合mysqlbinlog進行二進位制日誌增量備份，首先修改/etc/my.cnf檔案，在[mysqld]下啟用二進位制日誌：

  • log_bin=mysql-bin

開啟二進位制日誌，資料庫的任何更新操作都會記錄到二進位制日誌中。接下來看下mysqldump全量備份 + mysqlbinlog增量備份如何進行操作：

首先進行全量備份,新增--flush-logs引數生成新的二進位制日誌檔案：

  • mysqldump --single-transaction --flush-logs --master-data=2 > /usr/local/all1.sql

所以說實際上備份有兩個檔案：mysqldump全量備份生成的/usr/local/all1.sql以及二進位制日誌檔案/var/lib/mysql/mysql-bin.000003檔案。所以恢復資料時，我們需要分成兩部分去進行恢復資料：

先恢復全量備份的資料：

  • mysql < /usr/local/all1.sql -p

然後輸入密碼確認恢復資料。

然後恢復增量備份：

  • mysqlbinlog /var/lib/mysql/mysql-bin.000003 | mysql -p

這樣就可以將資料恢復到恢復點時的資料。而且mysqlbinlog可以通過時間間隔來精確地恢復某個時刻的資料，我們看看bonlog幾個可傳引數：

  • --start-datetime：從某個時間點開始恢復資料
  • --stop-datetime：恢復截止到某個時間內的資料
  • --start-position：開始恢復資料的位置資訊，位置資訊可以從日誌檔案的log_pos中獲取。
  • --end-position：截止恢復資料的位置資訊，位置資訊可以從日誌檔案的log_pos中獲取。

比如我們需要恢復2019-01-01到2019-09-09的資料，我們在恢復增量日誌時就可以這麼書寫mysqlbinlog命令：

  • mysqlbinlog --start-datetime="2019-01-01 00:00:00" --stop-datetime="2019-09-09 23:59:59" /var/lib/mysql/mysql-bin.000003 | mysql -p

歡迎關注公眾號：程式猿周先森。

file

歡迎關注公眾號：程式猿周先森。文章原創於微信公眾號，本平臺不定時更新。

相關文章