MySQL備份還原

標籤：操作   inf   最大   mysql備份還原   line   可靠   優點   size   ali   

mysql備份還原1.1 備份恢複-說明

營運工作的核心簡單概括就兩件事：

第一個:是保護公司的資料.第二個:是讓網站能7*24小時提供服務(使用者體驗)。

1.1.1 備份簡介

邏輯備份（文本表示：SQL 陳述式）物理備份（資料檔案的二進位副本）基於快照的備份基於複製的備份增量備份（重新整理二進位日誌）

1.2 mysqldump備份工具詳解

mysql原生內建很好用的邏輯備份工具

1.2.1 mysqldump常用參數

備份出來的xxx.sql檔案，裡面是建立與更新的SQL語句。

-A ; --all-databases 全庫備份參數-B  ：建議加上此參數，可接一個或多個庫（表）
注意：?    A與B最好不要同時使用。?    -B會在備份.sql中自動增加create和use語句，若不-B需要進入mysql建立庫和use庫。-d 僅表結構-t 僅資料--compact 減少無用資料輸出(調試)-R, --routines備份預存程序和函數資料--triggers備份觸發器資料冗餘的兩個參數-l  單表備份時使用-x  在做全庫備份時使用--master-data={1|2} 告訴你備份後時刻的binlog位置。2 注釋    1 非注釋,要執行(主從複製)--single-transaction  只支援innodb引擎，支援熱備。會單獨開啟一個事務進行備份，利用事務的快照技術實現的。

 

1.2.2 邏輯備份-SQL

#匯出單個資料庫 （建議加B）[[email protected] data]# mysqldump -uroot -p123456 -B world >/tmp/world.sql#匯出多個資料庫 （必須加B）[[email protected] data]# mysqldump -uroot -p123456 -B world mysql >111.sql#匯出單個資料庫表 （不能加B）[[email protected] data]# mysqldump -uroot -p123456 world city >111.sql#匯出所有資料庫 （-A或--all-databases）[[email protected] data]# mysqldump -u root -p123456 -A >all.sql# 備份-常規參數 （binlog位置、熱備-快照）[[email protected] data]# mysqldump -A -R --triggers --master-data=2 --single-transaction  |gzip >/opt/alL_$(date +%F).sql.gz

 

1.2.3 增量備份-binlog

     增量資料就是指上一次全量備份資料之後到下一次全備之前資料庫所更新的資料

對於mysqldump, binlog就是增量資料.

增量備份實現

# 準備工作開啟二進位日誌vi /etc/my.cnflog-bin=/data/mysql/mysql-bin  #在全域開啟binlogset sql_log_bin=0   #在會話層級修改為臨時關閉# 全備份mysqldump -B --master-data=2 --single-transaction oldboy|gzip>/data/backup/oldboy_$(date +%F).sql.gz# 增量備份 （將之後的binlog->.sql檔案）zcat oldboy_2017-06-22.sql.gz >oldboy_2017-06-22.sqlsed -n ‘22p‘ oldboy_2017-06-22.sql    #查看號# binlog->.sql檔案mysqlbinlog -d oldboy --start-position=339 oldboy-bin.000008 -r bin.sql 

 

1.2.4 還原（恢複）

法一：source  （推薦）# 加+B選項mysql> source /tmp/world.sql# 不加+B選項[[email protected] ~]# mysql -uroot -p66666mysql> create database xzy;mysql> use xzymysql> source /tmp/world.sql法二：命令列 <mysql> create database xzy;[[email protected] ~]# mysql -uroot -p666666 xzy < /tmp/world.sql

 

1.3 Xtrabackup 備份工具詳解1.3.1 xtrabackup簡介

xtrabackup是Percona公司CTO Vadim參與開發的一款基於InnoDB具有開源，免費，支援線上熱備，備份恢複速度快，佔用磁碟空間小等特點

 

xtrabackup熱備原理（保證資料一致性）-innodb引擎

     在資料還有修改操作的時刻，直接將資料檔案中的資料頁備份，並將備份過程中的redo和undo一併備走（復原或前滾）。恢複時，只要保證備份出來的資料頁LSN能和redo LSN匹配，達到資料的一致。

對於myisam表，實現自動鎖表拷貝檔案。

1.3.2 mysqldump與xtrabackup特點對比

mysqldump不足：

mysqldump備份方式是採用的邏輯備份，其最大的缺陷是備份和恢複速度較慢，如果資料庫大於50G，mysqldump備份就不太適合。

Xtrabackup優點

 1）備份速度快，物理備份可靠

 2）備份過程不會打斷正在執行的事務（無需鎖表）

 3）能夠基於壓縮等功能節約磁碟空間和流量

 4）自動備份校正

 5）還原速度快

 6）可以流傳將備份傳輸到另外一台機器上

 

1.3.3 安裝

# 安裝依賴包yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL# 下載安裝包wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpmyum localinstall percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

 

1.3.4 xtrabackup 備份恢複

1、備份#  首次-全量備份innobackupex --defaults-file=/etc/my.cnf --user=root --password=oldboy123 --socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp /server/backup/base_full# 第一次增量備份-（基於第一次全量）innobackupex --defaults-file=/etc/my.cnf --user=root --password=oldboy123 --socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp --incremental-basedir=/server/backup/base_full --incremental /server/backup/one_inc# 第二次增量備份，（基於第一次增量）innobackupex --defaults-file=/etc/my.cnf --user=root --password=oldboy123 --socket=/application/mysql-5.6.34/tmp/mysql.sock --no-timestamp --parallel=3 --incremental-basedir=/server/backup/one_inc/ --incremental /server/backup/two_inc2、準備增量恢複：# 應用全備日誌（--apply-log，暫時不需要做復原操作--redo-only）innobackupex --apply-log --use-memory=32M --redo-only /server/backup/base_full/# 合并第一次增量到全備中innobackupex --apply-log --use-memory=32M --redo-only --incremental-dir=/server/backup/one_inc /server/backup/base_full/#合并第二次增量到全備中 （不加--redo-only）innobackupex --apply-log --use-memory=32M --incremental-dir=/server/backup/two_inc /server/backup/base_full/#innobackupex --apply-log /backup/full# 確認binlog起點cat /backup/xbackup/inc2/xtrabackup_binlog_info  mysql-bin.000001    1121# 截取到drop操作之前的binlogmysqlbinlog  --start-position=1121 /tmp/mysql-bin.000003 找到drop之前的event和postion號做日誌截取，假如 1437mysqlbinlog  --start-position=1121 --stop-position=1437    /tmp/mysql-bin.000003 >/tmp/incbinlog.sql關閉資料庫/etc/init.d/mysqld stop刪除/移動MySQL所有資料cd /application/mysql/data/rm -rf *恢複資料恢複xtrabackup資料innobackupex --copy-back /backup/xbackup/full/chown -R mysql.mysql /application/mysql/data//etc/init.d/mysqld start恢複binlogset sql_log_bin=0source /tmp/incbinlog.sql

 

 

 

 

MySQL備份還原