mysql企业备份工具mysqlbackup

mysql企业备份工具mysqlbackup的学习与实践

一、mysqlbackup的安装 root@drbd-01 soft]# unzip p21339691_3120_Linux-x86-64.zip Archive:  p21339691_3120_Linux-x86-64.zip  extracting: meb-3.12.1-linux-glibc2.5-x86-64bit.tar.gz    extracting: meb-3.12.1-linux-glibc2.5-x86-64bit.tar.gz.asc    extracting: meb-3.12.1-linux-glibc2.5-x86-64bit.tar.gz.md5   extracting: README.txt                      [root@drbd-01 soft]# tar -xvf meb-3.12.1-linux-glibc2.5-x86-64bit.tar.gz mysql建议把mysqlbackup软件安装在目录/opt/mysql/meb-3.12.1下面，通过以上操作可以用mv命令把文件转移到该目录，然后运行以下语句，如需更方便的使用mysqlbackup命令可以做如下操作： [root@mysql2 local]#echo "export PATH=$PATH:/opt/mysql/meb-3.12.1/bin/" >> ~/.bashrc [root@mysql2 local]#. ~/.bashrc ################ 二、全库备份 参数说明：    --defaults-file my.cnf文件的路径,主要用于一台服务器多个mysql服务.默认位置是/etc/my.cnf --user  用户名,这个用户必须在mysql库里面有创建table和查询,插入的权限.在备份的过程中.mysqlbackup会在mysql库下建立backup_history, backup_progress表.用户保留备份的历史信息和备份的基础信息.    --password密码 --database 需要备份的数据库,要备份多个数据库需要用””包括起来,每个数据库中间用空格分开 --with-timestamp用户创建一个备份目录下面当前时间的文件夹,如果没有这个参数,多次备份时,制定同一个目录,会使上一次备份的文件覆盖掉. --backup-dir备份的目录 Backup  表明,这是备份操作 [root@drbd-01 backup]# mysqlbackup --defaults-file=/etc/my.cnf   --user=root --password=123 --databases="db1 db2" --with-timestamp --backup-dir=/app/backup backup MySQL Enterprise Backup version 3.12.1 Linux-2.6.18-194.el5-x86_64 [2015/06/26]  Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved.  mysqlbackup: INFO: Starting with following command line ...  mysqlbackup --defaults-file=/etc/my.cnf --user=root --password=xxx          --databases=db1 db2 --with-timestamp --backup-dir=/app/backup backup   mysqlbackup: INFO:   mysqlbackup: INFO: MySQL server version is '5.1.73'.  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!". 151110 13:40:11 mysqlbackup: INFO: MEB logfile created at /app/backup/2015-11-10_13-40-10/meta/MEB_2015-11-10.13-40-11_backup.log --------------------------------------------------------------------                        Server Repository Options: --------------------------------------------------------------------   datadir = /data/mysql/   innodb_data_home_dir =    innodb_data_file_path = ibdata1:10M:autoextend   innodb_log_group_home_dir = /data/mysql/   innodb_log_files_in_group = 2   innodb_log_file_size = 5242880   innodb_page_size = Null   innodb_checksum_algorithm = none --------------------------------------------------------------------                        Backup Config Options: --------------------------------------------------------------------   datadir = /app/backup/2015-11-10_13-40-10/datadir   innodb_data_home_dir = /app/backup/2015-11-10_13-40-10/datadir   innodb_data_file_path = ibdata1:10M:autoextend   innodb_log_group_home_dir = /app/backup/2015-11-10_13-40-10/datadir   innodb_log_files_in_group = 2   innodb_log_file_size = 5242880   innodb_page_size = 16384   innodb_checksum_algorithm = none  mysqlbackup: INFO: Unique generated backup id for this is 14471340109271073  mysqlbackup: INFO: Creating 14 buffers each of size 16777216. 151110 13:40:13 mysqlbackup: INFO: Full Backup operation starts with following threads                 1 read-threads    6 process-threads    1 write-threads 151110 13:40:13 mysqlbackup: INFO: System tablespace file format is Antelope. 151110 13:40:13 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. 151110 13:40:13 mysqlbackup: INFO: Copying /data/mysql/ibdata1 (Antelope file format). 151110 13:40:13 mysqlbackup: INFO: Found checkpoint at lsn 44233. 151110 13:40:13 mysqlbackup: INFO: Starting log scan from lsn 44032. 151110 13:40:13 mysqlbackup: INFO: Copying log... 151110 13:40:13 mysqlbackup: INFO: Log copied, lsn 44233. 151110 13:40:13 mysqlbackup: INFO: Completing the copy of innodb files. 151110 13:40:14 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server. 151110 13:40:14 mysqlbackup: INFO: Starting to lock all the tables... 151110 13:40:14 mysqlbackup: INFO: All tables are locked and flushed to disk 151110 13:40:14 mysqlbackup: INFO: Opening backup source directory '/data/mysql/' 151110 13:40:14 mysqlbackup: INFO: Starting to backup all non-innodb files in          subdirectories of '/data/mysql/' 151110 13:40:14 mysqlbackup: INFO: Copying the database directory 'db1' 151110 13:40:14 mysqlbackup: INFO: Copying the database directory 'db2' 151110 13:40:14 mysqlbackup: INFO: Completing the copy of all non-innodb files. 151110 13:40:16 mysqlbackup: INFO: A copied database page was modified at 44233.           (This is the highest lsn found on page)           Scanned log up to lsn 44233.           Was able to parse the log up to lsn 44233.           Maximum page number for a log record 0 151110 13:40:16 mysqlbackup: INFO: All tables unlocked 151110 13:40:16 mysqlbackup: INFO: All MySQL tables were locked for 1.552 seconds. 151110 13:40:16 mysqlbackup: INFO: Reading all global variables from the server. 151110 13:40:16 mysqlbackup: INFO: Completed reading of all global variables from the server. 151110 13:40:16 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /app/backup/2015-11-10_13-40-10 151110 13:40:16 mysqlbackup: INFO: Full Backup operation completed successfully. 151110 13:40:16 mysqlbackup: INFO: Backup created in directory '/app/backup/2015-11-10_13-40-10' -------------------------------------------------------------    Parameters Summary          -------------------------------------------------------------    Start LSN                  : 44032    End LSN                    : 44233 ------------------------------------------------------------- mysqlbackup completed OK! 上面已经备份完毕。 下面查看一下备份的文件。 [root@drbd-01 backup]# cd /app/backup [root@drbd-01 backup]# ls 2015-11-10_13-40-10 [root@drbd-01 backup]# cd 2015-11-10_13-40-10 [root@drbd-01 2015-11-10_13-40-10]# ls backup-my.cnf  datadir  meta  server-all.cnf  server-my.cnf [root@drbd-01 datadir]#cd datadir  [root@drbd-01 datadir]# ls db1  db2  ibbackup_logfile  ibdata1 看到备份目录里面只备份了db1和db2两个库。 三、全库还原第一步:检测事务日志 参数说明: apply-log:因为在备份的时候是在线的,如果有新插入的SQL语句,会记录新增加的LSN点,然后新修改的页面会放到这个文件里面（ibbackup_logfile）,同时也会放到表空间里面.当还原使用这个参数的时候,mysqlbackup会检测ibbackup_logfile和表空间的LSN点,然后比较ibbackup_logfile文件表空间LSN的差值,把这个值放到事务日志LOG里面.(事务日志如果填满了,会进入表空间的) [root@drbd-01 ~]# mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/app/backup/2015-11-10_13-40-10/ apply-log MySQL Enterprise Backup version 3.12.1 Linux-2.6.18-194.el5-x86_64 [2015/06/26]  Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved.  mysqlbackup: INFO: Starting with following command line ...  mysqlbackup --defaults-file=/etc/my.cnf          --backup-dir=/app/backup/2015-11-10_13-40-10/ apply-log   mysqlbackup: INFO:  IMPORTANT: Please check that mysqlbackup run completes successfully.            At the end of a successful 'apply-log' run mysqlbackup            prints "mysqlbackup completed OK!". 151110 13:55:02 mysqlbackup: INFO: MEB logfile created at /app/backup/2015-11-10_13-40-10/meta/MEB_2015-11-10.13-55-02_apply_log.log --------------------------------------------------------------------                        Backup Config Options: --------------------------------------------------------------------   datadir = /app/backup/2015-11-10_13-40-10/datadir   innodb_data_home_dir = /app/backup/2015-11-10_13-40-10/datadir   innodb_data_file_path = ibdata1:10M:autoextend   innodb_log_group_home_dir = /app/backup/2015-11-10_13-40-10/datadir   innodb_log_files_in_group = 2   innodb_log_file_size = 5242880   innodb_page_size = 16384   innodb_checksum_algorithm = none  mysqlbackup: INFO: Creating 14 buffers each of size 65536. 151110 13:55:02 mysqlbackup: INFO: Apply-log operation starts with following threads                 1 read-threads    1 process-threads  mysqlbackup: INFO: Using up to 100 MB of memory. 151110 13:55:02 mysqlbackup: INFO: ibbackup_logfile's creation parameters:           start lsn 44032, end lsn 44233,           start checkpoint 44233. InnoDB: Doing recovery: scanned up to log sequence number 44233  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 5242880  mysqlbackup: INFO: InnoDB: Setting log file size to 5242880 151110 13:55:02 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to           lsn 44233. 151110 13:55:02 mysqlbackup: INFO: The first data file is '/app/backup/2015-11-10_13-40-10/datadir/ibdata1'           and the new created log files are at '/app/backup/2015-11-10_13-40-10/datadir' 151110 13:55:03 mysqlbackup: INFO: Apply-log operation completed successfully. 151110 13:55:03 mysqlbackup: INFO: Full backup prepared for recovery successfully. mysqlbackup completed OK! 第二步:copy物理文件  [root@drbd-01 data]# mkdir /data/mysql_new [root@drbd-01 data]# chown mysql.mysql /data/mysql_new/ [root@drbd-01 data]# cd /data/mysql_new/ [root@drbd-01 mysql_new]# ls 看到现在我们新建立的/data/mysql_new/目录还是空的

