多種方法備份Mysql數(shù)據(jù)庫

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

一，備份工具

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

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

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

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

二，cp備份與恢復(fù)

先查看數(shù)據(jù)，我們用test庫里的student表實(shí)驗(yàn)

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

拷貝mysql數(shù)據(jù)到指定目錄

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

模擬丟失數(shù)據(jù)，刪除mysql數(shù)據(jù)

rm -rf /var/lib/mysql/*

重啟數(shù)據(jù)庫，發(fā)現(xiàn)數(shù)據(jù)丟失（這里是yum安裝能直接重啟數(shù)據(jù)庫，如果是編譯安裝還需要初始化）

service  mysqld restart

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

把備份的數(shù)據(jù)還原到數(shù)據(jù)目錄

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

登錄數(shù)據(jù)庫，發(fā)現(xiàn)數(shù)據(jù)已經(jīng)還原回來了

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

mysql>

三，mysqldump備份與恢復(fù)

先查看數(shù)據(jù)，我們用test庫里的student表實(shí)驗(yàn)

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

修改my.cnf，添加log_bin,重啟數(shù)據(jù)庫

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

service  mysqld  restart

利用Mysqldump備份數(shù)據(jù)，及參數(shù)說明

mysqldump  -uroot   -A  --events   --master-data=2  --single-transaction   >/opt/all.sql
-A  全備，恢復(fù)時不需要創(chuàng)建庫
--events  備份時間調(diào)度器
--master-data  記錄時刻點(diǎn)
--single-transaction 鎖表備份

插入新的數(shù)據(jù)

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)

模擬數(shù)據(jù)丟失，誤操作

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)

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

1，關(guān)閉日志記錄

set  global  sql_log_bin=0;

2，恢復(fù)全備,檢查數(shù)據(jù)情況

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

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

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

在看誤操作的時刻點(diǎn)

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

根據(jù)開始時刻點(diǎn)和結(jié)束時刻點(diǎn)恢復(fù)

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

4,恢復(fù)完成，查看數(shù)據(jù)

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

四，xtrabackup備份與恢復(fù)之全量

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，創(chuàng)建備份目錄，幷備份

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,模擬數(shù)據(jù)丟失，進(jìn)行恢復(fù)

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

5,利用備份文件，恢復(fù)數(shù)據(jù)，重啟數(shù)據(jù)庫，查看數(shù)據(jù)

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備份與恢復(fù)之增量

1，上面我們已經(jīng)做了全備，我們新添數(shù)據(jù)做增量備份實(shí)驗(yàn)

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

2，進(jìn)行增量備份，注意：下一次增量 --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,模擬數(shù)據(jù)丟失，進(jìn)行恢復(fù)

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

5,利用備份文件，恢復(fù)數(shù)據(jù)，重啟數(shù)據(jù)庫，查看數(shù)據(jù)

innobackupex --copy-back /exbackup/2016-11-02_20-14-08/  #直接通過全量進(jìn)行增量的恢復(fù)
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備份與恢復(fù) 多實(shí)例

1，步驟基本一樣，只是多加一些參數(shù)

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/

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