Mysql備份的三種方法

Mysql備份的主要作用是當(dāng)出現(xiàn)硬件故障、軟件故障、自然災(zāi)害、******、人為誤操作時(shí)能快速、高效、安全的解決問題，以免造成不必要的損失

Mysql備份時(shí)需要備份什么？

  1、需要用到的數(shù)據(jù)

  2、二進(jìn)制日志、InnoDB事務(wù)日志

  3、存儲(chǔ)代碼；例如：存儲(chǔ)過程、存儲(chǔ)函數(shù)、觸發(fā)器、時(shí)間調(diào)度器

  4、服務(wù)器的配置文件

Mysql備份時(shí)需要注意的要點(diǎn)：

  1、能容忍最多丟失多少數(shù)據(jù)

  2、恢復(fù)數(shù)據(jù)需要在多長(zhǎng)時(shí)間內(nèi)完成

  3、需要恢復(fù)那些數(shù)據(jù)

  4、做好備份之后，必須要做還原測(cè)試，用于測(cè)試備份的可用性

Mysql備份時(shí)需要考慮的因素

  1、持鎖需要多長(zhǎng)時(shí)間

  2、備份的過程時(shí)長(zhǎng)

  3、在備份時(shí)，是否會(huì)增加系統(tǒng)業(yè)務(wù)的負(fù)載

  4、恢復(fù)過程的時(shí)長(zhǎng)

Mysql備份的類型；

  以數(shù)據(jù)集角度來劃分：

    完全備份：備份整個(gè)數(shù)據(jù)集

    部分備份：只備份數(shù)據(jù)子集

  以時(shí)間軸角度來劃分：

    完全備份：備份整個(gè)數(shù)據(jù)集

    增量備份：備份最近一次完全備份或增量備份之后變化的數(shù)據(jù)

    差異備份：備份最近一次完全備份之后變化的數(shù)據(jù)

    注意：雖然增量備份比差異備份更節(jié)約磁盤空間，但是還原數(shù)據(jù)起來增量備份卻比差異備份更麻煩

  根據(jù)服務(wù)器業(yè)務(wù)系統(tǒng)能否在線來劃分：

    熱備份：讀寫操作均可執(zhí)行

    溫備份：讀操作可執(zhí)行，但寫操作不可執(zhí)行

    冷備份：讀寫操作均不可執(zhí)行

    注意：InnoDB存儲(chǔ)引擎即支持熱備份，也支持溫備份；而MyISAM存儲(chǔ)引擎只支持溫備份，不支持熱備份

  物理備份：直接復(fù)制數(shù)據(jù)文件進(jìn)行備份

  邏輯備份：從數(shù)據(jù)庫(kù)中導(dǎo)出數(shù)據(jù)從而進(jìn)行備份；邏輯備份與存儲(chǔ)引擎無關(guān)，如果備份的是InnoDB存儲(chǔ)引擎，用在MyISAM存儲(chǔ)引擎上也是可以的

Mysql備份的方式：

  1、mysqldump+復(fù)制binlog來實(shí)現(xiàn)：使用mysqldump來實(shí)現(xiàn)數(shù)據(jù)的完全備份，復(fù)制binlog中指定時(shí)間范圍內(nèi)的event來實(shí)現(xiàn)增量備份

  2、lvm2+復(fù)制binlog：利用lvm2快照以及使用cp等命令工具來實(shí)現(xiàn)物理備份、完全備份，復(fù)制binlog中指點(diǎn)事件范圍內(nèi)的event來實(shí)現(xiàn)增量備份

  3、xtrabackup：由Percona提供的備份工具；對(duì)InnoDB支持熱備、完全備份和增量備份，MySIAM支持溫備、完全備份

一、基于mysqldump+復(fù)制binlog來實(shí)現(xiàn)備份

準(zhǔn)備環(huán)境：

1、部署節(jié)點(diǎn)1

（1）安裝mysql服務(wù)，編輯其二進(jìn)制文件并進(jìn)行完全備份

