mysql backup server_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/目录还是空的