開啟二進(jìn)制日志

在 /etc/my.cnf 的 [mysqld] 配置域中添加如下配置內(nèi)容

log-bin=/var/log/mysql/mysql-bin

/var/log/mysql/mysql-bin 是二進(jìn)制日志文件的前綴速侈。
例如完整的日志可能是
/var/log/mysql/mysql-bin-000001

備份用戶需要的完整權(quán)限

select 
reload    # 允許使用該 FLUSH 語(yǔ)句，告訴服務(wù)器將授權(quán)表重新加載到內(nèi)存中

lock tables
show view
event
trigger

process  # 允許顯示有關(guān)服務(wù)器內(nèi)執(zhí)行的線程的信息（即有關(guān)會(huì)話正在執(zhí)行的語(yǔ)句的信息）

replication  client  # 讀取二進(jìn)制日志的位置信息涩盾，

普通文件方式

簡(jiǎn)單數(shù)據(jù)的導(dǎo)入和導(dǎo)出

編輯配置文件 my.cnf 添加如下配置項(xiàng)

[mysqld]
secure_file_priv = 保存到本地目錄

例如

secure_file_priv = /tmp

配置完成后需要重啟服務(wù)

本地目錄需要給 mysql 用戶授予讀寫權(quán)限

登錄到服務(wù)器辛辨，可以驗(yàn)證配置項(xiàng)是否生效

SELECT @@GLOBAL.secure_file_priv;
或者
SHOW VARIABLES LIKE "secure_file_priv";

假如沒(méi)有使用此配置項(xiàng)捕捂，查詢到的值為 NULL。

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

執(zhí)行下面的導(dǎo)入導(dǎo)出語(yǔ)句時(shí)就會(huì)報(bào)如下錯(cuò)誤

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

導(dǎo)出

select * from school.class into outfile "/tmp/class2.db";

導(dǎo)入

導(dǎo)入到數(shù)據(jù)文件中的列必須和表中的一一對(duì)應(yīng)斗搞。包含 id 列的值指攒。

實(shí)例文件

5,云計(jì)算1809
6,云計(jì)算1901
7,云計(jì)算1902

導(dǎo)入語(yǔ)句

載入外部“形式整齊”的數(shù)據(jù)(csv 格式的文件，沒(méi)有域之家用逗號(hào)隔開)： 
load data infile  '文件完整名（含路徑）' into table 表名
fields terminated by '域分隔符'   optionally    enclosed  by '"'
ignore 1 lines;

optionally enclosed by '"' 用于識(shí)別域值的界定符
ignore 1 lines; 忽略第一行

MySQL 邏輯備份 mysqldump

邏輯備份特點(diǎn)

• 備份的是建表僻焚、建庫(kù)允悦、插入等操作所執(zhí)行SQL語(yǔ)句（DDL DML DCL），適用于中小型數(shù)據(jù)庫(kù)虑啤。
• 效率相對(duì)較低

在日常工作中隙弛，我們會(huì)使用 mysqldump 命令創(chuàng)建SQL格式的轉(zhuǎn)儲(chǔ)文件來(lái)備份數(shù)據(jù)庫(kù)∧剑或者我們把數(shù)據(jù)導(dǎo)出后做數(shù)據(jù)遷移全闷，主從復(fù)制等操作。mysqldump是一個(gè)邏輯備份工具,復(fù)制原始的數(shù)據(jù)庫(kù)對(duì)象定義和表數(shù)據(jù)產(chǎn)生一組可執(zhí)行的SQL語(yǔ)句铣墨。 默認(rèn)情況下,生成insert語(yǔ)句室埋，也能生成其它分隔符的輸出或XML格式的文件。

特點(diǎn)

1. 自動(dòng)記錄position位置伊约。

show master  status\G;

2. 可用性姚淆，一致性

鎖表機(jī)制

用法

mysqldump  -h 服務(wù)器  -u用戶名  -p密碼   數(shù)據(jù)庫(kù)名  > 備份文件.sql

/*查看幫助*/
mysqldump --help

常見參數(shù)

• --single-transaction 備份前啟用一個(gè)事務(wù)，保證數(shù)據(jù)一致性屡律。
  僅對(duì) InnoDB 存儲(chǔ)引擎有效腌逢。還有需要保證沒(méi)有其他的連接正在使用以下語(yǔ)句： ALTER TABLE， CREATE TABLE超埋，DROP TABLE搏讶， RENAME TABLE， TRUNCATE TABLE

