普通文件方式
簡單數據的導入和導出
編輯配置文件my.cnf添加如下配置項
[mysqld]
secure_file_priv = 保存到本地目錄
例如
secure_file_priv = /tmp
配置完成后需要重啟服務
本地目錄需要給mysql用戶授予讀寫權限
登錄到服務器，可以驗證配置項是否生效
SELECT@@GLOBAL.secure_file_priv;或者SHOWVARIABLESLIKE"secure_file_priv";
假如沒有使用此配置項，查詢到的值為NULL。
mysql> SELECT @@GLOBAL.secure_file_priv;+---------------------------+| @@GLOBAL.secure_file_priv |+---------------------------+| NULL? ? ? ? ? ? ? ? ? ? ? |+---------------------------+1rowinset (0.00sec)
執(zhí)行下面的導入導出語句時就會報如下錯誤
ERROR1290(HY000): The MySQL server is runningwiththe --secure-file-priv option so it cannot executethisstatement
導出
select *fromschool.classinto outfile"/tmp/class2.db";
導入
導入到數據文件中的列必須和表中的一一對應。包含id列的值。
實例文件
5,云計算1809
6,云計算1901
7,云計算1902
導入語句
載入外部“形式整齊”的數據(csv 格式的文件，沒有域之家用逗號隔開)： load data infile'文件完整名（含路徑）'into table 表名fields terminated by'域分隔符'optionally? ? enclosed? by'"'ignore 1 lines;
optionally? ? enclosed? by '"' 用于識別域值的界定符
ignore 1 lines;? 忽略第一行
MySQL 邏輯備份 mysqldump
邏輯備份特點
備份的是建表、建庫、插入等操作所執(zhí)行SQL語句（DDL DML DCL），適用于中小型數據庫。
效率相對較低
在日常工作中，我們會使用 mysqldump 命令創(chuàng)建SQL格式的轉儲文件來備份數據庫?；蛘呶覀儼褦祿С龊笞鰯祿w移，主從復制等操作。mysqldump是一個邏輯備份工具,復制原始的數據庫對象定義和表數據產生一組可執(zhí)行的SQL語句。 默認情況下,生成insert語句，也能生成其它分隔符的輸出或XML格式的文件。
特點
自動記錄position位置。
show master? status\G;
可用性，一致性
鎖表機制
用法
mysqldump-h服務器-u用戶名-p密碼? 數據庫名? > 備份文件.sql/*查看幫助*/mysqldump--help
日常用法
備份所有庫
// 先配置用戶名和密碼shell> vi ~/.mysql_user[mysqldump]user=rootpassword=123shell> mysqldump --defaults-file=~/.mysql_user -h172.16.153.10 --all-databases > `date +%FT%H_%M_%S`dump_all.sql# 不包含 INFORMATION_SCHEMA,performance_schema,sys
備份指定的多個庫
// 為了考慮篇幅，請自行添加指定用戶名密碼參數和指定服務器的參數
// --defaults-file=~/.mysql_user -hip
shell> mysqldump --databases db1 db2 db3 > `date +%FT%H_%M_%S`dump_all.sql
備份指定庫的指定幾個表
shell>mysqldumpdb1t1t3t7>dump.sql
其他參數
--master-data=0|1|2
服務器的二進制日志必須打開
0? ? 不記錄二進制日志文件及位置：
1? ? 以CHANGE MASTER TO 的方式記錄位置，可用于恢復后直接啟動從服務器：
2? 以CHANGE MASTER TO 的方式記錄位置，但默認被注釋：
--dump-slave 用于在slave上dump數據，建立新的slave。因為我們在使用mysqldump時會鎖表，所以大多數情況下，我們的導出操作一般會在只讀備庫上做，為了獲取主庫的Relay_Master_Log_File(二進制日志)和Exec_Master_Log_Pos(主服務器二進制日志中數據所處的位置)，需要用到這個參數，不過這個參數只有在5.7以后的才會有
--no-data, -d 不導出任何數據，只導出數據庫表結構
--lock-all-tables:鎖定所有表 對MyISAM引擎的表開始備份前，先鎖定所有表。
優(yōu)勢
mysqldump的優(yōu)勢：
可以查看或者編輯十分方便，它也可以靈活性的恢復之前的數據。
不關心底層的存儲引擎，既適用于支持事務的，也適用于不支持事務的表。
不過它不能作為一個快速備份大量的數據或可伸縮的解決方案。如果數據庫過大,即使備份步驟需要的時間不算太久,但有可能恢復數據的速度也會非常慢,因為它涉及的SQL語句插入磁盤I/O,創(chuàng)建索引等等。 對于大規(guī)模的備份和恢復,更合適的做法是物理備份,復制其原始格式的數據文件,可以快速恢復。
恢復
shell>mysql
或者，在mysql中，使用source命令：
mysql>sourcedump.sql
如果文件是不包含CREATE DATABASE和USE語句的單數據庫轉儲 ，請首先創(chuàng)建數據庫（如有必要）：
shell> mysqladmin create db1
然后在加載轉儲文件時指定數據庫名稱：
shell>mysqldb1
或者，在mysql中創(chuàng)建數據庫，將其選為默認數據庫，然后加載轉儲文件：
mysql> CREATE DATABASEIFNOT EXISTS db1;mysql>USEdb1;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的熱備份的開源實用程序，它可以備份5.1到5.7版本上InnoDB,XtraDB,MyISAM存儲引擎的表， Xtrabackup有兩個主要的工具：xtrabackup、innobackupex 。
第一種安裝方式： 使用YUM方式安裝
地址
https://www.percona.com/downloads/Percona-XtraBackup-LATEST/
確保安裝 EPEL 源
yum installhttps://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
安裝libev
為了成功安裝Percona XtraBackup libev包需要先安裝。
yum? install -y libev
安裝Percona存儲庫
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
測試存儲庫的可用性
shell> yumlist| grep percona
應該輸出如下信息
...percona-xtrabackup-20.x86_642.0.8-587.rhel5percona-release-x86_64percona-xtrabackup-20-debuginfo.x86_642.0.8-587.rhel5percona-release-x86_64percona-xtrabackup-20-test.x86_642.0.8-587.rhel5percona-release-x86_64percona-xtrabackup-21.x86_642.1.9-746.rhel5percona-release-x86_64percona-xtrabackup-21-debuginfo.x86_642.1.9-746.rhel5percona-release-x86_64percona-xtrabackup-22.x86_642.2.13-1.el5percona-release-x86_64percona-xtrabackup-22-debuginfo.x86_642.2.13-1.el5percona-release-x86_64percona-xtrabackup-debuginfo.x86_642.3.5-1.el5percona-release-x86_64percona-xtrabackup-test.x86_642.3.5-1.el5percona-release-x86_64percona-xtrabackup-test-21.x86_642.1.9-746.rhel5percona-release-x86_64percona-xtrabackup-test-22.x86_642.2.13-1.el5percona-release-x86_64...
安裝軟件
shell> yum install percona-xtrabackup-24
驗證安裝
image.png
第二種安裝方式：下載對應版本的軟件包，在本地安裝
點擊下載頁面，選擇對應版本后進行下載
image.png
示例：
下載2.4.4版本
wgethttps://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
安裝
yumlocalinstallpercona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
注意：像這樣手動安裝軟件包時，您需要確保解決所有依賴項并自行安裝缺少的軟件包。
卸載
yum remove percona-xtrabackup
80 版本
注意：
這個版本只支持 MySQL8.0的數據進行備份，不支持 MySQL8.0版本之前的數據進行備份。
# 安裝倉庫文件yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm# 啟用倉庫percona-releaseenable-only tools release#安裝軟件yum install percona-xtrabackup-80
報錯解決
來自file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona 的無效 GPG 密鑰：No key foundingiven key data
或者
源"CentOS 7 - Percona"的 GPG 密鑰已安裝，但是不適用于此軟件包。請檢查源的公鑰 URL 是否配置正確。 失敗的軟件包是：Percona-Server-shared-56-5.6.43-rel84.3.el7.x86_64 GPG? 密鑰配置為：file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona
修改倉庫文件percona-release.repo不使用密鑰認證
image.png
再次安裝會看到如下報錯信息
Transaction check error:? file /etc/my.cnffrominstallofPercona-Server-shared-56-5.6.43-rel84.3.el7.x86_64 conflictswithfilefrompackage mysql-community-server-5.7.25-1.el7.x86_64錯誤概要-------------
需要安裝如下軟件
yum install -y? mysql-community-libs-compat
注意：
這個軟件的源是mysql57-community
[mysql57-community]name=MySQL5.7Community Serverbaseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/enabled=1gpgcheck=1gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql文件內容如下：
再執(zhí)行安裝命令
yum install percona-xtrabackup-24
日常操作
條件：
在 MySQL 服務器本地安裝 Xtrbackup 并執(zhí)行相關操作。
給執(zhí)行備份到用戶進行相應的授權。
配置選項
配置選項可以在sh命令行中直接使用，也可以在my.cnf文件中配置
# my.cnf 文件的配置[xtrabackup]target_dir = /backups/mysql/# 備份數據放置的位置
假如是編譯安裝的 mysql ，需要在配置文件my.cnf中指定 socket 文件的路徑。
[xtrabackup]socket =/tmp/mysql.sock
1. 全備
下面的命令均假設沒有在my.cnf中配置任何關于 xtrabackup 的選項
要執(zhí)行備份需要指定 備份數據放置的位置，就是目錄，假如目錄不存在，則會自動創(chuàng)建；==注意這個目錄不會被遞歸創(chuàng)建，僅僅會創(chuàng)建最后一級目錄；==假如存在，就會直接開始備份，并且不會覆蓋原來的數據。
開始備份
shell> xtrabackup --backup --user=root --password='123'--target-dir=/backups/full# 備份完成后，可以看到備份時的LSN號，當下次進行增量備份時，xtrabackup就只備份大于此號的page即可。
查看備份文件
[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 ibdata1drwxr-x--- 2 root root 4.0K 8月? 18 09:44 mysqldrwxr-x--- 2 root root? 88 8月? 18 09:44 one_dbdrwxr-x--- 2 root root 8.0K 8月? 18 09:44 performance_schemadrwxr-x--- 2 root root? 58 8月? 18 09:44 shark_dbdrwxr-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
進入目錄后，可以看到一些目錄，這些目錄與我們數據庫的名稱相同，沒錯，這些就是各個數據庫的數據文件備份目錄。
還有一個innodb的共享表空間文件，ibdata1，注意，如果想要使用xtrabackup備份眾多數據庫中的某一個，那么必須保證在創(chuàng)建這個數據庫時，已經開啟了innodb_file_per_table參數，否則將無法單獨備份數據庫服務器中的某一個數據庫。
除了剛才描述的這些數據文件，xtrabackup還為我們生成了一些文件，我們來看看這些文件都有什么用（不同版本的xtrabackup生成的文件可能不同）。
backup-my.cnf
此文件中包含了my.cnf中的一些設置信息，但是，并不是my.cnf中的所有信息都會包含在此文件中，此文件中只包含了備份時需要的信息。
xtrabackup_binlog_info
需要開啟二進制日志
此文件中記錄了備份開始時二進制日志文件的"位置(position)"
xtrabackup_checkpoints
此文件中記錄此次備份屬于那種類型的備份，是全量還是增量，備份時起始的LSN號碼，結束的LSN號碼等信息。
xtrabackup_info
本次備份的概要信息，此文件中的信息還是比較全面的。
xtrabackup_logfile
記錄了備份過程中的日志，在對數據進行prepare時需要通過日志將數據還原成一致的可用的數據。
準備恢復的數據
使用 xtrabackup --backup 選項進行備份后，并不能直接使用，首先需要準備它以便還原它。
如果您嘗試使用這些數據文件啟動InnoDB，它將檢測損壞并自行崩潰，以防止您在損壞的數據上運行。
因為備份出的數據是不一致的，我們需要將同時備份出的事務日志應用到備份中，才能得到一份完整、一致、可用的數據，xtrabackup稱這一步操作為prepare，直譯過來就是"準備"。
xtrabackup --prepare 步驟使文件在一個時刻完全一致
shell> xtrabackup --prepare --target-dir=/backups/full
如果你要備份的數據量巨大，那么備份時長會變長，期間備份的事務日志容量有可能會很大。那么，我們可以使用--use-memory選項，加速準備工作的完成，在不指定內存大小的情況下，準備工作默認會占用100MB的內存，如果服務器有一定的空閑內存，那么我們可以讓xtrabackup使用指定大小的內存完成準備工作，以提升準備工作完成的速度，示例語句如下。
shell> xtrabackup --prepare --use-memory=512M --target-dir=/backups/full
==準備備份時不建議中斷xtrabackup進程，因為這可能會導致數據文件損壞，備份將無法使用。如果準備過程中斷，則無法保證備份有效性。==
準備備份數據完成后，應該會看到如下信息。
InnoDB: Starting shutdown...InnoDB: Shutdown completed;logsequence number 13596200180818 10:09:19 completed OK!
恢復
xtrabackup 在執(zhí)行copyback時會讀取數據庫的my.cnf中的配置，但是如果my.cnf中沒有配置datadir，那么--datadir選項必須存在，而且，datadir目錄必須為空目錄，其中不能存在數據，否則在執(zhí)行上述命令時會報錯，--copy-back選項對應的目錄就是我們準備好的可用數據的目錄。
為了能夠正常的恢復數據，我們先確定數據庫服務已經停止了，而且對應的數據目錄中不存在數據，然后進行數據還原工作，刪除數據目錄中的文件與日志。
停止數據庫的服務
清理環(huán)境
修改權限
啟動數據庫
shell> systemctl stop mysqld.serviceshell> rm -rf /var/lib/mysql/*shell> xtrabackup --copy-back --datadir=/var/lib/mysql --target-dir=/backups/full# 下面為完成后的輸出結果180818 10:59:25 [01]? ? ? ? ...done180818 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
啟動數據庫
shell> systemctl start mysqld.service
innobackuper 命令實現
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
全量備份思路總結
執(zhí)行備份命令
指定 數據庫的用戶名和密碼
指定 備份目錄，注意只可以自動創(chuàng)建最后一級的目錄
準備備份的數據
就是指： --prepare 參數, 保證數據的統(tǒng)一且完整性
停服務，并且把 mysql 的數據目錄下的所有文件和文件夾清除。
/var/lib/mysql/此目錄必須是空的
恢復數據
本質上就是拷貝備份的文件到指定的 mysql 數據目錄下
修改 mysql 數據目錄的屬主和屬組為 MySQL 服務器進程啟動的用戶，默認是 mysql
啟動服務
2. 增量備份
image.png
特點：每次備份，都對自上一次備份（注意是上一次，不是第一次）到此時備份之間有變化的文件，進行備份。所 以備份體積小，備份速度快，但是恢復的時候，需要按備份時間順序，逐個備份版本進行恢復，恢復時持續(xù)的時間長。
無論xtrabackup和innobackupex工具支持增量備份，這意味著它們可以只復制自上次備份以來發(fā)生變化的數據。
您可以在每個完整備份之間執(zhí)行許多增量備份，因此您可以設置備份過程，例如每周一次完整備份和每天增量備份，或每天完整備份和每小時增量備份。
增量備份有效，因為每個InnoDB頁面都包含一個日志序列號或LSN。該LSN是整個數據庫系統(tǒng)的版本號。每個頁面的LSN顯示它最近的更改。
當我們做過全量備份以后會在目錄下產生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
增量備份實際上并不將數據文件與先前備份的數據文件進行比較。事實上，如果你知道它的LSN，你可以使用xtrabackup --incremental-lsn來執(zhí)行增量備份，而不需要先前的備份。增量備份只是讀取頁面并將其LSN與最后一個備份的LSN進行比較。但是，您仍需要完整備份來恢復增量更改;如果沒有完整備份作為基礎，增量備份將毫無用處。
創(chuàng)建增量備份
要進行增量備份，請像往常一樣以完整備份開始, 使用下面的命令創(chuàng)建基礎的全量備份。
shell> xtrabackup --backup --user=root --password=123 --target-dir=/backups/base
現在您已擁有完整備份，以后可以根據它進行增量備份。
向數據庫中添加數據，以便于測試
mysql> select count(id) from shark_db.student;+-----------+| count(id) |+-----------+|? ? 99213 |+-----------+1rowinset (0.04sec)mysql> insert into shark_db.student (name,age,phone) values('xiguatian',20,13149876789);Query OK,1row affected (0.00sec)mysql> select count(id) from shark_db.student;+-----------+| count(id) |+-----------+|? ? 99214 |+-----------+1rowinset (0.03sec)
使用以下命令進行增量備份：
shell> xtrabackup --backup --user=root --password=123 --target-dir=/backups/inc1 --incremental-basedir=/backups/base
該/data/backups/inc1/目錄現在應包含增量文件
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
這個時候去查看增量備份的xtrabackup_checkpoints,會發(fā)現同樣也記錄了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，對于增量，它必須與前一個/基本備份的to_lsn（如果它是最后一個檢查點）相同。
上面的情況是，to_lsn（上一個檢查點LSN）和last_lsn（上次復制的LSN）之間存在差異，這意味著在備份過程中服務器上存在一些流量
模擬增加數據
mysql> insert into shark_db.student (name,age,phone) values('xiguatian3',21,13149876789);Query OK,1row affected (0.01sec)mysql> select count(id) from shark_db.student;? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? +-----------+| count(id) |+-----------+|? ? 99215 |+-----------+1rowinset (0.03sec)
現在可以使用此目錄作為另一個增量備份的基礎：
shell> xtrabackup --backup --user=root --password=123 --target-dir=/backups/inc2 --incremental-basedir=/backups/inc1
準備增量備份的數據
增量備份的步驟與完全備份的步驟不同。在完全備份中，執(zhí)行兩種類型的操作以使數據庫保持一致：從日志文件中針對數據文件重播已提交的事務，并回滾未提交的事務。在準備增量備份時，必須跳過未提交事務的回滾，因為備份時未提交的事務可能正在進行中，并且很可能它們將在下一次增量備份中提交。您應該使用該 選項來阻止回滾階段.
xtrabackup --apply-log-only
警告
如果不使用該 選項來阻止回滾階段，那么增量備份將毫無用處?；貪L事務后，無法應用進一步的增量備份。
要準備數據，需要從一開始就準備，現在回想一下我們都有那些備份
/backups/base
/backups/inc1
/backups/inc2
image.png
準備基礎備份的數據
shell> xtrabackup --prepare --apply-log-only \--target-dir=/backups/base...省略...xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: Starting shutdown...InnoDB: Shutdown completed;logsequence number 13596441InnoDB: Number of pools: 1180818 11:56:55 completed OK!
注意:
即使已跳過回滾階段，此備份實際上也可以按原樣恢復。如果你恢復它并啟動MySQL，InnoDB將檢測到沒有執(zhí)行回滾階段，它將在后臺執(zhí)行，因為它通常用于啟動時的崩潰恢復。它會通知您數據庫未正常關閉。
把第一次增量備份的數據合并到基礎備份的數據中
shell> xtrabackup? --prepare? --apply-log-only? --user=root --password=123? --target-dir=/backups/base? --incremental-dir=/backups/inc1
再把第二次增量備份的數據也合并到基礎備份的數據中
shell> xtrabackup --prepare --user=root --password=123? --target-dir=/backups/base? --incremental-dir=/backups/inc2
==注意： 最后一次操作不需要加--apply-log-only參數==
停止 MySQL 服務，并刪除數據目錄和日志
shell> systemctl stop mysqld
shell> rm -rf /var/lib/mysql/*
開始恢復合并后的全部數據的數據庫
shell> xtrabackup --copy-back? --datadir=/var/lib/mysql? --target-dir=/backups/base/
更改數據庫目錄的權限并啟動數據庫
shell> chown mysql.mysql -R /var/lib/mysql
shell> systemctl start mysqld
關于加密備份、壓縮備份、部分備份
作者：xiguatian
鏈接：http://www.itdecent.cn/p/169f96c709b1
來源：簡書
簡書著作權歸作者所有，任何形式的轉載都請聯系作者獲得授權并注明出處。