[root@localhost ~]# yum install mysql-server -y  ##安裝Mysql服務(wù)
[root@localhost ~]# vim /etc/my.cnf  ##編輯其配置文件
[mysqld]
innodb_file_per_table = ON
log_bin=mysql-bin   ##啟用二進(jìn)制文件
[root@localhost ~]# mysqldump -uroot --lock-tables --master-data=2 --databases Mydata > db1.sql   ##在未修改數(shù)據(jù)庫(kù)之前做一次完全備份
## --lock-tables：鎖定指定的數(shù)據(jù)庫(kù)，建議在備份單個(gè)數(shù)據(jù)庫(kù)是使用
## --lock-all-tables：鎖定所有的數(shù)據(jù)庫(kù)，建議在備份多個(gè)數(shù)據(jù)庫(kù)時(shí)使用
## --master-data=1 | 2
  #1：記錄為change master to語句，此語句不會(huì)被注釋
  #2：記錄為change master to語句，此語句會(huì)被注釋 
## --databases：指定需要備份的數(shù)據(jù)庫(kù)，當(dāng)有多個(gè)數(shù)據(jù)庫(kù)時(shí)中間可用空格隔開
[root@localhost ~]# less db1.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1106;  ##記錄當(dāng)前備份時(shí)的二進(jìn)制日志，以及事件在二進(jìn)制日志中所在位置

（2）修改數(shù)據(jù)庫(kù)中的數(shù)據(jù)，并使用“mysqlbinlog”生成增量備份

mysql> select * from db1;   ##未修改數(shù)據(jù)之前查看一次數(shù)據(jù)
+----+------------+------+
| ID | Name     Age  
+----+------------+------+
|  1 | BaiGu jing  100 
|  2 | SunDa shen  200
|  3 | ZhuBa jie  150 
+----+------------+------+
mysql> insert into db1 (ID,Name,Age) values (4,'RuLai',999);
mysql> insert into db1 (ID,Name,Age) values (5,'YuDi',888);
mysql> select * from db1;   ##修改數(shù)據(jù)之后查看一次
+----+------------+------+
| ID | Name     Age  
+----+------------+------+
|  1 | BaiGu jing  100 
|  2 | SunDa shen  200 
|  3 | ZhuBa jie   150 
|  4 | RuLai     999 
|  5 | YuDi      888 
+----+------------+------+
[root@localhost ~]# mysqlbinlog -uroot --start-position=1106 /var/lib/mysql/mysql-bin.000003 > binary_log  ##生成增量備份
##--start-position：指明事件的pos
[root@localhost ~]# less binary_log   ##查看增量備份文件
insert into db1 (ID,Name,Age) values (4,'RuLai',999)   ##記錄了修改表的相關(guān)操作
/*!*/;
# at 1223
#160612 21:44:35 server id 1  end_log_pos 1339  Query   thread_id=9     exec_time=0     error_code=0
SET TIMESTAMP=1465739075/*!*/;
insert into db1 (ID,Name,Age) values (5,'YuDi',888)
/*!*/;
# at 1339
#160612 21:45:41 server id 1  end_log_pos 1441  Query   thread_id=9     exec_time=0     error_code=0
SET TIMESTAMP=1465739141/*!*/;

2、部署節(jié)點(diǎn)2

（1）提供mysql服務(wù)，并將節(jié)點(diǎn)1的完全備份文件“db1.sql”和增量備份文件“binary_log”發(fā)送給節(jié)點(diǎn)2

[root@localhost ~]# yum install mysql-server -y
[root@localhost ~]# scp db1.sql binary_log root@172.18.42.111:/root

（2）節(jié)點(diǎn)2導(dǎo)入“db1.sql"，并查看其數(shù)據(jù)

[root@localhost ~]# mysql < db1.sql 
mysql> select * from db1;
+----+------------+------+
| ID | Name     Age
+----+------------+------+
|  1 | BaiGu jing  100 
|  2 | SunDa shen  200 
|  3 | ZhuBa jie  150 
+----+------------+------+
##數(shù)據(jù)并不是我們修改之后的數(shù)據(jù)

