mysql備份還原

一、前言

數據對于一個公司來說有多重要這點不說大家也知道，數據還在，服務崩潰還可重建。為什么要備份？很簡單，天災人禍。比如硬件故障，軟件故障，自然災害，黑客攻擊或自身誤操作等因素。這時候備份，恢復數據就顯得至關重要了。

二、基礎概念

由于備份與恢復需要用到一些必要的知識，故在此先講解下

2.1二進制日志

myql日志有多種，其中二進制日志文件對數據恢復有很大作用。那么什么是二進制日志？二進制日志記錄了所有對數據庫執行的更改操作，只有對數據能產生更改的操作才會記錄下來。

日志格式：

2.2備份類型

備份按照不同的劃分方式可以劃分為多種類型

如 ：完全備份、增量備份、差異備份

完全備份：一個或多個數據庫實例完全備份下來

增量備份：備份從完全備份或上次增量備份到現在改變了的數據部分

差異備份：備份從完全備份到現在改變了的數據

熱備、溫備、冷備

熱備：在線備份，不需關閉mysql服務，業務不受影響

溫備：在線備份，只支持讀操作，

冷備：離線備份，需要關閉mysql服務

物理備份、邏輯備份

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

邏輯備份：從數據庫中導出數據另存進行備份

2.3備份對象

數據、配置文件、二進制日志、存儲過程、觸發器

2.4備份方案

完全備份+增量備份+二進制日志

2.5數據恢復

有大概如下步驟

1、停止MySQL服務器；

2、記錄服務器配置和文件權限；

3、將備份恢復到MySQL數據目錄；此步驟依賴具體的備份工具；

4、改變配置和文件權限；

5、以限制方式啟動MySQL服務器：比如通過網絡訪問；

[mysqld]

skip-networking 

socket=/tmp/mysql-recovery.sock

6、載入額外的邏輯備份；而檢查和重放二進制日志；

7、檢查已經還原的數據；

8、以完全訪問模式重啟服務器；

三、備份與恢復

3.1 mysqldump+二進制日志

備份

mysql> insert into classes(Class,NumOfStu) values('yue jiao','4');    #插入數據
Query OK, 1 row affected (0.03 sec)

mysql> show master logs;                        #查看二進制文件
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |     16274 |
| master-bin.000002 |       120 |
+-------------------+-----------+
2 rows in set (0.00 sec)

[root@vm ~]# mysqldump --databases test -E -R --triggers --master-data=2 > /tmp/test_backup.mysql    #-E表示事件，-R表示存儲過程，--triggers表示觸發器
[root@vm ~]# ls /tmp/test_backup.mysql -l
-rw-r--r--. 1 root root 8057 Nov 19 19:36 /tmp/test_backup.mysql
[root@vm ~]# cp /etc/my.cnf /tmp/my.cnf.backup                            #備份配置文件
 [root@vm ~]# ls /tmp/my.cnf.backup -l
-rw-r--r--. 1 root root 1171 Nov 19 19:37 /tmp/my.cnf.backup
[root@vm ~]# ls -l /data/mysql/binlogs/ 
total 24
-rw-rw----. 1 mysql mysql 16274 Nov 19 17:51 master-bin.000001
-rw-rw----. 1 mysql mysql   667 Nov 19 19:41 master-bin.000002
-rw-rw----. 1 mysql mysql    76 Nov 19 17:51 master-bin.index
[root@vm ~]# cp /data/mysql/binlogs/master-bin.* /tmp/                #備份二進制日志文件
[root@vm ~]# ls /tmp/master-bin.*
/tmp/master-bin.000001  /tmp/master-bin.000002  /tmp/master-bin.index

還原

[root@vm ~]# cp /tmp/my.cnf.backup /etc/my.cnf        #配置文件還原
skip-networking
[root@vm ~]# service mysqld start
Starting MySQL...... SUCCESS!


[root@vm ~]# mysqlbinlog --start-position=120 --database=test /tmp/master-bin.000002 >/tmp/new_log.sql    #指定從什么position開始查找

mysql> set session sql_log_bin=0;                #臨時關閉二進制日志功能，反正把還原過程也寫進日志文件
Query OK, 0 rows affected (0.04 sec)
mysql> source /tmp/test_backup.mysql            #執行完全備份的數據文件

mysql> source /tmp/new_log.sql                  #執行增量還原

mysql> select * from classes where Class='mo jiao';
+---------+---------+----------+
| ClassID | Class   | NumOfStu |
+---------+---------+----------+
|      10 | mo jiao |        3 |
+---------+---------+----------+
1 row in set (0.02 sec)

mysql> set sql_log_bin=1;                    #打開二進制文件功能
Query OK, 0 rows affected (0.00 sec)

[root@vm ~]# sed -in /skip-networking/d /etc/my.cnf    #此操作使用戶能遠程訪問數據庫

