MySQL 備份

老師筆記

MySQL 備份精編版
老師筆記

方法 解決無法進行邏輯備份（mysqldump），提示索引用戶為免密登錄的報錯

原理圖

MySQL 邏輯備份 mysqldump

備份的是建表、建庫、插入等操作所執(zhí)行SQL語句（DDL DML DCL），適用于中小型數(shù)據(jù)庫。
效率相對較低
mysqldump的優(yōu)勢：
可以查看或者編輯十分方便，它也可以靈活性的恢復(fù)之前的數(shù)據(jù)。
不關(guān)心底層的存儲引擎，既適用于支持事務(wù)的，也適用于不支持事務(wù)的表。
不過它不能作為一個快速備份大量的數(shù)據(jù)或可伸縮的解決方案。如果數(shù)據(jù)庫過大,即使備份步驟需要的時間不算太久,但有可能恢復(fù)數(shù)據(jù)的速度也會非常慢,因為它涉及的SQL語句插入磁盤I/O,創(chuàng)建索引等等。 對于大規(guī)模的備份和恢復(fù),更合適的做法是物理備份,復(fù)制其原始格式的數(shù)據(jù)文件,可以快速恢復(fù)

用法

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

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

常見參數(shù)

--single-transaction備份前啟用一個事務(wù)，保證數(shù)據(jù)一致性。
僅對 InnoDB 存儲引擎有效。還有需要保證沒有其他的連接正在使用以下語句： ALTER TABLE， CREATE TABLE，DROP TABLE， RENAME TABLE， TRUNCATE TABLE
-l, --lock tables對于不支持事務(wù)的存儲引擎的表備份使用此選項，比如 MySAM, 可以保證備份期間的數(shù)據(jù)一致性。會依次對正在備份的每個數(shù)據(jù)庫中的所有表進行鎖表操作，此時只可以讀。和 --single-transaction 互斥。
-x, --lock-all-tables鎖定所有數(shù)據(jù)庫中的所有表。這是通過在整個轉(zhuǎn)儲期間采用全局讀鎖來實現(xiàn)。

mysqldump --databases db1 db2 db3 > `date +%FT%H_%M_%S`dump_all.sql
                               備份數(shù)據(jù)庫
mysqldump db1 t1 t3 t7 > dump.sql            備份指定庫的指定幾個表


 
mysql -uroot -p123456   db1  < dump.sql          恢復(fù)數(shù)據(jù)庫

source dump.sql          在mysql中恢復(fù)數(shù)據(jù)庫

有些表需要先創(chuàng)一個數(shù)據(jù)庫才能恢復(fù)進去    

mysql -uroot -p'QFedu123!' -e "create database servers" < dum.mem

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

Percona XtraBackup是一款基于MySQL的熱備份的開源實用程序，它可以備份5.1到5.7版本上InnoDB,XtraDB,MyISAM存儲引擎的表， Xtrabackup有兩個主要的工具：xtrabackup、innobackupe

確保安裝 EPEL 源
yum install https://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> yum list | grep percona


安裝軟件
shell> yum install percona-xtrabackup-24

驗證安裝
rpm -qa | grep percona

物理備份數(shù)據(jù)庫以及還原步驟

1、將所有庫進行物理備份

 xtrabackup --backup --user=root --password='QFedu123!'  --target-dir=/backup/full   (mkdir -p /backup/full)

xtrabackup_logfile  記錄操作過程的日志，事件

2、停止數(shù)據(jù)庫服務(wù)

systemctl stop mysqld

3、清理環(huán)境

rm -rf /var/lib/mysql/*

4、將原來備份好的物理數(shù)據(jù)進行復(fù)原

xtrabackup --copy-back --datadir=/var/lib/mysql --target-dir=/backup/full

5、修改屬主權(quán)限

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

6、啟動數(shù)據(jù)庫查看是否還原

systemctl start mysqld

增量備份

image

1、創(chuàng)建增量備份
要進行增量備份，請像往常一樣以完整備份開始, 使用下面的命令創(chuàng)建基礎(chǔ)的全量備份。

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

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

2、使用以下命令進行增量備份：

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

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

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

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

增量備份的步驟與完全備份的步驟不同。在完全備份中，執(zhí)行兩種類型的操作以使數(shù)據(jù)庫保持一致：從日志文件中針對數(shù)據(jù)文件重播已提交的事務(wù)，并回滾未提交的事務(wù)。在準(zhǔn)備增量備份時，必須跳過未提交事務(wù)的回滾，因為備份時未提交的事務(wù)可能正在進行中，并且很可能它們將在下一次增量備份中提交。您應(yīng)該使用該 選項來阻止回滾階段。

xtrabackup --apply-log-only

警告
如果不使用該 選項來阻止回滾階段，那么增量備份將毫無用處?；貪L事務(wù)后，無法應(yīng)用進一步的增量備份。

要準(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
                           這個參數(shù)阻止回滾操作
...省略...
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!

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

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

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

xtrabackup --prepare --user=root --password=123  --target-dir=/backups/base  --incremental-dir=/backups/inc2
注意： 最后一次操作不需要加 --apply-log-only 參數(shù)

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

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

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

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

6、更改數(shù)據(jù)庫目錄的權(quán)限并啟動數(shù)據(jù)庫

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