所有備份數(shù)據(jù)都應(yīng)放在非數(shù)據(jù)庫本地耸携，而且建議有多份副本。
測試環(huán)境中做日吃玻恢復(fù)演練违帆，恢復(fù)較備份更為重要。
備份： 能夠防止由于機械故障以及人為誤操作帶來的數(shù)據(jù)丟失金蜀，例如將數(shù)據(jù)庫文件保存在了其它地方。
冗余： 數(shù)據(jù)有多份冗余的畴，但不等備份渊抄，只能防止機械故障還來的數(shù)據(jù)丟失，例如主備模式丧裁、數(shù)據(jù)庫集群护桦。
備份過程中必須考慮因素：
1. 數(shù)據(jù)的一致性
2. 服務(wù)的可用性
邏輯備份： 備份的是建表、建庫煎娇、插入等操作所執(zhí)行SQL語句（DDL DML DCL）二庵，適用于中小型數(shù)據(jù)庫，效率相對較低缓呛。
mysqldump
binlog
mydumper
phpmyadmin
物理備份： 直接復(fù)制數(shù)據(jù)庫文件催享，適用于大型數(shù)據(jù)庫環(huán)境，不受存儲引擎的限制哟绊，但不能恢復(fù)到不同的MySQL版本因妙。
tar,cp
mysqlhotcopy 只能用于備份MyISAM。
xtrabackup
inbackup
lvm snapshot
一票髓、物理備份的方式
1.完全備份-----完整備份：
每次都將所有數(shù)據(jù)（不管自第一次備份以來有沒有修改過）攀涵，進行一次完整的復(fù)制.
特點：占用空間大，備份速度慢洽沟，但恢復(fù)時一次恢復(fù)到位以故，恢復(fù)速度快。
2.增量備份: 每次備份上一次備份到現(xiàn)在產(chǎn)生的新數(shù)據(jù)
特點：因每次僅備份自上一次備份（注意是上一次裆操，不是第一次）以來有變化的文件怒详，所 以備份體積小炉媒，備份速度快，但是恢復(fù)的時候棘利，需要按備份時間順序橱野，逐個備份版本進行恢 復(fù)，恢復(fù)時間長善玫。
1566269843796.png
3.差異備份:只備份跟完整備份不一樣的
在第一次完整備份之后水援，第二次開始每次都將所有文件與第一次完整備份的文件做比較，把自第一次完整備份以來所有修改過的文件進行備份茅郎，且以后每次備份都是和第一次完整備份進行比較（注意是第一次蜗元，不是上一次），備份自第一次完整備份以來所有的修改過的文件系冗。
特點：占用空間比增量備份大奕扣，比完整備份小，恢復(fù)時僅需要恢復(fù)第一個完整版本和最后一次的差異版本掌敬，恢復(fù)速度介于完整備份和增量備份之間惯豆。
1566269915833.png
熱備份
數(shù)據(jù)庫啟動同時給客戶端提供服務(wù)的情況下
冷備份
數(shù)據(jù)庫要關(guān)掉或者不能給客戶端提供服務(wù)
二、percona-xtrabackup 物理備份
是開源免費的支持MySQL 數(shù)據(jù)庫熱備份的軟件奔害，它能對InnoDB和XtraDB存儲引擎的數(shù)據(jù)庫非阻塞地備份楷兽。它不暫停服務(wù)創(chuàng)建Innodb熱備份；
為mysql做增量備份华临；在mysql服務(wù)器之間做在線表遷移芯杀；使創(chuàng)建replication更加容易；備份mysql而不增加服務(wù)器的負(fù)載雅潭。
percona是一家老牌的mysql技術(shù)咨詢公司揭厚。它不僅提供mysql的技術(shù)支持、培訓(xùn)扶供、咨詢筛圆，還發(fā)布了mysql的分支版本--percona Server。并圍繞percona Server還發(fā)布了一系列的mysql工具诚欠。
1顽染、安裝xtrabackup
安裝xtrabackup
# wget http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
# rpm -ivh percona-release-0.1-4.noarch.rpm
[root@mysql_server yum.repos.d]# vim percona-release.repo
1566272559277.png
[root@mysql_server yum.repos.d]# yum -y install percona-xtrabackup-24.x86_64
2.完全備份流程:
[root@mysql_server ~]# mkdir /backup/full -p
[root@mysql_server ~]# innobackupex --user=root --password='Duan@123' /backup/full/
image.png
查看:
[root@mysql_server ~]# ls -l /backup/full/
total 0
drwxr-x---. 6 root root 205 Jan 30 10:41 2020-01-30_10-41-07
完全備份恢復(fù)流程：
1. 停止數(shù)據(jù)庫
2. 清理環(huán)境
3. 重演回滾
4. 恢復(fù)數(shù)據(jù)
5. 修改權(quán)限
6. 啟動數(shù)據(jù)庫
1、停止數(shù)據(jù)庫
[root@mysql_server ~]# systemctl stop mysqld
2轰绵、清理環(huán)境
[root@mysql_server ~]# rm -rf /var/lib/mysql/*
[root@mysql_server ~]# rm -rf /var/log/mysqld.log
[root@mysql_server ~]# rm -rf /var/log/mysql-slow/slow.log
3粉寞、重演回滾
[root@mysql_server ~]# innobackupex --apply-log /backup/full/2020-01-30_10-41-07/
4、恢復(fù)數(shù)據(jù)
恢復(fù)之前需要確認(rèn)配置文件內(nèi)有數(shù)據(jù)庫目錄指定左腔，不然xtrabackup不知道恢復(fù)到哪里
# cat /etc/my.cnf
datadir=/var/lib/mysql
[root@mysql_server ~]# innobackupex --copy-back /backup/full/2020-01-30_10-41-07/
5唧垦、修改權(quán)限
[root@mysql_server ~]# chown mysql.mysql /var/lib/mysql/ -R
6、啟動數(shù)據(jù)庫
[root@mysql_server ~]# systemctl start mysqld
3.增量備份流程
原理：每次備份上一次備份到現(xiàn)在產(chǎn)生的新數(shù)據(jù)
創(chuàng)建測試庫：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
mysql> use test
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
+------+
1.先創(chuàng)建一個完整備份
[root@mysql_server ~]# mkdir /backup/incremental
[root@mysql_server ~]# innobackupex --user=root --password='Duan@123' /backup/incremental/
[root@mysql_server ~]# cd /backup/incremental/
[root@mysql_server incremental]# ls
2020-01-30_11-26-13
[root@mysql_server incremental]# mv 2020-01-30_11-26-13/ 1/
[root@mysql_server incremental]# ls
1
2.模擬數(shù)據(jù)變化
數(shù)據(jù)第一次變化：
[root@mysql_server]# mysql -uroot -pDuan@123 -e"insert into test.t1 values(2);"
[root@mysql_server]# mysql -uroot -pDuan@123 -e"select * from test.t1;"
+------+
| id |
+------+
| 1 |
| 2 |
+------+
[root@mysql_server ~]# innobackupex --user=root --password='Duan@123' --incremental /backup/incremental/ --incremental-basedir=/backup/incremental/1/
--incremental-basedir:基于哪個增量
[root@mysql_server ~]# ls /backup/incremental/
1 2020-01-30_11-38-06 ---相當(dāng)于第一次數(shù)據(jù)變化的增量備份
[root@mysql_server ~]# mv /backup/incremental/2020-01-30_11-38-06/ /backup/incremental/2
數(shù)據(jù)第二次變化：
[root@mysql_server ~]# mysql -uroot -pDuan@123 -e"insert into test.t1 values(3);"
[root@mysql_server ~]# innobackupex --user=root --password='Duan@123' --incremental /backup/incremental/ --incremental-basedir=/backup/incremental/2
#基于上一次的備份為目錄
[root@mysql_server ~]# mv /backup/incremental/2020-01-30_11-42-15/ /backup/incremental/3
[root@mysql_server ~]# ls /backup/incremental/
1 2 3 --相當(dāng)于第二次數(shù)據(jù)變化的增量備份
[root@mysql_server ~]# ls /backup/incremental/
假設(shè)：
1 2 3
全備周一 增備周二 增備周三
生產(chǎn)中套路也是這樣液样，只不過操作都寫在腳本里
增量備份恢復(fù)流程
1. 停止數(shù)據(jù)庫
2. 清理環(huán)境
3. 依次重演回滾redo log #注意
4. 恢復(fù)數(shù)據(jù)
5. 修改權(quán)限
6. 啟動數(shù)據(jù)庫
1振亮、停止數(shù)據(jù)庫
[root@mysql_server ~]# systemctl stop mysqld
2巧还、清理環(huán)境
[root@mysql_server ~]# rm -rf /var/lib/mysql/*
3、依次重演回滾
[root@mysql_server ~]# innobackupex --apply-log --redo-only /backup/incremental/1
[root@mysql_server ~]# innobackupex --apply-log --redo-only /backup/incremental/1 --incremental-dir=/backup/incremental/2
--incremental-dir：增量目錄
#將想要恢復(fù)的數(shù)據(jù)節(jié)點按順序回滾到第一次完備回滾的目錄中
#如果想要回復(fù)到3這個節(jié)點坊秸，那么回滾順序就是1麸祷，2，最后才是3
[root@mysql_server ~]# innobackupex --apply-log --redo-only /backup/incremental/1 --incremental-dir=/backup/incremental/3
4褒搔、恢復(fù)數(shù)據(jù)
[root@mysql_server ~]# innobackupex --copy-back /backup/incremental/1
5阶牍、修改權(quán)限
[root@mysql_server ~]# chown -R mysql.mysql /var/lib/mysql
6、啟動數(shù)據(jù)庫
[root@mysql_server ~]# systemctl start mysqld
[root@mysql_server ~]# mysql -uroot -pDuan@123 -e'select * from test.t1;'
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
4星瘾、差異備份流程
環(huán)境準(zhǔn)備
[root@mysql_server ~]# mysql -uroot -pDuan@123
mysql> drop table test.t1;
mysql> create table test.t1(id int);
mysql> insert into test.t1 values(1);
mysql> select * from test.t1;
+------+
| id |
+------+
| 1 |
+------+
[root@mysql_server ~]# mkdir /backup/difference
第一次完備：
[root@mysql_server ~]# innobackupex --user=root --password='Duan@123' /backup/difference/
[root@mysql_server ~]# mv /backup/difference/2020-01-30_12-10-47/ /backup/difference/1
[root@mysql_server ~]# ls /backup/difference/
1
第一次差異備份環(huán)境準(zhǔn)備
[root@mysql_server ~]# mysql -uroot -pDuan@123 -e"insert into test.t1 values(2);"
第一次差異備份
語法: # innobackupex --user=root --password=888 --incremental /xtrabackup --incremental-basedir=/xtrabackup/完全備份目錄
[root@mysql_server ~]# innobackupex --user=root --password='Duan@123' --incremental /backup/difference/ --incremental-basedir=/backup/difference/1
#備份目錄基于周一的備份
[root@mysql_server ~]# mv /backup/difference/2020-01-30_12-15-12/ /backup/difference/2
第二次差異備份環(huán)境準(zhǔn)備
[root@mysql_server ~]# mysql -uroot -pDuan@123 -e"insert into test.t1 values(3);"
第二次差異備份
[root@mysql_server ~]# innobackupex --user=root --password='Duan@123' --incremental /backup/difference/ --incremental-basedir=/backup/difference/1
命令和第一次差異備份一模一樣走孽，都是基于第一次完備進行備份
[root@mysql_server ~]# mv /backup/difference/2020-01-30_12-18-25/ /backup/difference/3
第三次差異備份環(huán)境準(zhǔn)備
[root@mysql_server ~]# mysql -uroot -pDuan@123 -e"insert into test.t1 values(4);"
第三次差異備份
[root@mysql_server ~]# innobackupex --user=root --password='Duan@123' --incremental /backup/difference/ --incremental-basedir=/backup/difference/1
命令也是一樣
[root@mysql_server ~]# mv /backup/difference/2020-01-30_12-21-07/ /backup/difference/4
[root@mysql_server ~]# ls /backup/difference/
1 2 3 4
[root@mysql_server ~]# ls /backup/difference/
1 2 3 4
完備 基于完備的第一次差異備份
差異備份恢復(fù)流程
1. 停止數(shù)據(jù)庫
2. 清理環(huán)境
3. 重演回滾redo log（周一，某次差異）#注意
4. 恢復(fù)數(shù)據(jù)
5. 修改權(quán)限
6. 啟動數(shù)據(jù)庫
1琳状、停止數(shù)據(jù)庫
[root@mysql_server ~]# systemctl stop mysqld
2磕瓷、清理環(huán)境
[root@mysql_server ~]# rm -rf /var/lib/mysql/*
3、重演回滾
回滾只需要回第一次完備念逞，和你想要恢復(fù)的那一個節(jié)點
[root@mysql_server ~]# innobackupex --apply-log --redo-only /backup/difference/1
恢復(fù)差異的redo log
語法:# innobackupex --apply-log --redo-only /xtrabackup/完全備份目錄（周一）--incremental-dir=/xtrabacku/某個差異備份
[root@mysql_server ~]# innobackupex --apply-log --redo-only /backup/difference/1 --incremental-dir=/backup/difference/3
這里我們恢復(fù)第二次差異備份這個節(jié)點困食，表中數(shù)據(jù)應(yīng)該是1，2翎承，3
4陷舅、恢復(fù)數(shù)據(jù)
[root@mysql_server ~]# innobackupex --copy-back /backup/difference/1
5、修改權(quán)限
[root@mysql_server ~]# chown -R mysql.mysql /var/lib/mysql
6审洞、啟動數(shù)據(jù)庫
[root@mysql_server ~]# systemctl start mysqld
[root@mysql_server ~]# mysql -uroot -pDuan@123 -e'select * from test.t1;'
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3.mysqldump邏輯備份 ---- 推薦優(yōu)先使用
mysqldump可以保證 數(shù)據(jù)一致，服務(wù)可用待讳。
如何保證數(shù)據(jù)一致?在備份的時候進行鎖表會自動鎖表芒澜。鎖住之后在備份。
本身為客戶端工具:
遠(yuǎn)程備份語法: # mysqldump -h 服務(wù)器 -u用戶名 -p密碼 數(shù)據(jù)庫名 > 備份文件.sql
本地備份語法: # mysqldump -u用戶名 -p密碼 數(shù)據(jù)庫名 > 備份文件.sql
常用備份選項
-A, --all-databases
備份所有庫
-B, --databases bbs test mysql
備份多個數(shù)據(jù)庫
-F, --flush-logs
備份之前刷新binlog日志
--default-character-set
指定導(dǎo)出數(shù)據(jù)時采用何種字符集创淡，如果數(shù)據(jù)表不是采用默認(rèn)的 latin1 字符集的話痴晦，那么導(dǎo)出時必須指定該選項，否則再次導(dǎo)入數(shù)據(jù)后將產(chǎn)生亂碼問題琳彩。
--no-data誊酌，-d
不導(dǎo)出任何數(shù)據(jù)，只導(dǎo)出數(shù)據(jù)庫表結(jié)構(gòu)露乏。
注意
使用 mysqldump 備份數(shù)據(jù)庫時避免鎖表
對一個正在運行的數(shù)據(jù)庫進行備份請慎重１套恰！ 如果一定要 在服務(wù)運行期間備份瘟仿，可以選擇添加 --single-transaction選項箱锐，
類似執(zhí)行： mysqldump --single-transaction -u root -p123456 dbname > mysql.sql
環(huán)境準(zhǔn)備
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
mysql> use test
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
[root@mysql_server ~]# mkdir /home/backup
1、備份表
語法: # mysqldump -u root -p密碼 庫名 表名 > /db1.t1.bak
[root@mysql_server ~]# mysqldump -uroot -pDuan@123 test t1 > /home/backup/test.t1.bak
備份多個表：
語法: mysqldump -u root -p密碼 庫名 表名1 表名2 > /db1.t1_t2.bak
2劳较、備份庫
備份一個庫：相當(dāng)于將這個庫里面的所有表全部備份驹止。
語法: # mysqldump -u root -p1 db1 > /db1.bak
[root@mysql_server ~]# mysqldump -uroot -pDuan@123 test > /home/backup/test.bak
備份多個庫：
語法：mysqldump -u root -p1 -B db1 db2 db3 > /db123.bak
備份所有的庫：
語法：# mysqldump -u root -p1 -A > /alldb.bak
3浩聋、恢復(fù)數(shù)據(jù)庫和表
為保證數(shù)據(jù)一致性，應(yīng)在恢復(fù)數(shù)據(jù)之前停止數(shù)據(jù)庫對外的服務(wù),停止binlog日志 因為binlog使用binlog日志恢復(fù)數(shù)據(jù)時也會產(chǎn)生binlog日志臊恋。
清理環(huán)境
[root@mysql_server ~]# mysql -uroot -pDuan@123
mysql> drop database test;
恢復(fù)庫
登陸mysql創(chuàng)建一個庫
mysql> create database test;
恢復(fù)：
[root@mysql_server ~]# mysql -uroot -pDuan@123 test < /home/backup/test.bak
恢復(fù)表
登陸到剛才恢復(fù)的庫中將t1表刪除掉
mysql> use test
mysql> drop table t1;
開始恢復(fù):
mysql> set sql_log_bin=0; #停止binlog日志
mysql> source /home/backup/test.t1.bak; --加路徑和備份的文件
恢復(fù)方式二：
# mysql -u root -p1 db1 < db1.t1.bak
庫名 備份的文件路徑
4衣洁、備份及恢復(fù)表結(jié)構(gòu)
1.備份表結(jié)構(gòu)：
語法：mysqldump -uroot -p123456 -d database table > dump.sql
[root@mysql_server ~]# mysqldump -uroot -p'Duan@123' -d test t1 > /home/back/t1.bak
恢復(fù)表結(jié)構(gòu)：
登陸數(shù)據(jù)庫創(chuàng)建一個庫
mysql> create database t1;
語法：# mysql -u root -p密碼 -D 庫名 < t1.bak
[root@mysql_server ~]# mysql -uroot -p'Duan@123' -D t1 < /home/back/t1.bak
5、數(shù)據(jù)的導(dǎo)入導(dǎo)出抖仅。沒有表結(jié)構(gòu)坊夫。
表的導(dǎo)出和導(dǎo)入只備份表內(nèi)記錄，不會備份表結(jié)構(gòu)岸售，需要通過mysqldump備份表結(jié)構(gòu)践樱，恢復(fù)時先恢復(fù)表結(jié)構(gòu)，再導(dǎo)入數(shù)據(jù)凸丸。
mysql> show variables like "secure_file_priv"; ----查詢導(dǎo)入導(dǎo)出的目錄拷邢。（保證數(shù)據(jù)安全做共享）
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ | //可以修改
+------------------+-----------------------+
修改安全文件目錄：
1.創(chuàng)建一個目錄：mkdir 路徑目錄
[root@mysql_server ~]# mkdir /sql
2.修改權(quán)限
[root@mysql_server ~]# chown mysql.mysql /sql
3.編輯配置文件：
vim /etc/my.cnf
在[mysqld]里追加
secure_file_priv=/sql
4.重新啟動mysql.
1.導(dǎo)出數(shù)據(jù)
登陸數(shù)據(jù)查看數(shù)據(jù)
mysql> show databases; #找到test庫
mysql> use test #進入test庫
mysql> show tables; #找到它t1表
mysql> select * from t1 into outfile '/sql/test.t1.bak';
添加修飾的：
mysql> select * from t1 into outfile '/sql/test.t1.bak1' fields terminated by ',' lines terminated by '\n';
注：
fields terminated by ',' ：字段以逗號分割
lines terminated by '\n'：結(jié)尾換行
2.數(shù)據(jù)的導(dǎo)入
先將原來表里面的數(shù)據(jù)清除掉，只保留表結(jié)構(gòu)
mysql> delete from t1;
mysql> load data infile '/sql/test.t1.bak' into table t1;
如果將數(shù)據(jù)導(dǎo)入別的表屎慢，需要創(chuàng)建這個表并創(chuàng)建相應(yīng)的表結(jié)構(gòu)瞭稼。
6、通過binlog恢復(fù)
開啟binlog日志：
1566533393301.png
創(chuàng)建目錄并修改權(quán)限
[root@mysql_server ~]# mkdir /var/log/sql-bin
[root@mysql_server ~]# chown mysql.mysql /var/log/sql-bin
[root@mysql_server ~]# systemctl restart mysqld
1566533482923.png
mysql> flush logs; #刷新binlog日志會截斷產(chǎn)生新的日志文件
mysql> create database testdb;
mysql> create table testdb.t1(id int); #創(chuàng)建一個表
1566533650005.png
根據(jù)位置恢復(fù)
找到要恢復(fù)的sql語句的起始位置腻惠、結(jié)束位置
[root@mysql-server sql-bin]# mysqlbinlog mylog.000002
1566533960687.png
測試:
[root@mysql_server ~]# mysql -uroot -p'Duan@123'
mysql> drop table testdb.t1; #將這個表刪除
Query OK, 0 rows affected (0.01 sec)
恢復(fù)：
[root@mysql_server ~]# cd /var/log/sql-bin/
[root@mysql_server sql-bin]# mysqlbinlog --start-position 219 --stop-position 321 mylog.000002 |mysql -uroot -p'Duan@123'
mysql> use testdb
mysql> show tables;