在/etc/my.cnf里面修改参数为datadir=/data/mysql_new/

# 注意:在还原的时候,my.cnf文件中必要要有datadir的参数

[root@drbd-01 mysql_new]# mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/app/backup/2015-11-10_13-40-10/ --innodb_log_files_in_group=2 copy-back MySQL Enterprise Backup version 3.12.1 Linux-2.6.18-194.el5-x86_64 [2015/06/26]  Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved.  mysqlbackup: INFO: Starting with following command line ...  mysqlbackup --defaults-file=/etc/my.cnf          --backup-dir=/app/backup/2015-11-10_13-40-10/          --innodb_log_files_in_group=2 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!". 151110 14:05:12 mysqlbackup: INFO: MEB logfile created at /app/backup/2015-11-10_13-40-10/meta/MEB_2015-11-10.14-05-12_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:10M:autoextend' 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=5242880' to the target server configuration. --------------------------------------------------------------------                        Server Repository Options: --------------------------------------------------------------------   datadir = /data/mysql_new   innodb_data_home_dir = /data/mysql_new   innodb_data_file_path = ibdata1:10M:autoextend   innodb_log_group_home_dir = /data/mysql_new   innodb_log_files_in_group = 2   innodb_log_file_size = 5242880   innodb_page_size = Null   innodb_checksum_algorithm = none --------------------------------------------------------------------                        Backup Config Options: --------------------------------------------------------------------   datadir = /app/backup/2015-11-10_13-40-10/datadir   innodb_data_home_dir = /app/backup/2015-11-10_13-40-10/datadir   innodb_data_file_path = ibdata1:10M:autoextend   innodb_log_group_home_dir = /app/backup/2015-11-10_13-40-10/datadir   innodb_log_files_in_group = 2   innodb_log_file_size = 5242880   innodb_page_size = 16384   innodb_checksum_algorithm = none  mysqlbackup: INFO: Creating 14 buffers each of size 16777216. 151110 14:05:12 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. 151110 14:05:12 mysqlbackup: INFO: Copying /app/backup/2015-11-10_13-40-10/datadir/ibdata1. 151110 14:05:13 mysqlbackup: INFO: Copying the database directory 'db1' 151110 14:05:13 mysqlbackup: INFO: Copying the database directory 'db2' 151110 14:05:13 mysqlbackup: INFO: Completing the copy of all non-innodb files. 151110 14:05:13 mysqlbackup: INFO: Copying the log file 'ib_logfile0' 151110 14:05:13 mysqlbackup: INFO: Copying the log file 'ib_logfile1' 151110 14:05:14 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /data/mysql_new 151110 14:05:14 mysqlbackup: INFO: Copy-back operation completed successfully. 151110 14:05:14 mysqlbackup: INFO: Finished copying backup files to '/data/mysql_new' mysqlbackup completed OK! with 2 warnings

