MySQL學習筆記十二：資料備份與恢複

標籤：

MySQL學習筆記十二：資料備份與恢複

資料備份

1.物理備份與邏輯備份

物理備份

物理備份就是將資料庫的資料檔案，設定檔，記錄檔等複製一份到其他路徑上，這種備份速度一般較快，因為只有I/O操作。進行物理備份時，一般都 需要關閉mysql伺服器，或者對需要備份的對象進行鎖定，要不很容易造成備份的不一致性，恢複時可能會遺失資料。物理備份的方式有很多，如作業系統命令 copy(cp)，scp，mysqlbackup，以及MyISAM表的mysqlhotcopy。

邏輯備份

邏輯備份是對資料庫的邏輯結構(create database,create table)，以及其所儲存的資料(轉換為insert into)進行備份。這種備份非常的靈活，但是對於大型系統來講，邏輯備份進行恢複時效能較低。邏輯備份工具有 mysqldump，select...into outfile等。

2.聯機備份與離線備份

聯機備份又稱熱備份，即在mysql伺服器運行時進行備份，離線備份，即冷備份，在mysql伺服器停止運行後進行備份。

熱備份的優點是不會影響其他用戶端訪問mysql服務，仍可以正常讀取資料。但是在進行熱備份時需要發生避免資料的修改操作，以至於影響備份的一致性和完整性。

冷備份的有點是沒有用戶端的串連，直接進行備份即可。但為了降低對業務的影響，盡量在slave端進行備份。

3.完整備份和增量備份

完整備份是對整個資料庫所有的資料進行的備份，而增量備份就是指定時間點後發送的修改，增量備份基於mysql binary log來實現。

4.MyISAM表的物理備份

方法一:先鎖表在複製，樣本

mysql> flush tables stu with read lock;Query OK, 0 rows affected (0.00 sec)copy "D:\ProgramData\MySQL\MySQL Server 5.5\data\test\t2.*"  f:\backupD:\ProgramData\MySQL\MySQL Server 5.5\data\test\t2.frmD:\ProgramData\MySQL\MySQL Server 5.5\data\test\t2.MYDD:\ProgramData\MySQL\MySQL Server 5.5\data\test\t2.MYI已複製         3 個檔案。mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)

方法二：使用mysqlhotcopy命令

Windows平台下使用mysqlhotcopy需要安裝perl以及DBD-MYSQL，DBI模組，但是呢，mysqlhotcopy命令也是叫用作業系統命令(cp,scp)來複製檔案，因此在Windows平台下無法使用，故大家可以在Linux平台上實驗。

-----------------------DBD模組的安裝--------------------C:\Users\Lenovo>ppm install DBD::mysqlDownloading DBD-mysql-4.029...doneUnpacking DBD-mysql-4.029...doneGenerating HTML for DBD-mysql-4.029...doneUpdating files in site area...done  11 files installed----------------mysqlhotcopy命令使用------------------C:\Users\Lenovo>mysqlhotcopy.pl -u root -p 123456 test  f:/backupFlushed 11 tables with read lock (`test`.`b`, `test`.`emp_date`, `test`.`t1`, `test`.`t2`, `test`.`t3`, `test`.`tb_1`, `test`.`tb_2`, `test`.`tb_3`, `test`.`tb_5`, `test`.`tb_6`, `test`.`users`) in 1 seconds.Locked 0 views () in 0 seconds.Copying 22 files...------------------在Ubuntu上驗證結果---------------------root@zhumuxian-machine:/# mysqlhotcopy -u root -p 123456 test /data/mysql/backupWarning: /usr/bin/mysqlhotcopy is deprecated and will be removed in a future version.Flushed 1 tables with read lock (`test`.`stu`) in 0 seconds.Locked 0 views () in 0 seconds.Copying 4 files...Copying indices for 0 files...Unlocked tables.mysqlhotcopy copied 1 tables (4 files) in 0 seconds (0 seconds overall).root@zhumuxian-machine:/# ls /data/mysql/backup/test

5.使用mysqldump進行邏輯備份

使用mysqldump工具建立的備份組有兩種格式，一是標準的SQL語句，二是定界格式的檔案。

備份單個資料庫(可指定具體那個表，在資料庫名後面加上表名即可)

