mysql企业备份工具mysqlbackup

最新推荐文章于 2022-11-17 12:40:24 发布

cjfcxf010101

最新推荐文章于 2022-11-17 12:40:24 发布

阅读量249

点赞数

文章标签：数据库

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]

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]

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

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]

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...

Filling help tables...

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

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=1

1为快速压缩.共有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]

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]

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...

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]

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]

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

--------------------------------------------------------------------

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

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

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