MEB（MySQL Enterprise Backup）是MySQL商業版中提供的備份工具，屬於物理備份。 同XtraBackup一樣，mysqlbackup的使用過程同樣包含如下三個步驟： 備份（--backup）=> 應用日誌（--apply-log）=> 恢復（--copy-back） 備份 ...
MEB（MySQL Enterprise Backup）是MySQL商業版中提供的備份工具，屬於物理備份。
同XtraBackup一樣，mysqlbackup的使用過程同樣包含如下三個步驟：
備份（--backup）=> 應用日誌（--apply-log）=> 恢復（--copy-back）
備份
# ./mysqlbackup --backup_dir=/backup --socket=/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/mysql.sock backup
在備份目錄下，會生成以下文件
# ls
backup-my.cnf datadir meta server-all.cnf server-my.cnf
其中datadir和meta是目錄，其它是變數配置文件
backup-my.cnf
官方解釋如下：
Records the configuration parameters that specify the layout of the MySQL data files.
# cat backup-my.cnf # # Generated backup-my.cnf file. # Auto generated by mysqlbackup program. # [mysqld] innodb_data_file_path=ibdata1:12M:autoextend innodb_log_file_size=50331648 innodb_log_files_in_group=2 innodb_page_size=16384 innodb_checksum_algorithm=innodb
server-all.cnf
該文件記錄了備份資料庫的所有全局參數
server-my.cnf
該文件記錄了備份資料庫的非預設的全局參數
註意：在恢複數據庫時，可使用server-all.cnf和server-my.cnf中的任意一個。如果需要將資料庫恢復到非預設路徑，還要修改路徑相關的變數值。
再來看看meta目錄中的文件
# ls meta/ backup_content.xml backup_create.xml backup_gtid_executed.sql backup_variables.txt MEB_2016-09-27.10-49-21_backup.log
MEB_2016-09-27.10-49-21_backup.log
記錄了此次備份的詳細信息
backup_content.xml
記錄了備份文件的元數據信息和資料庫定義信息，同時還記錄了備份資料庫的plugin信息，恢復時必須確保這些插件在目標資料庫中同樣存在。
backup_create.xml
記錄了備份操作的相關信息，包括備份命令，備份的時間，備份的目錄，資料庫的參數信息
上述兩個文件可通過--disable-manifest選項禁用。
backup_gtid_executed.sql
因為備份資料庫啟動了GTID複製，故會生成該文件記錄GTID的信息
# cat backup_gtid_executed.sql # On a new slave, issue the following command if GTIDs are enabled: SET @@GLOBAL.GTID_PURGED='844e8202-8391-11e6-accb-000c29c64704:1-328944'; # Use the following command if you want to use the GTID handshake protocol: # CHANGE MASTER TO MASTER_AUTO_POSITION=1;
backup_variables.txt
記錄了備份的相關信息，該文件由mysqlbackup來控制的，譬如apply_log_done指示是否進行了apply-log操作
# cat backup_variables.txt # # This file is auto generated by mysqlbackup. # [backup_variables] start_lsn=98224128 end_lsn=98227650 apply_log_done=0 is_incremental=0 is_incremental_with_redo_log_only=0 is_partial=0 is_compressed=0 is_skip_unused_pages=0 binlog_position=mysql-bin.000005:65395090 gtid_executed=844e8202-8391-11e6-accb-000c29c64704:1-328944 is_onlyinnodb=0
關於各文件的說明，可參考官方文檔的解釋
https://dev.mysql.com/doc/mysql-enterprise-backup/3.12/en/meb-files-overview.html
應用日誌
./mysqlbackup --backup-dir=/backup/ apply-log
MySQL Enterprise Backup version 3.9.0 [2013/08/23] Copyright (c) 2003, 2013, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... ./mysqlbackup --backup-dir=/backup/ apply-log IMPORTANT: Please check that mysqlbackup run completes successfully. At the end of a successful 'apply-log' run mysqlbackup prints "mysqlbackup completed OK!". 160927 11:38:43 mysqlbackup: INFO: MEB logfile created at /backup/meta/MEB_2016-09-27.11-38-43_apply_log.log -------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /backup/datadir innodb_data_home_dir = /backup/datadir innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /backup/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 50331648 innodb_page_size = 16384 innodb_checksum_algorithm = innodb mysqlbackup: INFO: Uses posix_fadvise() for performance optimization. mysqlbackup: INFO: Creating 14 buffers each of size 65536. 160927 11:38:43 mysqlbackup: INFO: Apply-log operation starts with following threads 1 read-threads 1 process-threads 160927 11:38:43 mysqlbackup: INFO: ibbackup_logfile's creation parameters: start lsn 98224128, end lsn 98227650, start checkpoint 98224335. InnoDB: Doing recovery: scanned up to log sequence number 98227650 mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 mysqlbackup: INFO: InnoDB: Setting log file size to 50331648 mysqlbackup: INFO: InnoDB: Setting log file size to 50331648 160927 11:38:48 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to lsn 98227650. mysqlbackup: INFO: Last MySQL binlog file position 0 65394957, file name mysql-bin.000005 160927 11:38:48 mysqlbackup: INFO: The first data file is '/backup/datadir/ibdata1' and the new created log files are at '/backup/datadir' 160927 11:38:48 mysqlbackup: INFO: Apply-log operation completed successfully. 160927 11:38:48 mysqlbackup: INFO: Full backup prepared for recovery successfully. mysqlbackup completed OK!
在應用日誌之前，
數據目錄中並沒有redo log
[root@localhost datadir]# ls
ibbackup_logfile ibdata1 mysql performance_schema test
在應用完日誌之後，
數據目錄中會生成redo log
[root@localhost datadir]# ls
ibbackup_logfile ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test
恢復
./mysqlbackup --defaults-file=/backup/server-my.cnf --backup-dir=/backup/ copy-back
./mysqlbackup --defaults-file=/backup/server-my.cnf --backup-dir=/backup/ copy-back MySQL Enterprise Backup version 3.9.0 [2013/08/23] Copyright (c) 2003, 2013, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... ./mysqlbackup --defaults-file=/backup/server-my.cnf --backup-dir=/backup/ copy-back IMPORTANT: Please check that mysqlbackup run completes successfully. At the end of a successful 'copy-back' run mysqlbackup prints "mysqlbackup completed OK!". 160927 15:26:55 mysqlbackup: INFO: MEB logfile created at /backup/meta/MEB_2016-09-27.15-26-55_copy_back.log -------------------------------------------------------------------- Server Repository Options: -------------------------------------------------------------------- datadir = /usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/ innodb_data_home_dir = /usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/ innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/ innodb_log_files_in_group = 2 innodb_log_file_size = 50331648 innodb_page_size = 16384 innodb_checksum_algorithm = innodb innodb_undo_directory = /usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/ innodb_undo_tablespaces = 0 innodb_undo_logs = 128 -------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /backup/datadir innodb_data_home_dir = /backup/datadir innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /backup/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 50331648 innodb_page_size = 16384 innodb_checksum_algorithm = innodb mysqlbackup: INFO: Uses posix_fadvise() for performance optimization. mysqlbackup: INFO: Creating 14 buffers each of size 16777216. 160927 15:26:55 mysqlbackup: INFO: Copy-back operation starts with following threads 1 read-threads 1 write-threads 160927 15:26:55 mysqlbackup: INFO: Copying /backup/datadir/ibdata1 (to '/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data') .160927 15:26:59 mysqlbackup: INFO: Copying /backup/datadir/mysql/innodb_index_stats.ibd. 160927 15:26:59 mysqlbackup: INFO: Copying /backup/datadir/mysql/innodb_table_stats.ibd. 160927 15:26:59 mysqlbackup: INFO: Copying /backup/datadir/mysql/slave_master_info.ibd. 160927 15:26:59 mysqlbackup: INFO: Copying /backup/datadir/mysql/slave_relay_log_info.ibd. 160927 15:26:59 mysqlbackup: INFO: Copying /backup/datadir/mysql/slave_worker_info.ibd. 160927 15:27:00 mysqlbackup: INFO: Copying /backup/datadir/test/test.ibd. 160927 15:27:00 mysqlbackup: INFO: Copying the database directory 'mysql' 160927 15:27:00 mysqlbackup: INFO: Copying the database directory 'performance_schema' 160927 15:27:00 mysqlbackup: INFO: Copying the database directory 'test' 160927 15:27:00 mysqlbackup: INFO: Completing the copy of all non-innodb files. 160927 15:27:00 mysqlbackup: INFO: Copying the log file 'ib_logfile0' 160927 15:27:04 mysqlbackup: INFO: Copying the log file 'ib_logfile1' 160927 15:27:07 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /usr/local/mysql-advanced-5.6.23- linux-glibc2.5-x86_64/data/160927 15:27:07 mysqlbackup: INFO: Copy-back operation completed successfully. 160927 15:27:07 mysqlbackup: INFO: Finished copying backup files to '/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/' mysqlbackup completed OK!
關於恢復，有以下幾點需要註意
1. 恢復必須使用配置文件，建議備份目錄下的server-all.cnf和server-my.cnf，事實上，在恢復的過程中，mysqlbackup需要明確上述輸出中的參數，譬如，我使用了自己的配置文件進行恢復，結果報如下錯誤：
# ./mysqlbackup --defaults-file=/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/my.cnf --backup-dir=/backup/ copy-backMySQL Enterprise Backup version 3.9.0 [2013/08/23] Copyright (c) 2003, 2013, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... ./mysqlbackup --defaults-file=/usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/my.cnf --backup-dir=/backup/ copy-back IMPORTANT: Please check that mysqlbackup run completes successfully. At the end of a successful 'copy-back' run mysqlbackup prints "mysqlbackup completed OK!". 160927 15:26:09 mysqlbackup: INFO: MEB logfile created at /backup/meta/MEB_2016-09-27.15-26-09_copy_back.log mysqlbackup: ERROR: Server innodb_log_files_in_group is missing or invalid mysqlbackup: ERROR: Server repository configuration problem found. mysqlbackup failed with errors!
很顯然，我的配置文件中並沒有定義innodb_log_files_in_group參數。
2. 可在上述配置文件中server-all.cnf和server-my.cnf自定義數據目錄和base目錄，不然預設的都是備份資料庫的。
3. 雖然mysqlbackup --help中的解釋是：The restore operation assumes the server is offline. Use of this command when server is running is not supported.
但事實上，在server是online的情況下，執行恢復操作並沒有報錯，甚至還顯示“mysqlbackup completed OK!”。但是，並沒有覆蓋數據目錄中的文件。
4. 在啟動資料庫的過程中，可以使用server-my.cnf或備份資料庫的配置文件，不要使用server-all.cnf。在使用server-all.cnf啟動資料庫的過程中，會報如下錯誤：
2016-09-27 15:51:37 85229 [ERROR] /usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/bin/mysqld: Table './mysql/user' is marked a s crashed and should be repaired2016-09-27 15:51:37 85229 [ERROR] Fatal error: Can't open and lock privilege tables: Table './mysql/user' is marked as crashed and sh ould be repaired
這個坑定位了好久，因為server-all.cnf中定義的是所有參數的配置，懷疑跟某些參數有關，因參數較多，時間有限，並沒有一一驗證。
啟動資料庫
修改數據目錄的許可權
# chown -R mysql.mysql /usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data
啟動資料庫
# /usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/bin/mysqld --defaults-file=/usr/local/mysql-advanced-5.
6.23-linux-glibc2.5-x86_64/my.cnf --user=mysql &
總結
以上只是mysqlbackup的一個簡單的備份恢復流程，事實上，mysqlbackup還支持壓縮備份等高級特性，後續再表。