• -l, --lock tables 對(duì)于不支持事務(wù)的存儲(chǔ)引擎的表備份使用此選項(xiàng)霍殴，比如 MySAM, 可以保證備份期間的數(shù)據(jù)一致性媒惕。會(huì)依次對(duì)正在備份的每個(gè)數(shù)據(jù)庫(kù)中的所有表進(jìn)行鎖表操作，此時(shí)只可以讀来庭。和 --single-transaction 互斥妒蔚。

• -x, --lock-all-tables 鎖定所有數(shù)據(jù)庫(kù)中的所有表。這是通過(guò)在整個(gè)轉(zhuǎn)儲(chǔ)期間采用全局讀鎖來(lái)實(shí)現(xiàn)。

有幾個(gè)選項(xiàng)控制mysqldump如何 處理存儲(chǔ)的程序（存儲(chǔ)過(guò)程和函數(shù)肴盏，觸發(fā)器和事件）：

• --events：備份事件計(jì)劃程序事件

• --routines：備份存儲(chǔ)過(guò)程和函數(shù)

• --triggers：備份表的觸發(fā)器

--triggers默認(rèn)情況下啟用 該選項(xiàng)科盛，以便在轉(zhuǎn)儲(chǔ)表時(shí)，它們伴隨著它們具有的任何觸發(fā)器菜皂。

默認(rèn)情況下要禁用這些選項(xiàng)贞绵。需要明確設(shè)置這些選項(xiàng)：
--skip-events， --skip-routines, --skip-triggers恍飘。

日常用法

備份所有庫(kù)

// 先配置用戶名和密碼
shell> vi ~/.mysql_user
[mysqldump]
user=root
password=123

shell> mysqldump --defaults-file=~/.mysql_user -h172.16.153.10 --all-databases > `date +%FT%H_%M_%S`dump_all.sql
# 不包含 INFORMATION_SCHEMA,performance_schema,sys

備份指定的多個(gè)庫(kù)

// 為了考慮篇幅榨崩，請(qǐng)自行添加指定用戶名密碼參數(shù)和指定服務(wù)器的參數(shù)
// --defaults-file=~/.mysql_user -hip
shell> mysqldump --databases db1 db2 db3 > `date +%FT%H_%M_%S`dump_all.sql

備份指定庫(kù)的指定幾個(gè)表

shell> mysqldump db1 t1 t3 t7 > dump.sql

其他參數(shù)

• --master-data=0|1|2

  服務(wù)器的二進(jìn)制日志必須打開

  0 不記錄二進(jìn)制日志文件及位置：

  1 以CHANGE MASTER TO 的方式記錄位置，可用于恢復(fù)后直接啟動(dòng)從服務(wù)器：

  2 以CHANGE MASTER TO 的方式記錄位置常侣，但默認(rèn)被注釋：

• --dump-slave 用于在slave上dump數(shù)據(jù)蜡饵，建立新的slave。因?yàn)槲覀冊(cè)谑褂胢ysqldump時(shí)會(huì)鎖表胳施，所以大多數(shù)情況下溯祸，我們的導(dǎo)出操作一般會(huì)在只讀備庫(kù)上做，為了獲取主庫(kù)的Relay_Master_Log_File(二進(jìn)制日志)和Exec_Master_Log_Pos(主服務(wù)器二進(jìn)制日志中數(shù)據(jù)所處的位置)舞肆，需要用到這個(gè)參數(shù)焦辅，不過(guò)這個(gè)參數(shù)只有在5.7以后的才會(huì)有

• --no-data, -d 不導(dǎo)出任何數(shù)據(jù)，只導(dǎo)出數(shù)據(jù)庫(kù)表結(jié)構(gòu)

• --lock-all-tables:鎖定所有表 對(duì)MyISAM引擎的表開始備份前椿胯，先鎖定所有表筷登。

優(yōu)勢(shì)

mysqldump的優(yōu)勢(shì)：