上面已经将数据copy到新的数据目录下面，如下可以看到：

[root@drbd-01 mysql_new]# ls /data/mysql_new/backup_variables.txt  db1  db2  ibdata1  ib_logfile0  ib_logfile1  server-all.cnf  server-my.cnf

[root@drbd-01 mysql_new]# service mysqld start 初始化 MySQL 数据库： Installing MySQL system tables... OK Filling help tables... OK   To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system   PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands:   /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h drbd-01.i.12582.com password 'new-password'   Alternatively you can run: /usr/bin/mysql_secure_installation   which will also give you the option of removing the test databases and anonymous user created by default.  This is strongly recommended for production servers.   See the manual for more instructions.   You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe &   You can test the MySQL daemon with mysql-test-run.pl cd /usr/mysql-test ; perl mysql-test-run.pl   Please report any problems with the /usr/bin/mysqlbug script!                                                              [确定] 正在启动 mysqld：                                          [确定] [root@drbd-01 mysql_new]# ls /data/mysql_new backup_variables.txt  db1  db2  ibdata1  ib_logfile0  ib_logfile1  mysql  server-all.cnf  server-my.cnf  test启动数据库服务后， 对数据库进行了初始化， /data/mysql_new目录里面生成了系统库mysql和测试库test。这时候数据库已经还原完毕，可以登陆数据库查看一下数据是否已经恢复回来：

