多種方法備份Mysql數據庫

 前言：Mysql數據庫的備份是重中之重，在生產過程中，數據庫會因硬件故障，軟件故障，******，誤操作等造成數據丟失，但經過精密的備份，完全能把數據恢復過來.

一，備份工具

這里介紹幾種常用的備份方式

cp:物理備份工具, 適用于所有的存儲引擎, 冷備、完全備份、部分備份 

mysqldump:邏輯備份工具, 適用于所有的存儲引擎, 支持溫備、完全備份、部分備份、對于InnoDB存儲引擎支持熱備

xtrabackup:一款非常強大的InnoDB/XtraDB熱備工具, 支持完全備份、增量備份, 

二，cp備份與恢復

先查看數據，我們用test庫里的student表實驗

mysql> select * from test.student;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

拷貝mysql數據到指定目錄

mkdir  /backup
cp -a /var/lib/mysql/*  /backup

模擬丟失數據，刪除mysql數據

rm -rf /var/lib/mysql/*

重啟數據庫，發現數據丟失（這里是yum安裝能直接重啟數據庫，如果是編譯安裝還需要初始化）

service  mysqld restart

mysql> select * from test.student;
ERROR 1146 (42S02): Table 'test.student' doesn't exist
mysql>

把備份的數據還原到數據目錄

cp -a /backup/*  /var/lib/mysql/

登錄數據庫，發現數據已經還原回來了

mysql> select * from test.student;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

mysql>

三，mysqldump備份與恢復

先查看數據，我們用test庫里的student表實驗

mysql> select * from test.student;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

修改my.cnf，添加log_bin,重啟數據庫

vim /etc/my.cnf
[mysqld]
log_bin=mysql-bin

service  mysqld  restart

利用Mysqldump備份數據，及參數說明

mysqldump  -uroot   -A  --events   --master-data=2  --single-transaction   >/opt/all.sql
-A  全備，恢復時不需要創建庫
--events  備份時間調度器
--master-data  記錄時刻點
--single-transaction 鎖表備份

插入新的數據

mysql> use  test;
mysql> insert into student values(3,'c');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

模擬數據丟失，誤操作

mysql> drop database test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

開始恢復，利用mysqldump的全備加上增量備份

1，關閉日志記錄

set  global  sql_log_bin=0;

2，恢復全備,檢查數據情況

mysql -uroot < /opt/all.sql
mysql> select * from test.student;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

3，利用時刻點恢復增量恢復，查看/opt/all.sql的開始時刻點，在文件里有類似如下字符串

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000018', MASTER_LOG_POS=705

在看誤操作的時刻點

mysqlbinlog  /var/lib/mysql/mysql-bin.000018 
# at 869
#161102 18:36:09 server id 1  end_log_pos 896 	Xid = 4088
COMMIT/*!*/;
# at 896
#161102 18:36:25 server id 1  end_log_pos 977 	Query	thread_id=41	exec_time=0	error_code=0
SET TIMESTAMP=1478082985/*!*/;
drop database test

根據開始時刻點和結束時刻點恢復

mysqlbinlog  /var/lib/mysql/mysql-bin.000018 --start-position=705 --stop-position=896 | mysql -uroot

4,恢復完成，查看數據

mysql> select * from test.student;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

四，xtrabackup備份與恢復之全量

1，下載工具，幷安裝

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.4/binary/redhat/6/x86_64/percona-xtrabackup-2.3.4-1.el6.x86_64.rpm

yum install percona-xtrabackup-2.3.4-1.el6.x86_64.rpm

2，創建備份目錄，幷備份

mkdir /exbackup
innobackupex --user=root /exbackup/
innobackupex --apply-log /exbackup/2016-11-02_20-14-08/

3，查看備份文件