（3）假設(shè)節(jié)點(diǎn)1掛掉了，需要讓節(jié)點(diǎn)2恢復(fù)到節(jié)點(diǎn)1當(dāng)前修改數(shù)據(jù)庫(kù)時(shí)的數(shù)據(jù)，此時(shí)我們只需要節(jié)點(diǎn)2導(dǎo)入“binary_log”增量備份文件即可

[root@localhost ~]# mysql < binary_log 
mysql> select * from db1;    ##導(dǎo)入增量備份之后，節(jié)點(diǎn)2的數(shù)據(jù)和節(jié)點(diǎn)1修改數(shù)據(jù)庫(kù)之后的數(shù)據(jù)一樣
+----+------------+------+
| ID | Name     Age  
+----+------------+------+
|  1 | BaiGu jing  100 
|  2 | SunDa shen  200 
|  3 | ZhuBa jie  150 
|  4 | RuLai     999 
|  5 | YuDi     888 
+----+------------+------+

二：基于lvm2+復(fù)制binlog來實(shí)現(xiàn)備份

準(zhǔn)備環(huán)境：

1、部署節(jié)點(diǎn)1

（1）安裝mysql服務(wù)，滾動(dòng)日志并記錄

[root@localhost ~]# yum install mysql-server -y
[root@localhost ~]# service mysqld start
[root@localhost ~]# mysql -e "show master status" > binary_file   ##對(duì)于如何開啟二進(jìn)制日志小白我就不再說了
[root@localhost ~]# cat binary_file 
File    Position    Binlog_Do_DB    Binlog_Ignore_DB
mysql-bin.000004    208     ##記錄了當(dāng)前使用的二進(jìn)制日志以及事件所在二進(jìn)制日志中的pos
[root@localhost ~]# service mysqld stop  ##記錄完日志之后把mysql服務(wù)關(guān)閉

（2）創(chuàng)建lvm快照，并掛載

[root@localhost ~]# pvcreate /dev/sda5   ##創(chuàng)建物理卷
  Physical volume "/dev/sda5" successfully created
[root@localhost ~]# vgcreate wxpp /dev/sda5   ##創(chuàng)建卷組，名為“wxpp”
  Volume group "wxpp" successfully created
[root@localhost ~]# lvcreate -L +5G -n wxxp1 wxpp  ##創(chuàng)建邏輯卷，大小為5G，名為“wxpp1”
  Logical volume "wxxp1" created.