1. 可以查看或者編輯十分方便，它也可以靈活性的恢復(fù)之前的數(shù)據(jù)哩盲。
2. 不關(guān)心底層的存儲(chǔ)引擎前方，既適用于支持事務(wù)的，也適用于不支持事務(wù)的表廉油。
3. 不過(guò)它不能作為一個(gè)快速備份大量的數(shù)據(jù)或可伸縮的解決方案惠险。如果數(shù)據(jù)庫(kù)過(guò)大,即使備份步驟需要的時(shí)間不算太久,但有可能恢復(fù)數(shù)據(jù)的速度也會(huì)非常慢,因?yàn)樗婕暗腟QL語(yǔ)句插入磁盤I/O,創(chuàng)建索引等等。 對(duì)于大規(guī)模的備份和恢復(fù),更合適的做法是物理備份,復(fù)制其原始格式的數(shù)據(jù)文件,可以快速恢復(fù)抒线。

恢復(fù)

shell> mysql -uroot -p123456   db1  < dump.sql

或者班巩，在mysql中，使用 source命令：

mysql> source dump.sql

如果文件是不包含CREATE DATABASE和 USE語(yǔ)句的單數(shù)據(jù)庫(kù)轉(zhuǎn)儲(chǔ) 嘶炭，請(qǐng)首先創(chuàng)建數(shù)據(jù)庫(kù)（如有必要）：

shell> mysqladmin create db1

然后在加載轉(zhuǎn)儲(chǔ)文件時(shí)指定數(shù)據(jù)庫(kù)名稱：

shell> mysql db1 < dump.sql

或者抱慌，在mysql中創(chuàng)建數(shù)據(jù)庫(kù)，將其選為默認(rèn)數(shù)據(jù)庫(kù)眨猎，然后加載轉(zhuǎn)儲(chǔ)文件：

mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql>source dump.sql

Example

shell> mysql --defaults-file=~/.mysql_user < /backup/2016-12-08-04-mysql-all.sql

MySQL 物理備份： Innobackupex 和 xtrabackup（熱備）

Percona XtraBackup是一款基于MySQL的熱備份的開源實(shí)用程序抑进，它可以備份5.1到5.7版本上InnoDB,XtraDB,MyISAM存儲(chǔ)引擎的表， Xtrabackup有兩個(gè)主要的工具：xtrabackup睡陪、innobackupex 寺渗。

第一種安裝方式： 使用 YUM 方式安裝

地址
https://www.percona.com/downloads/Percona-XtraBackup-LATEST/

1. 確保安裝 EPEL 源

yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

2. 安裝 libev
   為了成功安裝Percona XtraBackup libev包需要先安裝夕凝。

yum  install -y libev

3. 安裝Percona存儲(chǔ)庫(kù)

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

4. 測(cè)試存儲(chǔ)庫(kù)的可用性

shell> yum list | grep percona

應(yīng)該輸出如下信息

...
percona-xtrabackup-20.x86_64               2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-debuginfo.x86_64     2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-test.x86_64          2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-21.x86_64               2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-21-debuginfo.x86_64     2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-22.x86_64               2.2.13-1.el5                percona-release-x86_64
percona-xtrabackup-22-debuginfo.x86_64     2.2.13-1.el5                percona-release-x86_64
percona-xtrabackup-debuginfo.x86_64        2.3.5-1.el5                 percona-release-x86_64
percona-xtrabackup-test.x86_64             2.3.5-1.el5                 percona-release-x86_64
percona-xtrabackup-test-21.x86_64          2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-test-22.x86_64          2.2.13-1.el5                percona-release-x86_64
...

5. 安裝軟件

shell> yum install percona-xtrabackup-24

6. 驗(yàn)證安裝

   image

第二種安裝方式：下載對(duì)應(yīng)版本的軟件包，在本地安裝

點(diǎn)擊 下載頁(yè)面户秤，選擇對(duì)應(yīng)版本后進(jìn)行下載

image

示例：

下載 2.4.4版本

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

安裝

yum localinstall percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

注意：像這樣手動(dòng)安裝軟件包時(shí)，您需要確保解決所有依賴項(xiàng)并自行安裝缺少的軟件包逮矛。

卸載

yum remove percona-xtrabackup

80 版本

注意：
這個(gè)版本只支持 MySQL8.0的數(shù)據(jù)進(jìn)行備份鸡号，不支持 MySQL8.0版本之前的數(shù)據(jù)進(jìn)行備份。

# 安裝倉(cāng)庫(kù)文件
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

# 啟用倉(cāng)庫(kù)
percona-release enable-only tools release

#安裝軟件
yum install percona-xtrabackup-80

報(bào)錯(cuò)解決

