首页 > > 网络编程 > Mysql >

基于MySQL MEB的备份恢复

2018-06-17 23:02:51来源：未知 阅读 ()

新老客户大回馈,云服务器低至5折

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还支持压缩备份等高级特性，后续再表。

 

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有

上一篇：【精粹系列】Mysql精粹

下一篇：【菜鸟玩Linux开发】通过MySQL自动同步刷新Redis

相关文章

IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设

网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源

网站联盟： 联盟新闻 联盟介绍 联盟点评 网赚技巧

行业资讯： 搜索引擎 网络游戏 电子商务 广告传媒

网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它

服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护

软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷

网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash

程序设计： Java技术 C/C++ VB delphi

网络知识： 网络协议 网络安全 网络管理 组网方案 Cisco技术

操作系统： Win2000 WinXP Win2003 Mac OS Linux FreeBSD

热门词条
最新资讯
热门关注
热门标签