mysql的備份恢復(fù)

一、mysql的備份
1、mysqldump
邏輯備份，支持溫備，熱備，使用mysqldump完全備份，binlog增量備份
mysqldump -utest -ptest -h292.168.0.204 test > database-date +'%Y-%m-%d'
1）、備份innodb引擎的數(shù)據(jù)庫時
mysqldump --single-transaction --master-data=2 --flush-log -u -p >mysql.bak
--single-transaction 啟動一個大的事務(wù)進行dump
--master-data=# 記錄change-master-to
=2時記錄為注釋，1不注釋
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=614;
--flush-logs 重新又新的二進制日志記錄
2)還原備份
在恢復(fù)數(shù)據(jù)的時候會生成新的二進制日志，如果不需要記錄需進行一下設(shè)置
set session sql_log_bin=0;
然后導(dǎo)入完全備份的數(shù)據(jù) source
然后 set session sql_log_bin=1
mysqlbinlog --start-position=# --stop-position=# binlog.00000# | mysql -u -p
二、xtarbackup
1、xtarbackup的備份
版本 innobackupex version 2.4.8 Linux (x86_64) (revision id: 97330f7)
參數(shù)說明
--compress：該選項表示壓縮innodb數(shù)據(jù)文件的備份。
--compress-threads：該選項表示并行壓縮worker線程的數(shù)量。
--compress-chunk-size：該選項表示每個壓縮線程worker buffer的大小，單位是字節(jié)，默認是64K。
--encrypt：該選項表示通過ENCRYPTION_ALGORITHM的算法加密innodb數(shù)據(jù)文件的備份，目前支持的算法有ASE128,AES192,AES256。
--encrypt-threads：該選項表示并行加密的worker線程數(shù)量。
--encrypt-chunk-size：該選項表示每個加密線程worker buffer的大小，單位是字節(jié)，默認是64K。
--encrypt-key：該選項使用合適長度加密key，因為會記錄到命令行，所以不推薦使用。
--encryption-key-file：該選項表示文件必須是一個簡單二進制或者文本文件，加密key可通過以下命令行命令生成：openssl rand -base64 24。
--include：該選項表示使用正則表達式匹配表的名字[db.tb]，要求為其指定匹配要備份的表的完整名稱，即databasename.tablename。
--user：該選項表示備份賬號。
--password：該選項表示備份的密碼。
--port：該選項表示備份數(shù)據(jù)庫的端口。
--host：該選項表示備份數(shù)據(jù)庫的地址。
--databases：該選項接受的參數(shù)為數(shù)據(jù)名，如果要指定多個數(shù)據(jù)庫，彼此間需要以空格隔開；如："xtra_test dba_test"，同時，在指定某數(shù)據(jù)庫時，也可以只指定其中的某張表。如："mydatabase.mytable"。該選項對innodb引擎表無效，還是會備份所有innodb表。此外，此選項也可以接受一個文件為參數(shù)，文件中每一行為一個要備份的對象。
--tables-file：該選項表示指定含有表列表的文件，格式為database.table，該選項直接傳給--tables-file。
--socket：該選項表示mysql.sock所在位置，以便備份進程登錄mysql。
--no-timestamp：該選項可以表示不要創(chuàng)建一個時間戳目錄來存儲備份，指定到自己想要的備份文件夾。
--ibbackup：該選項指定了使用哪個xtrabackup二進制程序。IBBACKUP-BINARY是運行percona xtrabackup的命令。這個選項適用于xtrbackup二進制不在你是搜索和工作目錄，如果指定了該選項,innoabackupex自動決定用的二進制程序。
--slave-info：該選項表示對slave進行備份的時候使用，打印出master的名字和binlog pos，同樣將這些信息以change master的命令寫入xtrabackup_slave_info文件?？梢酝ㄟ^基于這份備份啟動一個從庫。
--safe-slave-backup：該選項表示為保證一致性復(fù)制狀態(tài)，這個選項停止SQL線程并且等到show status中的slave_open_temp_tables為0的時候開始備份，如果沒有打開臨時表，bakcup會立刻開始，否則SQL線程啟動或者關(guān)閉知道沒有打開的臨時表。如果slave_open_temp_tables在--safe-slave-backup-timeount（默認300秒）秒之后不為0，從庫sql線程會在備份完成的時候重啟。
--rsync：該選項表示通過rsync工具優(yōu)化本地傳輸，當(dāng)指定這個選項，innobackupex使用rsync拷貝非Innodb文件而替換cp，當(dāng)有很多DB和表的時候會快很多，不能--stream一起使用。
--kill-long-queries-timeout：該選項表示從開始執(zhí)行FLUSH TABLES WITH READ LOCK到kill掉阻塞它的這些查詢之間等待的秒數(shù)。默認值為0，不會kill任何查詢，使用這個選項xtrabackup需要有Process和super權(quán)限。
--kill-long-query-type：該選項表示kill的類型，默認是all，可選select。
--ftwrl-wait-threshold：該選項表示檢測到長查詢，單位是秒，表示長查詢的閾值。
--ftwrl-wait-query-type：該選項表示獲得全局鎖之前允許那種查詢完成，默認是ALL，可選update。
--galera-info：該選項表示生成了包含創(chuàng)建備份時候本地節(jié)點狀態(tài)的文件xtrabackup_galera_info文件，該選項只適用于備份PXC。
--stream：該選項表示流式備份的格式，backup完成之后以指定格式到STDOUT，目前只支持tar和xbstream。
--defaults-file：該選項指定了從哪個文件讀取MySQL配置，必須放在命令行第一個選項的位置。
--defaults-extra-file：該選項指定了在標(biāo)準(zhǔn)defaults-file之前從哪個額外的文件讀取MySQL配置，必須在命令行的第一個選項的位置。一般用于存?zhèn)浞萦脩舻挠脩裘兔艽a的配置文件。
----defaults-group：該選項表示從配置文件讀取的組，innobakcupex多個實例部署時使用。
--no-lock：該選項表示關(guān)閉FTWRL的表鎖，只有在所有表都是Innodb表并且不關(guān)心backup的binlog pos點，如果有任何DDL語句正在執(zhí)行或者非InnoDB正在更新時（包括mysql庫下的表），都不應(yīng)該使用這個選項，后果是導(dǎo)致備份數(shù)據(jù)不一致，如果考慮備份因為獲得鎖失敗，可以考慮--safe-slave-backup立刻停止復(fù)制線程。
--tmpdir：該選項表示指定--stream的時候，指定臨時文件存在哪里，在streaming和拷貝到遠程server之前，事務(wù)日志首先存在臨時文件里。在 使用參數(shù)stream=tar備份的時候，你的xtrabackup_logfile可能會臨時放在/tmp目錄下，如果你備份的時候并發(fā)寫入較大的話 xtrabackup_logfile可能會很大(5G+)，很可能會撐滿你的/tmp目錄，可以通過參數(shù)--tmpdir指定目錄來解決這個問題。
--history：該選項表示percona server 的備份歷史記錄在percona_schema.xtrabackup_history表。
--incremental：該選項表示創(chuàng)建一個增量備份，需要指定--incremental-basedir。
--incremental-basedir：該選項表示接受了一個字符串參數(shù)指定含有full backup的目錄為增量備份的base目錄，與--incremental同時使用。
--incremental-dir：該選項表示增量備份的目錄。
--incremental-force-scan：該選項表示創(chuàng)建一份增量備份時，強制掃描所有增量備份中的數(shù)據(jù)頁。
--incremental-lsn：該選項表示指定增量備份的LSN，與--incremental選項一起使用。
--incremental-history-name：該選項表示存儲在PERCONA_SCHEMA.xtrabackup_history基于增量備份的歷史記錄的名字。Percona Xtrabackup搜索歷史表查找最近（innodb_to_lsn）成功備份并且將to_lsn值作為增量備份啟動出事lsn.與innobackupex--incremental-history-uuid互斥。如果沒有檢測到有效的lsn，xtrabackup會返回error。
--incremental-history-uuid：該選項表示存儲在percona_schema.xtrabackup_history基于增量備份的特定歷史記錄的UUID。
--close-files：該選項表示關(guān)閉不再訪問的文件句柄，當(dāng)xtrabackup打開表空間通常并不關(guān)閉文件句柄目的是正確的處理DDL操作。如果表空間數(shù)量巨大，這是一種可以關(guān)閉不再訪問的文件句柄的方法。使用該選項有風(fēng)險，會有產(chǎn)生不一致備份的可能。
--compact：該選項表示創(chuàng)建一份沒有輔助索引的緊湊的備份。
--throttle：該選項表示每秒IO操作的次數(shù)，只作用于bakcup階段有效。apply-log和--copy-back不生效不要一起用。
1）連接服務(wù)器權(quán)限
MariaDB [hellodb]> CREATE USER 'backuser'@'localhost' IDENTIFIED BY 'smile';
MariaDB [hellodb]> GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT,PROCESS ON *.* TO 'backuser'@'localhost';
MariaDB [hellodb]>FLUSH PRIVILEGES;
授權(quán)時特別注意 localhost 不能用 %
官方文檔中的權(quán)限描述：