[root@drbd-01 mysql_new]# mysql Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.73 Source distribution Copyright (c) 2000, 2013, 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 | | db1                | | db2                | | mysql              | | test               | +--------------------+ 5 rows in set (0.06 sec)

数据库的备份还有其他几种方法，下面再分别介绍之。

四、压缩全库备份

[root@drbd-01 data]# cd /app/backup/ [root@drbd-01 backup]# ls 参数说明：--compress-level=11为快速压缩.共有9个等级[root@drbd-01 backup]# mysqlbackup --defaults-file=/etc/my.cnf --user=root --password=123 --compress-level=1 --databases="db1 db2" --with-timestamp --backup-dir=/app/backup/ backup MySQL Enterprise Backup version 3.12.1 Linux-2.6.18-194.el5-x86_64 [2015/06/26] Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved.   mysqlbackup: INFO: Starting with following command line ... mysqlbackup --defaults-file=/etc/my.cnf --user=root --password=xxx         --compress-level=1 --databases=db1 db2 --with-timestamp         --backup-dir=/app/backup/ backup   mysqlbackup: INFO: mysqlbackup: INFO: MySQL server version is '5.1.73'. 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!".   151110 14:26:47 mysqlbackup: INFO: MEB logfile created at /app/backup/2015-11-10_14-26-47/meta/MEB_2015-11-10.14-26-47_compress_backup.log   --------------------------------------------------------------------                        Server Repository Options: --------------------------------------------------------------------   datadir = /data/mysql_new/   innodb_data_home_dir =   innodb_data_file_path = ibdata1:10M:autoextend   innodb_log_group_home_dir = /data/mysql_new/   innodb_log_files_in_group = 2   innodb_log_file_size = 5242880   innodb_page_size = Null   innodb_checksum_algorithm = none   --------------------------------------------------------------------                        Backup Config Options: --------------------------------------------------------------------   datadir = /app/backup/2015-11-10_14-26-47/datadir   innodb_data_home_dir = /app/backup/2015-11-10_14-26-47/datadir   innodb_data_file_path = ibdata1:10M:autoextend   innodb_log_group_home_dir = /app/backup/2015-11-10_14-26-47/datadir   innodb_log_files_in_group = 2   innodb_log_file_size = 5242880   innodb_page_size = 16384   innodb_checksum_algorithm = none   mysqlbackup: INFO: Unique generated backup id for this is 14471368073217474   mysqlbackup: INFO: Uses LZ4 r109 for data compression. mysqlbackup: INFO: Creating 18 buffers each of size 16794070. 151110 14:26:49 mysqlbackup: INFO: Compress Backup operation starts with following threads                 1 read-threads    6 process-threads    1 write-threads 151110 14:26:49 mysqlbackup: INFO: System tablespace file format is Antelope. 151110 14:26:49 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. 151110 14:26:49 mysqlbackup: INFO: Copying /data/mysql_new/ibdata1 (Antelope file format). 151110 14:26:49 mysqlbackup: INFO: Found checkpoint at lsn 44556. 151110 14:26:49 mysqlbackup: INFO: Starting log scan from lsn 44544. 151110 14:26:49 mysqlbackup: INFO: Copying log... 151110 14:26:49 mysqlbackup: INFO: Log copied, lsn 44556. 151110 14:26:49 mysqlbackup: INFO: Completing the copy of innodb files. 151110 14:26:50 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server. 151110 14:26:50 mysqlbackup: INFO: Starting to lock all the tables... 151110 14:26:50 mysqlbackup: INFO: All tables are locked and flushed to disk 151110 14:26:50 mysqlbackup: INFO: Opening backup source directory '/data/mysql_new/' 151110 14:26:50 mysqlbackup: INFO: Starting to backup all non-innodb files in         subdirectories of '/data/mysql_new/' 151110 14:26:50 mysqlbackup: INFO: Copying the database directory 'db1' 151110 14:26:50 mysqlbackup: INFO: Copying the database directory 'db2' 151110 14:26:50 mysqlbackup: INFO: Completing the copy of all non-innodb files. 151110 14:26:52 mysqlbackup: INFO: A copied database page was modified at 44233.           (This is the highest lsn found on page)           Scanned log up to lsn 44556.           Was able to parse the log up to lsn 44556.           Maximum page number for a log record 0 151110 14:26:52 mysqlbackup: INFO: All tables unlocked 151110 14:26:52 mysqlbackup: INFO: All MySQL tables were locked for 1.899 seconds. 151110 14:26:52 mysqlbackup: INFO: Reading all global variables from the server. 151110 14:26:52 mysqlbackup: INFO: Completed reading of all global variables from the server. 151110 14:26:52 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /app/backup/2015-11-10_14-26-47 151110 14:26:52 mysqlbackup: INFO: 10 MB of data files compressed to 48 kbytes (compression 99.53%). 151110 14:26:52 mysqlbackup: INFO: Compress Backup operation completed successfully. 151110 14:26:52 mysqlbackup: INFO: Backup created in directory '/app/backup/2015-11-10_14-26-47'   -------------------------------------------------------------    Parameters Summary         -------------------------------------------------------------    Start LSN                  : 44544    End LSN                    : 44556 -------------------------------------------------------------   mysqlbackup completed OK!

