几种备份方式的介绍
mysqlbackup是一个热备份工具、也就是说它不像mysqldump那样给表上一个全局锁，由于mysqldump上了这个锁，所以就造成客户端只能对
数据库进行读操作不能写，这也就是称mysqldump为温备份的原因。但是mysqlbackup真的有这么吊吗？答案是并没有。对于innodb引擎的表
mysqlbackup 热备的；但是对于非innodb表mysqlbackup就只能温备了，原因是这类引擎不支持事务也就是说不能通过事务日志来保证备份的
一致性，所以就只能给表加上一个全局锁来解决了。为了得到一致的备份mysqlbackup要不停的去追踪mysql数据库的sln号，也就是说mysqlback
要执行备份那么它一定要连接上数据库。mysqlbackup对数据库的备份是通过复制文件的方式进行的，也就是说mysqlback要和数据库在同一台机器
上。
###download mysqlbackup
MOS 下载对应的mysqlbacup
1.Access My Oracle Support.
Click on Patches & Updates navigation tab near the top of the screen.
2.In the Patch Search section, click on the Product or Family (Advanced Search) link that is just to the right of the box where you can enter a search term.
Type MySQL Enterprise Backup into the Product drop down list (the one containing the text Type in comma separated values or choose from list) , which will bring up an auto-complete list of the various MySQL products. Choose MySQL Enterprise Backup or whatever option you want. If you do not get a list, ensure that you have typed 3,MySQL with correct upper and lower case combination.
Expand the options in the Release drop down list and chose one or more releases.
Optional, recommended, use the Platform is option to pick one or more platforms, like Windows or Linux.
4.Click the Searchbutton.
Note that the result list is truncated with no indication of this. If you did not give a platform you may not see the version you need.
下载linux 通用版本的mysqlbackup 即可
p30806208_410_Linux-x86-64.zip
###########0 常见命令的说明：
A.1全库备份. 命令: mysqlbackup --host=127.0.0.1 --user=root --password=root@123 --port=3330 --with-timestamp --backup-dir=/verislog/mysqlbackup backup 参数说明： --defaults-file my.cnf文件的路径,主要用于一台服务器多个mysql服务.默认位置是/etc/my.cnf --host=127.0.0.1 --user 用户名,这个用户必须在mysql库里面有创建table和查询,插入的权限.在备份的过程中.mysqlbackup会在mysql库下建立backup_history, backup_progress表.用户保留备份的历史信息和备份的基础信息. --password密码 --database 需要备份的数据库,要备份多个数据库需要用""包括起来,每个数据库中间用空格分开 --with-timestamp用户创建一个备份目录下面当前时间的文件夹,如果没有这个参数,多次备份时,制定同一个目录,会使上一次备份的文件覆盖掉. --backup-dir备份的目录 Backup 表明,这是备份操作 A.2全库还原. 第一步:检测事务日志 mkdir -p /veris/billing/mysql/3320/data mkdir -p /veris/billing/mysql/3320/etc mkdir -p /veris/billing/mysql/3320/innodb mkdir -p /veris/billing/mysql/3320/innodb/log mkdir -p /veris/billing/mysql/3320/proc mkdir -p /veris/billing/mysql/3320/log mkdir -p /veris/billing/mysql/3320/log/audit mkdir -p /veris/billing/mysql/3320/log/binlog mkdir -p /veris/billing/mysql/3320/log/error mkdir -p /veris/billing/mysql/3320/log/general mkdir -p /veris/billing/mysql/3320/log/relay mkdir -p /veris/billing/mysql/3320/log/slow mkdir -p /veris/billing/mysql/3320/tmp chmod -R 755 /veris/billing chown -R mysql:mysql /veris/billing/mysql/3320/* chown -R mysql:mysql /veris/billing/mysql/3320/innodb/log chown -R mysql:mysql /veris/billing/mysql/3320/log/* mysqlbackup --defaults-file=/veris/dbm/mysql/3310/etc/my_3310.cnf --backup-dir=/verislog/mysqlbackup/2014-08-14_11-30-49/ apply-log 参数说明: apply-log:因为在备份的时候是在线的,如果有新插入的SQL语句,会记录新增加的LSN点,然后新修改的页面会放到这个文件里面（ibbackup_logfile）,同时也会放到表空间里面.当还原使用这个参数的时候,mysqlbackup会检测ibbackup_logfile和表空间的LSN点,然后比较ibbackup_logfile文件表空间LSN的差值,把这个值放到事务日志LOG里面.(事务日志如果填满了,会进入表空间的) 第二步:copy物理文件 mysqlbackup --defaults-file=/veris/dbm/mysql/3310/etc/my_3310.cnf --backup-dir=/verislog/mysqlbackup/2014-08-14_11-30-49/ copy-back chown -R mysql:mysql /veris/billing/mysql/3320/* chown -R mysql:mysql /veris/billing/mysql/3320/innodb/log chown -R mysql:mysql /veris/billing/mysql/3320/log/*
##############1
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_dbeate.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_dbeate.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_indbemental=0 is_indbemental_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 dbeated 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: dbeating 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 dbeation 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 dbeated 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 dbeated 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: dbeating 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: dbeating 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 dbeated 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 dbashed 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 dbashed 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还支持压缩备份等高级特性，后续再表。
###########2
001、mysqlbackup介绍：
mysqlbackup是一个热备份工具、也就是说它不像mysqldump那样给表上一个全局锁，由于mysqldump上了这个锁，所以就造成客户端只能对
数据库进行读操作不能写，这也就是称mysqldump为温备份的原因。但是mysqlbackup真的有这么吊吗？答案是并没有。对于innodb引擎的表
mysqlbackup 热备的；但是对于非innodb表mysqlbackup就只能温备了，原因是这类引擎不支持事务也就是说不能通过事务日志来保证备份的
一致性，所以就只能给表加上一个全局锁来解决了。为了得到一致的备份mysqlbackup要不停的去追踪mysql数据库的sln号，也就是说mysqlback
要执行备份那么它一定要连接上数据库。mysqlbackup对数据库的备份是通过复制文件的方式进行的，也就是说mysqlback要和数据库在同一台机器
上。
002、备份用户的创建与授权
grant reload on *.* to mybackup@'localhost' identified by '131417'; grant dbeate,insert,drop,update on mysql.backup_progress to mybackup@'localhost'; grant dbeate,insert,drop,update,select on mysql.backup_history to mybackup@'localhost'; grant super on *.* to mybackup@'localhost'; grant replication client on *.* to mybackup@'localhost'; grant process on *.* to mybackup@'localhost'; grant lock tables, select,dbeate,alter on *.* to mybackup@localhost; grant dbeate, insert, drop, update on mysql.backup_sbt_history to mybackup@'localhost';
003、由于mysqbackup是复制文件的方式备份的，所以用于备份的linux用户要对目标路径有相应的权限。
004、执行一次全备
root@localhost>mysqlbackup --user=mybackup --password=131417 --backup-image=/mysql_backup/data/mysql_server.mbi --backup-dir=/mysql_backup/temp backup-to-image
--user:用户名。
--password:密码。
--port:端口，默认值为3306。
--backup-dir:可以看成是mysqlback的工作目录。
--backup-image:备份文件名。
backup-to-image:把所有的备份信息输出到一个备份文件当中。
005、可以校验一下备份文件，用于确定备份是否损坏。
mysqlbackup --backup-image=/mysql_backup/data/mysql_server.mbi validate
006、还原备份
mysqlbackup --defaults-file=/etc/my.cnf --backup-image=/mysql_backup/data/mysql_server.mbi --backup-dir=/mysql_backup/temp/ copy-back-and-apply-log chown -R mysql.mysql /usr/local/mysql/data
007、备份文件转换到备份目录
mysqlbackup --backup-image=/mysql_backup/data/mysql_server.mbi --backup-dir=/mysql_backup/temp/ image-to-backup-dir #方法一
mysqlbackup --backup-image=/mysql_backup/data/mysql_server.mbi extract #方法二，导出到当前目录
008、查看备份文件中的内容
mysqlbackup --backup-image=/mysql_backup/data/mysql_server.mbi list-image
009、
########## 3
http://www.bubuko.com/infodetail-844864.html
sqldump当数据量很大的时候，不建议使用此工具，应当采用的是oracle公司提供了针对企业的备份软件Mysql Enterprise Backup简称mysqlbackup
一、安装
[root@slave bjia]# unzip p19957495_3110_Linux-x86-64.zip [root@slave bjia]# tar zxvf meb-3.11.1-linux-glibc2.5-x86-64bit.tar.gz [root@slave meb-3.11.1-linux-glibc2.5-x86-64bit]# cd meb-3.11.1-linux-glibc2.5-x86-64bit [root@slave meb-3.11.1-linux-glibc2.5-x86-64bit]# cd bin [root@slave bin]# cp mysqlbackup /usr/bin/ 至此Mysqlbackup工具已经安装完成
二、全量备份
mysqlbackup --socket=/tmp/mysql3003.sock --backup-dir=/root/ --with-timestamp backup-and-apply-log MySQL Enterprise Backup version 3.11.1 Linux-2.6.18-194.el5-x86_64 [2014/11/04] Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. Starting with following command line ... mysqlbackup --socket=/tmp/mysql3003.sock --backup-dir=/root/ --with-timestamp backup-and-apply-log MySQL server version is ‘5.6.23‘. Got some server configuration information from running server. IMPORTANT: Please check that mysqlbackup run completes successfully. At the end of a successful ‘backup-and-apply-log‘ run mysqlbackup prints "mysqlbackup completed OK!". 150602 12:20:00 mysqlbackup: INFO: MEB logfile dbeated at /root/2015-06-02_12-20-00/meta/MEB_2015-06-02.12-20-00_backup_apply_log.log -------------------------------------------------------------------- Server Repository Options: -------------------------------------------------------------------- datadir = /9tong/mysql/data/3003/ innodb_data_home_dir = innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /9tong/mysql/data/3003/ innodb_log_files_in_group = 2 innodb_log_file_size = 4294967296 innodb_page_size = 16384 innodb_checksum_algorithm = innodb innodb_undo_directory = /9tong/mysql/data/3003/ innodb_undo_tablespaces = 0 innodb_undo_logs = 128 -------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /root/2015-06-02_12-20-00/datadir innodb_data_home_dir = /root/2015-06-02_12-20-00/datadir innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /root/2015-06-02_12-20-00/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 4294967296 innodb_page_size = 16384 innodb_checksum_algorithm = innodb innodb_undo_directory = /root/2015-06-02_12-20-00/datadir innodb_undo_tablespaces = 0 innodb_undo_logs = 128 mysqlbackup: INFO: Unique generated backup id for this is 14332188007400510 mysqlbackup: INFO: dbeating 14 buffers each of size 16777216. 150602 12:20:02 mysqlbackup: INFO: Full Backup operation starts with following threads 1 read-threads 6 process-threads 1 write-threads 150602 12:20:02 mysqlbackup: INFO: System tablespace file format is Antelope. 150602 12:20:02 mysqlbackup: INFO: Starting to copy all innodb files... mysqlbackup: INFO: Could not find binlog index file. binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible. If this is online backup then server may not have started with --log-bin. You may specify its location with --log-bin-index option. 150602 12:20:03 mysqlbackup: INFO: Found checkpoint at lsn 171444289872. 150602 12:20:03 mysqlbackup: INFO: Starting log scan from lsn 171444289536. 150602 12:20:03 mysqlbackup: INFO: Copying log... 150602 12:20:03 mysqlbackup: INFO: Copying /9tong/mysql/data/3003/ibdata1 (Antelope file format). 150602 12:20:03 mysqlbackup: INFO: Log copied, lsn 171444289872. mysqlbackup: Progress in MB: 200 400 600 800 1000 1200 1400 1600 1800 2000 2200 2400 2600 2800 3000 3200 3400 3600 3800 4000 4200 4400 4600 4800 5000 5200 5400 5600 5800 6000 6200 6400 6600 6800 7000 7200 7400 7600 7800 8000 8200 8400 8600 8800 9000 9200 9400 9600 9800 10000 10200 10400 10600 10800 11000 11200 11400 11600 11800 12000 12200 12400 12600 12800 13000 13200 13400 13600 13800 14000 14200 14400 14600 14800 15000 15200 15400 15600 15800 16000 16200 16400 16600 16800 17000 17200 17400 17600 17800 18000 18200 18400 18600 18800 19000 150602 12:28:24 mysqlbackup: INFO: Completing the copy of innodb files. 150602 12:28:25 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server. 150602 12:28:25 mysqlbackup: INFO: Starting to lock all the tables... 150602 12:28:25 mysqlbackup: INFO: All tables are locked and flushed to disk 150602 12:28:25 mysqlbackup: INFO: Opening backup source directory ‘/9tong/mysql/data/3003/‘ 150602 12:28:25 mysqlbackup: INFO: Starting to backup all non-innodb files in subdirectories of ‘/9tong/mysql/data/3003/‘ 150602 12:28:25 mysqlbackup: INFO: Copying the database directory ‘9tong_ur‘ 150602 12:28:27 mysqlbackup: INFO: Copying the database directory ‘9tong_user‘ 150602 12:28:28 mysqlbackup: INFO: Copying the database directory ‘aclocal‘ 150602 12:28:28 mysqlbackup: INFO: Copying the database directory ‘mysql‘ 150602 12:28:28 mysqlbackup: INFO: Copying the database directory ‘performance_schema‘ 150602 12:28:28 mysqlbackup: INFO: Copying the database directory ‘ps_helper‘ 150602 12:28:28 mysqlbackup: INFO: Copying the database directory ‘sys‘ 150602 12:28:29 mysqlbackup: INFO: Copying the database directory ‘test‘ 150602 12:28:29 mysqlbackup: INFO: Completing the copy of all non-innodb files. 150602 12:28:29 mysqlbackup: INFO: A copied database page was modified at 171444289872. (This is the highest lsn found on page) Scanned log up to lsn 171444289872. Was able to parse the log up to lsn 171444289872. Maximum page number for a log record 0 150602 12:28:29 mysqlbackup: INFO: All tables unlocked 150602 12:28:29 mysqlbackup: INFO: All MySQL tables were locked for 4.072 seconds. 150602 12:28:29 mysqlbackup: INFO: Reading all global variables from the server. 150602 12:28:29 mysqlbackup: INFO: Completed reading of all global variables from the server. 150602 12:28:29 mysqlbackup: INFO: dbeating server config files server-my.cnf and server-all.cnf in /root/2015-06-02_12-20-00 150602 12:28:30 mysqlbackup: INFO: Full Backup operation completed successfully. 150602 12:28:30 mysqlbackup: INFO: Backup dbeated in directory ‘/root/2015-06-02_12-20-00‘ ------------------------------------------------------------- Parameters Summary ------------------------------------------------------------- Start LSN : 171444289536 End LSN : 171444289872 ------------------------------------------------------------- mysqlbackup: INFO: dbeating 14 buffers each of size 65536. 150602 12:28:30 mysqlbackup: INFO: Apply-log operation starts with following threads 1 read-threads 1 process-threads mysqlbackup: INFO: Using up to 100 MB of memory. 150602 12:28:30 mysqlbackup: INFO: ibbackup_logfile‘s dbeation parameters: start lsn 171444289536, end lsn 171444289872, start checkpoint 171444289872. mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database... mysqlbackup: INFO: InnoDB: Setting log file size to 4294967296 mysqlbackup: INFO: InnoDB: Setting log file size to 4294967296 150602 12:30:32 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to lsn 171444289872. mysqlbackup: INFO: Last MySQL binlog file position 0 791363474, file name mysql-bin.000023 150602 12:30:34 mysqlbackup: INFO: The first data file is ‘/root/2015-06-02_12-20-00/datadir/ibdata1‘ and the new dbeated log files are at ‘/root/2015-06-02_12-20-00/datadir‘ 150602 12:30:34 mysqlbackup: INFO: Apply-log operation completed successfully. 150602 12:30:34 mysqlbackup: INFO: Full backup prepared for recovery successfully. mysqlbackup completed OK! [root@dbbackup ~]# du -sh 2015-06-02_12-20-00/ 27G 2015-06-02_12-20-00/ 10分钟的时间，全备出时间10分钟。速度效率高啊！！！ 参数： --with-timestamp:为每一个备份目录增加时间戳，非常有用的一个参数 --backdir:备份的路径 --backup-and-apply-log ：全备的事物日志文件
三、全量恢复
root@dbbackup 3003]# mysqlbackup --datadir=/9tong/mysql/data/3003 --socket=/tmp/mysql3003.sock --backup-dir=/root/2015-06-02_12-20-00/ copy-back MySQL Enterprise Backup version 3.11.1 Linux-2.6.18-194.el5-x86_64 [2014/11/04] Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... mysqlbackup --datadir=/9tong/mysql/data/3003 --socket=/tmp/mysql3003.sock --backup-dir=/root/2015-06-02_12-20-00/ copy-back mysqlbackup: INFO: IMPORTANT: Please check that mysqlbackup run completes successfully. At the end of a successful ‘copy-back‘ run mysqlbackup prints "mysqlbackup completed OK!". 150602 15:45:05 mysqlbackup: INFO: MEB logfile dbeated at /root/2015-06-02_12-20-00/meta/MEB_2015-06-02.15-45-05_copy_back.log mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_data_file_path parameter might have a different default. In that case you need to add ‘innodb_data_file_path=ibdata1:12M:autoextend‘ to the target server configuration. mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_log_files_in_group parameter might have a different default. In that case you need to add ‘innodb_log_files_in_group=2‘ to the target server configuration. mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_log_file_size parameter might have a different default. In that case you need to add ‘innodb_log_file_size=4294967296‘ to the target server configuration. -------------------------------------------------------------------- Server Repository Options: -------------------------------------------------------------------- datadir = /9tong/mysql/data/3003 innodb_data_home_dir = /9tong/mysql/data/3003 innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /9tong/mysql/data/3003 innodb_log_files_in_group = 2 innodb_log_file_size = 4294967296 innodb_page_size = Null innodb_checksum_algorithm = innodb -------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /root/2015-06-02_12-20-00/datadir innodb_data_home_dir = /root/2015-06-02_12-20-00/datadir innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /root/2015-06-02_12-20-00/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 4294967296 innodb_page_size = 16384 innodb_checksum_algorithm = innodb mysqlbackup: INFO: dbeating 14 buffers each of size 16777216. 150602 15:45:06 mysqlbackup: INFO: Copy-back operation starts with following threads 1 read-threads 1 write-threads mysqlbackup: INFO: Could not find binlog index file. binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible. If this is online backup then server may not have started with --log-bin. You may specify its location with --log-bin-index option. 150602 15:45:06 mysqlbackup: INFO: Copying /root/2015-06-02_12-20-00/datadir/ibdata1. mysqlbackup: Progress in MB: 200 400 600 800 1000 1200 1400 1600 1800 2000 2200 2400 2600 2800 3000 3200 3400 3600 3800 4000 4200 4400 4600 4800 5000 5200 5400 5600 5800 6000 6200 6400 6600 6800 7000 7200 7400 7600 7800 8000 8200 8400 8600 8800 9000 9200 9400 9600 9800 10000 10200 10400 10600 10800 11000 11200 11400 11600 11800 12000 12200 12400 12600 12800 13000 13200 13400 13600 13800 14000 14200 14400 14600 14800 15000 15200 15400 15600 15800 16000 16200 16400 16600 16800 17000 17200 17400 17600 17800 18000 18200 18400 18600 18800 19000 19200 150602 15:53:27 mysqlbackup: INFO: Copying the database directory ‘9tong_ur‘ 150602 15:53:29 mysqlbackup: INFO: Copying the database directory ‘9tong_user‘ 150602 15:53:29 mysqlbackup: INFO: Copying the database directory ‘aclocal‘ 150602 15:53:29 mysqlbackup: INFO: Copying the database directory ‘mysql‘ 150602 15:53:29 mysqlbackup: INFO: Copying the database directory ‘performance_schema‘ 150602 15:53:29 mysqlbackup: INFO: Copying the database directory ‘ps_helper‘ 150602 15:53:29 mysqlbackup: INFO: Copying the database directory ‘sys‘ 150602 15:53:29 mysqlbackup: INFO: Copying the database directory ‘test‘ 150602 15:53:29 mysqlbackup: INFO: Copying the database directory ‘zxl‘ 150602 15:53:29 mysqlbackup: INFO: Completing the copy of all non-innodb files. 150602 15:53:29 mysqlbackup: INFO: Copying the log file ‘ib_logfile0‘ mysqlbackup: Progress in MB: 19400 19600 19800 20000 20200 20400 20600 20800 21000 21200 21400 21600 21800 22000 22200 22400 22600 22800 23000 23200 150602 15:55:23 mysqlbackup: INFO: Copying the log file ‘ib_logfile1‘ mysqlbackup: Progress in MB: 23400 23600 23800 24000 24200 24400 24600 24800 25000 25200 25400 25600 25800 26000 26200 26400 26600 26800 27000 27200 27400 150602 15:57:21 mysqlbackup: INFO: dbeating server config files server-my.cnf and server-all.cnf in /9tong/mysql/data/3003 150602 15:57:21 mysqlbackup: INFO: Copy-back operation completed successfully. 150602 15:57:21 mysqlbackup: INFO: Finished copying backup files to ‘/9tong/mysql/data/3003‘ mysqlbackup completed OK! with 3 warnings
备份完毕，务必要对data目录授权，否则没有权限操作读写数据库。
[root@dbbackup 3003]# chown -R mysql.mysql /9tong/mysql/data/3003/*
重启一下数据库，否则无法读取表的结构，这个也是非常重要的 [root@dbbackup 3003]#mysqladmin -u root -S /tmp/mysql3003 shutdown [root@dbbackup 3003]#mysqld_multi start 1-2 Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | 9tong_ur | | 9tong_user | | aclocal | | mysql | | performance_schema | | ps_helper | | sys | | test | | zxl | +--------------------+ 10 rows in set (0.00 sec) mysql> desc user_works; +-------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_indbement | | uid | int(11) | NO | MUL | 0 | | | companyName | varchar(100) | NO | | | | | title | varchar(30) | NO | | | | | location | varchar(30) | NO | | | | | startYear | decimal(4,0) | NO | | 0 | | | startMonth | decimal(2,0) | NO | | 0 | | | endYear | decimal(4,0) | NO | | 0 | | | endMonth | decimal(2,0) | NO | | 0 | | | desdbiption | varchar(1000) | NO | | | | | dbeateTime | decimal(16,0) | NO | | 0 | | | modifyTime | decimal(16,0) | NO | | 0 | | | industry | varchar(16) | NO | | | | +-------------+---------------+------+-----+---------+----------------+ 13 rows in set (0.00 sec)
至此，完全导入23G的数据量只要12分钟。
四、第一次增量备份
为了验证增量备份的可靠性，首先导入一张表 mysql> dbeate table user_account2016 as select * from user_account; [root@dbbackup ~]# /usr/local/bin/mysqlbackup --socket=/tmp/mysql3003.sock --indbemental --indbemental-base=dir:/root/2015-06-02_12-20-00/ --indbemental-backup-dir=/root backup MySQL Enterprise Backup version 3.11.1 Linux-2.6.18-194.el5-x86_64 [2014/11/04] Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... /usr/local/bin/mysqlbackup --socket=/tmp/mysql3003.sock --indbemental --indbemental-base=dir:/root/2015-06-02_12-20-00/ --indbemental-backup-dir=/root backup mysqlbackup: INFO: mysqlbackup: INFO: MySQL server version is ‘5.6.23‘. mysqlbackup: INFO: Got some server configuration information from running server. mysqlbackup: ERROR: Backup directory already exists and is not empty. Please remove backup directory and retry. mysqlbackup failed with errors! [root@dbbackup ~]# /usr/local/bin/mysqlbackup --socket=/tmp/mysql3003.sock --indbemental --indbemental-base=dir:/root/ --indbemental-backup-dir=/root/2015-06-02_1 backup 2015-06-02_11-00-55/ 2015-06-02_12-20-00/ [root@dbbackup ~]# /usr/local/bin/mysqlbackup --socket=/tmp/mysql3003.sock --indbemental --indbemental-base=dir:/root/ --indbemental-backup-dir=/root/2015-06-02_11-00-55/ backup MySQL Enterprise Backup version 3.11.1 Linux-2.6.18-194.el5-x86_64 [2014/11/04] Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... /usr/local/bin/mysqlbackup --socket=/tmp/mysql3003.sock --indbemental --indbemental-base=dir:/root/ --indbemental-backup-dir=/root/2015-06-02_11-00-55/ backup mysqlbackup: INFO: mysqlbackup: INFO: MySQL server version is ‘5.6.23‘. mysqlbackup: INFO: Got some server configuration information from running server. mysqlbackup: ERROR: Backup directory already exists and is not empty. Please remove backup directory and retry. mysqlbackup failed with errors! [root@dbbackup ~]# /usr/local/bin/mysqlbackup --socket=/tmp/mysql3003.sock --indbemental --indbemental-base=dir:/root/2015-06-02_1 --indbemental-backup-dir=/root/2015-06-02_11-00-55/ backup 2015-06-02_11-00-55/ 2015-06-02_12-20-00/ [root@dbbackup ~]# /usr/local/bin/mysqlbackup --socket=/tmp/mysql3003.sock --indbemental --indbemental-base=dir:/root/2015-06-02_12-20-00/ --indbemental-backup-dir=/root/ backup MySQL Enterprise Backup version 3.11.1 Linux-2.6.18-194.el5-x86_64 [2014/11/04] Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... /usr/local/bin/mysqlbackup --socket=/tmp/mysql3003.sock --indbemental --indbemental-base=dir:/root/2015-06-02_12-20-00/ --indbemental-backup-dir=/root/ backup mysqlbackup: INFO: mysqlbackup: INFO: MySQL server version is ‘5.6.23‘. mysqlbackup: INFO: Got some server configuration information from running server. mysqlbackup: ERROR: Backup directory already exists and is not empty. Please remove backup directory and retry. mysqlbackup failed with errors! [root@dbbackup ~]# /usr/local/bin/mysqlbackup --socket=/tmp/mysql3003.sock --with-timestamp --indbemental --indbemental-base=dir:/root/2015-06-02_12-20-00/ --indbemental-backup-dir=/root/ backup MySQL Enterprise Backup version 3.11.1 Linux-2.6.18-194.el5-x86_64 [2014/11/04] Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... /usr/local/bin/mysqlbackup --socket=/tmp/mysql3003.sock --with-timestamp --indbemental --indbemental-base=dir:/root/2015-06-02_12-20-00/ --indbemental-backup-dir=/root/ backup mysqlbackup: INFO: mysqlbackup: INFO: MySQL server version is ‘5.6.23‘. mysqlbackup: INFO: Got some server configuration information from running server. IMPORTANT: Please check that mysqlbackup run completes successfully. At the end of a successful ‘backup‘ run mysqlbackup prints "mysqlbackup completed OK!". 150602 13:17:52 mysqlbackup: INFO: MEB logfile dbeated at /root/2015-06-02_13-17-52/meta/MEB_2015-06-02.13-17-52_inc_backup.log 150602 13:17:52 mysqlbackup: INFO: Using start_lsn=171444289872, calculated from backup_variables.txt file of indbemental-base backup. -------------------------------------------------------------------- Server Repository Options: -------------------------------------------------------------------- datadir = /9tong/mysql/data/3003/ innodb_data_home_dir = innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /9tong/mysql/data/3003/ innodb_log_files_in_group = 2 innodb_log_file_size = 4294967296 innodb_page_size = 16384 innodb_checksum_algorithm = innodb innodb_undo_directory = /9tong/mysql/data/3003/ innodb_undo_tablespaces = 0 innodb_undo_logs = 128 -------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /root/2015-06-02_13-17-52/datadir innodb_data_home_dir = /root/2015-06-02_13-17-52/datadir innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /root/2015-06-02_13-17-52/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 4294967296 innodb_page_size = 16384 innodb_checksum_algorithm = innodb innodb_undo_directory = /root/2015-06-02_13-17-52/datadir innodb_undo_tablespaces = 0 innodb_undo_logs = 128 mysqlbackup: INFO: Unique generated backup id for this is 14332222728145270 mysqlbackup: INFO: dbeating 14 buffers each of size 16908288. 150602 13:17:55 mysqlbackup: INFO: Indbemental Backup operation starts with following threads 1 read-threads 6 process-threads 1 write-threads 150602 13:17:55 mysqlbackup: INFO: System tablespace file format is Antelope. 150602 13:17:55 mysqlbackup: INFO: Starting to copy all innodb files... mysqlbackup: INFO: Could not find binlog index file. binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible. If this is online backup then server may not have started with --log-bin. You may specify its location with --log-bin-index option. 150602 13:17:55 mysqlbackup: INFO: Found checkpoint at lsn 171829647523. 150602 13:17:55 mysqlbackup: INFO: Starting log scan from lsn 171829647360. 150602 13:17:55 mysqlbackup: INFO: Copying log... 150602 13:17:55 mysqlbackup: INFO: Copying /9tong/mysql/data/3003/ibdata1 (Antelope file format). 150602 13:17:55 mysqlbackup: INFO: Log copied, lsn 171829647523. mysqlbackup: Progress in MB: 200 400 600 800 1000 1200 1400 1600 1800 2000 2200 2400 2600 2800 3000 3200 3400 3600 3800 4000 4200 4400 4600 4800 5000 5200 5400 5600 5800 6000 6200 6400 6600 6800 7000 7200 7400 7600 7800 8000 8200 8400 8600 8800 9000 9200 9400 9600 9800 10000 10200 10400 10600 10800 11000 11200 11400 11600 11800 12000 12200 12400 12600 12800 13000 13200 13400 13600 13800 14000 14200 14400 14600 14800 15000 15200 15400 15600 15800 16000 16200 16400 16600 16800 17000 17200 17400 17600 17800 18000 18200 18400 18600 18800 19000 19200 150602 13:21:42 mysqlbackup: INFO: Completing the copy of innodb files. 150602 13:21:43 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server. 150602 13:21:43 mysqlbackup: INFO: Starting to lock all the tables... 150602 13:21:43 mysqlbackup: INFO: All tables are locked and flushed to disk 150602 13:21:43 mysqlbackup: INFO: Opening backup source directory ‘/9tong/mysql/data/3003/‘ 150602 13:21:43 mysqlbackup: INFO: Starting to backup all non-innodb files in subdirectories of ‘/9tong/mysql/data/3003/‘ 150602 13:21:43 mysqlbackup: INFO: Copying the database directory ‘9tong_ur‘ 150602 13:21:45 mysqlbackup: INFO: Copying the database directory ‘9tong_user‘ 150602 13:21:46 mysqlbackup: INFO: Copying the database directory ‘aclocal‘ 150602 13:21:46 mysqlbackup: INFO: Copying the database directory ‘mysql‘ 150602 13:21:46 mysqlbackup: INFO: Copying the database directory ‘performance_schema‘ 150602 13:21:46 mysqlbackup: INFO: Copying the database directory ‘ps_helper‘ 150602 13:21:47 mysqlbackup: INFO: Copying the database directory ‘sys‘ 150602 13:21:48 mysqlbackup: INFO: Copying the database directory ‘test‘ 150602 13:21:48 mysqlbackup: INFO: Copying the database directory ‘zxl‘ 150602 13:21:48 mysqlbackup: INFO: Completing the copy of all non-innodb files. 150602 13:21:49 mysqlbackup: INFO: A copied database page was modified at 171829647523. (This is the highest lsn found on page) Scanned log up to lsn 171829647523. Was able to parse the log up to lsn 171829647523. Maximum page number for a log record 0 150602 13:21:49 mysqlbackup: INFO: All tables unlocked 150602 13:21:49 mysqlbackup: INFO: All MySQL tables were locked for 5.470 seconds. 150602 13:21:49 mysqlbackup: INFO: Reading all global variables from the server. 150602 13:21:49 mysqlbackup: INFO: Completed reading of all global variables from the server. 150602 13:21:49 mysqlbackup: INFO: dbeating server config files server-my.cnf and server-all.cnf in /root/2015-06-02_13-17-52 150602 13:21:49 mysqlbackup: INFO: Indbemental Backup operation completed successfully. 150602 13:21:49 mysqlbackup: INFO: Backup dbeated in directory ‘/root/2015-06-02_13-17-52‘ 150602 13:21:49 mysqlbackup: INFO: Backup contains changes from lsn 171444289873 to lsn 171829647523 ------------------------------------------------------------- Parameters Summary ------------------------------------------------------------- Start LSN : 171444289873 End LSN : 171829647523 ------------------------------------------------------------- [root@dbbackup ~]# du -sh 2015-06-02_13-17-52/ 316M 2015-06-02_13-17-52/ 增量备份还是比较快的，4分钟完成了 参数： --indbemental:代表为增量备份 --indbemental-backup-dir:增量备份存放到哪个路径下 --indbemental-base: 增量备份的基础备份或增量备份的文件 重要：增量备份的前提必须要有一份完整的全备数据。如果是从一台机器上已经备过来的数据，不能直接用增量备份的，还需要先在导入数据的这台机器上，做一份完全备份，在操作增量备份。因为导入数据的时候会读取数据库存储的LSN，LSN不一致会出现如下错误： mysqlbackup --defaults-file=/etc/my.cnf --socket=/tmp/mysql3001.sock --with-timestamp --indbemental --indbemental-backup-dir=/root --indbemental-base=dir:/usr/loacal/2015-04-07_12-28-31 backup mysqlbackup: ERROR: Log scan was only able to reach to 1630720, but a copied database page was modified at 171444286878. mysqlbackup: INFO: LSN 171444286878 found in the file /9tong/mysql/data/3003/ibdata1 150602 12:05:36 mysqlbackup: INFO: All tables unlocked 150602 12:05:36 mysqlbackup: INFO: All MySQL tables were locked for 8.602 seconds. 150602 12:05:36 mysqlbackup: INFO: Reading all global variables from the server. 150602 12:05:36 mysqlbackup: INFO: Completed reading of all global variables from the server. 150602 12:05:36 mysqlbackup: INFO: dbeating server config files server-my.cnf and server-all.cnf in /145mysql/2015-06-02_11-56-53 mysqlbackup failed with errors!
五、第一次增量还原
为了验证增量还原的可靠性删除之前创建的表 drop table user_account2016; 1. 全备检测匹配释放事务日志 mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/root/015-06-02_12-20-00/ apply-log 2.检测匹配释放第一次的增量备份 mysqlbackup --backup-dir=/root/2015-06-02_12-20-00/ --indbemental-backup-dir=/root/2015-06-02_13-17-52/ apply-indbemental-backup 3、进行物理文件复制 [root@dbbackup meta]# mysqlbackup --defaults-file=/etc/my.cnf --datadir=/9tong/mysql/data/3003/ --backup-dir=/root/2015-06-02_12-20-00/ copy-back MySQL Enterprise Backup version 3.11.1 Linux-2.6.18-194.el5-x86_64 [2014/11/04] Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... mysqlbackup --defaults-file=/etc/my.cnf --datadir=/9tong/mysql/data/3003/ --backup-dir=/root/2015-06-02_12-20-00/ copy-back mysqlbackup: INFO: IMPORTANT: Please check that mysqlbackup run completes successfully. At the end of a successful ‘copy-back‘ run mysqlbackup prints "mysqlbackup completed OK!". 150602 13:40:13 mysqlbackup: INFO: MEB logfile dbeated at /root/2015-06-02_12-20-00/meta/MEB_2015-06-02.13-40-13_copy_back.log mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_data_file_path parameter might have a different default. In that case you need to add ‘innodb_data_file_path=ibdata1:12M:autoextend‘ to the target server configuration. mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_log_files_in_group parameter might have a different default. In that case you need to add ‘innodb_log_files_in_group=2‘ to the target server configuration. mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_log_file_size parameter might have a different default. In that case you need to add ‘innodb_log_file_size=4294967296‘ to the target server configuration. -------------------------------------------------------------------- Server Repository Options: -------------------------------------------------------------------- datadir = /9tong/mysql/data/3003/ innodb_data_home_dir = /9tong/mysql/data/3003/ innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /9tong/mysql/data/3003/ innodb_log_files_in_group = 2 innodb_log_file_size = 4294967296 innodb_page_size = Null innodb_checksum_algorithm = innodb -------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /root/2015-06-02_12-20-00/datadir innodb_data_home_dir = /root/2015-06-02_12-20-00/datadir innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /root/2015-06-02_12-20-00/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 4294967296 innodb_page_size = 16384 innodb_checksum_algorithm = innodb mysqlbackup: INFO: dbeating 14 buffers each of size 16777216. 150602 13:40:13 mysqlbackup: INFO: Copy-back operation starts with following threads 1 read-threads 1 write-threads mysqlbackup: INFO: Could not find binlog index file. binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible. If this is online backup then server may not have started with --log-bin. You may specify its location with --log-bin-index option. 150602 13:40:13 mysqlbackup: INFO: Copying /root/2015-06-02_12-20-00/datadir/ibdata1 (to ‘/9tong/mysql/data/3003‘). mysqlbackup: Progress in MB: 200 400 600 800 1000 1200 1400 1600 1800 2000 2200 2400 2600 2800 3000 3200 3400 3600 3800 4000 4200 4400 4600 4800 5000 5200 5400 5600 5800 6000 6200 6400 6600 6800 7000 7200 7400 7600 7800 8000 8200 8400 8600 8800 9000 9200 94009600 9800 10000 10200 10400 10600 10800 11000 11200 11400 11600 11800 12000 12200 12400 12600 12800 13000 13200 13400 13600 13800 14000 14200 14400 14600 14800 15000 15200 15400 15600 15800 16000 16200 16400 16600 16800 17000 17200 17400 17600 17800 18000 18200 18400 18600 18800 19000 19200 150602 13:48:35 mysqlbackup: INFO: Copying the database directory ‘9tong_ur‘ 150602 13:48:39 mysqlbackup: INFO: Copying the database directory ‘9tong_user‘ 150602 13:48:39 mysqlbackup: INFO: Copying the database directory ‘aclocal‘ 150602 13:48:39 mysqlbackup: INFO: Copying the database directory ‘mysql‘ 150602 13:48:40 mysqlbackup: INFO: Copying the database directory ‘performance_schema‘ 150602 13:48:40 mysqlbackup: INFO: Copying the database directory ‘ps_helper‘ 150602 13:48:41 mysqlbackup: INFO: Copying the database directory ‘sys‘ 150602 13:48:42 mysqlbackup: INFO: Copying the database directory ‘test‘ 150602 13:48:42 mysqlbackup: INFO: Copying the database directory ‘zxl‘ 150602 13:48:42 mysqlbackup: INFO: Completing the copy of all non-innodb files. 150602 13:48:42 mysqlbackup: INFO: Copying the log file ‘ib_logfile0‘ mysqlbackup: Progress in MB: 19400 19600 19800 20000 20200 20400 20600 20800 21000 21200 21400 21600 21800 22000 22200 22400 22600 22800 23000 23200 150602 13:50:38 mysqlbackup: INFO: Copying the log file ‘ib_logfile1‘ mysqlbackup: Progress in MB: 23400 23600 23800 24000 24200 24400 24600 24800 25000 25200 25400 25600 25800 26000 26200 26400 26600 26800 27000 27200 27400 150602 13:51:45 mysqlbackup: INFO: dbeating server config files server-my.cnf and server-all.cnf in /9tong/mysql/data/3003/ 150602 13:51:45 mysqlbackup: INFO: Copy-back operation completed successfully. 150602 13:51:45 mysqlbackup: INFO: Finished copying backup files to ‘/9tong/mysql/data/3003/‘ mysqlbackup completed OK! with 3 warnings
4、验证增量备份是否正常
mysql> desc user_account2016 ; +--------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | uid | int(11) | NO | | 0 | | | account | varchar(50) | NO | | | | | pswd | varchar(45) | NO | | | | | realName | varchar(50) | NO | | | | | verifyCode | decimal(10,0) | NO | | 0 | | | verifyTimes | decimal(1,0) | NO | | 0 | | | registerTime | decimal(16,0) | NO | | 0 | | | activeTime | decimal(16,0) | NO | | 0 | | | lastActive | decimal(16,0) | NO | | 0 | | +--------------+---------------+------+-----+---------+-------+ 10 rows in set (0.00 sec) 正常显示，增量备份成功完成。
注：1、首先检查匹配释放全备事物日志文件（当然如果备份中使用了backup-and-apply-log在备份的时候已经检测匹配了，就不需要这一步了。由于做全量备份的时候已经匹配了，故第一步操作可以省去）
2、第一次增量备份的文件释放到全备文件里面（首先会进入事物日志，然后是表空间），所以--backup-dir指向全备目录（基于LSN点向后增量）
5、第二次增量备份
1、做之前先操作以下文件 mysql> drop table user_active_60 ; Query OK, 0 rows affected (0.12 sec) mysql> drop table user_active_15; Query OK, 0 rows affected (0.04 sec) mysql> drop table user_active_30; Query OK, 0 rows affected (0.07 sec) 2、第二次备份操作 [root@dbbackup meta]# mysqlbackup --backup-dir=/root/2015-06-02_12-20-00/ --indbemental-backup-dir=/root/2015-06-02_13-17-52/ apply-indbemental-backup MySQL Enterprise Backup version 3.11.1 Linux-2.6.18-194.el5-x86_64 [2014/11/04] Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... mysqlbackup --backup-dir=/root/2015-06-02_12-20-00/ --indbemental-backup-dir=/root/2015-06-02_13-17-52/ apply-indbemental-backup mysqlbackup: INFO: IMPORTANT: Please check that mysqlbackup run completes successfully. At the end of a successful ‘apply-indbemental-backup‘ run mysqlbackup prints "mysqlbackup completed OK!". 150602 13:32:04 mysqlbackup: INFO: MEB logfile dbeated at /root/2015-06-02_12-20-00/meta/MEB_2015-06-02.13-32-04_apply_inc.log -------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /root/2015-06-02_12-20-00/datadir innodb_data_home_dir = /root/2015-06-02_12-20-00/datadir innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /root/2015-06-02_12-20-00/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 4294967296 innodb_page_size = 16384 innodb_checksum_algorithm = innodb mysqlbackup: INFO: dbeating 14 buffers each of size 16908288. 150602 13:32:04 mysqlbackup: INFO: Apply-Indbemental-Pages operation starts with following threads 1 read-threads 1 write-threads mysqlbackup: INFO: Could not find binlog index file. binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible. If this is online backup then server may not have started with --log-bin. You may specify its location with --log-bin-index option. 150602 13:32:04 mysqlbackup: INFO: Starting deletion of obsolete ibd files in backup-dir... 150602 13:32:04 mysqlbackup: INFO: Deleting dropped ibd files. 150602 13:32:04 mysqlbackup: INFO: Deletion of obsolete ibd files in backup-dir is completed. 150602 13:32:04 mysqlbackup: INFO: Deleting non-innodb files in backup-dir... 150602 13:32:04 mysqlbackup: INFO: Applying diff pages from `/root/2015-06-02_13-17-52/datadir/ibdata1` to `/root/2015-06-02_12-20-00/datadir/ibdata1`. mysqlbackup: Progress in MB: 200 150602 13:32:13 mysqlbackup: INFO: Applying indbemental pages to innodb data files... 150602 13:32:13 mysqlbackup: INFO: Starting to copy all non-innodb files in subdirectories of ‘/root/2015-06-02_13-17-52/datadir‘ 150602 13:32:13 mysqlbackup: INFO: Copying the database directory ‘9tong_ur‘ 150602 13:32:15 mysqlbackup: INFO: Copying the database directory ‘9tong_user‘ 150602 13:32:15 mysqlbackup: INFO: Copying the database directory ‘aclocal‘ 150602 13:32:15 mysqlbackup: INFO: Copying the database directory ‘mysql‘ 150602 13:32:16 mysqlbackup: INFO: Copying the database directory ‘performance_schema‘ 150602 13:32:16 mysqlbackup: INFO: Copying the database directory ‘ps_helper‘ 150602 13:32:17 mysqlbackup: INFO: Copying the database directory ‘sys‘ 150602 13:32:18 mysqlbackup: INFO: Copying the database directory ‘test‘ 150602 13:32:18 mysqlbackup: INFO: Copying the database directory ‘zxl‘ 150602 13:32:18 mysqlbackup: INFO: Completing the copy of all non-innodb files. 150602 13:32:18 mysqlbackup: INFO: Copying server configuration files to backup_dir. 150602 13:32:19 mysqlbackup: INFO: Apply-Indbemental-Pages operation completed successfully. mysqlbackup: INFO: dbeating 14 buffers each of size 65536. 150602 13:32:19 mysqlbackup: INFO: Apply-log operation starts with following threads 1 read-threads 1 process-threads mysqlbackup: INFO: Using up to 100 MB of memory. 150602 13:32:20 mysqlbackup: INFO: ibbackup_logfile‘s dbeation parameters: start lsn 171829647360, end lsn 171829647523, start checkpoint 171829647523. InnoDB: Doing recovery: scanned up to log sequence number 171829647523 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 4294967296 InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200 2300 2400 2500 2600 2700 2800 2900 3000 3100 3200 3300 3400 3500 3600 3700 3800 3900 4000 mysqlbackup: INFO: InnoDB: Setting log file size to 4294967296 InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200 2300 2400 2500 2600 2700 2800 2900 3000 3100 3200 3300 3400 3500 3600 3700 3800 3900 4000 150602 13:34:29 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to lsn 171829647523. mysqlbackup: INFO: Last MySQL binlog file position 0 791363474, file name mysql-bin.000023 150602 13:34:29 mysqlbackup: INFO: The first data file is ‘/root/2015-06-02_12-20-00/datadir/ibdata1‘ and the new dbeated log files are at ‘/root/2015-06-02_12-20-00/datadir‘ 150602 13:34:29 mysqlbackup: INFO: Apply-log operation completed successfully. 150602 13:34:29 mysqlbackup: INFO: Full backup prepared for recovery successfully. mysqlbackup completed OK! 3、检测匹配释放第二次的增量备份 [root@dbbackup meta]# mysqlbackup --backup-dir=/root/2015-06-02_12-20-00/ --indbemental-backup-dir=/root/2015-06-02_16-37-40/ apply-indbemental-backup MySQL Enterprise Backup version 3.11.1 Linux-2.6.18-194.el5-x86_64 [2014/11/04] Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... mysqlbackup --backup-dir=/root/2015-06-02_12-20-00/ --indbemental-backup-dir=/root/2015-06-02_16-37-40/ apply-indbemental-backup mysqlbackup: INFO: IMPORTANT: Please check that mysqlbackup run completes successfully. At the end of a successful ‘apply-indbemental-backup‘ run mysqlbackup prints "mysqlbackup completed OK!". 150602 17:18:03 mysqlbackup: INFO: MEB logfile dbeated at /root/2015-06-02_12-20-00/meta/MEB_2015-06-02.17-18-03_apply_inc.log -------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /root/2015-06-02_12-20-00/datadir innodb_data_home_dir = /root/2015-06-02_12-20-00/datadir innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /root/2015-06-02_12-20-00/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 4294967296 innodb_page_size = 16384 innodb_checksum_algorithm = innodb mysqlbackup: INFO: dbeating 14 buffers each of size 16908288. 150602 17:18:03 mysqlbackup: INFO: Apply-Indbemental-Pages operation starts with following threads 1 read-threads 1 write-threads mysqlbackup: INFO: Could not find binlog index file. binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible. If this is online backup then server may not have started with --log-bin. You may specify its location with --log-bin-index option. 150602 17:18:03 mysqlbackup: INFO: Starting deletion of obsolete ibd files in backup-dir... 150602 17:18:03 mysqlbackup: INFO: Deleting dropped ibd files. 150602 17:18:03 mysqlbackup: INFO: Deletion of obsolete ibd files in backup-dir is completed. 150602 17:18:03 mysqlbackup: INFO: Deleting non-innodb files in backup-dir... 150602 17:18:03 mysqlbackup: INFO: Applying diff pages from `/root/2015-06-02_16-37-40/datadir/ibdata1` to `/root/2015-06-02_12-20-00/datadir/ibdata1`. 150602 17:18:03 mysqlbackup: INFO: Applying indbemental pages to innodb data files... 150602 17:18:03 mysqlbackup: INFO: Starting to copy all non-innodb files in subdirectories of ‘/root/2015-06-02_16-37-40/datadir‘ 150602 17:18:03 mysqlbackup: INFO: Copying the database directory ‘9tong_ur‘ 150602 17:18:05 mysqlbackup: INFO: Copying the database directory ‘9tong_user‘ 150602 17:18:05 mysqlbackup: INFO: Copying the database directory ‘aclocal‘ 150602 17:18:05 mysqlbackup: INFO: Copying the database directory ‘mysql‘ 150602 17:18:06 mysqlbackup: INFO: Copying the database directory ‘performance_schema‘ 150602 17:18:06 mysqlbackup: INFO: Copying the database directory ‘ps_helper‘ 150602 17:18:07 mysqlbackup: INFO: Copying the database directory ‘sys‘ 150602 17:18:07 mysqlbackup: INFO: Copying the database directory ‘test‘ 150602 17:18:07 mysqlbackup: INFO: Copying the database directory ‘zxl‘ 150602 17:18:07 mysqlbackup: INFO: Completing the copy of all non-innodb files. 150602 17:18:07 mysqlbackup: INFO: Copying server configuration files to backup_dir. 150602 17:18:08 mysqlbackup: INFO: Apply-Indbemental-Pages operation completed successfully. mysqlbackup: INFO: dbeating 14 buffers each of size 65536. 150602 17:18:08 mysqlbackup: INFO: Apply-log operation starts with following threads 1 read-threads 1 process-threads mysqlbackup: INFO: Using up to 100 MB of memory. 150602 17:18:09 mysqlbackup: INFO: ibbackup_logfile‘s dbeation parameters: start lsn 171829666304, end lsn 171829666420, start checkpoint 171829666420. InnoDB: Doing recovery: scanned up to log sequence number 171829666420 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 4294967296 InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200 2300 2400 2500 2600 2700 2800 2900 3000 3100 3200 3300 3400 3500 3600 3700 3800 3900 4000 mysqlbackup: INFO: InnoDB: Setting log file size to 4294967296 InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200 2300 2400 2500 2600 2700 2800 2900 3000 3100 3200 3300 3400 3500 3600 3700 3800 3900 4000 150602 17:20:11 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to lsn 171829666420. mysqlbackup: INFO: Last MySQL binlog file position 0 791363474, file name mysql-bin.000023 150602 17:20:11 mysqlbackup: INFO: The first data file is ‘/root/2015-06-02_12-20-00/datadir/ibdata1‘ and the new dbeated log files are at ‘/root/2015-06-02_12-20-00/datadir‘ 150602 17:20:11 mysqlbackup: INFO: Apply-log operation completed successfully. 150602 17:20:11 mysqlbackup: INFO: Full backup prepared for recovery successfully. mysqlbackup completed OK! 4、进行物理文件复制 [root@dbbackup meta]# mysqlbackup --defaults-file=/etc/my.cnf --datadir=/9tong/mysql/data/3003/ --backup-dir=/root/2015-06-02_12-20-00/ copy-back MySQL Enterprise Backup version 3.11.1 Linux-2.6.18-194.el5-x86_64 [2014/11/04] Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... mysqlbackup --defaults-file=/etc/my.cnf --datadir=/9tong/mysql/data/3003/ --backup-dir=/root/2015-06-02_12-20-00/ copy-back mysqlbackup: INFO: IMPORTANT: Please check that mysqlbackup run completes successfully. At the end of a successful ‘copy-back‘ run mysqlbackup prints "mysqlbackup completed OK!". 150602 17:21:54 mysqlbackup: INFO: MEB logfile dbeated at /root/2015-06-02_12-20-00/meta/MEB_2015-06-02.17-21-54_copy_back.log mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_data_file_path parameter might have a different default. In that case you need to add ‘innodb_data_file_path=ibdata1:12M:autoextend‘ to the target server configuration. mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_log_files_in_group parameter might have a different default. In that case you need to add ‘innodb_log_files_in_group=2‘ to the target server configuration. mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_log_file_size parameter might have a different default. In that case you need to add ‘innodb_log_file_size=4294967296‘ to the target server configuration. -------------------------------------------------------------------- Server Repository Options: -------------------------------------------------------------------- datadir = /9tong/mysql/data/3003/ innodb_data_home_dir = /9tong/mysql/data/3003/ innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /9tong/mysql/data/3003/ innodb_log_files_in_group = 2 innodb_log_file_size = 4294967296 innodb_page_size = Null innodb_checksum_algorithm = innodb -------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /root/2015-06-02_12-20-00/datadir innodb_data_home_dir = /root/2015-06-02_12-20-00/datadir innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /root/2015-06-02_12-20-00/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 4294967296 innodb_page_size = 16384 innodb_checksum_algorithm = innodb mysqlbackup: INFO: dbeating 14 buffers each of size 16777216. 150602 17:21:54 mysqlbackup: INFO: Copy-back operation starts with following threads 1 read-threads 1 write-threads mysqlbackup: INFO: Could not find binlog index file. binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible. If this is online backup then server may not have started with --log-bin. You may specify its location with --log-bin-index option. 150602 17:21:54 mysqlbackup: INFO: Copying /root/2015-06-02_12-20-00/datadir/ibdata1 (to ‘/9tong/mysql/data/3003‘). mysqlbackup: Progress in MB: 200 400 600 800 1000 1200 1400 1600 1800 2000 2200 2400 2600 2800 3000 3200 3400 3600 3800 4000 4200 4400 4600 4800 5000 5200 5400 5600 5800 6000 6200 6400 6600 6800 7000 7200 7400 7600 7800 8000 8200 8400 8600 8800 9000 9200 9400 9600 9800 10000 10200 10400 10600 10800 11000 11200 11400 11600 11800 12000 12200 12400 12600 12800 13000 13200 13400 13600 13800 14000 14200 14400 14600 14800 15000 15200 15400 15600 15800 16000 16200 16400 16600 16800 17000 17200 17400 17600 17800 18000 18200 18400 18600 18800 19000 19200 150602 17:30:21 mysqlbackup: INFO: Copying the database directory ‘9tong_ur‘ 150602 17:30:24 mysqlbackup: INFO: Copying the database directory ‘9tong_user‘ 150602 17:30:24 mysqlbackup: INFO: Copying the database directory ‘aclocal‘ 150602 17:30:24 mysqlbackup: INFO: Copying the database directory ‘mysql‘ 150602 17:30:25 mysqlbackup: INFO: Copying the database directory ‘performance_schema‘ 150602 17:30:26 mysqlbackup: INFO: Copying the database directory ‘ps_helper‘ 150602 17:30:26 mysqlbackup: INFO: Copying the database directory ‘sys‘ 150602 17:30:27 mysqlbackup: INFO: Copying the database directory ‘test‘ 150602 17:30:27 mysqlbackup: INFO: Copying the database directory ‘zxl‘ 150602 17:30:27 mysqlbackup: INFO: Completing the copy of all non-innodb files. 150602 17:30:27 mysqlbackup: INFO: Copying the log file ‘ib_logfile0‘ mysqlbackup: Progress in MB: 19400 19600 19800 20000 20200 20400 20600 20800 21000 21200 21400 21600 21800 22000 22200 22400 22600 22800 23000 23200 150602 17:32:21 mysqlbackup: INFO: Copying the log file ‘ib_logfile1‘ mysqlbackup: Progress in MB: 23400 23600 23800 24000 24200 24400 24600 24800 25000 25200 25400 25600 25800 26000 26200 26400 26600 26800 27000 27200 27400 150602 17:33:23 mysqlbackup: INFO: dbeating server config files server-my.cnf and server-all.cnf in /9tong/mysql/data/3003/ 150602 17:33:23 mysqlbackup: INFO: Copy-back operation completed successfully. 150602 17:33:23 mysqlbackup: INFO: Finished copying backup files to ‘/9tong/mysql/data/3003/‘ mysqlbackup completed OK! with 3 warnings [root@dbbackup meta]# mysqlbackup --defaults-file=/etc/my.cnf --datadir=/9tong/mysql/data/3003/ --backup-dir=/root/2015-06-02_12-20-00/ copy-back MySQL Enterprise Backup version 3.11.1 Linux-2.6.18-194.el5-x86_64 [2014/11/04] Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... mysqlbackup --defaults-file=/etc/my.cnf --datadir=/9tong/mysql/data/3003/ --backup-dir=/root/2015-06-02_12-20-00/ copy-back mysqlbackup: INFO: IMPORTANT: Please check that mysqlbackup run completes successfully. At the end of a successful ‘copy-back‘ run mysqlbackup prints "mysqlbackup completed OK!". 150602 17:37:26 mysqlbackup: INFO: MEB logfile dbeated at /root/2015-06-02_12-20-00/meta/MEB_2015-06-02.17-37-26_copy_back.log mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_data_file_path parameter might have a different default. In that case you need to add ‘innodb_data_file_path=ibdata1:12M:autoextend‘ to the target server configuration. mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_log_files_in_group parameter might have a different default. In that case you need to add ‘innodb_log_files_in_group=2‘ to the target server configuration. mysqlbackup: WARNING: If you restore to a server of a different version, the innodb_log_file_size parameter might have a different default. In that case you need to add ‘innodb_log_file_size=4294967296‘ to the target server configuration. -------------------------------------------------------------------- Server Repository Options: -------------------------------------------------------------------- datadir = /9tong/mysql/data/3003/ innodb_data_home_dir = /9tong/mysql/data/3003/ innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /9tong/mysql/data/3003/ innodb_log_files_in_group = 2 innodb_log_file_size = 4294967296 innodb_page_size = Null innodb_checksum_algorithm = innodb -------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /root/2015-06-02_12-20-00/datadir innodb_data_home_dir = /root/2015-06-02_12-20-00/datadir innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /root/2015-06-02_12-20-00/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 4294967296 innodb_page_size = 16384 innodb_checksum_algorithm = innodb mysqlbackup: INFO: dbeating 14 buffers each of size 16777216. 150602 17:37:26 mysqlbackup: INFO: Copy-back operation starts with following threads 1 read-threads 1 write-threads mysqlbackup: INFO: Could not find binlog index file. binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible. If this is online backup then server may not have started with --log-bin. You may specify its location with --log-bin-index option. 150602 17:37:26 mysqlbackup: INFO: Copying /root/2015-06-02_12-20-00/datadir/ibdata1 (to ‘/9tong/mysql/data/3003‘). mysqlbackup: Progress in MB: 200 400 600 800 1000 1200 1400 1600 1800 2000 2200 2400 2600 2800 3000 3200 3400 3600 3800 4000 4200 4400 4600 4800 5000 5200 5400 5600 5800 6000 6200 6400 6600 6800 7000 7200 7400 7600 7800 8000 8200 8400 8600 8800 9000 9200 9400 9600 9800 10000 10200 10400 10600 10800 11000 11200 11400 11600 11800 12000 12200 12400 12600 12800 13000 13200 13400 13600 13800 14000 14200 14400 14600 14800 15000 15200 15400 15600 15800 16000 16200 16400 16600 16800 17000 17200 17400 17600 17800 18000 18200 18400 18600 18800 19000 19200 150602 17:45:52 mysqlbackup: INFO: Copying the database directory ‘9tong_ur‘ 150602 17:45:54 mysqlbackup: INFO: Copying the database directory ‘9tong_user‘ 150602 17:45:54 mysqlbackup: INFO: Copying the database directory ‘aclocal‘ 150602 17:45:54 mysqlbackup: INFO: Copying the database directory ‘mysql‘ 150602 17:45:55 mysqlbackup: INFO: Copying the database directory ‘performance_schema‘ 150602 17:45:55 mysqlbackup: INFO: Copying the database directory ‘ps_helper‘ 150602 17:45:56 mysqlbackup: INFO: Copying the database directory ‘sys‘ 150602 17:45:57 mysqlbackup: INFO: Copying the database directory ‘test‘ 150602 17:45:57 mysqlbackup: INFO: Copying the database directory ‘zxl‘ 150602 17:45:57 mysqlbackup: INFO: Completing the copy of all non-innodb files. 150602 17:45:57 mysqlbackup: INFO: Copying the log file ‘ib_logfile0‘ mysqlbackup: Progress in MB: 19400 19600 19800 20000 20200 20400 20600 20800 21000 21200 21400 21600 21800 22000 22200 22400 22600 22800 23000 23200 150602 17:47:50 mysqlbackup: INFO: Copying the log file ‘ib_logfile1‘ mysqlbackup: Progress in MB: 23400 23600 23800 24000 24200 24400 24600 24800 25000 25200 25400 25600 25800 26000 26200 26400 26600 26800 27000 27200 27400 150602 17:49:46 mysqlbackup: INFO: dbeating server config files server-my.cnf and server-all.cnf in /9tong/mysql/data/3003/ 150602 17:49:46 mysqlbackup: INFO: Copy-back operation completed successfully. 150602 17:49:46 mysqlbackup: INFO: Finished copying backup files to ‘/9tong/mysql/data/3003/‘ mysqlbackup completed OK! with 3 warnings 数据验证 mysql> show tables; +-----------------------------+ | Tables_in_9tong_user | +-----------------------------+ | account_extension | | cast_users | | industry | | news_cast | | news_cast_history | | notactive_user | | product_info_temp | | sys_users | | sys_verify | | user_account | | user_account2016 | | user_active_15 | | user_active_30 | | user_active_60 | | user_bargain | | user_bid | | user_education | | user_info | | user_purchase | | user_purchase_choose | | user_purchase_follow | | user_purchase_message_click | | user_remark | | user_sdr | | user_sdr_visitor | | user_verify_sms | | user_visitor | | user_works | +-----------------------------+ 删除的3张表都出现了，说明增量备份成功。 注：第二次增量备份的文件也是释放到全备文件里面，（首先会进入事物日志，然后是表空间），因为第一次的增量备份后，全库里面已经有了第一次的LSN点，所以二次还原的时间同样指向全备文件里面使LSN点在外后增加。
结论：增量备份导入所花费的时间和全量差不多，而且增量的越多，细节处理也越多。因此建议是全量做一次，增量少做。切记导入数据还原后。注意权限的修改，及重启数据库生效。
#################5
http://blog.itpub.net/12679300/viewspace-2145129/ mysqlbackup软件的下载和使用
1.1 mysqlbackup软件的说明undefined
Mysql的备份方法有很多种，大部分企业当数据量很小的时候都是选择mysqldump导出数据库的方式来进行备份，但是当数据量较多的时候，就不建议用这种方法进行。
公司的Mysql数据库300GB，用mysqldump的方法进行恢复的时候，居然用了整整的一个星期，如果真正在灾难的时候需要恢复，那简直就是灾难啊。
经过网上的查找和搜索，发现mysql的备份软件确实太多，第三方的机构也有提供了相应的备份软件，ORACLE公司也提供了针对企业的备份软件MySQL Enterprise Backup简称：mysqlbackup。
考虑到软件的生命周期和软件的文档考虑，还是使用mysqlbakcup，原因如下：
mysqlbackup从3.5到4.1版本说明mysql公司一直在更新这个软件，这样随着mysql软件的更新备份软件也会跟着更新，避免出现bug没有解决，同时mysql dba掌握了这么技术后也可以一直延续使用，提高了学习的收益。
在mysql的网站里面有完整的使用手册，这些文档对于整个软件的使用至关重要；
1.2 mysqlbackup软件的下载
mysqlbackup企业版在mysql的开源社区是下载不了的，需要有metalink的账号才能下载。（早期是可以下载的，估计后来用户的体验不错，需要授权购买oracle的服务才可以了）
目前我的环境都还是mysql5.6的，所以选择用3.12的版本（3.12的版本修复了几个重要的bug，建议用这个版本）
1.3 mysqlbackup软件的安装
选择相应的版本进行下载，每台要进行备份的mysql服务器都需要安装这个软件。Mysql提供了两种包，安装的方法也有所不同。常用的安装步骤如下：
sudo rpm -i package_name.rpm
tar xvzf package.tgz
安装的目录如下：/opt/mysql/meb-3.12
其他设置命令使用的快捷方式：ln -s /opt/mysql/meb-3.12/bin/mysqlbackup /usr/bin/mysqlback
二、mysqlbackup工具的介绍
mysqlbackup和mysqldump的主要功能是备份跟还原，mysqlbackup是oracle的企业产品感觉整个产品的思路和oracle的rman很像。mysqlbackup的功能对比成oracle的expdp。
Mysqlbackup提供了备份还原一些企业的功能：提供了更快的备份方式和更快的还原方式，支持在线全备和增倍、备份的压缩、并行备份、备份的加密、数据库的还原和基于时间点的还原。
通过mysqlbackup --help可以查看相关的命令操作。
三、mysqlbackup备份准备工作
本章介绍使用mysqlbakcup的准备工作和使用方法；
3.1 备份的准备工作
备份无小事，在备份之前，需要做好以下的准备工作，保证备份的正常运行。
需要做准备的工作如下几点：
3.1.1 备份位置和空间的配置。
建议备份的目录和数据库文件的目录区分开，避免备份把空间撑爆了而导致系统不能使用；
3.1.2 设置备份策略（备份频率、备份保留周期）
同时计算下每次备份所需要的磁盘空间再根据备份策略合理安排备份的空间；
3.1.3 创建单独的备份用户
其他业务分开账号的使用，数据库作为重要的系统，一般都会每年变更管理密码的，这里单独使用一个避免后期还需要变更；
备份用户所需要的基本权限
- GRANT dbEATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'mysqlbackup'@'localhost';
- GRANT dbEATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'mysqlbackup'@'localhost';
- GRANT REPLICATION CLIENT ON *.* TO 'mysqlbackup'@'localhost';
- GRANT SUPER ON *.* TO 'mysqlbackup'@'localhost';
- GRANT LOCK TABLES, SELECT, dbEATE, DROP, FILE ON *.* TO 'mysqlbackup'@'localhost'
3.2 备份库主要选项
备份其实主要的内容也就是backup、restore和校验。每个阶段使用的主要参数如下：
- Backup operations: backup, backup-and-apply-log, backup-to-image
- Update operations: apply-log, apply-indbemental-backup
- Restore operations: copy-back, copy-back-and-apply-log
- Validation operation: validate Single-file backup
- operations: image-to-backup-dir, backup-dir-to-image, list-image, extract
在3.12.3以下的版本backup-and-apply-log这个备份的时候不能结合参数compress使用。mysqlbackup官方的文档建议使用backup-to-image选项，该方法性能更好，而且备份的文件也更小。
备注：
When used with the apply-log or copy-back-and-apply-log operation, uncompresses a compressed backup before applying the InnoDB log. When used with the copy-back operation,uncompresses a compressed prepared backup (dbeated by the backup-and-apply-log command with the --compress option) before restoring it to a server (only supported for MySQL Enterprise
Backup 3.12.3 and later). backup-to-image Produces a single-file backup holding the backup data. In most cases, single-file backups are preferred over directory backups, which are dbeated using the backup command.
3.3 备份库主要参数
可以通过mysqlbackup --help会显示丰富的参数说明,这边列几个比较常见的参数
参数 | 说明 | 备注 |
--backup-dir | The directory to store the backup data. | |
--backup-image | Specifies the path name of the backup image. | 结合backup-to-image使用 |
--compress | dbeate backup in compressed format. | 默认级别是1 |
--compress-level | Specifies the level of compression. | --compress-level=LEVEL Specify value from 0-9. Specify value 0 to disable compression. Specify value 1 for fastest compression. Specify value 9 for best compression. |
--uncompress | Uncompress the compressed backup before an apply-log,copy-back, or copy-back-and-apply-log operation. | 如果备份是有使用compress，还原的时候就得使用该参数 |
--datadir | Path to mysql server data directory. | |
--defaults-file | Only read default options from the given file. | |
--force | Force overwriting of data, log, or image files, depending onthe operation. | 进行强制覆盖 |
--indbemental | Specifies that the associated backup or backup-to-imageoperation is indbemental. | 增量备份选项 |
--indbemental-backup-dir | Specifies the location under which to store data from anindbemental backup. | 增量备份保存位置 |
--indbemental-base | The specification of base backup for --indbemental option. | 上次完整备份的位置 |
--with-timestamp | dbeate a subdirectory underneath the backup directorywith a name formed from the timestamp of the backupoperation. | 根据时间戳生成一个文件，因为备份的时候需要在一个空的目录保存文件，所以一般建议使用整个参数 |
四、mysqlbackup备份及还原
方法一：使用backup
点击(此处)折叠或打开
- 全备的脚本
- [root@db02 backup]# mysqlbackup --user=mysqlbackup --password=123 --backup-dir=/backup --with-timestamp backup
- 还原的脚本
- [root@db02 backup]# mysqlbackup --backup-dir=/backup/2017-09-18_13-49-11 apply-log #因为在备份期间数据库还在读写，把这期间的log进行应用，达到数据的一致性
- [root@db02 backup]# mysqlbackup --datadir=/data/mysql --backup-dir=/backup/2017-09-18_13-49-11 copy-back
方法二：在不同的时期使用app-log
点击(此处)折叠或打开
- 全备的脚本
- [root@db02 backup]#mysqlbackup --defaults-file=/etc/my.cnf --user=mysqlbackup --password=123 --backup-dir=/backup --with-timestamp backup-and-apply-log
- 还原的脚本
- [root@db02 backup]# mysqlbackup --datadir=/data/mysql --backup-dir=/backup/2017-09-18_13-49-11 copy-back
【注】backup-and-apply-log使用这个脚本，mysql官方文档有以下说明，不能用于增量备份，但是在实验中却是可以的； backup-and-apply-log A combination of backup and apply-log. It cannot be used for an indbemental backup.
方法三：使用backup-to-image的方式
这种方法是mysql官方推荐的，只有产生单个的备份二进制文件，整个备份的大小也比前面两种要小 点击(此处)折叠或打开
- 全备的脚本
- [root@db02 mysql]# mysqlbackup --user=mysqlbackup --password=123 --backup-image=backup.mbi --backup-dir=/backup --with-timestamp backup-to-image
- 还原的脚本
- [root@db02 data]# mysqlbackup --defaults-file=/backup/2017-09-15_17-06-07/server-my.cnf --datadir=/data/mysql --backup-dir=/backup/2017-09-15_17-06-07 copy-back
五、mysqlbackup增量备份及还原
方法一：用bakcup的方法进行的增量备份
点击(此处)折叠或打开
- 备份的操作：先进行全备后进行增备
- mysqlbackup --defaults-file=/etc/my.cnf --user=mysqlbackup --password=123 --backup-dir=/backup --with-timestamp backup #全备
- mysqlbackup --defaults-file=/etc/my.cnf --user=mysqlbackup --password=123 --with-timestamp --indbemental --indbemental-backup-dir=/backup/ --indbemental-base=dir:/backup/2017-09-18_14-51-25 backup #增量备份
- --indbemental-base 为前面全备的路径
点击(此处)折叠或打开
- 还原的操作
- mysqlbackup --backup-dir=/backup/2017-09-18_15-25-59 apply-log #全备的库先apply-log
- mysqlbackup --backup-dir=/backup/2017-09-18_15-25-59 --indbemental-backup-dir=/backup/2017-09-18_15-42-56 apply-indbemental-backup #在步骤一的基础上继续恢复
- mysqlbackup --defaults-file=/etc/my.cnf --force --backup-dir=/backup/2017-09-18_15-25-59/ --datadir=/data/mysql copy-back-and-apply-log #拷贝然后再恢复增备的log
方法二：用
点击(此处)折叠或打开
- 备份的操作：先进行全备后进行增备
- mysqlbackup --user=mysqlbackup --password=123 --backup-image=backup.mbi --backup-dir=/backup --with-timestamp backup-to-image #先备份成image
- mysqlbackup --defaults-file=/etc/my.cnf --user=mysqlbackup --password=123 --with-timestamp --indbemental --indbemental-backup-dir=/backup/ --indbemental-base=dir:/backup/2017-09-18_15-53-57 backup #在原来备份的基础上进行增量备份
点击(此处)折叠或打开
- 还原的操作
- mysqlbackup --backup-dir=/backup/full --backup-image=/backup/2017-09-18_15-53-57/backup.mbi image-to-backup-dir 先转换成方法一的备份文件，后续的步骤都跟前面一样
- mysqlbackup --backup-dir=/backup/full apply-log
- mysqlbackup --backup-dir=/backup/full --indbemental-backup-dir=/backup/2017-09-18_15-56-13 apply-indbemental-backup
- mysqlbackup --defaults-file=/etc/my.cnf --force --backup-dir=/backup/full --datadir=/data/mysql copy-back-and-apply-log
六、其他操作
6.1 关于image备份的一些操作
点击(此处)折叠或打开
- mysqlbackup --backup-image=/backup/2017-09-18_15-53-57/backup.mbi validate 检查备份的有效性；
- mysqlbackup --backup-image=/backup/2017-09-18_15-53-57/backup.mbi list-image 列出备份对象
- mysqlbackup --backup-image=/backup/2017-09-18_15-53-57/backup.mbi extract 进行解压，直接在备份目录里面操作
- mysqlbackup --backup-dir=/backup/backup --backup-image=/backup/2017-09-18_15-53-57/backup.mbi image-to-backup-dir 指定备份目录进行解压
6.2 其他
关于备份的压缩：其实备份的压缩挺好用的，经过测试可以显著的节省备份的空间。虽然在上面测试的时候没有演示，实际上本人是有操作过备份压缩的，而且相对于正常的备份其实并没有消耗很多的时间； 关于image的增量备份：image的增量备份恢复步骤相对全备麻烦了很多，在官方文档里面找了很久是不是还有更便捷的操作，但始终没有找到相关操作说明，如果知道的朋友麻烦留下言，感激不尽； 关于增量备份：增量备份有没有必要了，其实这要看我们系统的重要性、数据增长的速度。目前我们这边最大的系统2天才产生一个G的binlog，整体数据库也不算很大，所以现在每天都是使用全备了。 关于该软件：mysqlbackup其实还有很多的功能的，可以针对表、数据库对象进行备份。同时也可以用于搭建从库，这个功能对于数据库很大的用户是个福音，强烈对剑该功能； 但是由于这个软件是Oracle的企业版，付费用户才能使用的，所以网上相关的资料并不多。但是有很全的官方文档。
############6
mysqldump全量备份+mysqlbinlog二进制日志增量备份
日常的数据备份及恢复测试，是DBA工作重中之重的事情，所以要做好备份及测试，日常的备份常见有mysqldump+binlog备份、xtrabackup+binlog备份，无论那一种，几乎都少不了对binlog的备份，说明了binlog在数据恢复中的重要性，下面做个小测试，是工作中不少运维或者新人DBA容易犯的错。
创建一个测试表tb1
<test>(root@localhost) [xuanzhi]> show dbeate table tb1\G *************************** 1. row *************************** Table: tb1 dbeate Table: dbEATE TABLE `tb1` ( `id` int(10) NOT NULL AUTO_INdbEMENT, `name` char(10) CHARACTER SET latin1 DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) <test>(root@localhost) [xuanzhi]>
往表里插入两条数据
<test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('aa'),('bb'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 <test>(root@localhost) [xuanzhi]> show master logs; +----------------------+-----------+ | Log_name | File_size | +----------------------+-----------+ | localhost-bin.000001 | 329 | +----------------------+-----------+ 1 row in set (0.00 sec) <test>(root@localhost) [xuanzhi]>
对数据备份
如果是xtrabackup备份的话，会在有xtrabackup_binlog_info文件中记录此时备份是到那个binlog文件和pos点的，如果是mysqldump备份，则需要带上--master-data=2这个参数，下面我们的数据量少，用mysqldump备份：
[root@localhost ~]# mysqldump -uroot -p123456 -R --events --triggers=true --master-data=2 --single-transaction xuanzhi > xuanzhi.sql Warning: Using a password on the command line interface can be insecure. [root@localhost ~]# grep -i "CHANGE MASTER" xuanzhi.sql -- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000001', MASTER_LOG_POS=329; [root@localhost ~]#
继续模拟数据库有写入
这个时候是还是写在mysql-bin.000001
<test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('cc'),('dd');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
<test>(root@localhost) [xuanzhi]> show master logs;
+----------------------+-----------+
| Log_name | File_size |
+----------------------+-----------+
| mysql-bin.000001 | 538 |
+----------------------+-----------+
1 row in set (0.00 sec)
<test>(root@localhost) [xuanzhi]> flush logs;
Query OK, 0 rows affected (0.01 sec)
<test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('ee');
Query OK, 1 row affected (0.00 sec)
<test>(root@localhost) [xuanzhi]> show master logs;
+----------------------+-----------+
| Log_name | File_size |
+----------------------+-----------+
| mysql-bin.000001 | 589 || mysql-bin.000002 | 321 |
+----------------------
+-----------+
2 rows in set (0.00 sec) <test>(root@localhost) [xuanzhi]>
上面我们进行flush logs是为了模拟现在已经有多个binlog文件了，恢复时进行多个binlog一起恢复。
模拟误操作
把xunazhi库drop了：
<test>(root@localhost) [xuanzhi]> drop database xuanzhi; Query OK, 1 row affected (0.02 sec) <test>(root@localhost) [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) <test>(root@localhost) [(none)]>
创建数据xuanzhi，把备份导入
<test>(root@localhost) [(none)]> dbeate database xuanzhi; Query OK, 1 row affected (0.00 sec)
[root@localhost ~]# mysql -uroot -p123456 xuanzhi <./xuanzhi.sql # 这个sql文件可以是绝对路径也可以是相对路径 Warning: Using a password on the command line interface can be insecure. [root@localhost ~]#
查看数据
<test>(root@localhost) [(none)]> use xuanzhi Database changed <test>(root@localhost) [xuanzhi]> select * from tb1; +----+------+ | id | name | +----+------+ | 1 | aa | | 2 | bb | +----+------+ 2 rows in set (0.00 sec) <test>(root@localhost) [xuanzhi]>
可以看到备份前的数据恢复了
接下来要结合Binlog来恢复
但前提要找出误操作前的pos点，也就是drop database xuanzhi前的pos点：
[root@localhost ~]# mysqlbinlog -v --base64-output=DECODE-ROWS mysql-bin.000002 |grep -C 10 -i "drop database" ### INSERT INTO `xuanzhi`.`tb1` ### SET ### @1=5 ### @2='ee' # at 290 #170327 21:10:55 server id 1313306 end_log_pos 321 dbC32 0x825a2f99 Xid = 78 COMMIT/*!*/; # at 321 #170327 21:19:25 server id 1313306 end_log_pos 422 dbC32 0x8c139cac Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1490620765/*!*/; drop database xuanzhi /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@localhost ~]# mysql -uroot -p123456 xuanzhi <./xuanzhi.sql Warning: Using a password on the command line interface can be insecure. [root@localhost ~]#
从上面可以看到，误操作前的pos点是321，那我们现在通过binlog来进行数据恢复：
[root@localhost mysql-5.6]# mysqlbinlog --start-position=329 --stop-position=321 mysql-bin.000001 mysql-bin.000002 |mysql -uroot -p123456 xuanzhi
# 这里的mysql-bin.000001和mysql-bin.000002等日志文件要从原来的位置拷贝出来，可以是绝对路径也可以是相对路径
Warning: Using a password on the command line interface can be insecure.
[root@localhost mysql-5.6]#
--start-position是备份后记录下的pos点， --stop-position是误操前的pos点，如果批多个binlog文件，那么start-position是第一个binlog文件的pos点，stop-position是最后一个binlog的pos点，下面我们看下数据是否恢复回来了：
<test>(root@localhost) [xuanzhi]> select * from tb1; +----+------+ | id | name | +----+------+ | 1 | aa | | 2 | bb | | 3 | cc | | 4 | dd | | 5 | ee | +----+------+ 5 rows in set (0.00 sec) <test>(root@localhost) [xuanzhi]>
这里要提的是进行恢复前，要把需要恢复的binlog备份好，或者移动拷贝一份到另一个目录，因为进行数据导入时也会继续写binlog。假如你没有误操作的情况下，就是想测试一下数据的恢复，很多人的操作是导入备份，再从备份里记录的binlog文件名和pos点进行binlog恢复，发现步骤都很完美，也没报错，恢复后就是只有备份时的数据，没有备份后的数据，下面测试一下给大家看：
<test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('aa'),('bb'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 <test>(root@localhost) [xuanzhi]> select * from tb1; +----+------+ | id | name | +----+------+ | 1 | aa | | 2 | bb | +----+------+ 2 rows in set (0.00 sec) <test>(root@localhost) [xuanzhi]> show master logs; +----------------------+-----------+ | Log_name | File_size | +----------------------+-----------+ | localhost-bin.000001 | 329 | +----------------------+-----------+ 1 row in set (0.00 sec) <test>(root@localhost) [xuanzhi]>
进行备份操作：
[root@localhost ~]# mysqldump -uroot -p123456 -R --events --triggers=true --master-data=2 --single-transaction xuanzhi > xuanzhi.sql Warning: Using a password on the command line interface can be insecure. [root@localhost ~]# grep -i "change master" xuanzhi.sql -- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000001', MASTER_LOG_POS=329; [root@localhost ~]#
继续写localhost-bin.000001后进行flush logs生成新的binlog再继续写数据，这里只是想模拟localhost-
<test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('cc'),('dd'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 <test>(root@localhost) [xuanzhi]> flush logs; Query OK, 0 rows affected (0.00 sec) <test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('dd'); Query OK, 1 row affected (0.01 sec) <test>(root@localhost) [xuanzhi]> show master logs; +----------------------+-----------+ | Log_name | File_size | +----------------------+-----------+ | localhost-bin.000001 | 589 | | localhost-bin.000002 | 321 | +----------------------+-----------+ 2 rows in set (0.00 sec) <test>(root@localhost) [xuanzhi]>
下面进行恢复测试，正常来说先把备份导入：
[root@localhost ~]# mysql -uroot -p123456 xuanzhi <./xuanzhi.sql Warning: Using a password on the command line interface can be insecure. [root@localhost ~]#
查看数据，只有备份的那两条记录：
<test>(root@localhost) [xuanzhi]> select * from tb1; +----+------+ | id | name | +----+------+ | 1 | aa | | 2 | bb | +----+------+ 2 rows in set (0.00 sec) <test>(root@localhost) [xuanzhi]>
那现在通过localhost-bin.000001，localhost-bin.000002来恢复后面那3条数据，那么起始pos是那个呢，就是上面备份完后备份文件里的那个pos，我们进到binlog的存放路径：
可以看到备份后的数据是没有恢复回来的。为什么呢？因为导入备份的时候，又开始写binlog了，而你恢复时用的binlog也就是现在导入备份时正在写的binlog。大体过程是这样的：
1、导入备份后，备份的所有操作都写进最后一个binlog了，也就是上面的localhost-bin.000002
2、进行binlog恢复，从备份文件里的pos点开始，按理来说是可以恢复到最新数据的，但是上面导入了备份，导入时的所有操作都会记录到localhost-bin.000002
3、备份导入时会有DROP TABLE和dbEATE TABLE的动作写进binlog里，所以最终得到的数据，还是备份时的数据。
总结：
一、在恢复全备数据之前必须将该binlog文件移出，否则恢复过程中，会继续写入语句到binlog，最终导致增量恢复数据部分变得比较混乱
二、做好数据文件及binlog的备份至关重要，但不是备份完就算了，要定期进行数据恢复测试或演练
三、恢复时建议对外停止更新，即禁止更新数据库
->
mysqlbinlog工具的使用
binlog是二进制文件，普通文件查看器cat，vim等都无法打开，必须使用自带的mysqlbinlog命令查看
1 | |
上面这种办法读取出binlog日志的全文内容较多，不容易分辨查看pos点信息，这里介绍一种更为方便的查询命令：
show binlog 格式如下：
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
选项解析：
IN 'log_name' 指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset,] 偏移量(不指定就是0)
row_count 查询总条数(不指定就是所有行)
这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数；
1.查询第一个(最早)的binlog日志：
mysql> show binlog events;
2.指定查询 mysql-bin.000021 这个文件：
mysql> show binlog events in 'mysql-bin.000021';
3.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起：
mysql> show binlog events in 'mysql-bin.000021' from 8224;
4.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10;
5.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;
用mysqlbinlog将查询到的数据导入到mysql中
1 | |
###############sample
###sample：
->mysqlbacup顺序：
1.全量备份
(脚本执行备份）
mysqlbackup --login-path=root --socket=/db/mysql/data/mysqltmp/mysql.sock --backup-dir=/db/my3306/backup/full backup-and-apply-log
全量恢复
(cd /db/mysql/data/mydata
rm -rf *
export MYSQL_HOME=/db/mysql/app/mysql
export PATH=$MYSQL_HOME/bin/:/db/mysql/tools/bin:$PATH
mysqlbackup --datadir=/db/mysql/data/mydata --socket=/db/mysql/data/mysqltmp/mysql.sock --backup-dir=/db/my3306/backup/full/ copy-back
)
务必要对data目录授权
(chown -R mysql:mysql /db/mysql/data/mydata )
重启mysql
dbdown
dbup
首先导入一张表
(dbeate table hr_org20190823 as select * from hr_org;)
2.第一次增量备份时间点
(脚本执行备份）(基于fullback)
mysqlbackup --login-path=root --socket=/db/mysql/data/mysqltmp/mysql.sock --indbemental --indbemental-backup-dir=/db/my3306/backup/indb --indbemental-base=dir:full backup
为了验证增量还原的可靠性删除之前创建的表
(drop table hr_org20190823;）
第一次增量还原
(1. 全备检测匹配释放事务日志
mysqlbackup --defaults-file=/db/mysql/app/mysql/my.cnf --backup-dir=/db/my3306/backup/full/ apply-log
2.检测匹配释放第一次的增量备份,并将增量备份注册到全备份
mysqlbackup --backup-dir=/db/my3306/backup/full/ --indbemental-backup-dir=/db/my3306/backup/indb/ apply-indbemental-backup
3、进行物理文件复制（全备份恢复）
mysqlbackup --defaults-file=/db/mysql/app/mysql/my.cnf --datadir=/db/mysql/data/mydata --backup-dir=/db/my3306/backup/full/ copy-back --force
4.目录授权:
chown -R mysql:mysql /db/mysql/data/mydata
）
验证增量还原的可靠性删除之前创建的表
mysql> select count(*) from hr_org20190823;
（全备检测匹配释放事务日志
检测匹配释放第一次的增量备份
(mysqlbackup --backup-dir=/root/2015-06-02_12-20-00/ --indbemental-backup-dir=/root/2015-06-02_13-17-52/ apply-indbemental-backup
)
进行物理文件复制
验证增量备份是否正常 ）
3.第二次增量备份
1、做之前先操作以下文件
mysql> drop table hr_org2; ;
Query OK, 0 rows affected (0.12 sec)
做之前先操作以下文件
2.第二次增量备份操作(基于fullback)
(脚本执行备份）
cd /db/my3306/backup
mv indb indb_bak
mysqlbackup --login-path=root --socket=/db/mysql/data/mysqltmp/mysql.sock --indbemental --indbemental-backup-dir=/db/my3306/backup/indb --indbemental-base=dir:full backup
3.
检测匹配释放第二次的增量备份
mysqlbackup --backup-dir=/db/my3306/backup/full/ --indbemental-backup-dir=/db/my3306/backup/indb/ apply-indbemental-backup
4.
dbdown
5.
进行物理文件复制
mysqlbackup --defaults-file=/db/mysql/app/mysql/my.cnf --datadir=/db/mysql/data/mydata --backup-dir=/db/my3306/backup/full/ copy-back --force
验证增量备份是否正常
6.
chown -R mysql:mysql /db/mysql/data/mydata
7.
dbup
结论：增量备份导入所花费的时间和全量差不多，而且增量的越多，细节处理也越多。因此建议是全量做一次，增量少做。切记导入数据还原后。注意权限的修改，及重启数据库生效。
->mysqlbinlog二进制日志增量备份
0
show variables like '%binlog%';
mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | MIXED |
| sync_binlog | 1 |
+-----------------------------------------+----------------------+
15 rows in set (0.01 sec)
mysql>
日志格式是MIXED的，这个表示一些特殊的uuid以及now()之类会记录成row，其它的仍然是记录sql模式。
1.dbeate database xuanzhi;
use xuanzhi;
创建一个测试表tb1
dbeate Table: dbEATE TABLE `tb1` (
`id` int(10) NOT NULL AUTO_INdbEMENT,
`name` char(10) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
往表里插入两条数据
insert into tb1 (name) value ('aa'),('bb');
2.
show master logs;
| mysql-bin.000071 | 745 |
3.
(恢复全量，检测并应用日志，应用增量，物理文件复制还原
mysqlbackup恢复完成之后，data目录下会生成backup_variables.txt的文件 (一般在meta目录下，/db/my3306/backup/full/meta)
（其实在备份的时候就已经有这些文件的），找到备份的时候的log position,
(binlog_position=mysql-bin.000071:745)
然后从binlog恢复无备份的数据
查找binglog 时间点)
4.
往表里插入两条数据
insert into tb1 (name) value ('cc'),('dd');
show master logs;
(可以查看master数据库当前正在使用的二进制日志及当前执行二进制日志位置)
flush logs;
(dbeate new binglog)
insert into tb1 (name) value ('ee');
show master logs;
| mysql-bin.000072 | 384 |
5.
pos点 模拟误操作
把xunazhi库drop了：
drop database xuanzhi;
6.恢复操作,首先数据挖掘，到删除数据库之前的log_pos
cd /db/mysql/data/mydata
mysqlbinlog -v --base64-output=DECODE-ROWS mysql-bin.000072 |grep -C 10 -i "drop database"
(
use `xuanzhi`/*!*/;
SET TIMESTAMP=1567046148/*!*/;
{
/*!*/;nto tb1 (name) value ('ee')
# at 353
#190829 10:35:48 server id 1 end_log_pos 384 Xid = 165 <- 删除点之前的scn
COMMIT/*!*/;
# at 384
#190829 10:35:56 server id 1 end_log_pos 485 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1567046156/*!*/;
drop database xuanzhi瀎
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
)
接下来要结合Binlog来恢复
但前提要找出误操作前的pos点，也就是drop database xuanzhi前的pos点：)
(##190829 10:35:48 server id 1 end_log_pos 384 Xid = 165 ）
（从上面可以看到，误操作前的pos点是384，那我们现在通过binlog来进行数据恢复：）
7.
（从上面可以看到，误操作前的pos点是mysql-bin.000072的384，起点备份后记录下的pos，745那我们现在通过binlog来进行数据恢复）：
(复制一份到/tmp目录)
cd /db/mysql/data/mydata
cp mysql-bin.000071 /tmp/
cp mysql-bin.000072 /tmp/
(目的先使用mysqlbackup 恢复全亮备份数据（方法参考以上全量恢复），然后使用备份的binglog来进行之后的数据恢复,binglog 一定要提前复制一份到其他目录，
不然会被覆盖)
（/db/mysql/app/mysql/bin/mysqlbinlog version 是3.4， /usr/bin/mysqlbinlog --version 是3.3 ，一定要用3.4绝对路径 ，不然有问题 ,
后即在bash_profile 加入PATH=/db/mysql/app/mysql/bin:$PATH:$HOME/bin）
cd /tmp
/db/mysql/app/mysql/bin/mysqlbinlog --start-position=745 --stop-position=485 mysql-bin.000071 mysql-bin.000072 |mysql -uroot -p123456
（
-> --start-position是备份后记录下的pos点， --stop-position是误操前的pos点，如果批多个binlog文件，那么start-position是第一个binlog文件的pos点，stop-position是最后一个binlog的pos点，下面我们看下数据是否恢复回来了：
-> 这里要提的是进行恢复前，要把需要恢复的binlog备份好，或者移动拷贝一份到另一个目录，因为进行数据导入时也会继续写binlog。
）
注意：一、在恢复全备数据之前必须将该binlog文件移出，否则恢复过程中，会继续写入语句到binlog，最终导致增量恢复数据部分变得比较混乱
二、做好数据文件及binlog的备份至关重要，但不是备份完就算了，要定期进行数据恢复测试或演练
三、恢复时建议对外停止更新，即禁止更新数据库
炊烟起了；夕阳下了；细雨来了 多调试,交互式编程体验 记录，独立思考，对比 感谢转载作者 修车 国产化 read and connect 匍匐前进, 讲故事
日常的数据备份及恢复测试，是DBA工作重中之重的事情，所以要做好备份及测试，日常的备份常见有mysqldump+binlog备份、xtrabackup+binlog备份，无论那一种，几乎都少不了对binlog的备份，说明了binlog在数据恢复中的重要性，下面做个小测试，是工作中不少运维或者新人DBA容易犯的错。
创建一个测试表tb1
<test>(root@localhost) [xuanzhi]> show dbeate table tb1\G *************************** 1. row *************************** Table: tb1 dbeate Table: dbEATE TABLE `tb1` ( `id` int(10) NOT NULL AUTO_INdbEMENT, `name` char(10) CHARACTER SET latin1 DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) <test>(root@localhost) [xuanzhi]>
往表里插入两条数据
<test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('aa'),('bb'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 <test>(root@localhost) [xuanzhi]> show master logs; +----------------------+-----------+ | Log_name | File_size | +----------------------+-----------+ | localhost-bin.000001 | 329 | +----------------------+-----------+ 1 row in set (0.00 sec) <test>(root@localhost) [xuanzhi]> 
对数据备份
如果是xtrabackup备份的话，会在有xtrabackup_binlog_info文件中记录此时备份是到那个binlog文件和pos点的，如果是mysqldump备份，则需要带上--master-data=2这个参数，下面我们的数据量少，用mysqldump备份：
[root@localhost ~]# mysqldump -uroot -p123456 -R --events --triggers=true --master-data=2 --single-transaction xuanzhi > xuanzhi.sql Warning: Using a password on the command line interface can be insecure. [root@localhost ~]# grep -i "CHANGE MASTER" xuanzhi.sql -- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000001', MASTER_LOG_POS=329; [root@localhost ~]#
继续模拟数据库有写入
这个时候是还是写在mysql-bin.000001
<test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('cc'),('dd');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
<test>(root@localhost) [xuanzhi]> show master logs;
+----------------------+-----------+
| Log_name | File_size |
+----------------------+-----------+
| mysql-bin.000001 | 538 |
+----------------------+-----------+
1 row in set (0.00 sec)
<test>(root@localhost) [xuanzhi]> flush logs;
Query OK, 0 rows affected (0.01 sec)
<test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('ee');
Query OK, 1 row affected (0.00 sec)
<test>(root@localhost) [xuanzhi]> show master logs;
+----------------------+-----------+
| Log_name | File_size |
+----------------------+-----------+
| mysql-bin.000001 | 589 || mysql-bin.000002 | 321 |
+----------------------
+-----------+
2 rows in set (0.00 sec) <test>(root@localhost) [xuanzhi]>
上面我们进行flush logs是为了模拟现在已经有多个binlog文件了，恢复时进行多个binlog一起恢复。
模拟误操作
把xunazhi库drop了：
<test>(root@localhost) [xuanzhi]> drop database xuanzhi; Query OK, 1 row affected (0.02 sec) <test>(root@localhost) [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) <test>(root@localhost) [(none)]>
创建数据xuanzhi，把备份导入
<test>(root@localhost) [(none)]> dbeate database xuanzhi; Query OK, 1 row affected (0.00 sec)
[root@localhost ~]# mysql -uroot -p123456 xuanzhi <./xuanzhi.sql # 这个sql文件可以是绝对路径也可以是相对路径 Warning: Using a password on the command line interface can be insecure. [root@localhost ~]#
查看数据
<test>(root@localhost) [(none)]> use xuanzhi Database changed <test>(root@localhost) [xuanzhi]> select * from tb1; +----+------+ | id | name | +----+------+ | 1 | aa | | 2 | bb | +----+------+ 2 rows in set (0.00 sec) <test>(root@localhost) [xuanzhi]>
可以看到备份前的数据恢复了
接下来要结合Binlog来恢复
但前提要找出误操作前的pos点，也就是drop database xuanzhi前的pos点：
[root@localhost ~]# mysqlbinlog -v --base64-output=DECODE-ROWS mysql-bin.000002 |grep -C 10 -i "drop database" ### INSERT INTO `xuanzhi`.`tb1` ### SET ### @1=5 ### @2='ee' # at 290 #170327 21:10:55 server id 1313306 end_log_pos 321 dbC32 0x825a2f99 Xid = 78 COMMIT/*!*/; # at 321 #170327 21:19:25 server id 1313306 end_log_pos 422 dbC32 0x8c139cac Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1490620765/*!*/; drop database xuanzhi /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@localhost ~]# mysql -uroot -p123456 xuanzhi <./xuanzhi.sql Warning: Using a password on the command line interface can be insecure. [root@localhost ~]#
从上面可以看到，误操作前的pos点是321，那我们现在通过binlog来进行数据恢复：
[root@localhost mysql-5.6]# mysqlbinlog --start-position=329 --stop-position=321 mysql-bin.000001 mysql-bin.000002 |mysql -uroot -p123456 xuanzhi
# 这里的mysql-bin.000001和mysql-bin.000002等日志文件要从原来的位置拷贝出来，可以是绝对路径也可以是相对路径
Warning: Using a password on the command line interface can be insecure.
[root@localhost mysql-5.6]#
--start-position是备份后记录下的pos点， --stop-position是误操前的pos点，如果批多个binlog文件，那么start-position是第一个binlog文件的pos点，stop-position是最后一个binlog的pos点，下面我们看下数据是否恢复回来了：
<test>(root@localhost) [xuanzhi]> select * from tb1; +----+------+ | id | name | +----+------+ | 1 | aa | | 2 | bb | | 3 | cc | | 4 | dd | | 5 | ee | +----+------+ 5 rows in set (0.00 sec) <test>(root@localhost) [xuanzhi]>
这里要提的是进行恢复前，要把需要恢复的binlog备份好，或者移动拷贝一份到另一个目录，因为进行数据导入时也会继续写binlog。假如你没有误操作的情况下，就是想测试一下数据的恢复，很多人的操作是导入备份，再从备份里记录的binlog文件名和pos点进行binlog恢复，发现步骤都很完美，也没报错，恢复后就是只有备份时的数据，没有备份后的数据，下面测试一下给大家看：
<test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('aa'),('bb'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 <test>(root@localhost) [xuanzhi]> select * from tb1; +----+------+ | id | name | +----+------+ | 1 | aa | | 2 | bb | +----+------+ 2 rows in set (0.00 sec) <test>(root@localhost) [xuanzhi]> show master logs; +----------------------+-----------+ | Log_name | File_size | +----------------------+-----------+ | localhost-bin.000001 | 329 | +----------------------+-----------+ 1 row in set (0.00 sec) <test>(root@localhost) [xuanzhi]>
进行备份操作：
[root@localhost ~]# mysqldump -uroot -p123456 -R --events --triggers=true --master-data=2 --single-transaction xuanzhi > xuanzhi.sql Warning: Using a password on the command line interface can be insecure. [root@localhost ~]# grep -i "change master" xuanzhi.sql -- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000001', MASTER_LOG_POS=329; [root@localhost ~]#
继续写localhost-bin.000001后进行flush logs生成新的binlog再继续写数据，这里只是想模拟localhost-bin.000001写满了切localhost-bin.000002，结合多个binlog一起恢复
<test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('cc'),('dd'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 <test>(root@localhost) [xuanzhi]> flush logs; Query OK, 0 rows affected (0.00 sec) <test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('dd'); Query OK, 1 row affected (0.01 sec) <test>(root@localhost) [xuanzhi]> show master logs; +----------------------+-----------+ | Log_name | File_size | +----------------------+-----------+ | localhost-bin.000001 | 589 | | localhost-bin.000002 | 321 | +----------------------+-----------+ 2 rows in set (0.00 sec) <test>(root@localhost) [xuanzhi]>
下面进行恢复测试，正常来说先把备份导入：
[root@localhost ~]# mysql -uroot -p123456 xuanzhi <./xuanzhi.sql Warning: Using a password on the command line interface can be insecure. [root@localhost ~]#
查看数据，只有备份的那两条记录：
<test>(root@localhost) [xuanzhi]> select * from tb1; +----+------+ | id | name | +----+------+ | 1 | aa | | 2 | bb | +----+------+ 2 rows in set (0.00 sec) <test>(root@localhost) [xuanzhi]>
那现在通过localhost-bin.000001，localhost-bin.000002来恢复后面那3条数据，那么起始pos是那个呢，就是上面备份完后备份文件里的那个pos，我们进到binlog的存放路径：
可以看到备份后的数据是没有恢复回来的。为什么呢？因为导入备份的时候，又开始写binlog了，而你恢复时用的binlog也就是现在导入备份时正在写的binlog。大体过程是这样的：
1、导入备份后，备份的所有操作都写进最后一个binlog了，也就是上面的localhost-bin.000002
2、进行binlog恢复，从备份文件里的pos点开始，按理来说是可以恢复到最新数据的，但是上面导入了备份，导入时的所有操作都会记录到localhost-bin.000002
3、备份导入时会有DROP TABLE和dbEATE TABLE的动作写进binlog里，所以最终得到的数据，还是备份时的数据。
总结：
一、在恢复全备数据之前必须将该binlog文件移出，否则恢复过程中，会继续写入语句到binlog，最终导致增量恢复数据部分变得比较混乱
二、做好数据文件及binlog的备份至关重要，但不是备份完就算了，要定期进行数据恢复测试或演练
三、恢复时建议对外停止更新，即禁止更新数据库
->
mysqlbinlog工具的使用
binlog是二进制文件，普通文件查看器cat，vim等都无法打开，必须使用自带的mysqlbinlog命令查看
1 | |
上面这种办法读取出binlog日志的全文内容较多，不容易分辨查看pos点信息，这里介绍一种更为方便的查询命令：
show binlog 格式如下：
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
选项解析：
IN 'log_name' 指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset,] 偏移量(不指定就是0)
row_count 查询总条数(不指定就是所有行)
这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数；
1.查询第一个(最早)的binlog日志：
mysql> show binlog events;
2.指定查询 mysql-bin.000021 这个文件：
mysql> show binlog events in 'mysql-bin.000021';
3.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起：
mysql> show binlog events in 'mysql-bin.000021' from 8224;
4.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10;
5.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;
用mysqlbinlog将查询到的数据导入到mysql中
1 | |
###############sample
###sample：
->mysqlbacup顺序：
1.全量备份
(脚本执行备份）
mysqlbackup --login-path=root --socket=/db/mysql/data/mysqltmp/mysql.sock --backup-dir=/db/my3306/backup/full backup-and-apply-log
全量恢复
(cd /db/mysql/data/mydata
rm -rf *
export MYSQL_HOME=/db/mysql/app/mysql
export PATH=$MYSQL_HOME/bin/:/db/mysql/tools/bin:$PATH
mysqlbackup --datadir=/db/mysql/data/mydata --socket=/db/mysql/data/mysqltmp/mysql.sock --backup-dir=/db/my3306/backup/full/ copy-back
)
务必要对data目录授权
(chown -R mysql:mysql /db/mysql/data/mydata )
重启mysql
dbdown
dbup
首先导入一张表
(dbeate table hr_org20190823 as select * from hr_org;)
2.第一次增量备份时间点
(脚本执行备份）(基于fullback)
mysqlbackup --login-path=root --socket=/db/mysql/data/mysqltmp/mysql.sock --indbemental --indbemental-backup-dir=/db/my3306/backup/indb --indbemental-base=dir:full backup
为了验证增量还原的可靠性删除之前创建的表
(drop table hr_org20190823;）
第一次增量还原
(1. 全备检测匹配释放事务日志
mysqlbackup --defaults-file=/db/mysql/app/mysql/my.cnf --backup-dir=/db/my3306/backup/full/ apply-log
2.检测匹配释放第一次的增量备份,并将增量备份注册到全备份
mysqlbackup --backup-dir=/db/my3306/backup/full/ --indbemental-backup-dir=/db/my3306/backup/indb/ apply-indbemental-backup
3、进行物理文件复制（全备份恢复）
mysqlbackup --defaults-file=/db/mysql/app/mysql/my.cnf --datadir=/db/mysql/data/mydata --backup-dir=/db/my3306/backup/full/ copy-back --force
4.目录授权:
chown -R mysql:mysql /db/mysql/data/mydata
）
验证增量还原的可靠性删除之前创建的表
mysql> select count(*) from hr_org20190823;
（全备检测匹配释放事务日志
检测匹配释放第一次的增量备份
(mysqlbackup --backup-dir=/root/2015-06-02_12-20-00/ --indbemental-backup-dir=/root/2015-06-02_13-17-52/ apply-indbemental-backup
)
进行物理文件复制
验证增量备份是否正常 ）
3.第二次增量备份
1、做之前先操作以下文件
mysql> drop table hr_org2; ;
Query OK, 0 rows affected (0.12 sec)
做之前先操作以下文件
2.第二次增量备份操作(基于fullback)
(脚本执行备份）
cd /db/my3306/backup
mv indb indb_bak
mysqlbackup --login-path=root --socket=/db/mysql/data/mysqltmp/mysql.sock --indbemental --indbemental-backup-dir=/db/my3306/backup/indb --indbemental-base=dir:full backup
3.
检测匹配释放第二次的增量备份
mysqlbackup --backup-dir=/db/my3306/backup/full/ --indbemental-backup-dir=/db/my3306/backup/indb/ apply-indbemental-backup
4.
dbdown
5.
进行物理文件复制
mysqlbackup --defaults-file=/db/mysql/app/mysql/my.cnf --datadir=/db/mysql/data/mydata --backup-dir=/db/my3306/backup/full/ copy-back --force
验证增量备份是否正常
6.
chown -R mysql:mysql /db/mysql/data/mydata
7.
dbup
结论：增量备份导入所花费的时间和全量差不多，而且增量的越多，细节处理也越多。因此建议是全量做一次，增量少做。切记导入数据还原后。注意权限的修改，及重启数据库生效。
->mysqlbinlog二进制日志增量备份
0
show variables like '%binlog%';
mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | MIXED |
| sync_binlog | 1 |
+-----------------------------------------+----------------------+
15 rows in set (0.01 sec)
mysql>
日志格式是MIXED的，这个表示一些特殊的uuid以及now()之类会记录成row，其它的仍然是记录sql模式。
1.dbeate database xuanzhi;
use xuanzhi;
创建一个测试表tb1
dbeate Table: dbEATE TABLE `tb1` (
`id` int(10) NOT NULL AUTO_INdbEMENT,
`name` char(10) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
往表里插入两条数据
insert into tb1 (name) value ('aa'),('bb');
2.
show master logs;
| mysql-bin.000071 | 745 |
3.
(恢复全量，检测并应用日志，应用增量，物理文件复制还原
mysqlbackup恢复完成之后，data目录下会生成backup_variables.txt的文件 (一般在meta目录下，/db/my3306/backup/full/meta)
（其实在备份的时候就已经有这些文件的），找到备份的时候的log position,
(binlog_position=mysql-bin.000071:745)
然后从binlog恢复无备份的数据
查找binglog 时间点)
4.
往表里插入两条数据
insert into tb1 (name) value ('cc'),('dd');
show master logs;
(可以查看master数据库当前正在使用的二进制日志及当前执行二进制日志位置)
flush logs;
(dbeate new binglog)
insert into tb1 (name) value ('ee');
show master logs;
| mysql-bin.000072 | 384 |
5.
pos点 模拟误操作
把xunazhi库drop了：
drop database xuanzhi;
6.恢复操作,首先数据挖掘，到删除数据库之前的log_pos
cd /db/mysql/data/mydata
mysqlbinlog -v --base64-output=DECODE-ROWS mysql-bin.000072 |grep -C 10 -i "drop database"
(
use `xuanzhi`/*!*/;
SET TIMESTAMP=1567046148/*!*/;
{
/*!*/;nto tb1 (name) value ('ee')
# at 353
#190829 10:35:48 server id 1 end_log_pos 384 Xid = 165 <- 删除点之前的scn
COMMIT/*!*/;
# at 384
#190829 10:35:56 server id 1 end_log_pos 485 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1567046156/*!*/;
drop database xuanzhi瀎
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
)
接下来要结合Binlog来恢复
但前提要找出误操作前的pos点，也就是drop database xuanzhi前的pos点：)
(##190829 10:35:48 server id 1 end_log_pos 384 Xid = 165 ）
（从上面可以看到，误操作前的pos点是384，那我们现在通过binlog来进行数据恢复：）
7.
（从上面可以看到，误操作前的pos点是mysql-bin.000072的384，起点备份后记录下的pos，745那我们现在通过binlog来进行数据恢复）：
(复制一份到/tmp目录)
cd /db/mysql/data/mydata
cp mysql-bin.000071 /tmp/
cp mysql-bin.000072 /tmp/
(目的先使用mysqlbackup 恢复全亮备份数据（方法参考以上全量恢复），然后使用备份的binglog来进行之后的数据恢复,binglog 一定要提前复制一份到其他目录，
不然会被覆盖)
（/db/mysql/app/mysql/bin/mysqlbinlog version 是3.4， /usr/bin/mysqlbinlog --version 是3.3 ，一定要用3.4绝对路径 ，不然有问题 ,
后即在bash_profile 加入PATH=/db/mysql/app/mysql/bin:$PATH:$HOME/bin）
cd /tmp
/db/mysql/app/mysql/bin/mysqlbinlog --start-position=745 --stop-position=485 mysql-bin.000071 mysql-bin.000072 |mysql -uroot -p123456
（
-> --start-position是备份后记录下的pos点， --stop-position是误操前的pos点，如果批多个binlog文件，那么start-position是第一个binlog文件的pos点，stop-position是最后一个binlog的pos点，下面我们看下数据是否恢复回来了：
-> 这里要提的是进行恢复前，要把需要恢复的binlog备份好，或者移动拷贝一份到另一个目录，因为进行数据导入时也会继续写binlog。
）
注意：一、在恢复全备数据之前必须将该binlog文件移出，否则恢复过程中，会继续写入语句到binlog，最终导致增量恢复数据部分变得比较混乱
二、做好数据文件及binlog的备份至关重要，但不是备份完就算了，要定期进行数据恢复测试或演练
三、恢复时建议对外停止更新，即禁止更新数据库