來(lái)自 file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona 的無(wú)效 GPG 密鑰：No key found in given key data

或者

源 "CentOS 7 - Percona" 的 GPG 密鑰已安裝须鼎，但是不適用于此軟件包鲸伴。請(qǐng)檢查
源的公鑰 URL 是否配置正確。

 失敗的軟件包是：Percona-Server-shared-56-5.6.43-rel84.3.el7.x86_64
 GPG  密鑰配置為：file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona

修改倉(cāng)庫(kù)文件 percona-release.repo 不使用密鑰認(rèn)證

image

再次安裝會(huì)看到如下報(bào)錯(cuò)信息

Transaction check error:
  file /etc/my.cnf from install of Percona-Server-shared-56-5.6.43-rel84.3.el7.x86_64 conflicts with file from package mysql-community-server-5.7.25-1.el7.x86_64

錯(cuò)誤概要
-------------

需要安裝如下軟件

yum install -y   mysql-community-libs-compat

注意：
這個(gè)軟件的源是 mysql57-community

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql 文件內(nèi)容如下：

點(diǎn)我傳送門

3. 再執(zhí)行安裝命令

yum install percona-xtrabackup-24

日常操作

條件：

1. 在 MySQL 服務(wù)器本地安裝 Xtrbackup 并執(zhí)行相關(guān)操作晋控。
2. 給執(zhí)行備份到用戶進(jìn)行相應(yīng)的授權(quán)汞窗。

配置選項(xiàng)

配置選項(xiàng)可以在sh命令行中直接使用，也可以在 my.cnf 文件中配置

# my.cnf 文件的配置
[xtrabackup]
target_dir = /backups/mysql/   # 備份數(shù)據(jù)放置的位置

假如是編譯安裝的 mysql 赡译，需要在配置文件my.cnf 中指定 socket 文件的路徑仲吏。

[xtrabackup]
socket = /tmp/mysql.sock

1. 全備

下面的命令均假設(shè)沒(méi)有在 my.cnf 中配置任何關(guān)于 xtrabackup 的選項(xiàng)

要執(zhí)行備份需要指定 備份數(shù)據(jù)放置的位置，就是目錄蝌焚，假如目錄不存在裹唆，則會(huì)自動(dòng)創(chuàng)建；==注意這個(gè)目錄不會(huì)被遞歸創(chuàng)建只洒，僅僅會(huì)創(chuàng)建最后一級(jí)目錄许帐；==假如存在，就會(huì)直接開始備份毕谴，并且不會(huì)覆蓋原來(lái)的數(shù)據(jù)成畦。

1. 開始備份

shell> xtrabackup --backup --user=root --password='123'  --target-dir=/backups/full

# 備份完成后，可以看到備份時(shí)的LSN號(hào)涝开，當(dāng)下次進(jìn)行增量備份時(shí)循帐，xtrabackup就只備份大于此號(hào)的page即可。

1. 查看備份文件

[root@mysql-master ~]# ls -lh /backups/full
總用量 13M
-rw-r----- 1 root root  487 8月  18 09:44 backup-my.cnf
-rw-r----- 1 root root  293 8月  18 09:44 ib_buffer_pool
-rw-r----- 1 root root  12M 8月  18 09:44 ibdata1
drwxr-x--- 2 root root 4.0K 8月  18 09:44 mysql
drwxr-x--- 2 root root   88 8月  18 09:44 one_db
drwxr-x--- 2 root root 8.0K 8月  18 09:44 performance_schema
drwxr-x--- 2 root root   58 8月  18 09:44 shark_db
drwxr-x--- 2 root root 8.0K 8月  18 09:44 sys
-rw-r----- 1 root root  115 8月  18 09:44 xtrabackup_checkpoints
-rw-r----- 1 root root  446 8月  18 09:44 xtrabackup_info
-rw-r----- 1 root root 2.5K 8月  18 09:44 xtrabackup_logfile

進(jìn)入目錄后忠寻，可以看到一些目錄惧浴，這些目錄與我們數(shù)據(jù)庫(kù)的名稱相同，沒(méi)錯(cuò)奕剃，這些就是各個(gè)數(shù)據(jù)庫(kù)的數(shù)據(jù)文件備份目錄衷旅。