[root@drbd-01 backup]# pwd /app/backup [root@drbd-01 backup]# ls 2015-11-10_14-26-47 [root@drbd-01 backup]# ls 2015-11-10_14-26-47 backup-my.cnf  datadir  meta  server-all.cnf  server-my.cnf [root@drbd-01 backup]# ls 2015-11-10_14-26-47/datadir/ db1  db2  ibbackup_logfile  ibdata1.ibz [root@drbd-01 backup]#

五、压缩备份还原

第一步:检测事务日志,并解压

参数说明：uncompress解压压缩后的文件.增量备份不支持压缩. backup-and-apply-log参数不能跟--compress-level同用[root@drbd-01 backup]# mysqlbackup --defaults-file=/etc/my.cnf --uncompress --backup-dir=/app/backup/2015-11-10_14-26-47/ apply-log MySQL Enterprise Backup version 3.12.1 Linux-2.6.18-194.el5-x86_64 [2015/06/26]  Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved.  mysqlbackup: INFO: Starting with following command line ...  mysqlbackup --defaults-file=/etc/my.cnf --uncompress          --backup-dir=/app/backup/2015-11-10_14-26-47/ apply-log   mysqlbackup: INFO:  IMPORTANT: Please check that mysqlbackup run completes successfully.            At the end of a successful 'apply-log' run mysqlbackup            prints "mysqlbackup completed OK!". 151110 14:33:57 mysqlbackup: INFO: MEB logfile created at /app/backup/2015-11-10_14-26-47/meta/MEB_2015-11-10.14-33-57_uncompress_apply_log.log --------------------------------------------------------------------                        Backup Config Options: --------------------------------------------------------------------   datadir = /app/backup/2015-11-10_14-26-47/datadir   innodb_data_home_dir = /app/backup/2015-11-10_14-26-47/datadir   innodb_data_file_path = ibdata1:10M:autoextend   innodb_log_group_home_dir = /app/backup/2015-11-10_14-26-47/datadir   innodb_log_files_in_group = 2   innodb_log_file_size = 5242880   innodb_page_size = 16384   innodb_checksum_algorithm = none  mysqlbackup: INFO: Creating 18 buffers each of size 16794070. 151110 14:33:57 mysqlbackup: INFO: Uncompress operation starts with following threads                 1 read-threads    6 process-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. 151110 14:33:57 mysqlbackup: INFO: Uncompressing /app/backup/2015-11-10_14-26-47/datadir/ibdata1.ibz. 151110 14:33:58 mysqlbackup: INFO: Compressed files removed successfully 151110 14:33:58 mysqlbackup: INFO: Uncompress operation completed successfully.  mysqlbackup: INFO: Creating 12 buffers each of size 65678. 151110 14:33:58 mysqlbackup: INFO: Apply-log operation starts with following threads                 1 read-threads    1 process-threads  mysqlbackup: INFO: Using up to 300 MB of memory. 151110 14:33:58 mysqlbackup: INFO: ibbackup_logfile's creation parameters:           start lsn 44544, end lsn 44556,           start checkpoint 44556. InnoDB: Doing recovery: scanned up to log sequence number 44556  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 5242880  mysqlbackup: INFO: InnoDB: Setting log file size to 5242880 151110 14:33:58 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to           lsn 44556. 151110 14:33:58 mysqlbackup: INFO: The first data file is '/app/backup/2015-11-10_14-26-47/datadir/ibdata1'           and the new created log files are at '/app/backup/2015-11-10_14-26-47/datadir' 151110 14:33:59 mysqlbackup: INFO: Apply-log operation completed successfully. 151110 14:33:59 mysqlbackup: INFO: Full backup prepared for recovery successfully. mysqlbackup completed OK!

