基于MySQL MEB的备份恢复

MEB是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在备份目录下，会生成以下文件# lsbackup-my.cnfdatadirmetaserver-all.cnfserver-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:autoextendinnodb_log_file_size=50331648innodb_log_files_in_group=2innodb_page_size=16384innodb_checksum_algorithm=innodbserver-all.cnf该文件记录了备份数据库的所有全局参数server-my.cnf该文件记录了备份数据库的非默认的全局参数注意：在恢复数据库时，可使用server-all.cnf和server-my.cnf中的任意一个。如果需要将数据库恢复到非默认路径，还要修改路径相关的变量值。再来看看meta目录中的文件# ls meta/backup_content.xmlbackup_create.xmlbackup_gtid_executed.sqlbackup_variables.txtMEB_2016-09-27.10-49-21_backup.logMEB_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=98224128end_lsn=98227650apply_log_done=0is_incremental=0is_incremental_with_redo_log_only=0is_partial=0is_compressed=0is_skip_unused_pages=0binlog_position=mysql-bin.000005:65395090gtid_executed=844e8202-8391-11e6-accb-000c29c64704:1-328944is_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/datadirinnodb_data_home_dir = /backup/datadirinnodb_data_file_path = ibdata1:12M:autoextendinnodb_log_group_home_dir = /backup/datadirinnodb_log_files_in_group = 2innodb_log_file_size = 50331648innodb_page_size = 16384innodb_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 threads1 read-threads1 process-threads160927 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 99mysqlbackup: INFO: InnoDB: Setting log file size to 50331648 mysqlbackup: INFO: InnoDB: Setting log file size to 50331648160927 11:38:48 mysqlbackup: INFO: We were able to parse ibbackup_logfile up tolsn 98227650. mysqlbackup: INFO: Last MySQL binlog file position 0 65394957, file name mysql-bin.000005160927 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]# lsibbackup_logfileibdata1mysqlperformance_schematest在应用完日志之后，数据目录中会生成redo log[root@localhost datadir]# lsibbackup_logfileibdata1ib_logfile0ib_logfile1mysqlperformance_schematest恢复./mysqlbackup --defaults-file=/backup/server-my.cnf --backup-dir=/backup/ copy-back ./mysqlbackup --defaults-file=/backup/server-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=/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:autoextendinnodb_log_group_home_dir = /usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/innodb_log_files_in_group = 2innodb_log_file_size = 50331648innodb_page_size = 16384innodb_checksum_algorithm = innodbinnodb_undo_directory = /usr/local/mysql-advanced-5.6.23-linux-glibc2.5-x86_64/data/innodb_undo_tablespaces = 0innodb_undo_logs = 128-------------------------------------------------------------------- Backup Config Options:--------------------------------------------------------------------datadir = /backup/datadirinnodb_data_home_dir = /backup/datadirinnodb_data_file_path = ibdata1:12M:autoextendinnodb_log_group_home_dir = /backup/datadirinnodb_log_files_in_group = 2innodb_log_file_size = 50331648innodb_page_size = 16384innodb_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 threads1 read-threads1 write-threads160927 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 as 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 should 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还支持压缩备份等高级特性，后续再表。本文永久更新链接地址