[root@data-1-1 3306]# ll /exbackup/2016-11-02_20-14-08/
總用量 30760
drwx------. 2 root root     4096 11月  2 20:49 2016-11-02_20-49-56
-rw-r-----. 1 root root      386 11月  2 20:14 backup-my.cnf
-rw-r-----. 1 root root 18874368 11月  2 20:21 ibdata1
-rw-r--r--. 1 root root  5242880 11月  2 20:21 ib_logfile0
-rw-r--r--. 1 root root  5242880 11月  2 20:21 ib_logfile1
drwx------. 2 root root     4096 11月  2 20:14 mysql
drwx------. 2 root root     4096 11月  2 20:14 oldboy
drwx------. 2 root root     4096 11月  2 20:14 performance_schema
drwx------. 2 root root     4096 11月  2 20:14 test
-rw-r-----. 1 root root       21 11月  2 20:14 xtrabackup_binlog_info
-rw-r--r--. 1 root root       23 11月  2 20:21 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 root root      113 11月  2 20:21 xtrabackup_checkpoints
-rw-r-----. 1 root root      511 11月  2 20:14 xtrabackup_info
-rw-r-----. 1 root root  2097152 11月  2 20:21 xtrabackup_logfile

4,模擬數據丟失，進行恢復

rm -rf /data/*
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

5,利用備份文件，恢復數據，重啟數據庫，查看數據

innobackupex --copy-back /exbackup/2016-11-02_20-14-08/ 
chown -R  mysql:mysql /data/*
service mysqld restart

mysql> show databases;
+------------------------------+
| Database                     |
+------------------------------+
| information_schema           |
| #mysql50#2016-11-02_20-49-56 |
| mysql                        |
| oldboy                       |
| performance_schema           |
| test                         |
+------------------------------+
6 rows in set (0.00 sec)

五，xtrabackup備份與恢復之增量

1，上面我們已經做了全備，我們新添數據做增量備份實驗

mysql> show databases;
+------------------------------+
| Database                     |
+------------------------------+
| information_schema           |
| #mysql50#2016-11-02_20-49-56 |
| mysql                        |
| oldboy                       |
| performance_schema           |
| t1                           |
| t2                           |
| test                         |
+------------------------------+

2，進行增量備份，注意：下一次增量 --incremental-basedir=最近的增量備份路徑

 innobackupex --incremental /exbackup/ --incremental-basedir=/exbackup/2016-11-02_20-14-08/
 innobackupex --apply-log --redo-only /exbackup/2016-11-02_20-14-08/ 
 innobackupex --apply-log --redo-only /exbackup/2016-11-02_20-14-08/ --incremental-basedir=/exbackup/2016-11-02_21-31-50/

3，查看增量備份

[root@data-1-1 exbackup]# ll /exbackup/
總用量 8
drwx------. 7 root root 4096 11月  2 20:49 2016-11-02_20-14-08
drwx------. 9 root root 4096 11月  2 21:32 2016-11-02_21-31-50

4,模擬數據丟失，進行恢復

rm -rf /data/*
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

5,利用備份文件，恢復數據，重啟數據庫，查看數據

innobackupex --copy-back /exbackup/2016-11-02_20-14-08/  #直接通過全量進行增量的恢復
chown -R  mysql:mysql /data/*
service mysqld restart

mysql> show databases;
+------------------------------+
| Database                     |
+------------------------------+
| information_schema           |
| #mysql50#2016-11-02_20-49-56 |
| mysql                        |
| oldboy                       |
| performance_schema           |
| t1                           |
| t2                           |
| test                         |
+------------------------------+
8 rows in set (0.01 sec)

六，xtrabackup備份與恢復 多實例

1，步驟基本一樣，只是多加一些參數

innobackupex   --defaults-file=/data/3306/my.cnf --user=root  /exbackup
innobackupex   --apply-log /exbackup/2016-11-02_20-14-08/ 
innobackupex   --defaults-file=/data/3306/my.cnf --copy-back /exbackup/2016-11-02_20-14-08/

2，增量

innobackupex   --defaults-file=/data/3306/my.cnf --user=root  --incremental /exbackup/ --incremental-basedir=/exbackup/2016-11-02_20-14-08/
innobackupex   --apply-log --redo-only /exbackup/2016-11-02_20-14-08/  
innobackupex   --apply-log --redo-only /exbackup/2016-11-02_20-14-08/ --incremental-dir=/exbackup/2016-11-02_21-31-50/
innobackupex   --defaults-file=/data/3306/my.cnf --copy-back /exbackup/2016-11-02_20-14-08/

  總結：只是多了配置文件的參數，其他步驟完全一致，這里就不花篇幅進行這些操作了，大家可以進行測試，本文沒有過多的原理解釋，全是干貨操作，想了解這些備份方法的原理，參數的網友就見諒了