第二步:copy物理文件

[root@drbd-01 backup]# mkdir /data/mysql_new2 [root@drbd-01 backup]# chown mysql.mysql /data/mysql_new2

注意:在还原的时候,my.cnf文件中必要要有datadir的参数

[root@drbd-01 backup]# vi /etc/my.cnf

datadir=/data/mysql_new2

：wq

[root@drbd-01 backup]# ls /data/mysql_new2 [root@drbd-01 backup]# mysqlbackup --defaults-file=/etc/my.cnf --innodb_log_files_in_group=2 --backup-dir=/app/backup/2015-11-10_14-26-47/ copy-bak MySQL Enterprise Backup version 3.12.1 Linux-2.6.18-194.el5-x86_64 [2015/06/26] Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved.   mysqlbackup: INFO: Starting with following command line ... mysqlbackup --defaults-file=/etc/my.cnf --innodb_log_files_in_group=2         --backup-dir=/app/backup/2015-11-10_14-26-47/ copy-bak   mysqlbackup: ERROR: Operation command unrecognized:'copy-bak' Use --help option for usage description.   mysqlbackup failed with errors! [root@drbd-01 backup]# mysqlbackup --defaults-file=/etc/my.cnf --innodb_log_files_in_group=2 --backup-dir=/app/backup/2015-11-10_14-26-47/ copy-back MySQL Enterprise Backup version 3.12.1 Linux-2.6.18-194.el5-x86_64 [2015/06/26] Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved.   mysqlbackup: INFO: Starting with following command line ... mysqlbackup --defaults-file=/etc/my.cnf --innodb_log_files_in_group=2         --backup-dir=/app/backup/2015-11-10_14-26-47/ 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!".   151110 14:41:21 mysqlbackup: INFO: MEB logfile created at /app/backup/2015-11-10_14-26-47/meta/MEB_2015-11-10.14-41-21_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:10M:autoextend' 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=5242880' to the target server configuration. --------------------------------------------------------------------                        Server Repository Options: --------------------------------------------------------------------   datadir = /data/mysql_new2   innodb_data_home_dir = /data/mysql_new2   innodb_data_file_path = ibdata1:10M:autoextend   innodb_log_group_home_dir = /data/mysql_new2   innodb_log_files_in_group = 2   innodb_log_file_size = 5242880   innodb_page_size = Null   innodb_checksum_algorithm = none   --------------------------------------------------------------------                        Backup Config Options: --------------------------------------------------------------------   datadir = /app/backup/2015-11-10_14-26-47/datadir   innodb_data_home_dir = /app/backup/2015-11-10_14-26-47/datadir   innodb_data_file_path = ibdata1:10M:autoextend   innodb_log_group_home_dir = /app/backup/2015-11-10_14-26-47/datadir   innodb_log_files_in_group = 2   innodb_log_file_size = 5242880   innodb_page_size = 16384   innodb_checksum_algorithm = none   mysqlbackup: INFO: Creating 14 buffers each of size 16777216. 151110 14:41:21 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. 151110 14:41:22 mysqlbackup: INFO: Copying /app/backup/2015-11-10_14-26-47/datadir/ibdata1. 151110 14:41:22 mysqlbackup: INFO: Copying the database directory 'db1' 151110 14:41:22 mysqlbackup: INFO: Copying the database directory 'db2' 151110 14:41:22 mysqlbackup: INFO: Completing the copy of all non-innodb files. 151110 14:41:22 mysqlbackup: INFO: Copying the log file 'ib_logfile0' 151110 14:41:22 mysqlbackup: INFO: Copying the log file 'ib_logfile1' 151110 14:41:23 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /data/mysql_new2 151110 14:41:23 mysqlbackup: INFO: Copy-back operation completed successfully. 151110 14:41:23 mysqlbackup: INFO: Finished copying backup files to '/data/mysql_new2'   mysqlbackup completed OK! with 2 warnings [root@drbd-01 backup]# ls /data/mysql_new2 backup_variables.txt  db1  db2  ibdata1  ib_logfile0  ib_logfile1  server-all.cnf  server-my.cnf [root@drbd-01 backup]# service mysqld start [root@drbd-01 backup]# ls /data/mysql_new2 backup_variables.txt  db1  db2  ibdata1  ib_logfile0  ib_logfile1  mysql  server-all.cnf  server-my.cnf  test