2）備份
innobackupex -ubackuser -psmile /backup
備份后在/backup 文件夾下生成一個當(dāng)前時間戳的文件夾

xtrabackup_binlog_info 記錄mysql服務(wù)器當(dāng)前正在使用的二進制日志文件及至備份這一刻為止二進制日志事件的位置
[root@node4 2017-12-04_15-56-40]# cat xtrabackup_binlog_info <br/>binlog.000010 1814 0-1-56<br/>MariaDB [hellodb]&gt; SHOW MASTER STATUS;<br/>+---------------+----------+--------------+------------------+<br/>| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |<br/>+---------------+----------+--------------+------------------+<br/>| binlog.000010 | 1814 | | |<br/>+---------------+----------+--------------+------------------+
xtrabackup_checkpoints 記錄備份類型，日志序列號（LSN），及備份狀態(tài)
[root@node4 2017-12-04_15-56-40]# cat xtrabackup_checkpoints <br/>backup_type = full-backuped（當(dāng)--apply-log后 backup_type = full-prepared）<br/>from_lsn = 0<br/>to_lsn = 1899901<br/>last_lsn = 1899910<br/>compact = 0<br/>recover_binlog_info = 0
ib_buffer_pool， buffer pool 中的熱數(shù)據(jù)，當(dāng)設(shè)置 innodb_buffer_pool_dump_at_shutdown=1 ，在關(guān)閉 MySQL 時，會把內(nèi)存中的熱數(shù)據(jù)保存在磁盤里 ib_buffer_pool 文件中，位于數(shù)據(jù)目錄下。
ibdata1，備份的共享表空間文件；
xtrabackup_info，記錄備份的基本信息，uuid、備份命令、備份時間、binlog、LSN、以及其他加密壓縮等信息。
[root@node4 2017-12-04_15-56-40]# cat xtrabackup_info <br/>uuid = abd4d399-d8c8-11e7-b084-000c293e4e76<br/>name = <br/>tool_name = innobackupex<br/>tool_command = -ubackuser -psmile /backup<br/>tool_version = 2.4.8<br/>ibbackup_version = 2.4.8<br/>server_version = 10.2.10-MariaDB-log<br/>start_time = 2017-12-04 15:56:40<br/>end_time = 2017-12-04 15:56:44<br/>lock_time = 0<br/>binlog_pos = filename 'binlog.000010', position '1814', GTID of the last change '0-1-56'<br/>innodb_from_lsn = 0<br/>innodb_to_lsn = 1899901<br/>partial = N<br/>incremental = N<br/>format = file<br/>compact = N<br/>compressed = N<br/>encrypted = N
xtrabackup_logfile，備份的重做日志文件。
backup-my.cnf，備份命令用到的配置選項信息
2、還原
還原之前需prepare備份文件
innobackupex --apply-log [--use-memory=B]<br/>[--defaults-file=MY.CNF]<br/>[--export] [--redo-only] [--ibbackup=IBBACKUP-BINARY]<br/>BACKUP-DIR
--apply-log：該選項表示同xtrabackup的--prepare參數(shù),一般情況下,在備份完成后，數(shù)據(jù)尚且不能用于恢復(fù)操作，因為備份的數(shù)據(jù)中可能會包含尚未提交的事務(wù)或已經(jīng)提交但尚未同步至數(shù)據(jù)文件中的事務(wù)。因此，此時數(shù)據(jù) 文件仍處理不一致狀態(tài)。--apply-log的作用是通過回滾未提交的事務(wù)及同步已經(jīng)提交的事務(wù)至數(shù)據(jù)文件使數(shù)據(jù)文件處于一致性狀態(tài)。
--use-memory：該選項表示和--apply-log選項一起使用，prepare 備份的時候，xtrabackup做crash recovery分配的內(nèi)存大小，單位字節(jié)。也可(1MB,1M,1G,1GB)，推薦1G。
--defaults-file：該選項指定了從哪個文件讀取MySQL配置，必須放在命令行第一個選項的位置。
--export：這個選項表示開啟可導(dǎo)出單獨的表之后再導(dǎo)入其他Mysql中。
--redo-only：這個選項在prepare base full backup，往其中merge增量備份（但不包括最后一個）時候使用。