還有一個(gè)innodb的共享表空間文件，ibdata1纵朋，注意柿顶，如果想要使用xtrabackup備份眾多數(shù)據(jù)庫(kù)中的某一個(gè)，那么必須保證在創(chuàng)建這個(gè)數(shù)據(jù)庫(kù)時(shí)操软，已經(jīng)開啟了innodb_file_per_table參數(shù)嘁锯，否則將無(wú)法單獨(dú)備份數(shù)據(jù)庫(kù)服務(wù)器中的某一個(gè)數(shù)據(jù)庫(kù)。

除了剛才描述的這些數(shù)據(jù)文件，xtrabackup還為我們生成了一些文件家乘，我們來(lái)看看這些文件都有什么用（不同版本的xtrabackup生成的文件可能不同）蝗羊。

1. backup-my.cnf
   此文件中包含了my.cnf中的一些設(shè)置信息，但是仁锯，并不是my.cnf中的所有信息都會(huì)包含在此文件中耀找，此文件中只包含了備份時(shí)需要的信息。

2. xtrabackup_binlog_info
   需要開啟二進(jìn)制日志
   此文件中記錄了備份開始時(shí)二進(jìn)制日志文件的"位置(position)"

3. xtrabackup_checkpoints
   此文件中記錄此次備份屬于那種類型的備份业崖，是全量還是增量野芒，備份時(shí)起始的LSN號(hào)碼，結(jié)束的LSN號(hào)碼等信息双炕。

4. xtrabackup_info
   本次備份的概要信息狞悲，此文件中的信息還是比較全面的。

5. xtrabackup_logfile
   記錄了備份過(guò)程中的日志妇斤，在對(duì)數(shù)據(jù)進(jìn)行prepare時(shí)需要通過(guò)日志將數(shù)據(jù)還原成一致的可用的數(shù)據(jù)摇锋。

6. 準(zhǔn)備恢復(fù)的數(shù)據(jù)

使用 xtrabackup --backup 選項(xiàng)進(jìn)行備份后，并不能直接使用站超，首先需要準(zhǔn)備它以便還原它乱投。

如果您嘗試使用這些數(shù)據(jù)文件啟動(dòng)InnoDB，它將檢測(cè)損壞并自行崩潰顷编，以防止您在損壞的數(shù)據(jù)上運(yùn)行戚炫。

因?yàn)閭浞莩龅臄?shù)據(jù)是不一致的，我們需要將同時(shí)備份出的事務(wù)日志應(yīng)用到備份中媳纬，才能得到一份完整双肤、一致、可用的數(shù)據(jù)钮惠，xtrabackup稱這一步操作為prepare茅糜，直譯過(guò)來(lái)就是"準(zhǔn)備"。

xtrabackup --prepare 步驟使文件在一個(gè)時(shí)刻完全一致

shell> xtrabackup --prepare --target-dir=/backups/full

如果你要備份的數(shù)據(jù)量巨大素挽，那么備份時(shí)長(zhǎng)會(huì)變長(zhǎng)蔑赘，期間備份的事務(wù)日志容量有可能會(huì)很大。那么预明，我們可以使用--use-memory選項(xiàng)缩赛，加速準(zhǔn)備工作的完成，在不指定內(nèi)存大小的情況下撰糠，準(zhǔn)備工作默認(rèn)會(huì)占用100MB的內(nèi)存酥馍，如果服務(wù)器有一定的空閑內(nèi)存，那么我們可以讓xtrabackup使用指定大小的內(nèi)存完成準(zhǔn)備工作阅酪，以提升準(zhǔn)備工作完成的速度旨袒，示例語(yǔ)句如下汁针。

shell> xtrabackup --prepare --use-memory=512M --target-dir=/backups/full

==準(zhǔn)備備份時(shí)不建議中斷xtrabackup進(jìn)程，因?yàn)檫@可能會(huì)導(dǎo)致數(shù)據(jù)文件損壞砚尽，備份將無(wú)法使用施无。如果準(zhǔn)備過(guò)程中斷，則無(wú)法保證備份有效性必孤。==

準(zhǔn)備備份數(shù)據(jù)完成后帆精，應(yīng)該會(huì)看到如下信息。

InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13596200
180818 10:09:19 completed OK!

恢復(fù)