[root@localhost ~]# mke2fs -t ext4 /dev/wxpp/wxxp1  ##將邏輯卷格式化為“ext4”的文件系統(tǒng)
[root@localhost ~]# mount /dev/wxpp/wxxp1 /data/   ##將邏輯卷掛載至“/data”目錄下
[root@localhost ~]# mkdir /data/mysql   ##創(chuàng)建mysql數(shù)據(jù)庫(kù)存儲(chǔ)數(shù)據(jù)的目錄，注意：此路徑一定要和配置文件里的“datadir”指向的路徑相同
[root@localhost ~]# chown mysql.mysql /data/mysql   ##讓mysql用戶具有存儲(chǔ)數(shù)據(jù)的權(quán)限
[root@localhost ~]# ll /data/
drwxr-xr-x 2 mysql mysql 4096 Jun 12 22:20 /data/mysql
drwx------ 2 root  root  16384 Jun 12 22:18 lost+found
[root@localhost ~]# service mysqld start   ##啟動(dòng)mysql服務(wù)
[root@localhost ~]# ll /data/mysql/   ##生成了大量的日志文件
total 21272
-rw-rw---- 1 mysql mysql 10485760 Jun 12 22:28 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Jun 12 22:28 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Jun 12 22:24 ib_logfile1
drwx------ 2 mysql mysql     4096 Jun 12 22:25 Mydata
drwx------ 2 mysql mysql     4096 Jun 12 22:24 mysql
-rw-rw---- 1 mysql mysql    19758 Jun 12 22:24 mysql-bin.000001
-rw-rw---- 1 mysql mysql   765307 Jun 12 22:24 mysql-bin.000002
-rw-rw---- 1 mysql mysql     1190 Jun 12 22:28 mysql-bin.000003
-rw-rw---- 1 mysql mysql      106 Jun 12 22:28 mysql-bin.000004
-rw-rw---- 1 mysql mysql       76 Jun 12 22:28 mysql-bin.index
drwx------ 2 mysql mysql     4096 Jun 12 22:24 test
mysql> flush tables with read lock;   ##給數(shù)據(jù)施加讀鎖，防止數(shù)據(jù)改變
[root@localhost ~]# lvcreate -L +1G -s -n binary_log /dev/wxpp/wxxp1  ##創(chuàng)建lvm快照，大小為1G，名為“binary_log”
[root@localhost ~]# mount /dev/wxpp/binary_log /mnt/   ##將快照掛載至“/mnt”目錄下
[root@localhost ~]# ll /mnt/mysql/  
total 21272
-rw-rw---- 1 mysql mysql 10485760 Jun 12 22:28 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Jun 12 22:28 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Jun 12 22:24 ib_logfile1
drwx------ 2 mysql mysql     4096 Jun 12 22:25 Mydata
drwx------ 2 mysql mysql     4096 Jun 12 22:24 mysql
-rw-rw---- 1 mysql mysql    19758 Jun 12 22:24 mysql-bin.000001
-rw-rw---- 1 mysql mysql   765307 Jun 12 22:24 mysql-bin.000002
-rw-rw---- 1 mysql mysql     1190 Jun 12 22:28 mysql-bin.000003
-rw-rw---- 1 mysql mysql      106 Jun 12 22:28 mysql-bin.000004
-rw-rw---- 1 mysql mysql       76 Jun 12 22:28 mysql-bin.index
drwx------ 2 mysql mysql     4096 Jun 12 22:24 test
mysql> unlock tables;  ##釋放寫鎖

（3）生成增量備份

mysql> select * from db1;  ##未修改前查看一次數(shù)據(jù)
+----+-------+------+
| ID | Name     Age  
+----+-------+------+
|  1 | MaGe   100 
|  2 | Lweim    200 
|  3 | Wzx    300 
|  4 | wxpp   400 
+----+-------+------+
mysql> delete from db1 where ID=1;
mysql> select * from db1;
+----+-------+------+
| ID | Name  Age  
+----+-------+------+
|  2 | Lweim  200 
|  3 | Wzx   300 
|  4 | wxpp   400 
+----+-------+------+
[root@localhost ~]# mysqlbinlog --start-position=208 /data/mysql/mysql-bin.000004 > backup.sql   ##生成增量備份文件

2、部署節(jié)點(diǎn)2

（1）將節(jié)點(diǎn)1快照下“/mnt/mysql“目錄下的文件以及增量備份文件發(fā)送給節(jié)點(diǎn)2；

[root@localhost ~]# scp -r /mnt/mysql/*  backup.sql root@172.18.42.111:/data/mysql/
[root@localhost ~]# chown -R mysql.mysql /data/mysql/  ##將權(quán)限更改為“mysql”
[root@localhost ~]# ll /data/mysql/
total 21272
-rw-r----- 1 mysql mysql 10485760 May  9 22:33 ibdata1
-rw-r----- 1 mysql mysql  5242880 May  9 22:33 ib_logfile0
-rw-r----- 1 mysql mysql  5242880 May  9 22:33 ib_logfile1
drwx------ 2 mysql mysql     4096 May  9 22:33 Mydata
drwx------ 2 mysql mysql     4096 May  9 22:33 mysql
-rw-r----- 1 mysql mysql    19758 May  9 22:33 mysql-bin.000001
-rw-r----- 1 mysql mysql   765307 May  9 22:33 mysql-bin.000002
-rw-r----- 1 mysql mysql     1190 May  9 22:33 mysql-bin.000003
-rw-r----- 1 mysql mysql      208 May  9 22:33 mysql-bin.000004
-rw-r----- 1 mysql mysql       76 May  9 22:33 mysql-bin.index
drwx------ 2 mysql mysql     4096 May  9 22:33 test