還原
innobackupex --copy-back [--defaults-file=MY.CNF] [--defaults-group=GROUP-NAME] BACKUP-DIR<br/>innobackupex --move-back [--defaults-file=MY.CNF] [--defaults-group=GROUP-NAME] BACKUP-DIR
--copy-back：做數(shù)據(jù)恢復(fù)時將備份數(shù)據(jù)文件拷貝到MySQL服務(wù)器的datadir。
--move-back：這個選項與--copy-back相似，唯一的區(qū)別是它不拷貝文件，而是移動文件到目的地。這個選項移除backup文件，用時候必須小心。使用場景：沒有足夠的磁盤空間同事保留數(shù)據(jù)文件和Backup副本
注意：
1.datadir目錄必須為空。除非指定innobackupex --force-non-empty-directorires選項指定，否則--copy-backup選項不會覆蓋
2.在restore之前,必須shutdown MySQL實例，你不能將一個運行中的實例restore到datadir目錄中
3.由于文件屬性會被保留，大部分情況下你需要在啟動實例之前將文件的屬主改為mysql，這些文件將屬于創(chuàng)建備份的用戶
chown -R mysql:mysql /data1/dbrestore
以上需要在用戶調(diào)用Innobackupex之前完成
--force-non-empty-directories：指定該參數(shù)時候，使得innobackupex --copy-back或--move-back選項轉(zhuǎn)移文件到非空目錄，已存在的文件不會被覆蓋。如果--copy-back和--move-back文件需要從備份目錄拷貝一個在datadir已經(jīng)存在的文件，會報錯失敗。

3、增量備份
1)全備 全備操作如上
2)在全備基礎(chǔ)上增備

[root@node4 2017-12-05_10-35-33]# innobackupex -ubackuser -psmile --incremental --incremental-basedir=/backup/2017-12-05_10-35-33 /backup/

3)還原先--apply-log 且--redo-only全備 然后在準(zhǔn)備好的全備上--apply-log和--redo-only增備 最后一次無需--redo-only

[root@node4 2017-12-05_10-35-33]# innobackupex --apply-log --redo-only /backup/2017-12-05_10-35-33

 innobackupex --apply-log --incremental-dir=/backup/2017-12-05_10-47-40/ /backup/2017-12-05_10-35-33

備份報錯：
innobackupex version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mydata
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: File ./ib_logfile0: 'open' returned OS error 71. Cannot continue operation
InnoDB: Cannot continue operation.

報此錯需要指定--defaults-file 并且配置文件中需指定datadir和basedir并重啟mysql