[root@drbd-01 backup]# mysql Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.73 Source distribution Copyright (c) 2000, 2013, 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 | | db1                | | db2                | | mysql              | | test               | +--------------------+ 5 rows in set (0.01 sec) 六、备份时,同时释放事务日志

参数说明:backup-and-apply-log 这个参数,在备份的时候,就把事务日志检测的功能就完成了,并把ibbackup_logfile与表空间差值的LSN内容放入到了事务日志里面,所以在还原的时候,就只需要备份物理文件就行

[root@drbd-01 backup]# mysqlbackup --defaults-file=/etc/my.cnf --user=root --password=123 --databases="db1 db2" --with-timestamp --backup-dir=/app/backup/ backup-and-apply-log

还原：

[root@drbd-01 mysql_new3]# vi /etc/my.cnf [mysqld] datadir=/data/mysql_new3

[root@drbd-01 mysql_new3]# mysqlbackup --defaults-file=/etc/my.cnf --innodb_log_files_in_group=2 --backup-dir=/app/backup/2015-11-10_15-04-25/ copy-back

[root@drbd-01 mysql_new3]# service mysqld start

[root@drbd-01 mysql_new3]# mysql Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.73 Source distribution Copyright (c) 2000, 2013, 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 | | db1                | | db2                | | mysql              | | test               | +--------------------+ 5 rows in set (0.02 sec) 七、增量备份

