MySQL備份的主要作用是當(dāng)出現(xiàn)硬件故障、軟件故障、自然災(zāi)害、******、人為誤操作時能快速、高效、安全的解決問題，以免造成不必要的損失
創(chuàng)新互聯(lián)建站專業(yè)為企業(yè)提供樂都網(wǎng)站建設(shè)、樂都做網(wǎng)站、樂都網(wǎng)站設(shè)計(jì)、樂都網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計(jì)與制作、樂都企業(yè)網(wǎng)站模板建站服務(wù)，十年樂都做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。
Mysql備份時需要備份什么？
1、需要用到的數(shù)據(jù)
2、二進(jìn)制日志、InnoDB事務(wù)日志
3、存儲代碼；例如：存儲過程、存儲函數(shù)、觸發(fā)器、時間調(diào)度器
4、服務(wù)器的配置文件
Mysql備份時需要注意的要點(diǎn)：
1、能容忍最多丟失多少數(shù)據(jù)
2、恢復(fù)數(shù)據(jù)需要在多長時間內(nèi)完成
3、需要恢復(fù)那些數(shù)據(jù)
4、做好備份之后，必須要做還原測試，用于測試備份的可用性
Mysql備份時需要考慮的因素
1、持鎖需要多長時間
2、備份的過程時長
3、在備份時，是否會增加系統(tǒng)業(yè)務(wù)的負(fù)載
4、恢復(fù)過程的時長
Mysql備份的類型；
以數(shù)據(jù)集角度來劃分：
完全備份：備份整個數(shù)據(jù)集
部分備份：只備份數(shù)據(jù)子集
以時間軸角度來劃分：
完全備份：備份整個數(shù)據(jù)集
增量備份：備份最近一次完全備份或增量備份之后變化的數(shù)據(jù)
差異備份：備份最近一次完全備份之后變化的數(shù)據(jù)
注意：雖然增量備份比差異備份更節(jié)約磁盤空間，但是還原數(shù)據(jù)起來增量備份卻比差異備份更麻煩
根據(jù)服務(wù)器業(yè)務(wù)系統(tǒng)能否在線來劃分：
熱備份：讀寫操作均可執(zhí)行
溫備份：讀操作可執(zhí)行，但寫操作不可執(zhí)行
冷備份：讀寫操作均不可執(zhí)行
注意：InnoDB存儲引擎即支持熱備份，也支持溫備份；而MyISAM存儲引擎只支持溫備份，不支持熱備份
物理備份：直接復(fù)制數(shù)據(jù)文件進(jìn)行備份
邏輯備份：從數(shù)據(jù)庫中導(dǎo)出數(shù)據(jù)從而進(jìn)行備份；邏輯備份與存儲引擎無關(guān)，如果備份的是InnoDB存儲引擎，用在MyISAM存儲引擎上也是可以的
Mysql備份的方式：
1、mysqldump+復(fù)制binlog來實(shí)現(xiàn)：使用mysqldump來實(shí)現(xiàn)數(shù)據(jù)的完全備份，復(fù)制binlog中指定時間范圍內(nèi)的event來實(shí)現(xiàn)增量備份
2、lvm2+復(fù)制binlog：利用lvm2快照以及使用cp等命令工具來實(shí)現(xiàn)物理備份、完全備份，復(fù)制binlog中指點(diǎn)事件范圍內(nèi)的event來實(shí)現(xiàn)增量備份
3、xtrabackup：由Percona提供的備份工具；對InnoDB支持熱備、完全備份和增量備份，MySIAM支持溫備、完全備份
一、基于mysqldump+復(fù)制binlog來實(shí)現(xiàn)備份
準(zhǔn)備環(huán)境：
| 節(jié)點(diǎn)1 | IP：172.18.42.100 |
| 節(jié)點(diǎn)2 | IP：172.18.42.111 |
1、部署節(jié)點(diǎn)1
（1）安裝mysql服務(wù)，編輯其二進(jìn)制文件并進(jìn)行完全備份
[root@localhost ~]# yum install mysql-server -y ##安裝Mysql服務(wù) [root@localhost ~]# vim /etc/my.cnf ##編輯其配置文件 [mysqld] innodb_file_per_table = ON log_bin=mysql-bin ##啟用二進(jìn)制文件 [root@localhost ~]# mysqldump -uroot --lock-tables --master-data=2 --databases Mydata > db1.sql ##在未修改數(shù)據(jù)庫之前做一次完全備份 ## --lock-tables：鎖定指定的數(shù)據(jù)庫，建議在備份單個數(shù)據(jù)庫是使用 ## --lock-all-tables：鎖定所有的數(shù)據(jù)庫，建議在備份多個數(shù)據(jù)庫時使用 ## --master-data=1 | 2 #1：記錄為change master to語句，此語句不會被注釋 #2：記錄為change master to語句，此語句會被注釋 ## --databases：指定需要備份的數(shù)據(jù)庫，當(dāng)有多個數(shù)據(jù)庫時中間可用空格隔開 [root@localhost ~]# less db1.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1106; ##記錄當(dāng)前備份時的二進(jìn)制日志，以及事件在二進(jìn)制日志中所在位置
（2）修改數(shù)據(jù)庫中的數(shù)據(jù)，并使用“mysqlbinlog”生成增量備份
mysql> select * from db1; ##未修改數(shù)據(jù)之前查看一次數(shù)據(jù) +----+------------+------+ | ID | Name Age +----+------------+------+ | 1 | BaiGu jing 100 | 2 | SunDa shen 200 | 3 | ZhuBa jie 150 +----+------------+------+ mysql> insert into db1 (ID,Name,Age) values (4,'RuLai',999); mysql> insert into db1 (ID,Name,Age) values (5,'YuDi',888); mysql> select * from db1; ##修改數(shù)據(jù)之后查看一次 +----+------------+------+ | ID | Name Age +----+------------+------+ | 1 | BaiGu jing 100 | 2 | SunDa shen 200 | 3 | ZhuBa jie 150 | 4 | RuLai 999 | 5 | YuDi 888 +----+------------+------+ [root@localhost ~]# mysqlbinlog -uroot --start-position=1106 /var/lib/mysql/mysql-bin.000003 > binary_log ##生成增量備份 ##--start-position：指明事件的pos [root@localhost ~]# less binary_log ##查看增量備份文件 insert into db1 (ID,Name,Age) values (4,'RuLai',999) ##記錄了修改表的相關(guān)操作 /*!*/; # at 1223 #160612 21:44:35 server id 1 end_log_pos 1339 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1465739075/*!*/; insert into db1 (ID,Name,Age) values (5,'YuDi',888) /*!*/; # at 1339 #160612 21:45:41 server id 1 end_log_pos 1441 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1465739141/*!*/;
2、部署節(jié)點(diǎn)2
（1）提供mysql服務(wù)，并將節(jié)點(diǎn)1的完全備份文件“db1.sql”和增量備份文件“binary_log”發(fā)送給節(jié)點(diǎn)2
[root@localhost ~]# yum install mysql-server -y [root@localhost ~]# scp db1.sql binary_log root@172.18.42.111:/root
（2）節(jié)點(diǎn)2導(dǎo)入“db1.sql"，并查看其數(shù)據(jù)
[root@localhost ~]# mysql < db1.sql mysql> select * from db1; +----+------------+------+ | ID | Name Age +----+------------+------+ | 1 | BaiGu jing 100 | 2 | SunDa shen 200 | 3 | ZhuBa jie 150 +----+------------+------+ ##數(shù)據(jù)并不是我們修改之后的數(shù)據(jù)
（3）假設(shè)節(jié)點(diǎn)1掛掉了，需要讓節(jié)點(diǎn)2恢復(fù)到節(jié)點(diǎn)1當(dāng)前修改數(shù)據(jù)庫時的數(shù)據(jù)，此時我們只需要節(jié)點(diǎn)2導(dǎo)入“binary_log”增量備份文件即可
[root@localhost ~]# mysql < binary_log mysql> select * from db1; ##導(dǎo)入增量備份之后，節(jié)點(diǎn)2的數(shù)據(jù)和節(jié)點(diǎn)1修改數(shù)據(jù)庫之后的數(shù)據(jù)一樣 +----+------------+------+ | ID | Name Age +----+------------+------+ | 1 | BaiGu jing 100 | 2 | SunDa shen 200 | 3 | ZhuBa jie 150 | 4 | RuLai 999 | 5 | YuDi 888 +----+------------+------+
二：基于lvm2+復(fù)制binlog來實(shí)現(xiàn)備份
準(zhǔn)備環(huán)境：
| 節(jié)點(diǎn)1 | IP：172.18.42.100 |
| 節(jié)點(diǎn)2 | IP：172.18.42.111 |
1、部署節(jié)點(diǎn)1
（1）安裝mysql服務(wù)，滾動日志并記錄
[root@localhost ~]# yum install mysql-server -y [root@localhost ~]# service mysqld start [root@localhost ~]# mysql -e "show master status" > binary_file ##對于如何開啟二進(jìn)制日志小白我就不再說了 [root@localhost ~]# cat binary_file File Position Binlog_Do_DB Binlog_Ignore_DB mysql-bin.000004 208 ##記錄了當(dāng)前使用的二進(jìn)制日志以及事件所在二進(jìn)制日志中的pos [root@localhost ~]# service mysqld stop ##記錄完日志之后把mysql服務(wù)關(guān)閉
（2）創(chuàng)建lvm快照，并掛載
[root@localhost ~]# pvcreate /dev/sda5 ##創(chuàng)建物理卷 Physical volume "/dev/sda5" successfully created [root@localhost ~]# vgcreate wxpp /dev/sda5 ##創(chuàng)建卷組，名為“wxpp” Volume group "wxpp" successfully created [root@localhost ~]# lvcreate -L +5G -n wxxp1 wxpp ##創(chuàng)建邏輯卷，大小為5G，名為“wxpp1” Logical volume "wxxp1" created. [root@localhost ~]# mke2fs -t ext4 /dev/wxpp/wxxp1 ##將邏輯卷格式化為“ext4”的文件系統(tǒng) [root@localhost ~]# mount /dev/wxpp/wxxp1 /data/ ##將邏輯卷掛載至“/data”目錄下 [root@localhost ~]# mkdir /data/mysql ##創(chuàng)建mysql數(shù)據(jù)庫存儲數(shù)據(jù)的目錄，注意：此路徑一定要和配置文件里的“datadir”指向的路徑相同 [root@localhost ~]# chown mysql.mysql /data/mysql ##讓mysql用戶具有存儲數(shù)據(jù)的權(quán)限 [root@localhost ~]# ll /data/ drwxr-xr-x 2 mysql mysql 4096 Jun 12 22:20 /data/mysql drwx------ 2 root root 16384 Jun 12 22:18 lost+found [root@localhost ~]# service mysqld start ##啟動mysql服務(wù) [root@localhost ~]# ll /data/mysql/ ##生成了大量的日志文件 total 21272 -rw-rw---- 1 mysql mysql 10485760 Jun 12 22:28 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Jun 12 22:28 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Jun 12 22:24 ib_logfile1 drwx------ 2 mysql mysql 4096 Jun 12 22:25 Mydata drwx------ 2 mysql mysql 4096 Jun 12 22:24 mysql -rw-rw---- 1 mysql mysql 19758 Jun 12 22:24 mysql-bin.000001 -rw-rw---- 1 mysql mysql 765307 Jun 12 22:24 mysql-bin.000002 -rw-rw---- 1 mysql mysql 1190 Jun 12 22:28 mysql-bin.000003 -rw-rw---- 1 mysql mysql 106 Jun 12 22:28 mysql-bin.000004 -rw-rw---- 1 mysql mysql 76 Jun 12 22:28 mysql-bin.index drwx------ 2 mysql mysql 4096 Jun 12 22:24 test mysql> flush tables with read lock; ##給數(shù)據(jù)施加讀鎖，防止數(shù)據(jù)改變 [root@localhost ~]# lvcreate -L +1G -s -n binary_log /dev/wxpp/wxxp1 ##創(chuàng)建lvm快照，大小為1G，名為“binary_log” [root@localhost ~]# mount /dev/wxpp/binary_log /mnt/ ##將快照掛載至“/mnt”目錄下 [root@localhost ~]# ll /mnt/mysql/ total 21272 -rw-rw---- 1 mysql mysql 10485760 Jun 12 22:28 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Jun 12 22:28 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Jun 12 22:24 ib_logfile1 drwx------ 2 mysql mysql 4096 Jun 12 22:25 Mydata drwx------ 2 mysql mysql 4096 Jun 12 22:24 mysql -rw-rw---- 1 mysql mysql 19758 Jun 12 22:24 mysql-bin.000001 -rw-rw---- 1 mysql mysql 765307 Jun 12 22:24 mysql-bin.000002 -rw-rw---- 1 mysql mysql 1190 Jun 12 22:28 mysql-bin.000003 -rw-rw---- 1 mysql mysql 106 Jun 12 22:28 mysql-bin.000004 -rw-rw---- 1 mysql mysql 76 Jun 12 22:28 mysql-bin.index drwx------ 2 mysql mysql 4096 Jun 12 22:24 test mysql> unlock tables; ##釋放寫鎖
（3）生成增量備份
mysql> select * from db1; ##未修改前查看一次數(shù)據(jù) +----+-------+------+ | ID | Name Age +----+-------+------+ | 1 | MaGe 100 | 2 | Lweim 200 | 3 | Wzx 300 | 4 | wxpp 400 +----+-------+------+ mysql> delete from db1 where ID=1; mysql> select * from db1; +----+-------+------+ | ID | Name Age +----+-------+------+ | 2 | Lweim 200 | 3 | Wzx 300 | 4 | wxpp 400 +----+-------+------+ [root@localhost ~]# mysqlbinlog --start-position=208 /data/mysql/mysql-bin.000004 > backup.sql ##生成增量備份文件
2、部署節(jié)點(diǎn)2
（1）將節(jié)點(diǎn)1快照下“/mnt/mysql“目錄下的文件以及增量備份文件發(fā)送給節(jié)點(diǎn)2；
[root@localhost ~]# scp -r /mnt/mysql/* backup.sql root@172.18.42.111:/data/mysql/ [root@localhost ~]# chown -R mysql.mysql /data/mysql/ ##將權(quán)限更改為“mysql” [root@localhost ~]# ll /data/mysql/ total 21272 -rw-r----- 1 mysql mysql 10485760 May 9 22:33 ibdata1 -rw-r----- 1 mysql mysql 5242880 May 9 22:33 ib_logfile0 -rw-r----- 1 mysql mysql 5242880 May 9 22:33 ib_logfile1 drwx------ 2 mysql mysql 4096 May 9 22:33 Mydata drwx------ 2 mysql mysql 4096 May 9 22:33 mysql -rw-r----- 1 mysql mysql 19758 May 9 22:33 mysql-bin.000001 -rw-r----- 1 mysql mysql 765307 May 9 22:33 mysql-bin.000002 -rw-r----- 1 mysql mysql 1190 May 9 22:33 mysql-bin.000003 -rw-r----- 1 mysql mysql 208 May 9 22:33 mysql-bin.000004 -rw-r----- 1 mysql mysql 76 May 9 22:33 mysql-bin.index drwx------ 2 mysql mysql 4096 May 9 22:33 test
（2）啟動mysql服務(wù)，并查看其數(shù)據(jù)庫
[root@localhost ~]# service mysqld start mysql> select * from db1; ##并不是修改之后的數(shù)據(jù) +----+-------+------+ | ID | Name Age +----+-------+------+ | 1 | MaGe 100 | 2 | Lweim 200 | 3 | Wzx 300 | 4 | wxpp 400 +----+-------+------+
（3）假設(shè)節(jié)點(diǎn)1因?yàn)槿藶檎`操作導(dǎo)致數(shù)據(jù)庫崩了，此時讓節(jié)點(diǎn)二導(dǎo)入增量備份“backup.sql”即可
[root@localhost ~]# mysql < backup.sql mysql> select * from db1; +----+-------+------+ | ID | Name Age +----+-------+------+ | 2 | Lweim 200 | 3 | Wzx 300 | 4 | wxpp 400 +----+-------+------+
（4）移除lvm快照
[root@localhost ~]# umount /mnt [root@localhost ~]# lvremove /dev/wxpp/binary_log Logical volume "binary_log" successfully removed
當(dāng)有大量數(shù)據(jù)庫需要復(fù)制時，此時使用快照是最快、最有效的方式，防止對數(shù)據(jù)庫施加過長時間的讀鎖，以免造成不必要的損失
三、基于xtrabackup命令工具來實(shí)現(xiàn)備份恢復(fù)
準(zhǔn)備環(huán)境
| 節(jié)點(diǎn)1 | IP：172.18.42.100 |
| 節(jié)點(diǎn)2 | IP：172.18.42.111 |
1、部署節(jié)點(diǎn)1
（1）安裝“xtrabackup”工具
[root@node0 ~]# yum install percona-xtrabackup-2.3.2-1.el7.x86_64.rpm -y ##下載rpm包之后安裝 [root@node0 ~]# rpm -ql percona-xtrabackup ##查看percona-xtrabackup生成的文件 /usr/bin/innobackupex ##我們主要使用這個命令 /usr/bin/xbcloud /usr/bin/xbcloud_osenv /usr/bin/xbcrypt /usr/bin/xbstream /usr/bin/xtrabackup /usr/share/doc/percona-xtrabackup-2.3.2 /usr/share/doc/percona-xtrabackup-2.3.2/COPYING /usr/share/man/man1/innobackupex.1.gz /usr/share/man/man1/xbcrypt.1.gz /usr/share/man/man1/xbstream.1.gz /usr/share/man/man1/xtrabackup.1.gz
（2）首先確保存儲引擎為innodb，且“innodb_file_per_table”為ON狀態(tài)
MariaDB [Mydata]> show table status\G; Name: db1 Engine: InnoDB MariaDB [Mydata]> show global variables like "%innodb%"; innodb_file_per_table ON ##確保每個表使用單獨(dú)的表空間，如果不指明，則所有數(shù)據(jù)庫中的所有表將放在同一空間下
（3）實(shí)現(xiàn)整個數(shù)據(jù)庫備份
[root@node0 ~]# innobackupex --user=root /backup/ ##對整個數(shù)據(jù)庫做一次備份，放在“/backup”目錄下；注意：如果登錄mysql需要密碼，則需要指明密碼 [root@node0 ~]# ll /backup/2016-04-20_10-34-41/ total 18460 -rw-r----- 1 root root 385 Apr 20 10:34 backup-my.cnf -rw-r----- 1 root root 18874368 Apr 20 10:34 ibdata1 drwx------ 2 root root 47 Apr 20 10:34 Mydata drwx------ 2 root root 4096 Apr 20 10:34 mysql drwx------ 2 root root 4096 Apr 20 10:34 performance_schema drwx------ 2 root root 19 Apr 20 10:34 test -rw-r----- 1 root root 21 Apr 20 10:34 xtrabackup_binlog_info -rw-r----- 1 root root 113 Apr 20 10:34 xtrabackup_checkpoints -rw-r----- 1 root root 457 Apr 20 10:34 xtrabackup_info -rw-r----- 1 root root 2560 Apr 20 10:34 xtrabackup_logfile
（4）修改數(shù)據(jù)，生成增量備份
MariaDB [Mydata]> select * from db1; ##未修改數(shù)據(jù)之前查看一次 +------+ | id +------+ | 1 | 2 | 3 +------+ MariaDB [Mydata]> insert into db1 values (100),(200); ##插入兩個“ID” MariaDB [Mydata]> select * from db1; +------+ | id +------+ | 1 | 2 | 3 | 100 | 200 +------+ [root@node0 ~]# innobackupex --incremental /backup --incremental-basedir=/backup/2016-04-20_10-34-41/ ##生成增量備份 ##--incremental：把增量備份存儲在哪個路徑下 ##--incremental-basedir：基于誰來做增量備份 [root@node0 ~]# ll /backup/ drwx------ 6 root root 4096 Apr 20 10:34 2016-04-20_10-34-41 ##完全備份文件 drwx------ 6 root root 4096 Apr 20 10:41 2016-04-20_10-41-42 ##增量備份文件
（5）對完全備份數(shù)據(jù)做出整理，并把增量備份導(dǎo)入到完全備份中
[root@node0 ~]# innobackupex --apply-log --redo-only /backup/2016-04-20_10-34-41/ ##對完全備份做出整理 [root@node0 ~]# innobackupex --apply-log --redo-only /backup/2016-04-20_10-34-41/ --increment-dir=/backup/2016-04-20_10-41-42 ##將增量備份導(dǎo)入到完全備份中
2、部署節(jié)點(diǎn)2
（1）安裝xtrabackup工具
[root@node0 ~]# yum install percona-xtrabackup-2.3.2-1.el7.x86_64.rpm -y
（2）將節(jié)點(diǎn)1上已經(jīng)導(dǎo)入增量備份的完全備份目錄發(fā)送給節(jié)點(diǎn)2，如果節(jié)點(diǎn)1掛了，我們可以基于導(dǎo)入增量備份的完全備份來恢復(fù)
[root@node0 ~]# scp -r /backup/2016-04-20_10-34-41/ root@172.18.42.201:/root [root@node1 ~]# innobackupex --copy-back 2016-04-20_10-34-41/ ##導(dǎo)出數(shù)據(jù) [root@node1 ~]# chown -R mysql.mysql /data/ ##更改權(quán)限為mysql [root@node1 ~]# ll /data/mysql/ total 18448 drwx------ 2 root root 6 Apr 20 11:02 2016-04-20_10-34-41 -rw-r----- 1 root root 18874368 Apr 20 11:02 ibdata1 drwx------ 2 root root 47 Apr 20 11:02 Mydata drwx------ 2 root root 4096 Apr 20 11:02 mysql drwx------ 2 root root 4096 Apr 20 11:02 performance_schema drwx------ 2 root root 19 Apr 20 11:02 test -rw-r----- 1 root root 23 Apr 20 11:02 xtrabackup_binlog_pos_innodb -rw-r----- 1 root root 457 Apr 20 11:02 xtrabackup_info [root@node1 ~]# systemctl start mariadb.service MariaDB [Mydata]> select * from db1; +------+ | id +------+ | 1 | 2 | 3 | 100 | 200 +------+
問題小結(jié)：
1、當(dāng)使用mysqldump+復(fù)制binlog方法時，最好不要將數(shù)據(jù)文件和二進(jìn)制文件放在同一磁盤上，且需要對二進(jìn)制文件作出備份
2、使用lvm2+復(fù)制binlog方法時，在創(chuàng)建快照的同時，最好對數(shù)據(jù)庫施加讀鎖，以免數(shù)據(jù)發(fā)生改變，同時要確保文件的屬主、屬組為mysql
3、在使用xtrabackup方法時，整個過程中mysql存儲數(shù)據(jù)的目錄必須要和配置文件里面的“datadir”“相同，同時要確保文件的屬主、屬組為mysql
重慶分公司
重慶分公司