PS C:\Users\Lenovo> mysqldump -u root -p123456 --default-character-set gbk zz > f:\zz.sql

備份多個資料庫

PS F:\> mysqldump -u root -p7758520 --default-character-set gbk --databases zz,test > zztest.sql

備份所有的資料庫（如果有不存在的資料庫，則會報錯）

PS F:\> mysqldump -u root -p7758520 --default-character-set gbk --all-databases > db_all.sql

輸出成定界格式檔案

PS F:\> mysqldump -u root -p123456 --default-character-set gbk --tab=f:\mysql_backup --fields-terminated-by ‘,‘ --lines-terminated-by ‘\r\n‘ zz stu---------------如果指定的路徑不存在，則會報錯errorCode2-------------------------------------PS F:\mysql_backup> dirMode                LastWriteTime     Length Name----                -------------     ------ -----a---         2015/4/21      9:18       1482 stu.sql-a---         2015/4/21      9:18        108 stu.txt

6.恢複mysqldump命令建立的備份，使用MYSQL命令

SQL格式恢複

F:\mysql_backup>mysql -u root -p123456 --default-character-set=utf8  zz < .\zz_stu.sql

定界格式恢複

由於這種備份的分為表對象的備份和其資料的備份，首先將表對象恢複，然後利用LOAD DATA INFILE語句匯入表資料。

View Code

 7.增量備份的建立

Mysql的增量備份主要藉助於二進位日誌，因此必須開啟二進位日誌功能，可以在設定檔中設定log-bin的參數或者在開啟mysql服務時指 定--log-bin參數的值開啟該功能。所謂增量備份就是將冷備份之後產生的二進位記錄備份到指定路徑，在需要恢複時，先進行冷備份恢複，再對備份的二 進位日誌進行分析，然後拿到mysql命令中執行。

分析二進位日誌並輸出到SQL檔案中

[email protected]:/home/zhumuxian# mysqlbinlog --no-defaults /data/mysql/mysql-bin.000001 > /data/backup/000001.sql

利用mysql命令執行分析得到的SQL檔案內容

[email protected]:/data/backup# mysql -u root -p123456 < ./000001.sql 

上面兩條命令可以利用管道合并在一起寫

[email protected]:/home/zhumuxian# mysqlbinlog --no-defaults /data/mysql/mysql-bin.000001 | mysql -u root -p123456

如果執行mysqlbinlog命令時出現類似"unknown variable  default-character-set=utf8" 錯誤時，一般都是用戶端識別不了設定檔的某個變數，解決方案如下：

方法一：在設定檔中，將該變數注釋掉

方法二：執行mysqlbinlog命令時，加上--no-defaults選項

方法三：將變數改為loose-variable-name=xxx（我這裡是loose-default-character-set=utf8）

由於mysql二進位日誌採用ROW格式記錄，使用base64編碼，幾乎看不懂，可以利用--base64-output選項參數來解決，該參數 有三個內建值：none(不做ROW格式處理)，decode-rows(解碼處理，一般於-v組合使用)，auto(預設，採用ROW格式)，例示如 下：

-----------------------base64編碼的資訊---------------------------mOA1VQ8BAAAAdAAAAHgAAAAAAAQANS42LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACY4DVVEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAUNe----------------------使用decode-rows顯示-------------------------[email protected]:/data/backup# mysqlbinlog --no-defaults --base64-output=decode-rows -v /data/mysql/mysql-bin.000001

8.第三方聯機備份工具XtraBackup

XtraBackup是一個熱備份工具，支援Innodb，MyISAM，CSV等引擎對象的備份，還是比較流行的。

XtraBackup的安裝：

首先到Percona官網下載最新版的XtraBackup，我使用的是percona-xtrabackup_2.2.10-1.trusty_i386.deb，安裝非常的簡單，只需一條命令即可，如下所示。

[email protected]:/# dpkg -i percona-xtrabackup_2.2.10-1.trusty_i386.deb

查看安裝是否成功

[email protected]:/usr/bin# ls xtra* inno* 
innobackupex  innochecksum  xtrabackup-----------------或者----------------------[email protected]:/usr/bin# xtrabackup --versionxtrabackup version 2.2.10 based on MySQL server 5.6.22 Linux (i686) (revision id: )

