mysql資料備份還原

標籤：函數   percona   SQ   儲存   soc   com   cal   鎖定   default   

mysqldump

備份工具mysqldump，建議使用在10g以下的資料進行備份，是mysql資料庫的邏輯備份。
MyISAM儲存引擎：支援溫備，備份時要鎖定表；
InnoDB儲存引擎：支援溫備和熱備；
參數說明

-x, --lock-all-tables：鎖定所有庫的所有表，讀鎖；
-l, --lock-tables：鎖定指定庫所有表；
--single-transaction：建立一個事務，基於此快照執行備份； 只範圍innodb做熱備
-R, --routines：預存程序和儲存函數；
--triggers 觸發器
-E, --events 事件
--flush-logs：鎖定表完成後，即進行日誌重新整理
--master-data[=#]
|----1：記錄為CHANGE MASTER TO語句，此語句不被注釋；
|---- 2：記錄為CHANGE MASTER TO語句，此語句被注釋； 用來記錄

一次完整的備份還原
1、備份

[[email protected] data]# mysqldump  -uroot -hlocalhost -p -l -R --triggers -E --master-data=2 --flush-logs  -B  hellodb > hellodb-fullback-$(date +%F).sql

2、查看二進位日誌

還原時候：--flush-logs,這裡只要查看從哪個檔案開始就可以了，每次滾動後都是從154開始，前面還有4，123兩個操作，4做復原，123是個空命令
CHANGE MASTER TO MASTER_LOG_FILE=‘master-bin.000023‘, MASTER_LOG_POS=154;

3、匯出二進位日誌

[[email protected] data]# cp /www/data/mysql/log-bin/master-bin.00002{3,4} ./[[email protected] data]# mysqlbinlog  master-bin.000023 >tmp.sql   #如有有必要，參考mysqlbinlog[[email protected] data]# mysqlbinlog  master-bin.000024 >>tmp.sql

4、還原資料

mysql> set @@session.sql_log_bin=OFF;mysql> source /root/data/hellodb-fullback-2018-06-20.sqlmysql> source /root/data/tmp.sqlmysql> set @@session.sql_log_bin=ON;

Xtrabackup

Xtrabackup 是由percona提供的mysqlDatabase Backup工具，是一個開源的工具，能夠對innodb和xtradb資料庫進行熱備和增量備份，對於MyISAM， 僅支援到溫備，對MyISAM使用增量備份時，其實是完全備份。Xtrabackup使用簡單，功能強大。Xtrabackup具備的特點：(1)備份過 程快速、可靠；(2)備份過程不會打斷正在執行的事務；(3)能夠基於壓縮等功能節約磁碟空間和流量；(4)自動實現備份檢驗；(5)還原速度快；

安裝

[[email protected] ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.11/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.11-1.el7.x86_64.rpm[[email protected] ~]# yum install percona-xtrabackup-24-2.4.11-1.el7.x86_64.rpm

全量備份（各種引擎都支援）

1、全量備份

[[email protected] data]# innobackupex --defaults-file=/www/server/mysql/my.cnf --user=root  --password=111111 -Hlocalhost   --socket=/tmp/mysql.sock   ./

2、備份資料查看

#使用者增量合并資訊[[email protected] 2018-06-20_14-11-46]# cat xtrabackup_checkpointsbackup_type = full-backupedfrom_lsn = 0to_lsn = 886153284last_lsn = 886153293compact = 0recover_binlog_info = 0#可以用於還原位元據[[email protected] 2018-06-20_14-11-46]# cat xtrabackup_binlog_infomaster-bin.000028   10232

3、準備資料，復原未提交資料 (在另外機器上做還原)

[[email protected] data]# innobackupex --apply-log 2018-06-20_14-11-46/

4、還原資料流程大概就是將 備份資料複製到mysql資料目錄下

[[email protected] data]# rm -fr /www/data/mysql/data/*   #注意盡量不要刪二進位日誌#把準備的好的資料複製到mysql資料目錄下[[email protected] data]# innobackupex --defaults-file=/www/server/mysql/my.cnf --copy-back  2018-06-20_14-11-46/[[email protected] data]# chown -R mysql.mysql /www/data/mysql/data/

增量備份（只作用innodb表）

備份過程
全量 -> 增量1（incremental-basedir=全量） -> 增量2（incremental-basedir=增量1） -> 增量3（incremental-basedir=增量2）

1、在上述的全量備份上，建立一個增量

[[email protected] data]# innobackupex --defaults-file=/www/server/mysql/my.cnf --user=root  --password=111111 -Hlocalhost   --socket=/tmp/mysql.sock --incremental  ./    --incremental-basedir=2018-06-20_14-11-46/

2、查看最後一個增量備份的二進位日誌最後位置

[[email protected] data]# cat 2018-06-20_15-05-08/xtrabackup_binlog_infomaster-bin.000028   11092

3、匯出最後一個備份後面所有的位元據（可能是多個）

[[email protected] log-bin]# mysqlbinlog -j 11092 master-bin.000028 -vv --base64-output=decode-rows[[email protected] log-bin]#  mysqlbinlog -j 11092 master-bin.000028 >/root/data/bin.log

4、合并增量

增量合并過程：以全量為根本，按順序，分次將增量合并到全量上。
合并過程中的交易處理，--redo-only表示合并，不做復原，最後一次記得不要添加，讓其復原那些沒提交的事務

[[email protected] data]# innobackupex --apply-log --redo-only 2018-06-20_14-11-46/

最後一次合并（不做復原，如果不是最後，請添加--redo-only）

[[email protected] data]# innobackupex --apply-log  2018-06-20_14-11-46 --incremental-dir=2018-06-20_15-05-08/

5、增量合并完畢，第一份全量就是真正的全量量（複製資料到mysql資料目錄下）

[[email protected] data]# innobackupex --defaults-file=/www/server/mysql/my.cnf --copy-back 2018-06-20_14-11-46/

6、最後執行二進位日誌，增量未執行的部分

mysql> set @@session.sql_log_bin=OFF;mysql> source /root/data/bin.log;mysql> set @@session.sql_log_bin=ON;

mysql資料備份還原