（2）啟動(dòng)mysql服務(wù)，并查看其數(shù)據(jù)庫(kù)

[root@localhost ~]# service mysqld start
mysql> select * from db1;    ##并不是修改之后的數(shù)據(jù)
+----+-------+------+
| ID | Name   Age  
+----+-------+------+
|  1 | MaGe   100 
|  2 | Lweim  200 
|  3 | Wzx   300 
|  4 | wxpp  400 
+----+-------+------+

（3）假設(shè)節(jié)點(diǎn)1因?yàn)槿藶檎`操作導(dǎo)致數(shù)據(jù)庫(kù)崩了，此時(shí)讓節(jié)點(diǎn)二導(dǎo)入增量備份“backup.sql”即可

[root@localhost ~]# mysql < backup.sql 
mysql> select * from db1;
+----+-------+------+
| ID | Name    Age  
+----+-------+------+
|  2 | Lweim  200 
|  3 | Wzx   300 
|  4 | wxpp   400 
+----+-------+------+

（4）移除lvm快照

[root@localhost ~]# umount /mnt
[root@localhost ~]# lvremove /dev/wxpp/binary_log 
 Logical volume "binary_log" successfully removed

  當(dāng)有大量數(shù)據(jù)庫(kù)需要復(fù)制時(shí)，此時(shí)使用快照是最快、最有效的方式，防止對(duì)數(shù)據(jù)庫(kù)施加過長(zhǎng)時(shí)間的讀鎖，以免造成不必要的損失

三、基于xtrabackup命令工具來實(shí)現(xiàn)備份恢復(fù)

準(zhǔn)備環(huán)境

1、部署節(jié)點(diǎn)1

（1）安裝“xtrabackup”工具

[root@node0 ~]# yum install percona-xtrabackup-2.3.2-1.el7.x86_64.rpm -y  ##下載rpm包之后安裝
[root@node0 ~]# rpm -ql percona-xtrabackup    ##查看percona-xtrabackup生成的文件
/usr/bin/innobackupex   ##我們主要使用這個(gè)命令
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-2.3.2
/usr/share/doc/percona-xtrabackup-2.3.2/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz

（2）首先確保存儲(chǔ)引擎為innodb，且“innodb_file_per_table”為ON狀態(tài)

MariaDB [Mydata]> show table status\G;
Name: db1
Engine: InnoDB
MariaDB [Mydata]> show global variables like "%innodb%";
innodb_file_per_table    ON   ##確保每個(gè)表使用單獨(dú)的表空間，如果不指明，則所有數(shù)據(jù)庫(kù)中的所有表將放在同一空間下

（3）實(shí)現(xiàn)整個(gè)數(shù)據(jù)庫(kù)備份

[root@node0 ~]# innobackupex --user=root /backup/  ##對(duì)整個(gè)數(shù)據(jù)庫(kù)做一次備份，放在“/backup”目錄下；注意：如果登錄mysql需要密碼，則需要指明密碼
[root@node0 ~]# ll /backup/2016-04-20_10-34-41/
total 18460
-rw-r----- 1 root root      385 Apr 20 10:34 backup-my.cnf
-rw-r----- 1 root root 18874368 Apr 20 10:34 ibdata1
drwx------ 2 root root       47 Apr 20 10:34 Mydata
drwx------ 2 root root     4096 Apr 20 10:34 mysql
drwx------ 2 root root     4096 Apr 20 10:34 performance_schema
drwx------ 2 root root       19 Apr 20 10:34 test
-rw-r----- 1 root root       21 Apr 20 10:34 xtrabackup_binlog_info
-rw-r----- 1 root root      113 Apr 20 10:34 xtrabackup_checkpoints
-rw-r----- 1 root root      457 Apr 20 10:34 xtrabackup_info
-rw-r----- 1 root root     2560 Apr 20 10:34 xtrabackup_logfile

（4）修改數(shù)據(jù)，生成增量備份

MariaDB [Mydata]> select * from db1;  ##未修改數(shù)據(jù)之前查看一次
+------+
| id   
+------+
|  1 
|  2 
|  3 
+------+
MariaDB [Mydata]> insert into db1 values (100),(200);   ##插入兩個(gè)“ID”
MariaDB [Mydata]> select * from db1;
+------+
| id   
+------+
|  1 
|  2 
|  3 
|  100 
|  200 
+------+
[root@node0 ~]# innobackupex --incremental /backup --incremental-basedir=/backup/2016-04-20_10-34-41/   ##生成增量備份
##--incremental：把增量備份存儲(chǔ)在哪個(gè)路徑下
##--incremental-basedir：基于誰來做增量備份
[root@node0 ~]# ll /backup/
drwx------ 6 root root 4096 Apr 20 10:34 2016-04-20_10-34-41   ##完全備份文件
drwx------ 6 root root 4096 Apr 20 10:41 2016-04-20_10-41-42   ##增量備份文件

（5）對(duì)完全備份數(shù)據(jù)做出整理，并把增量備份導(dǎo)入到完全備份中

[root@node0 ~]# innobackupex --apply-log --redo-only /backup/2016-04-20_10-34-41/   ##對(duì)完全備份做出整理
[root@node0 ~]# innobackupex --apply-log --redo-only /backup/2016-04-20_10-34-41/ --increment-dir=/backup/2016-04-20_10-41-42  ##將增量備份導(dǎo)入到完全備份中

2、部署節(jié)點(diǎn)2

（1）安裝xtrabackup工具

[root@node0 ~]# yum install percona-xtrabackup-2.3.2-1.el7.x86_64.rpm -y

（2）將節(jié)點(diǎn)1上已經(jīng)導(dǎo)入增量備份的完全備份目錄發(fā)送給節(jié)點(diǎn)2，如果節(jié)點(diǎn)1掛了，我們可以基于導(dǎo)入增量備份的完全備份來恢復(fù)

[root@node0 ~]# scp -r /backup/2016-04-20_10-34-41/ root@172.18.42.201:/root
[root@node1 ~]# innobackupex --copy-back 2016-04-20_10-34-41/   ##導(dǎo)出數(shù)據(jù)
[root@node1 ~]# chown -R mysql.mysql /data/   ##更改權(quán)限為mysql
[root@node1 ~]# ll /data/mysql/
total 18448
drwx------ 2 root root        6 Apr 20 11:02 2016-04-20_10-34-41
-rw-r----- 1 root root 18874368 Apr 20 11:02 ibdata1
drwx------ 2 root root       47 Apr 20 11:02 Mydata
drwx------ 2 root root     4096 Apr 20 11:02 mysql
drwx------ 2 root root     4096 Apr 20 11:02 performance_schema
drwx------ 2 root root       19 Apr 20 11:02 test
-rw-r----- 1 root root       23 Apr 20 11:02 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root      457 Apr 20 11:02 xtrabackup_info
[root@node1 ~]# systemctl start mariadb.service
MariaDB [Mydata]> select * from db1;
+------+
| id   
+------+
|  1 
|  2 
|  3 
|  100 
|  200 
+------+

問題小結(jié)：

1、當(dāng)使用mysqldump+復(fù)制binlog方法時(shí)，最好不要將數(shù)據(jù)文件和二進(jìn)制文件放在同一磁盤上，且需要對(duì)二進(jìn)制文件作出備份

2、使用lvm2+復(fù)制binlog方法時(shí)，在創(chuàng)建快照的同時(shí)，最好對(duì)數(shù)據(jù)庫(kù)施加讀鎖，以免數(shù)據(jù)發(fā)生改變，同時(shí)要確保文件的屬主、屬組為mysql

3、在使用xtrabackup方法時(shí)，整個(gè)過程中mysql存儲(chǔ)數(shù)據(jù)的目錄必須要和配置文件里面的“datadir”“相同，同時(shí)要確保文件的屬主、屬組為mysql