第一次增量备份： mysqlbackup  --defaults-file=/home/mysql-server/mysql3/my.cnf  --user=root --password=root   --with-timestamp --databases=”mysql total2” --incremental --incremental-backup-dir=/home/mysql-server/in/  --incremental-base=dir:/home/mysql-server/backup/2012-03-22_11-10-28  backup 参数说明:  --incremental ：代表为增量备份. --incremental-backup-dir : 增量备份存放到哪个路径下 --incremental-base : 增量备份的基础备份或增量备份的文件   第二次增量备份: mysqlbackup  --defaults-file=/home/mysql-server/mysql3/my.cnf  --user=root --password=root   --with-timestamp --databases=”mysql total2”  --incremental --incremental-backup-dir=/home/mysql-server/in/  --incremental-base=dir:/home/mysql-server/backup/2012-03-22_11-15-28  backup   参数说明:  --incremental-base : 这里的备份,就是上一次增量备份的路径 增量备份原理:增量备份是基于第一次的完整备份之后,通过完整最后的LSN点这个基础在往后进行备份.当第二次增量备份的时,就基于前一次增量备份的LSN点的基础在进行备份.所以--incremental-base这个参数在增量备份的时候，是指向上一次全备份或增量备份的LSN点       八、 增量还原 1. 全备检测匹配释放事务日志    mysqlbackup --defaults-file=/home/mysql-server/mysql2/my.cnf  --backup-dir=/home/mysql-server/backup/2012-03-21_17-59-54/ apply-log   2. 检测匹配释放第一次的增量备份    mysqlbackup  --backup-dir=/home/mysql-server/backup/2012-03-21_17-59-54/  --incremental-backup-dir=/home/mysql-server/in/2012-03-22_11-47-47/  apply-incremental-backup   3.检测匹配释放第二次的增量备份   mysqlbackup  --backup-dir=/home/mysql-server/backup/2012-03-21_17-59-54/  --incremental-backup-dir=/home/mysql-server/in/2012-03-22_11-50-47/  apply-incremental-backup   4. 最后进行物理文件复制    mysqlbackup --defaults-file=/home/mysql-server/mysql2/my.cnf  --backup-dir=/home/mysql-server/backup/2012-03-21_17-59-54/copy-back   增量还原原理: 1.               首先检测匹配释放全备事务日志文件(当然如果备份中使用了backup-and-apply-log,在备份的时候,已经检测匹配了,就不需要这一步了) 2.               第一次增量备份的文件释放到全备文件里面.(首先会进入事务日志,然后是表空间),所以--backup-dir指向全备目录（基于LSN点向后增加） 3.               第二次增量备份的文件也是释放到全备文件里面. (首先会进入事务日志,然后是表空间),因为第一次的增量备份后,全库里面已经有了第一次的LSN点,所以二次还原的时候同样指向全备文件里面使LSN点在外后增加 4.               因为增量的页面,已经全部进入了事务日志或表空间,这个时候,就可以直接备份物理文件了.   但最后依然记住… # ======================================================================= 把先备份的系统库MV到原来的地方 修改data目录的权限(如果你设置的是已MySQL用户访问的话)      chown -R mysql.mysql data # ========================================================================= 在启动mysql server..     Mysqlbackup工作原理 1.       mysqlbackup对innodb的表空间进行物理复制,但是,它是记录LSN点的,在备份过程中,新增加的输入直接写入备份文件的ibbackup_logfile中.同时记录最后的LSN点 2.       mysqlbackup对 myisam进行的是锁表全备.就算是增量备份,它依然是全备. 3.       还原的时候,检测对比ibbackup_logfile文件里面与表空间里面的差值,使ibbackup_logfile里面的数据进入事务日志或表空间 4.       在备份文件中的meta/backup_variables.txt文件中记录了备份的一些信息   [backup_variables] start_lsn=1602048                                               #开始备份的LSN点 end_lsn=687810168                                           # 结束LSN点 apply_log_done=1                                             #　是否释放检测ibbackup_logfile文件(0表示没有,1表示已经释放) is_incremental=0                                                #是否是增量为增量备份文件(0:否,1:是) is_incremental_with_redo_log_only=0              #　只配置重做日志,当输入数据大小重做日志大小时,会有一场抛出 is_partial=1                                                        #　是服务器备份还是部分备份（0表示全服务器备份,1表示部分备份） is_compressed=0                                              #是否压缩(0表示没有压缩,1表示压缩) binlog_position=mysql-bin.000001:107          #　二进制文件大小 is_onlyinnodb=0                                               #是否只备份了innodb的表

#######################

参考文档：