多種方法備份Mysql資料庫

標籤：mysql   備份與恢複   xtrabackup   

 前言：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  /backupcp -a /var/lib/mysql/*  /backup

類比遺失資料，刪除mysql資料

rm -rf /var/lib/mysql/*

重啟資料庫，探索資料丟失（這裡是yum安裝能直接重啟資料庫，如果是編譯安裝還需要初始化）

service  mysqld restartmysql> select * from test.student;ERROR 1146 (42S02): Table ‘test.student‘ doesn‘t existmysql>

把備份的資料還原到資料目錄

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-binservice  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.sqlmysql> 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 = 4088COMMIT/*!*/;# at 896#161102 18:36:25 server id 1  end_log_pos 977 Querythread_id=41exec_time=0error_code=0SET 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 /exbackupinnobackupex --user=root /exbackup/innobackupex --apply-log /exbackup/2016-11-02_20-14-08/

3，查看備份檔案

[[email protected] 3306]# ll /exbackup/2016-11-02_20-14-08/總用量 30760drwx------. 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_logfile1drwx------. 2 root root     4096 11月  2 20:14 mysqldrwx------. 2 root root     4096 11月  2 20:14 oldboydrwx------. 2 root root     4096 11月  2 20:14 performance_schemadrwx------. 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 restartmysql> 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，查看增量備份

[[email protected] exbackup]# ll /exbackup/總用量 8drwx------. 7 root root 4096 11月  2 20:49 2016-11-02_20-14-08drwx------. 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 restartmysql> 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  /exbackupinnobackupex   --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/

  

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

本文出自 “資料庫學習曆程” 部落格，請務必保留此出處http://yuemu.blog.51cto.com/10688235/1868733

多種方法備份Mysql資料庫