XtraBackup工具提供了兩個命令，xtrabackup與innobackupex。

xtrabackup有兩種模式，backup（備份）與prepare(恢複)，並且只能備份Innodb，xtradb引擎對象。

使用xtrabackup命令建立備份

[email protected]:/data/mydata/backup# xtrabackup --defaults-file=/etc/mysql/my.cnf --backup --terget-dir=/data/mydata/backup

上面執行xtrabbackup命令使用了三個參數：

--defaults-files：指定mysql設定檔的路徑，最重要的功能時擷取datadir的值

--backup ： 指定為備份

--target-dir：指定儲存備份組的路徑

查看備份組的路徑

[email protected]:/# ls /data/mydata/backup/ibdata1  test                    xtrabackup_checkpointsmysql    xtrabackup_backupfiles  xtrabackup_logfile

從上面的結果可以看出，xtrabackup只備份資料檔案，不會備份表的結構檔案(.frm)，因此還需手動複製表結構檔案。

innobackupex命令，支援多種引擎對象，該命令建立備份，需要串連資料庫，可以建立一個使用者專門用於備份，如下所示。

mysql> create user [email protected]‘localhost‘ identified by ‘123456‘;Query OK, 0 rows affected (0.00 sec)mysql> grant reload,lock tables,replication client,super on *.* to [email protected]‘localhost‘;Query OK, 0 rows affected (0.00 sec)

使用innobackupex命令建立完整備份，如下所示。

root@zhumuxian-machine:/# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password=‘123456‘ /data/mydata/backup/

上面命令使用了四個參數：--defaults-file 指定設定檔路徑 --user 使用者名稱  --password　使用者密碼   [backup_dir]　指定備份組儲存的路徑

使用innobackupex建立增量備份

root@zhumuxian-machine:/# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password=‘123456‘ --incremental --incremental-basedir=/data/mydata/backup/2015-04-21_22-18-27 /data/mydata/incremental/

上面建立增量備份的命令中，新增了2個參數：--incremental  指定備份為增量備份  --incremental-basedir 指定增量備份基於的全量備份的路徑。

其實只有Innodb引擎對象才能算是增量備份，因為只有該引擎對象才有LSN（記錄序號），增量備份又是基於LSN來實現的，其它的引擎對象增量備份都是完整備份。

使用innobackupex全量恢複

----------------------應用日誌，使資料檔案達到一致性----------------[email protected]:/data/mysql# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password=‘123456‘ --apply-log /data/mydata/backup/2015-04-21_22-18-27/------------------------執行恢複--------------------------------[email protected]:/data/mysql# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password=‘123456‘ --copy-back /data/mydata/backup/2015-04-21_22-18-27/--執行完畢後，查看許可權是否正確

使用innobackupex增量恢複

-------------------------應用日誌------------------------[email protected]:/data/mysql# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password=‘123456‘ --apply-log --redo-only /data/mydata/backup/2015-04-21_22-18-27///有增量備份必須指定--redo-only參數，強制xtrabackup只進行redo而不進行復原------------------------應用增量備份----------------------[email protected]:/data/mysql# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password=‘123456‘ --apply-log  /data/mydata/backup/2015-04-21_22-18-27/ --incremental-dir=/data/mydata/incremental/2015-04-21_22-33-40/----------------------------復原-------------------------[email protected]:/data/mysql# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password=‘123456‘ --apply-log  /data/mydata/backup/2015-04-21_22-18-27/----------------------------拷貝-------------------------[email protected]:/data/mysql# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password=‘123456‘ --copy-back  /data/mydata/backup/2015-04-21_22-18-27/

使用innobackupex打包和壓縮備份組，可以使用--stream參數來指定流格式，只支援tar,xbstream。

[email protected]:/tmp# innobackupex --defaults-file=/etc/mysql/my.cnf --user=innobk --password=‘123456‘ --stream=tar /tmp | gzip > /data/mydata/backup/bk.tar.gz//流格式輸出資料會臨時儲存在/tmp目錄下，然後利用管道儲存在指定路徑，解壓時需要使用-i參數

MySQL學習筆記十二：資料備份與恢複