xtrabackup 在執(zhí)行copyback時(shí)會(huì)讀取數(shù)據(jù)庫(kù)的my.cnf中的配置隧魄，但是如果my.cnf中沒(méi)有配置datadir，那么--datadir選項(xiàng)必須存在隘蝎，而且购啄，datadir目錄必須為空目錄，其中不能存在數(shù)據(jù)嘱么，否則在執(zhí)行上述命令時(shí)會(huì)報(bào)錯(cuò)狮含，--copy-back選項(xiàng)對(duì)應(yīng)的目錄就是我們準(zhǔn)備好的可用數(shù)據(jù)的目錄。

為了能夠正常的恢復(fù)數(shù)據(jù)曼振，我們先確定數(shù)據(jù)庫(kù)服務(wù)已經(jīng)停止了几迄，而且對(duì)應(yīng)的數(shù)據(jù)目錄中不存在數(shù)據(jù)，然后進(jìn)行數(shù)據(jù)還原工作冰评，刪除數(shù)據(jù)目錄中的文件與日志映胁。

1. 停止數(shù)據(jù)庫(kù)的服務(wù)
2. 清理環(huán)境
3. 修改權(quán)限
4. 啟動(dòng)數(shù)據(jù)庫(kù)

shell> systemctl stop mysqld.service

