Mysql備份的三種方法

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

Mysql備份時需要備份什么？

  1、需要用到的數據

  2、二進制日志、InnoDB事務日志

  3、存儲代碼；例如：存儲過程、存儲函數、觸發器、時間調度器

  4、服務器的配置文件

Mysql備份時需要注意的要點：

  1、能容忍最多丟失多少數據

  2、恢復數據需要在多長時間內完成

  3、需要恢復那些數據

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

Mysql備份時需要考慮的因素

  1、持鎖需要多長時間

  2、備份的過程時長

  3、在備份時，是否會增加系統業務的負載

  4、恢復過程的時長

Mysql備份的類型；

  以數據集角度來劃分：

    完全備份：備份整個數據集

    部分備份：只備份數據子集

  以時間軸角度來劃分：

    完全備份：備份整個數據集

    增量備份：備份最近一次完全備份或增量備份之后變化的數據

    差異備份：備份最近一次完全備份之后變化的數據

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

  根據服務器業務系統能否在線來劃分：

    熱備份：讀寫操作均可執行

    溫備份：讀操作可執行，但寫操作不可執行

    冷備份：讀寫操作均不可執行

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

  物理備份：直接復制數據文件進行備份

  邏輯備份：從數據庫中導出數據從而進行備份；邏輯備份與存儲引擎無關，如果備份的是InnoDB存儲引擎，用在MyISAM存儲引擎上也是可以的

Mysql備份的方式：

  1、mysqldump+復制binlog來實現：使用mysqldump來實現數據的完全備份，復制binlog中指定時間范圍內的event來實現增量備份

  2、lvm2+復制binlog：利用lvm2快照以及使用cp等命令工具來實現物理備份、完全備份，復制binlog中指點事件范圍內的event來實現增量備份

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

一、基于mysqldump+復制binlog來實現備份

準備環境：

1、部署節點1

（1）安裝mysql服務，編輯其二進制文件并進行完全備份

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

（2）修改數據庫中的數據，并使用“mysqlbinlog”生成增量備份

mysql> select * from db1;   ##未修改數據之前查看一次數據
+----+------------+------+
| 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;   ##修改數據之后查看一次
+----+------------+------+
| 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)   ##記錄了修改表的相關操作
/*!*/;
# 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、部署節點2

（1）提供mysql服務，并將節點1的完全備份文件“db1.sql”和增量備份文件“binary_log”發送給節點2

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

（2）節點2導入“db1.sql"，并查看其數據

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

（3）假設節點1掛掉了，需要讓節點2恢復到節點1當前修改數據庫時的數據，此時我們只需要節點2導入“binary_log”增量備份文件即可

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

二：基于lvm2+復制binlog來實現備份

準備環境：

1、部署節點1

（1）安裝mysql服務，滾動日志并記錄

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

（2）創建lvm快照，并掛載

[root@localhost ~]# pvcreate /dev/sda5   ##創建物理卷
  Physical volume "/dev/sda5" successfully created
[root@localhost ~]# vgcreate wxpp /dev/sda5   ##創建卷組，名為“wxpp”
  Volume group "wxpp" successfully created
[root@localhost ~]# lvcreate -L +5G -n wxxp1 wxpp  ##創建邏輯卷，大小為5G，名為“wxpp1”
  Logical volume "wxxp1" created.
[root@localhost ~]# mke2fs -t ext4 /dev/wxpp/wxxp1  ##將邏輯卷格式化為“ext4”的文件系統
[root@localhost ~]# mount /dev/wxpp/wxxp1 /data/   ##將邏輯卷掛載至“/data”目錄下
[root@localhost ~]# mkdir /data/mysql   ##創建mysql數據庫存儲數據的目錄，注意：此路徑一定要和配置文件里的“datadir”指向的路徑相同
[root@localhost ~]# chown mysql.mysql /data/mysql   ##讓mysql用戶具有存儲數據的權限
[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   ##啟動mysql服務
[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;   ##給數據施加讀鎖，防止數據改變
[root@localhost ~]# lvcreate -L +1G -s -n binary_log /dev/wxpp/wxxp1  ##創建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;  ##未修改前查看一次數據
+----+-------+------+
| 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、部署節點2

（1）將節點1快照下“/mnt/mysql“目錄下的文件以及增量備份文件發送給節點2；

[root@localhost ~]# scp -r /mnt/mysql/*  backup.sql root@172.18.42.111:/data/mysql/
[root@localhost ~]# chown -R mysql.mysql /data/mysql/  ##將權限更改為“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）啟動mysql服務，并查看其數據庫

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

（3）假設節點1因為人為誤操作導致數據庫崩了，此時讓節點二導入增量備份“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

  當有大量數據庫需要復制時，此時使用快照是最快、最有效的方式，防止對數據庫施加過長時間的讀鎖，以免造成不必要的損失

三、基于xtrabackup命令工具來實現備份恢復

準備環境

1、部署節點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   ##我們主要使用這個命令
/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）首先確保存儲引擎為innodb，且“innodb_file_per_table”為ON狀態

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

（3）實現整個數據庫備份

[root@node0 ~]# innobackupex --user=root /backup/  ##對整個數據庫做一次備份，放在“/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）修改數據，生成增量備份

MariaDB [Mydata]> select * from db1;  ##未修改數據之前查看一次
+------+
| id   
+------+
|  1 
|  2 
|  3 
+------+
MariaDB [Mydata]> insert into db1 values (100),(200);   ##插入兩個“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：把增量備份存儲在哪個路徑下
##--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）對完全備份數據做出整理，并把增量備份導入到完全備份中

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

2、部署節點2

（1）安裝xtrabackup工具

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

（2）將節點1上已經導入增量備份的完全備份目錄發送給節點2，如果節點1掛了，我們可以基于導入增量備份的完全備份來恢復

[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/   ##導出數據
[root@node1 ~]# chown -R mysql.mysql /data/   ##更改權限為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 
+------+

問題小結：

1、當使用mysqldump+復制binlog方法時，最好不要將數據文件和二進制文件放在同一磁盤上，且需要對二進制文件作出備份

2、使用lvm2+復制binlog方法時，在創建快照的同時，最好對數據庫施加讀鎖，以免數據發生改變，同時要確保文件的屬主、屬組為mysql

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