今天有點時間，自己總結下mysql備份的幾種方法以備查閱！
生産環境中資料無疑是一個公司的命脈，作為一個運維工程師無法保證企業資料的安全，那麼是一個不合格的工程師。我們如何保證資料不丢失、或者丢失後快速恢複呢？下面介紹mysql資料庫備份和恢複方法。
目錄大綱
一、Mysql 基礎知識
二、CP/tar 實體備份還原
三、Mysqldump邏輯備份
四、使用lvm2快照備份
五、使用Xtrabackup備份
一、MYSQL基礎知識總結
1、為什麼需要備份資料？
生成環境中我們的資料庫可能會遭遇各種各樣的不測導緻資料丢失，大概有幾種情況（硬體故障、軟體故障、自然災害、黑客攻擊、誤操作（占比最大））是以，為了在資料丢失後能恢複資料，我們就必須定期備份資料，備份資料的政策要根據不同的企業場景進行定制，我們可以根據這幾個因素取定制如何自己企業的備份政策（如：資料庫的大小、能容忍的資料丢失度、能容忍的恢複時間、需要恢複的資料）
2、資料備份分類
根據資料庫的完整度分： 完全備份 和部分備份（增量備份和差異備份）
完全備份：備份整個資料庫集，
部分備份：備份部分資料庫集（如，指備份某個資料庫，或者某個表）
增量備份：備份自上一次備份（增量或完全）以來變化的資料；特定：節約空間、還原較麻煩。
差異備份：備份自上一次完全備份以來變化的資料；特定：浪費空間，還原比增量還原簡單。
根據備份時資料庫的狀态分： 熱備份、溫備份、冷備份
熱備份：指資料庫備份時，資料庫讀寫不受影響
溫備份：指資料庫備份時，資料庫隻讀，不能寫操作
冷備份：指資料庫備份時，資料庫不能進行讀寫操作，即資料庫要下線。
資料庫的不同的存儲引擎支援不同的備份方式
MyISAM ：支援 溫備、冷備
InnoDB ：支援 熱備 、溫備、冷備
根據備份的手段分：實體備份和邏輯備份
實體備份：通過tar,cp等指令直接打包複制資料庫的資料檔案達到備份的效果。
邏輯備份：通過特定的工具從資料庫中導出資料或SQL語句并另存備份（會丢失資料的精度）
3、備份需要考慮的問題
（1）我們需要備份什麼資料？
資料、二進制日志，innodb事務日志、代碼（存儲過程、函數庫、觸發器、排程器）、配置檔案
（2）備份工具如何選擇
mysqldump：邏輯備份工具，适用于所有的存儲引擎，支援溫備份、完全備份和部分備份；innodb支援熱備份。
cp\tar:實體備份工具，适用于所有存儲引擎，冷備、完全備份、部分備份
lvm2 snapshot:幾乎熱備，借助檔案系統管理工具進行備份
xtrabackup:一款非常強大的innodb/xtraDB熱備份工具，支援完全備份、增量備份，由perconat提供
4、設計合适的備份政策
針對不同的企業場景，制定不同的備份政策對資料庫進行備份，備份政策一般為三種：
直接cp\tar 複制資料庫檔案 (适合資料庫較小，直接複制)
mysqldump+複制binlogs （資料量一般，先mysqldump對資料完備，然後定期備份日志文）
lvm2快照+複制binlogs （資料量一般，不過分影響業務運作，使用LVM2快照完備後，增量備份日志）
xtrabackup （資料量大100G以上，不過分影響業務運作使用xtrabackup進行完全備份後，定期進行增 量或差異備份）
yum安裝mysql,預設資料庫路徑/var/lib/msyql,如下
向資料庫施加讀鎖
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
開始備份資料檔案
[root@localhost ~]# mkdir /backup ###建立備份資料庫目錄
[root@localhost ~]# cp -a /var/lib/mysql/* /backup ###保留權限的拷貝源資料檔案
[root@localhost ~]# ls /backup
ibdata1 ib_logfile1 mysql.sock test
ib_logfile0 mysql nginx-1.12.0.tar.gz yema
[root@localhost ~]#
模拟資料丢失并恢複
[root@localhost ~]# rm -rf /var/lib/mysql/*
[root@localhost ~]# ls /var/lib/mysql/
[root@localhost ~]# service mysqld restart
Stopping mysqld: [ OK ]
Initializing MySQL database: Installing MySQL system tables...
OK 重新開機mysql，如果是編譯安裝的應該不能啟動，如果是rpm安裝的則重新初始化資料庫
Filling help tables...
OK
^^^^^^^^
Please report any problems with the /usr/bin/mysqlbug script!
[ OK ]
Starting mysqld: [ OK ]
You have new mail in /var/spool/mail/root
重新開機完成後連接配接資料庫，可以看到資料庫初始化完成，yema資料庫不存在了！
mysql> show databases;
+--------------------+
| Database |
| information_schema |
| mysql |
| test |
3 rows in set (0.01 sec)
mysql>
将備份的資料還原回去，重新開機mysql，并重新連接配接mysql,發現資料庫已恢複！！！
[root@localhost ~]# cp -a /backup/* /var/lib/mysql/
| yema |
4 rows in set (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_yema |
| ym_001 |
| ym_002 |
到此，用cp的方式備份、還原資料庫就完成了。
三、Mysqldump邏輯備份+二進制還原
通過mysqldump進行一次完全備份，再修改資料，然後通過二進制日志增量備份，再恢複，需要在mysql配置檔案中開啟log_bin=on。mysqldump 是一個用戶端的邏輯備份工具，可以生成一個重制建立原始資料庫和表的sql語句，可以支援所有的存儲引擎，對于innodb 支援熱備。
基本文法格式：
mysqldump [options] db_name [tb_name] 恢複時需要手動CREATE DATABASES
mysqldump [options] --databases db_name 恢複時不需要手動CREATE DATABASES
mysqldump [options] --all-databases 恢複時不需要手動建立資料庫
其他選項：-E ,--events 備份事件排程器
-R，--routines 備份存儲過程和存儲函數
--triggers,備份表的觸發器；--skip-triggers
--master-data=[value]
1:記錄為CHANGE MASTER TO語句、語句不被注釋
2：記錄為注釋的CHANGE MASTER TO語句，基于二進制還原隻能全庫還原
--flush-logs:日志滾動 ，鎖定表完成後執行日志滾動
檢視資料庫資訊
| test
yema |
3 rows in set (0.00 sec)
mysql> use yema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
2 rows in set (0.00 sec)
使用mysqldump備份資料庫
#檢視目前二進制檔案的狀态，并記錄position的數字
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin.000003 | 787 | | |
1 row in set (0.00 sec)
[root@localhost mysql]# mysqldump --all-databases --lock-all-tables >/backup/backup.sql
#備份資料庫到backup.sql 檔案中
#建立一個資料庫haha，記錄現在的position 值。
mysql> create database haha;
Query OK, 1 row affected (0.00 sec)
| mysql-bin.000003 | 941 | | |
[root@localhost mysql]# cp /var/lib/mysql/on.000001 /backup
#備份二進制檔案到/backup 目錄
#停止msyql服務，删除所有的資料檔案
#service mysqld stop
[root@localhost mysql]# rm -rf /var/lib/mysql/*
[root@host52 ~]# ls /var/lib/mysql/
[root@host52 ~]#
重新啟動mysql服務（如果是編譯安裝不能啟動，rpm安裝則會初始化資料庫）
[root@localhost mysql]# service mysqld restart
Filling help tables...
檢視資料庫，資料丢失！！！！沒有之前的資料庫了
暫時先将二進制日志關閉
mysql> set sql_log_bin=off;
恢複資料庫，所需時間根資料庫大小而定
mysql> source /backup/backup.sql
開啟二進制日志
mysql> set sql_log_bin=on;
檢視資料庫資訊，發現資料庫yema恢複了，但缺少haha.
通過二進制日志增量恢複資料(haha), 兩個POS點的值非常重要！！！
[root@host52 mysql]# cp /backup/mysql-bin.000003 /var/lib/mysql/
[root@msyql]#mysqlbinlog --start-position=787 --stop-position=941 mysql-bin.000003|mysql
| yema |
| haha |
至此 完成了資料的完備+增量恢複!
lvm2-snapshot知識回顧：
LVM快照是将快照的源分區一個時間點所有檔案的中繼資料進行儲存，
如果源檔案沒有改變，那麼通路快照卷的相應檔案則直接指向源分區的源檔案；
如果源檔案發生變化，快照卷中與之對應的檔案不會發生變化。
（1）部署LVM環境、添加硬碟并分區
[root@node1 ~]# ls /dev/sd* #隻有以下幾塊硬碟, 但是我們不重新開機可以讓系統識别新添加的硬碟
/dev/sda /dev/sda1 /dev/sda2
[root@node1 ~]# echo '- - -' > /sys/class/scsi_host/host0/scan
[root@node1 ~]# echo '- - -' > /sys/class/scsi_host/host1/scan
[root@node1 ~]# echo '- - -' > /sys/class/scsi_host/host2/scan
[root@node1 ~]# ls /dev/sd* #看！sdb識别出來了
/dev/sda /dev/sda1 /dev/sda2 /dev/sdb
[root@node1 ~]# fdisk /dev/sdb #分區
…………
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-2610, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-2610, default 2610): +15G
Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
You have new mail in /var/spool/mail/root
[root@node1 ~]# partx -a /dev/sdb
BLKPG: Device or resource busy
error adding partition 1
（2）建立邏輯卷并挂載
##建立邏輯卷
[root@node1 ~]# pvcreate /dev/sdb1
Physical volume "/dev/sdb1" successfully created
[root@node1 ~]# vgcreate myvg /dev/sdb1
Volume group "myvg" successfully created
[root@node1 ~]# lvcreate -n mydata -L 5G myvg
Logical volume "mydata" created.
[root@node1 ~]# mkfs.ext4 /dev/mapper/myvg-mydata #格式化
[root@node1 ~]# mkdir /lvm_data
[root@node1 ~]# mount /dev/mapper/myvg-mydata /lvm_data #挂載到/lvm_data （4）mysql 配置檔案設定，并導入資料庫
[root@node1 ~]# vim /etc/my.cnf #修改mysql配置檔案的datadir如下
datadir=/lvm_data
[root@node1 ~]# service mysqld restart #重新開機MySQL
檢視資料庫的資訊
mysql> SHOW DATABASES; #檢視目前的資料庫, 我們的資料庫為employees
+--------------------+
| Database |
+--------------------+
| information_schema |
| employees |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> USE employees;
Database changed
mysql> SHOW TABLES; #檢視目前庫中的表
+---------------------+
| Tables_in_employees |
+---------------------+
| departments |
| dept_emp |
| dept_manager |
| employees |
| salaries |
| titles |
+---------------------+
6 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM employees; #由于篇幅原因, 我們這裡隻看一下employees的行數為300024
+----------+
| COUNT(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.05 sec)
（5）建立快照卷并備份
mysql> FLUSH TABLES WITH READ LOCK; #鎖定所有表
Query OK, 0 rows affected (0.00 sec)
[root@node1 lvm_data]# lvcreate -L 1G -n mydata-snap -p r -s /dev/mapper/myvg-mydata #建立快照卷
Logical volume "mydata-snap" created.
mysql> UNLOCK TABLES; #解鎖所有表
Query OK, 0 rows affected (0.00 sec)
[root@node1 lvm_data]# mkdir /lvm_snap #建立檔案夾
[root@node1 lvm_data]# mount /dev/myvg/mydata-snap /lvm_snap/ #挂載snap
mount: block device /dev/mapper/myvg-mydata--snap is write-protected, mounting read-only
[root@node1 lvm_data]# cd /lvm_snap/
[root@node1 lvm_snap]# ls
employees ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.index test
[root@node1 lvm_snap]# tar cf /tmp/mysqlback.tar * #打封包件到/tmp/mysqlback.tar
[root@node1 ~]# umount /lvm_snap/ #解除安裝snap
[root@node1 ~]# lvremove myvg mydata-snap #删除snap （6）資料恢複
[root@node1 lvm_snap]# rm -rf /lvm_data/*
[root@node1 ~]# service mysqld start #啟動MySQL, 如果是編譯安裝的應該不能啟動(需重新初始化), 如果rpm安裝則會重新初始化資料庫
mysql> SHOW DATABASES; #檢視資料庫, 資料丢失!
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
[root@node1 ~]# cd /lvm_data/
[root@node1 lvm_data]# rm -rf * #删除所有檔案
[root@node1 lvm_data]# tar xf /tmp/mysqlback.tar #解壓備份資料庫到此檔案夾
[root@node1 lvm_data]# ls #檢視目前的檔案
employees ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.index test
mysql> SHOW DATABASES; #資料恢複了
+--------------------+
| Database |
+--------------------+
| information_schema |
| employees |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
五、使用Xtrabackup備份
改天繼續