shell> rm -rf /var/lib/mysql/*

shell> xtrabackup --copy-back --datadir=/var/lib/mysql --target-dir=/backups/full

# 下面為完成后的輸出結(jié)果
180818 10:59:25 [01]        ...done
180818 10:59:25 completed OK!
shell> chown  mysql.mysql -R /var/lib/mysql

或者使用 rsync 命令

shell> rsync -avrP /backup/ /var/lib/mysql/
shell> chown  mysql.mysql -R /var/lib/mysql

啟動(dòng)數(shù)據(jù)庫(kù)

shell> systemctl start mysqld.service

innobackuper 命令實(shí)現(xiàn)

shell> innobackupex --defaults-file=/etc/my.cnf --host=192.168.1.146 --user=root --password=123123 /backup

shell> nnobackupex --apply-log --use-memory=4G /backups/2018-08-17_15-53-11

shell> systemctl stop mysqld.service

shell> rm -rf /var/lib/mysql/*

shell> innobackupex --datadir=/var/lib/mysql --copy-back 2018-08-17_15-53-11

shell> chown  mysql.mysql -R /var/lib/mysql

shell> systemctl start mysqld.service

全量備份思路總結(jié)

1. 執(zhí)行備份命令
   • 指定 數(shù)據(jù)庫(kù)的用戶名和密碼
   • 指定 備份目錄，注意只可以自動(dòng)創(chuàng)建最后一級(jí)的目錄
2. 準(zhǔn)備備份的數(shù)據(jù)
   • 就是指： --prepare 參數(shù), 保證數(shù)據(jù)的統(tǒng)一且完整性
3. 停服務(wù)甲雅，并且把 mysql 的數(shù)據(jù)目錄下的所有文件和文件夾清除解孙。
   • /var/lib/mysql/ 此目錄必須是空的
4. 恢復(fù)數(shù)據(jù)
5. • 本質(zhì)上就是拷貝備份的文件到指定的 mysql 數(shù)據(jù)目錄下
6. 修改 mysql 數(shù)據(jù)目錄的屬主和屬組為 MySQL 服務(wù)器進(jìn)程啟動(dòng)的用戶，默認(rèn)是 mysql
7. 啟動(dòng)服務(wù)

2. 增量備份

image

特點(diǎn)：每次備份抛人，都對(duì)自上一次備份（注意是上一次弛姜，不是第一次）到此時(shí)備份之間有變化的文件，進(jìn)行備份妖枚。所 以備份體積小廷臼，備份速度快，但是恢復(fù)的時(shí)候绝页，需要按備份時(shí)間順序荠商，逐個(gè)備份版本進(jìn)行恢復(fù)，恢復(fù)時(shí)持續(xù)的時(shí)間長(zhǎng)续誉。

無(wú)論xtrabackup和innobackupex工具支持增量備份结啼，這意味著它們可以只復(fù)制自上次備份以來(lái)發(fā)生變化的數(shù)據(jù)。

您可以在每個(gè)完整備份之間執(zhí)行許多增量備份屈芜，因此您可以設(shè)置備份過(guò)程郊愧，例如每周一次完整備份和每天增量備份朴译，或每天完整備份和每小時(shí)增量備份。

增量備份有效属铁，因?yàn)槊總€(gè)InnoDB頁(yè)面都包含一個(gè)日志序列號(hào)或LSN眠寿。該LSN是整個(gè)數(shù)據(jù)庫(kù)系統(tǒng)的版本號(hào)。每個(gè)頁(yè)面的LSN顯示它最近的更改焦蘑。

當(dāng)我們做過(guò)全量備份以后會(huì)在目錄下產(chǎn)生xtrabackup_checkpoints的文件 這里面記錄了lsn和備份方式盯拱，我們可以基于這次的全量做增量的備份。

shell> cat /backups/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 13593159
last_lsn = 13593168
compact = 0
recover_binlog_info = 0

增量備份實(shí)際上并不將數(shù)據(jù)文件與先前備份的數(shù)據(jù)文件進(jìn)行比較例嘱。事實(shí)上狡逢，如果你知道它的LSN，你可以使用 xtrabackup --incremental-lsn來(lái)執(zhí)行增量備份拼卵，而不需要先前的備份奢浑。增量備份只是讀取頁(yè)面并將其LSN與最后一個(gè)備份的LSN進(jìn)行比較。但是腋腮，您仍需要完整備份來(lái)恢復(fù)增量更改;如果沒(méi)有完整備份作為基礎(chǔ)雀彼，增量備份將毫無(wú)用處。

創(chuàng)建增量備份

要進(jìn)行增量備份即寡，請(qǐng)像往常一樣以完整備份開始, 使用下面的命令創(chuàng)建基礎(chǔ)的全量備份徊哑。

shell> xtrabackup --backup --user=root --password=123 --target-dir=/backups/base

現(xiàn)在您已擁有完整備份，以后可以根據(jù)它進(jìn)行增量備份聪富。

向數(shù)據(jù)庫(kù)中添加數(shù)據(jù)莺丑，以便于測(cè)試

mysql> select count(id) from shark_db.student;
+-----------+
| count(id) |
+-----------+
|     99213 |
+-----------+
1 row in set (0.04 sec)

mysql> insert into shark_db.student (name,age,phone) values('xiguatian',20,13149876789);
Query OK, 1 row affected (0.00 sec)

mysql> select count(id) from shark_db.student;
+-----------+
| count(id) |
+-----------+
|     99214 |
+-----------+
1 row in set (0.03 sec)

使用以下命令進(jìn)行增量備份：

shell> xtrabackup --backup --user=root --password=123 --target-dir=/backups/inc1 --incremental-basedir=/backups/base

該/data/backups/inc1/目錄現(xiàn)在應(yīng)包含增量文件

ls -lh /backups/inc1/
總用量 116K
-rw-r----- 1 root root  487 8月  18 11:40 backup-my.cnf
-rw-r----- 1 root root  293 8月  18 11:40 ib_buffer_pool
-rw-r----- 1 root root  64K 8月  18 11:40 ibdata1.delta
-rw-r----- 1 root root   44 8月  18 11:40 ibdata1.meta
drwxr-x--- 2 root root 4.0K 8月  18 11:40 mysql
drwxr-x--- 2 root root  144 8月  18 11:40 one_db
drwxr-x--- 2 root root 8.0K 8月  18 11:40 performance_schema
drwxr-x--- 2 root root   88 8月  18 11:40 shark_db
drwxr-x--- 2 root root 8.0K 8月  18 11:40 sys
-rw-r----- 1 root root  120 8月  18 11:40 xtrabackup_checkpoints
-rw-r----- 1 root root  498 8月  18 11:40 xtrabackup_info
-rw-r----- 1 root root 2.5K 8月  18 11:40 xtrabackup_logfile

這個(gè)時(shí)候去查看增量備份的xtrabackup_checkpoints,會(huì)發(fā)現(xiàn)同樣也記錄了LSN 等信息

shell> cat /backups/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 13596423
to_lsn = 13596628
last_lsn = 13596637
compact = 0
recover_binlog_info = 0
// 這也意味著你可以在增量的備份上繼續(xù)增量的備份。

from_lsn是備份的起始LSN墩蔓，對(duì)于增量窒盐，它必須與前一個(gè)/基本備份的to_lsn（如果它是最后一個(gè)檢查點(diǎn)）相同。

上面的情況是钢拧，to_lsn （上一個(gè)檢查點(diǎn)LSN）和last_lsn（上次復(fù)制的LSN）之間存在差異蟹漓，這意味著在備份過(guò)程中服務(wù)器上存在一些流量

模擬增加數(shù)據(jù)

mysql> insert into shark_db.student (name,age,phone) values('xiguatian3',21,13149876789);
Query OK, 1 row affected (0.01 sec)

mysql> select count(id) from shark_db.student;                                     +-----------+
| count(id) |
+-----------+
|     99215 |
+-----------+
1 row in set (0.03 sec)

現(xiàn)在可以使用此目錄作為另一個(gè)增量備份的基礎(chǔ)：

shell> xtrabackup --backup --user=root --password=123 --target-dir=/backups/inc2 --incremental-basedir=/backups/inc1

準(zhǔn)備增量備份的數(shù)據(jù)

增量備份的步驟與完全備份的步驟不同。在完全備份中源内，執(zhí)行兩種類型的操作以使數(shù)據(jù)庫(kù)保持一致：從日志文件中針對(duì)數(shù)據(jù)文件重播已提交的事務(wù)葡粒，并回滾未提交的事務(wù)。在準(zhǔn)備增量備份時(shí)膜钓，必須跳過(guò)未提交事務(wù)的回滾嗽交，因?yàn)閭浞輹r(shí)未提交的事務(wù)可能正在進(jìn)行中，并且很可能它們將在下一次增量備份中提交颂斜。您應(yīng)該使用該 選項(xiàng)來(lái)阻止回滾階段.

xtrabackup --apply-log-only

警告

如果不使用該 選項(xiàng)來(lái)阻止回滾階段夫壁，那么增量備份將毫無(wú)用處∥执回滾事務(wù)后盒让，無(wú)法應(yīng)用進(jìn)一步的增量備份梅肤。

要準(zhǔn)備數(shù)據(jù)，需要從一開始就準(zhǔn)備邑茄，現(xiàn)在回想一下我們都有那些備份

/backups/base
/backups/inc1
/backups/inc2

image

1. 準(zhǔn)備基礎(chǔ)備份的數(shù)據(jù)

   shell> xtrabackup --prepare --apply-log-only \
   --target-dir=/backups/base
   ...省略...
   xtrabackup: starting shutdown with innodb_fast_shutdown = 1
   InnoDB: Starting shutdown...
   InnoDB: Shutdown completed; log sequence number 13596441
   InnoDB: Number of pools: 1
   180818 11:56:55 completed OK!
   
   

注意:
即使已跳過(guò)回滾階段姨蝴，此備份實(shí)際上也可以按原樣恢復(fù)。如果你恢復(fù)它并啟動(dòng)MySQL肺缕，InnoDB將檢測(cè)到?jīng)]有執(zhí)行回滾階段左医，它將在后臺(tái)執(zhí)行，因?yàn)樗ǔＳ糜趩?dòng)時(shí)的崩潰恢復(fù)同木。它會(huì)通知您數(shù)據(jù)庫(kù)未正常關(guān)閉浮梢。

2. 把第一次增量備份的數(shù)據(jù)合并到基礎(chǔ)備份的數(shù)據(jù)中

shell> xtrabackup  --prepare  --apply-log-only  --user=root --password=123  --target-dir=/backups/base  --incremental-dir=/backups/inc1

3. 再把第二次增量備份的數(shù)據(jù)也合并到基礎(chǔ)備份的數(shù)據(jù)中

shell> xtrabackup --prepare --user=root --password=123  --target-dir=/backups/base  --incremental-dir=/backups/inc2

==注意： 最后一次操作不需要加 --apply-log-only 參數(shù)==

4. 停止 MySQL 服務(wù)，并刪除數(shù)據(jù)目錄和日志

shell> systemctl stop mysqld
shell> rm -rf /var/lib/mysql/*

5. 開始恢復(fù)合并后的全部數(shù)據(jù)的數(shù)據(jù)庫(kù)

shell> xtrabackup --copy-back  --datadir=/var/lib/mysql  --target-dir=/backups/base/

6. 更改數(shù)據(jù)庫(kù)目錄的權(quán)限并啟動(dòng)數(shù)據(jù)庫(kù)

shell> chown mysql.mysql -R /var/lib/mysql
shell> systemctl start mysqld

關(guān)于加密備份彤路、壓縮備份秕硝、部分備份

備份的擴(kuò)展功能，自修
點(diǎn)我上車