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=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]
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的表
#######################
參考文件：
http://blog.csdn.net/m582445672/article/details/7649944
http://blog.itpub.net/12679300/viewspace-1262774/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28916011/viewspace-1836376/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql企業備份工具(MEB)之mysqlbackup安裝及使用MySql
- Mysqlbackup 備份詳解MySql
- MYSQL 企業級備份MySql
- mysql備份工具MySql
- 【MySql】 MySql備份工具Xtrabackup之二MySql
- 【MySql】 MySql備份工具Xtrabackup之一MySql
- MYSQLBACKUP工具的使用MySql
- Mysql 企業級備份與恢復(學習筆記七)MySql筆記
- MySQL 5.7 mysqlpump 備份工具說明MySql
- XtraBackup 2.2.8 釋出，MySQL 備份工具MySql
- Mysql 物理備份工具xtrabackup的使用MySql
- MySQL中如何選擇合適的備份策略和備份工具MySql
- MySQL資料庫備份種類以及常用備份工具彙總MySql資料庫
- MySQL 資料庫備份種類以及常用備份工具彙總MySql資料庫
- MySQL備份和恢復工具圖譜MySql
- mysql的常用備份工具：mysqldump和mysqlhotcopyMySql
- 企業雲盤是企業的必備辦公工具
- MySQL DBA備份工具Xtrabackup使用指南（轉）MySql
- 企業網盤伺服器資料異地備份、遠端備份、增量備份解決方案伺服器
- 【備份工具】mydumper
- mysql的冷備份與熱備份MySql
- MySQL資料庫備份工具Mydumper使用介紹MySql資料庫
- 建立MySQL資料庫備份的好工具：XtraBackupMySql資料庫
- Mysql備份系列MySql
- Java備份MySQLJavaMySql
- MySQL：xtrabackup備份MySql
- XtraBackup備份MySQLMySql
- Mysql增量備份MySql
- mysql備份--mysqlhotcopyMySql
- 企業低成本備份和恢復策略探析
- 關於企業的備份幾點總結
- mysqlbackup線上配置Mysql主從架構MySql架構
- Mysql備份與恢復(1)---物理備份MySql
- 初探MySQL資料備份及備份原理MySql
- MySQL · 物理備份 · Percona XtraBackup 備份原理MySql
- MySQL備份與主備配置MySql
- mysql多執行緒備份與還原工具mydumperMySql執行緒
- MySQL5.7新特性之備份工具mysqlpump的使用MySql