[root@vm ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL...... SUCCESS!

3.2 lvm+二進制日志

這種備份要求mysql的data目錄放在邏輯卷組中。

本實驗中mysql的data目錄放在邏輯卷組lv1中

mysql> show master logs;                #查看二進制文件及position
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |     16274 |
| master-bin.000002 |       690 |
| master-bin.000003 |       143 |
| master-bin.000004 |       143 |
| master-bin.000005 |       143 |
| master-bin.000006 |       143 |
| master-bin.000007 |       417 |
| master-bin.000008 |       143 |
| master-bin.000009 |       143 |
| master-bin.000010 |      2857 |
| master-bin.000011 |     10293 |
+-------------------+-----------+
11 rows in set (0.03 sec)

mysql> flush tables with read lock;        #全庫鎖表
Query OK, 0 rows affected (0.06 sec)

mysql> set sql_log_bin=0;            #關閉二進制日志功能
Query OK, 0 rows affected (0.01 sec)



[root@vm ~]# rm -rf /mydata/test/    #模擬破壞數據庫操作

[root@vm ~]# lvcreate -s  -n backup -L 3G /dev/mapper/vg-lv1     #建立快照
  Logical volume "backup" created


[root@vm ~]# cp -r /data/mysql/binlogs /tmp/    #備份二進制日志文件


mysql> set sql_log_bin=1;                    #打開二進制日志功能
Query OK, 0 rows affected (0.18 sec)

mysql> unlock tables;                        #解鎖
Query OK, 0 rows affected (0.00 sec)

mysql> insert into classes(Class,NumOfStu) values('yue jiao','4');    #插入數據
Query OK, 1 row affected (0.09 sec)

還原

[root@vm ~]# service mysqld stop        #停止數據庫服務
Shutting down MySQL.. SUCCESS!

 [root@vm ~]# mount /dev/mapper/vg-backup /mnt/backup/        #掛載快照

[root@vm ~]# cp -r /mnt/backup/test /mydata/                #還原數據文件
[root@vm ~]# chown mysql.mysql /mydata/test –R            #更改文件屬性
[root@vm ~]# mysqlbinlog --start-position=10293--database=test /tmp/master-bin.000011 >/tmp/new_log.sql     #提取從數據庫備份那時的位置到最后的位置的二進制日志信息

[root@vm ~]# service mysqld start
Starting MySQL..... SUCCESS!

mysql> set sql_log_bin=0;            #關閉二進制日志功能
Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/new_log.sql        #增量還原

3.3 Xtrabackup 備份

使用innobakupex備份時，其會調用xtrabackup備份所有的InnoDB表，復制所有關于表結構定義的相關文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相關文件，同時還會備份觸發器和數據庫配置信息相關的文件。這些文件會被保存至一個以時間命名的目錄中。

在備份的同時，innobackupex還會在備份目錄中創建如下文件：

(1)xtrabackup_checkpoints —— 備份類型（如完全或增量）、備份狀態（如是否已經為prepared狀態）和LSN(日志序列號)范圍信息；

每個InnoDB頁(通常為16k大小)都會包含一個日志序列號，即LSN。LSN是整個數據庫系統的系統版本號，每個頁面相關的LSN能夠表明此頁面最近是如何發生改變的。

(2)xtrabackup_binlog_info —— mysql服務器當前正在使用的二進制日志文件及至備份這一刻為止二進制日志事件的位置。

(3)xtrabackup_binlog_pos_innodb —— 二進制日志文件及用于InnoDB或XtraDB表的二進制日志文件的當前position。

(4)xtrabackup_binary —— 備份中用到的xtrabackup的可執行文件；

(5)backup-my.cnf —— 備份命令用到的配置選項信息；

在使用innobackupex進行備份時，還可以使用–no-timestamp選項來阻止命令自動創建一個以時間命名的目錄；如此一來，innobackupex命令將會創建一個BACKUP-DIR目錄來存儲備份數據。

從http://www.percona.com/software/percona-xtrabackup/ 下載

語法

innobackupex [options]...

創建最小權限用戶用于備份

mysql> grant reload,lock tables,replication client on *.* to 'xtrabackup'@'localhost' identified by 'xtrabackup';
Query OK, 0 rows affected (0.23 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)

安裝xtrabackup

[root@vm ~]# yum install -y percona-xtrabackup-2.2.3-4982.el6.x86_64.rpm

完全備份一個數據庫

[root@vm ~]# innobackupex --user=xtrabackup --password=xtrabackup /tmp/backup/

MariaDB [test]>  insert into classes (Class,NumOfstu) values ('mou pai','50');        #插入數據
Query OK, 1 row affected (0.00 sec)
[root@vm backup]# innobackupex --incremental /tmp/backup/ --incremental-basedir=/tmp/backup/2015-11-27_06-11-01/    #增量備份一個數據庫
MariaDB [test]>  insert into classes (Class,NumOfstu) values ('huo pai','51');        #繼續插入數據
Query OK, 1 row affected (0.00 sec)
[root@vm backup]# innobackupex --incremental /tmp/backup/ --incremental-basedir=/tmp/backup/2015-11-27_06-14-53/    #第二次增量備份一個數據庫

備份還原

[root@vm backup]# innobackupex --apply-log --redo-only /tmp/backup/2015-11-27_06-11-01/    #整理備份文件
[root@vm backup]# innobackupex --apply-log --redo-only /tmp/backup/2015-11-27_06-11-01/ --incremental-dir=/tmp/backup/2015-11-27_06-14-53/    #整理第一個備份文件到完全備份中
[root@vm backup]# innobackupex --apply-log --redo-only /tmp/backup/2015-11-27_06-11-01/ --incremental-dir=/tmp/backup/2015-11-27_06-16-19/    #整理第二個備份文件到完全備份中
[root@vm backup]# rm -rf /mydata/*
[root@vm backup]# innobackupex --copy-back --user=xtrabackup --password=xtrabackup /tmp/backup/2015-11-27_06-11-01/    #還原數據庫
[root@vm backup]# chown mysql.mysl -R /mydata
[root@vm backup]# service mysqld start

四、總結

備份和還原十分重要，這點就不啰嗦了，備份的時候注意不要讓用戶對數據庫進行寫操作，還原的時候需要注意關閉日志文件功能，反正還原的時候還原日志也寫進二進制日志中。還需禁止數據庫遠程登錄而進行讀寫操作。可以通過防火墻或者配置文件來實現。