資料意味着企業的命脈，如果，資料丢失，就表示企業就要面臨巨大的經濟損失，甚至是更加嚴重的後果，是以，作為運維人員，對于資料庫的備份與恢複是一種非常重要的任務，下面，我将介紹三種備份方式，分别為：mysqldump備份（mysql自帶的），邏輯卷備份，xtrabackup備份（專用于InnoDB存儲引擎）。
首先，先介紹mysqldump這個備份工具，實作的主要是實體備份，之後，還需要根據二進制日志的事件進行增量備份，這主要是針對InnoDB存儲引擎的，MyISAM存儲引擎，隻需要将資料庫目錄下的資料拷貝到另一個主機即可；這種備份方式實作的是：完全備份+增量備份。
備份的政策：最好一周進行一次完全備份，每天進行一次增量備份
mysqldump的備份及資料恢複實驗：
我們先單獨建立一個目錄，作為存放二進制日志檔案的位置，最後是另一個磁盤上
進行備份：此時，MySQL應該為啟動狀态
[root@localhost ~]# mysqldump -B hellodb -lock-all-tables -master-data=2 -flush-logs -u root -h localhost -p >
/myback/hellodb-`date +%F-%T`.sql
#-h，這裡指localhost，但是，實際應該是MySQL伺服器的位址，用戶端為另一個位址
Enter password: 上面的認證資訊可以寫到目前使用者的家目錄下
備份的資料庫檔案中，有如下語句
– CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=245; #增量備份從這裡開始
登入到資料庫上，在hellodb的資料庫中建立一個表
MariaDB [hellodb]> create table test(id int); 插入資料
MariaDB [hellodb]> insert into test values (1),(11); 在增量備份前，滾動一下日志檔案
MariaDB [(none)]> flush logs; 生成環境中，可能會自動滾動多個檔案，備份時，要備份多個檔案
進行增量備份：
根據時間進行增量備份，起始時間位增量備份的時間
[root@localhost ~]# mysqlbinlog –start-datetime '2014-09-22 18:20:43'
–stop-datetime '2014-09-22 18:23:23' /MySQL/binlogs/mysql-bin.* >
/myback/icrm-`date +%F-%T`.sql 此時，再次登入資料庫，進行一些操作
MariaDB [(none)]> USE hellodb
MariaDB [hellodb]> insert into test values (100),(200); 由于可能的誤操作，hellodb資料庫被删除了
MariaDB [hellodb]> DROP DATABASE hellodb; 此時，我們就要保護好我們的二進制日志檔案了，我們将數字最大的二進制日志導出到臨時檔案，查找到DROP DATABASE這一欄目，記下其執行的編号
[root@localhost ~]# mysqlbinlog /MySQL/binlogs/mysql-bin.000006 > /myback/bny.sql
[root@localhost ~]# vim /myback/bny.sql
# at 444 #事件的序号為444
#140922 18:26:48 server id 1 end_log_pos 531 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1411381608/*!*/;
drop database hellodb
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 重新生成二進制日志的檔案
[root@localhost ~]# mysqlbinlog –stop-position 444 /MySQL/binlogs/mysql-bin.000006 > /myback/bny.sql 可将原來生成的進行覆寫
連接配接到MariaDB上，将二進制日志記錄的功能暫時關閉
MariaDB [(none)]> set session sql_log_bin=0; 還原資料庫，依次導入，這裡可以在另一個終端上導入
導入第一次完全備份的資料
[root@localhost ~]# mysql < /myback/hellodb-2014-09-18-00\:15\:22.sql 
此時，後面的資料都沒有，test表都不存在
導入第一次增量備份
[root@localhost myback]# mysql < icrm-2014-09-22-18\:24\:39.sql 
導入二進制日志的操作部分
[root@localhost myback]# mysql < bny.sql 邏輯備份對于資料集非常大的時候，效率非常的差，可以執行實體備份，直接拷貝資料
使用rsync，鎖定mysql，将資料拉到遠端的用戶端主機上
下面要介紹的是基于LVM邏輯卷的一種實體備份，前提是，我們的MariaDB服務的資料庫目錄是基于LVM的邏輯卷挂載的目錄，我們在做邏輯卷的時候，要預留一部分空間，要進行實體備份的時候，将所有的表施加讀鎖，不讓使用者進行寫操作，同時，馬上建立快照，進行挂載，及時的釋放讀鎖，這樣，實體備份就完成了，如果，資料還在不斷的寫入，其餘的要借助于我們的二進制日志進行恢複資料了。
我們先在mysql中導入hellodb這個資料庫
[root@localhost ~]# mysql < hellodb.sql 再使用mysqldump工具進行完全備份
[root@localhost ~]# mysqldump -A –lock-all-tables > backup.sql 停止我們的mysql服務
[root@localhost ~]# service mysqld stop 首先，我們建立一個分區，并将其作為邏輯卷
[root@localhost ~]# fdisk /dev/sda #建立10G分區
[root@localhost ~]# kpartx -af /dev/sda
[root@localhost ~]# partx -a /dev/sda
[root@localhost ~]# pvcreate /dev/sda3
[root@localhost ~]# vgcreate myvg /dev/sda3
[root@localhost ~]# lvcreate -L 6G -n mydata myvg
[root@localhost ~]# mke2fs -t ext4 /dev/myvg/mydata
[root@localhost ~]# mount /dev/myvg/mydata /MySQL/data/
[root@localhost ~]# chown -R mysql:mysql /MySQL/data/ 重新初始化
[root@localhost mysql]# cd /usr/local/mysql/
[root@localhost mysql]# ./scripts/mysql_install_db –user=mysql –datadir=/MySQL/data/ 啟動MySQL服務
[root@localhost mysql]# service mysqld start 恢複前面備份的資料
[root@localhost ~]# mysql < backup.sql 上面的操作是為了建立将資料庫目錄建立在邏輯卷上的資料庫
下面就基于邏輯卷做實體備份
将所有的表鎖定
MariaDB [(none)]> flush tables with read lock; #會話不能退出 檢視一下我們目前的資料所處的二進制日志位置，也可以滾動下日志
如果在腳本中檢視，使用：mysql -e 'show master status' > /backup/binary-log
另起一個終端，建立快照卷
[root@localhost data]# lvcreate -L 1G -n mydata_snap -s -p r /dev/myvg/mydata 此時，立刻釋放前一個會話中的鎖
MariaDB [(none)]> unlock tables; 将快照卷進行挂載
[root@localhost ~]# mkdir /snap
[root@localhost ~]# mount /dev/myvg/mydata_snap /snap/ 此時，在/snap目錄下就是備份的資料
将快照卷備份的資料移到另一塊磁盤中去
[root@localhost ~]# cp -a /snap/ /backup/bk-`date +%F-%T` 就在此刻，有個使用者登入到資料庫上，操作了一下指令
MariaDB [(none)]> use hellodb
MariaDB [hellodb]> drop table toc; toc表被删除了，在mysql的資料庫目錄中也沒有這個表的資料了
但是，快照卷上還是有toc這個表的資料的
我們該如何将toc這個表恢複回來呢？
[root@localhost ~]# mysqlbinlog /MySQL/data/mysql-bin.000003 #檢視删除toc表的序号
[root@localhost ~]# mysqlbinlog –stop-position=245 /MySQL/data/mysql-bin.000003 > /backup/bk.sql 然後，我們實行全庫恢複
[root@localhost ~]# service mysqld stop
[root@localhost ~]# rm -rf /MySQL/data/* #删除現在的資料
[root@localhost ~]# cp -a /backup/bk-2014-09-21-20:39:55/* /MySQL/data/
#将備份的資料複制過去 啟動mysql，将二進制日志時間導入
[root@localhost ~]# service mysqld start
[root@localhost ~]# mysql < /backup/bk.sql 登入資料庫，驗證
下面是最後一種備份，，使用xtrabackup備份工具備份資料，這是一種對于InnoDB存儲引擎來說是較優的熱備工具，是由percona研發，同時，這種備份工具還支援增量備份
安裝xtrabackup
[root@localhost src]# yum install percona-xtrabackup-2.2.3-4982.el6.x86_64.rpm 這樣安裝會自動解決依賴關系
檢視我們的xtrabackup軟體生成了哪些檔案
[root@localhost src]# rpm -ql percona-xtrabackup
/usr/bin/innobackupex
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-2.2.3
/usr/share/doc/percona-xtrabackup-2.2.3/COPYING innobackupex：用戶端工具，要以mysql協定連入mysqld，不支援離線備份
實作完整備份：
innobackupex –user=DBUSER –password=DBUSERPASS /path/to/BACKUP-DIR
–user=DBUSR
–password=DBUSREPASS 備份時，最小使用一個全新最小的使用者備份，下面我們就建立這麼個使用者
MariaDB [(none)]> create user 'bkuser'@'localhost' identified by 'bk@123'; #建立使用者
MariaDB [(none)]> grant reload,lock tables,replication client on *.* to 'bkuser'@'localhost'; #賦予指定權限
MariaDB [(none)]> flush privileges; 進行備份：
[root@localhost ~]# innobackupex –user=bkuser –password=bk@123 /backup 然後，備份到一半時，就開始報錯了
InnoDB: Error: log file ./ib_logfile0 is of different size 5242880 bytes
InnoDB: than specified in the .cnf file 50331648 bytes!
innobackupex: Error: The xtrabackup child process has died at /usr/bin/innobackupex line 2672.
解決方法，修改mysql的配置檔案：innodb_log_file_size=5M #大小改為5M
然後，備份就ok了，隻要出現下面的提示就可以了
140921 22:53:05 innobackupex: Connection to database server closed
140921 22:53:05 innobackupex: completed OK!
在/backup目錄下，會建立一個以日期指令的目錄
備份的配置檔案是不完整的，MySQL的配置檔案還是要手動備份
備份的同時，在備份目錄中建立如下檔案
xtrabackup_checkpoints –> 備份類型、備份狀态、LSN(日志序列号)範圍
LSN是增量備份的一個重要憑據
xtrabackup_binlog_info –>MySQL伺服器目前正在使用的二進制日志及北方這一刻二進制日志事件的位置
[root@localhost 2014-09-22_09-56-04]# cat xtrabackup_binlog_info
mysql-bin.000005 7925 #備份時，二進制日志事件的位置
backup-my.cnf #備份指令用到的配置選項資訊 準備資料：通過復原為送出的事務及同步已經送出的事務至資料檔案也使得資料檔案處于一緻性狀态
innobackupex –apply-log
此時，關閉資料庫，删除資料庫中的所有資料，注意，我們的二進制日志是另外存放的
通過innobackup還原資料
[root@localhost ~]# innobackupex –copy-back /backup/2014-09-22_09-56-04/ 通過–copy-back選項，注意，還原時，MySQL可以不啟動
檢視MySQL的資料庫目錄
此時，資料都被還原了，我們的二進制預設存在其他位置
重要的一步，恢複的資料的屬主和屬組是root使用者，mysql是無法讀取的，要改變屬主資訊
[root@localhost data]# chown -R mysql:mysql ./* 啟動mysql服務，登入上去，檢視我們的資料是否還在
資料都被恢複了，此時，我們應該做一次完全備份
[root@localhost ~]# innobackupex –user=root –password='' /backup/ 完全備份後的xtrabackup_checkpoints中的資訊如下
然後，我們連接配接到我們的資料庫
建立一個test的新表，并插入部分資料
MariaDB [hellodb]> create table test(id int,name varchar(20));
MariaDB [hellodb]> insert into test values(1,'luffy'),(2,'wsy'),(3,'c-shang'); 然後，删除students這個資料庫
MariaDB [hellodb]> drop table students; 對我們的資料庫，進行增量備份
[root@localhost ~]# innobackupex –incremental /backup/ –incremental-basedir=/backup/2014-09-22_10-33-00/ 這個增量是針對我們恢複資料後的完全備份而進行的
會在/backup目錄下生成一個新的目錄，檢視新的lsn序列号
檢視新的增量備份的目錄下的xtrabackup_checkpoints檔案的lsn
假設，此時，我們的使用者還在使用資料庫，插入了資料，但是，資料庫在這時崩潰了，資料全部丢失，我們如何恢複
連接配接資料庫，在test表中插入新的資料
MariaDB [hellodb]> insert into test values (4,'abc'),(5,'xyz'); 模拟資料庫伺服器崩潰，二進制日志是另外存放的，如果不是的，注意保持二進制日志
[root@localhost ~]# service mysqld stop
[root@localhost data]# rm -rf /MySQL/data/* 此時，我們就要檢視最後一次增量備份時，二進制日志事件的位置
[root@localhost 2014-09-22_10-37-50]# cat xtrabackup_binlog_info
mysql-bin.000001 9182 我們要利用我們的二進制日志檔案，導出9182事件之後的資料庫操作了
[root@localhost ~]# mysqlbinlog –start-position=9182 /binlogs/mysql-bin.000001 > /backup/bk-`date +%F-%T`.sql 還原我們的所有的資料庫
準備過程，隻能送出
[root@localhost ~]# innobackupex –apply-log –redo-only /backup/2014-09-22_10-33-00/ 合并第一個增量
[root@localhost ~]# innobackupex –apply-log –redo-only /backup/2014-09-22_10-33-00/
–incremental-dir=/backup/2014-09-22_10-37-50/ 最後一個增量恢複，可以不用加–redo-only了
将恢複的資料拷貝回去
[root@localhost ~]# innobackupex –copy-back /backup/2014-09-22_10-33-00/ #完全備份的目錄
[root@localhost ~]# cd /MySQL/data/
[root@localhost data]# chown -R mysql:mysql ./* 啟動mysql服務
MariaDB [(none)]> SET SESSION sql_log_bin=0; #關閉二進制日志記錄
MariaDB [hellodb]> SOURCE /back/bk-2014-09-22-10:50:42.sql;
#可以在另一個終端中導入
[root@localhost ~]# mysql < /backup/bk-2014-09-22-10:50:42.sql
MariaDB [hellodb]> SET SESSION sql_log_bin=1; 此時，檢視我們的資料庫