共计 9754 个字符,预计需要花费 25 分钟才能阅读完成。
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
在备份目录下,会生成以下文件
# 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 还支持压缩备份等高级特性,后续再表。
本文永久更新链接地址 :http://www.linuxidc.com/Linux/2016-09/135580.htm