教你如何恢复使用MEB备份的MySQL数据库

[root@test bin]# ./mysqlbackup --defaults-file=/service/mysql5.5/my.cnf --socket=/data/mysql5.5/mysql.sock --user=root --backup-dir=/backup/5.5/normal --password backupMySQL Enterprise Backup version 3.8.2 [2013/06/18]Copyright (c) 2003, 2012,Oracle and/or its affiliates. All Rights Reserved.mysqlbackup: INFO: Starting with following command line ..../mysqlbackup --defaults-file=/service/mysql5.5/my.cnf--socket=/data/mysql5.5/mysql.sock --user=root--backup-dir=/backup/5.5/normal --password backupEnter password:mysqlbackup: INFO: MySQL server version is '5.5.25-log'.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 mysqlbackupprints "mysqlbackup completed OK!".

datadir = /data/mysql5.5/innodb_data_home_dir =innodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = /data/mysql5.5/innodb_log_files_in_group = 3innodb_log_file_size = 536870912innodb_page_size = Nullinnodb_checksum_algorithm = innodb

datadir = /backup/5.5/normal/datadirinnodb_data_home_dir = /backup/5.5/normal/datadirinnodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = /backup/5.5/normal/datadirinnodb_log_files_in_group = 3innodb_log_file_size = 536870912innodb_page_size = 16384innodb_checksum_algorithm = innodbmysqlbackup: INFO: Unique generated backup id for this is 13851351066141087mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.mysqlbackup: INFO: Creating 14 buffers each of size 16777216.131122 23:45:08 mysqlbackup: INFO: Full Backup operation starts with following threads1 read-threads  6 process-threads  1 write-threads131122 23:45:08 mysqlbackup: INFO: System tablespace file format is Antelope.131122 23:45:08 mysqlbackup: INFO: Starting to copy all innodb files...131122 23:45:08 mysqlbackup: INFO: Copying /data/mysql5.5/ibdata1 (Antelope file format).131122 23:45:08 mysqlbackup: INFO: Found checkpoint at lsn 3406087628.131122 23:45:08 mysqlbackup: INFO: Starting log scan from lsn 3406087168.131122 23:45:08 mysqlbackup: INFO: Copying log...131122 23:45:08 mysqlbackup: INFO: Log copied, lsn 3406087628.131122 23:45:08 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/_global_qs_variables.ibd (Antelope file format).131122 23:45:08 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/_global_script_report_data.ibd (Antelope file format).131122 23:45:08 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/_global_split_column_names_table.ibd (Antelope file format).131122 23:45:08 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/_global_sql_tokens.ibd (Antelope file format).131122 23:45:08 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/_known_thread_states.ibd (Antelope file format).131122 23:45:08 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/_named_scripts.ibd (Antelope file format).131122 23:45:08 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/_script_statements.ibd (Antelope file format).131122 23:45:08 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/_waits.ibd (Antelope file format).131122 23:45:08 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/help_content.ibd (Antelope file format).131122 23:45:08 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/metadata.ibd (Antelope file format).131122 23:45:08 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/numbers.ibd (Antelope file format).131122 23:45:08 mysqlbackup: INFO: Copying /data/mysql5.5/zwc/t2.ibd (Antelope file format).mysqlbackup: Progress in MB: 200 400131122 23:45:20 mysqlbackup: INFO: Completing the copy of innodb files.131122 23:45:21 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.131122 23:45:21 mysqlbackup: INFO: Starting to lock all the tables...131122 23:45:21 mysqlbackup: INFO: All tables are locked and flushed to disk131122 23:45:21 mysqlbackup: INFO: Opening backup source directory '/data/mysql5.5/'131122 23:45:21 mysqlbackup: INFO: Starting to backup all non-innodb files insubdirectories of '/data/mysql5.5/'131122 23:45:21 mysqlbackup: INFO: Copying the database directory 'common_schema'131122 23:45:21 mysqlbackup: INFO: Copying the database directory 'mysql'131122 23:45:21 mysqlbackup: INFO: Copying the database directory 'performance_schema'131122 23:45:21 mysqlbackup: INFO: Copying the database directory 'test'131122 23:45:21 mysqlbackup: INFO: Copying the database directory 'zwc'mysqlbackup: Progress in MB: 600 800131122 23:45:33 mysqlbackup: INFO: Completing the copy of all non-innodb files.131122 23:45:35 mysqlbackup: INFO: A copied database page was modified at 3406087628.(This is the highest lsn found on page)Scanned log up to lsn 3406090810.Was able to parse the log up to lsn 3406090810.Maximum page number for a log record 315131122 23:45:35 mysqlbackup: INFO: All tables unlocked131122 23:45:35 mysqlbackup: INFO: All MySQL tables were locked for 13.758 seconds.131122 23:45:35 mysqlbackup: INFO: Full Backup operation completed successfully.131122 23:45:35 mysqlbackup: INFO: Backup created in directory '/backup/5.5/normal'131122 23:45:35 mysqlbackup: INFO: MySQL binlog position: filename ZWC-TEST.000024, position 16178-------------------------------------------------------------Parameters Summary    -------------------------------------------------------------Start LSN         : 3406087168End LSN          : 3406090810-------------------------------------------------------------mysqlbackup completed OK!

[root@test bin]# ./mysqlbackup --defaults-file=/service/mysql5.5/my.cnf --socket=/data/mysql5.5/mysql.sock --user=root --backup-dir=/backup/5.5/compress --compress --password backupMySQL Enterprise Backup version 3.8.2 [2013/06/18]Copyright (c) 2003, 2012, Oracle and/or its affiliates. All Rights Reserved.mysqlbackup: INFO: Starting with following command line ..../mysqlbackup --defaults-file=/service/mysql5.5/my.cnf--socket=/data/mysql5.5/mysql.sock --user=root--backup-dir=/backup/5.5/compress --compress --password backupEnter password:mysqlbackup: INFO: MySQL server version is '5.5.25-log'.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 mysqlbackupprints "mysqlbackup completed OK!".

datadir = /data/mysql5.5/innodb_data_home_dir =innodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = /data/mysql5.5/innodb_log_files_in_group = 3innodb_log_file_size = 536870912innodb_page_size = Nullinnodb_checksum_algorithm = innodb

datadir = /backup/5.5/compress/datadirinnodb_data_home_dir = /backup/5.5/compress/datadirinnodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = /backup/5.5/compress/datadirinnodb_log_files_in_group = 3innodb_log_file_size = 536870912innodb_page_size = 16384innodb_checksum_algorithm = innodbmysqlbackup: INFO: Unique generated backup id for this is 13851352214073555mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.mysqlbackup: INFO: Creating 18 buffers each of size 16794070.131122 23:47:03 mysqlbackup: INFO: Compress Backup operation starts with following threads1 read-threads  6 process-threads  1 write-threads131122 23:47:03 mysqlbackup: INFO: System tablespace file format is Antelope.131122 23:47:03 mysqlbackup: INFO: Starting to copy all innodb files...131122 23:47:03 mysqlbackup: INFO: Copying /data/mysql5.5/ibdata1 (Antelope file format).131122 23:47:03 mysqlbackup: INFO: Found checkpoint at lsn 3406092146.131122 23:47:03 mysqlbackup: INFO: Starting log scan from lsn 3406091776.131122 23:47:03 mysqlbackup: INFO: Copying log...131122 23:47:03 mysqlbackup: INFO: Log copied, lsn 3406092146.131122 23:47:03 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/_global_qs_variables.ibd (Antelope file format).131122 23:47:03 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/_global_script_report_data.ibd (Antelope file format).131122 23:47:03 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/_global_split_column_names_table.ibd (Antelope file format).131122 23:47:03 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/_global_sql_tokens.ibd (Antelope file format).131122 23:47:03 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/_known_thread_states.ibd (Antelope file format).131122 23:47:03 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/_named_scripts.ibd (Antelope file format).131122 23:47:03 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/_script_statements.ibd (Antelope file format).131122 23:47:03 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/_waits.ibd (Antelope file format).131122 23:47:03 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/help_content.ibd (Antelope file format).131122 23:47:03 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/metadata.ibd (Antelope file format).131122 23:47:03 mysqlbackup: INFO: Copying /data/mysql5.5/common_schema/numbers.ibd (Antelope file format).131122 23:47:03 mysqlbackup: INFO: Copying /data/mysql5.5/zwc/t2.ibd (Antelope file format).mysqlbackup: Progress in MB: 200 400131122 23:47:11 mysqlbackup: INFO: Completing the copy of innodb files.131122 23:47:14 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.131122 23:47:14 mysqlbackup: INFO: Starting to lock all the tables...131122 23:47:14 mysqlbackup: INFO: All tables are locked and flushed to disk131122 23:47:14 mysqlbackup: INFO: Opening backup source directory '/data/mysql5.5/'131122 23:47:14 mysqlbackup: INFO: Starting to backup all non-innodb files insubdirectories of '/data/mysql5.5/'131122 23:47:14 mysqlbackup: INFO: Copying the database directory 'common_schema'131122 23:47:14 mysqlbackup: INFO: Copying the database directory 'mysql'131122 23:47:15 mysqlbackup: INFO: Copying the database directory 'performance_schema'131122 23:47:15 mysqlbackup: INFO: Copying the database directory 'test'131122 23:47:15 mysqlbackup: INFO: Copying the database directory 'zwc'mysqlbackup: Progress in MB: 600 800131122 23:47:26 mysqlbackup: INFO: Completing the copy of all non-innodb files.131122 23:47:28 mysqlbackup: INFO: A copied database page was modified at 3406092146.(This is the highest lsn found on page)Scanned log up to lsn 3406095314.Was able to parse the log up to lsn 3406095314.Maximum page number for a log record 319131122 23:47:29 mysqlbackup: INFO: All tables unlocked131122 23:47:29 mysqlbackup: INFO: All MySQL tables were locked for 14.330 seconds.131122 23:47:29 mysqlbackup: INFO: Compressed 496 MB of data files to 200577 kbytes (compression 60.51%).131122 23:47:29 mysqlbackup: INFO: Compress Backup operation completed successfully.131122 23:47:29 mysqlbackup: INFO: Backup created in directory '/backup/5.5/compress'131122 23:47:29 mysqlbackup: INFO: MySQL binlog position: filename ZWC-TEST.000024, position 24831-------------------------------------------------------------Parameters Summary    -------------------------------------------------------------Start LSN         : 3406091776End LSN          : 3406095314-------------------------------------------------------------mysqlbackup completed OK!

[root@test bin]# ./mysqlbackup --defaults-file=/service/mysql5.5/my.cnf --backup-dir=/backup/5.5/normal apply-logMySQL Enterprise Backup version 3.8.2 [2013/06/18]Copyright (c) 2003, 2012, Oracle and/or its affiliates. All Rights Reserved.mysqlbackup: INFO: Starting with following command line ..../mysqlbackup --defaults-file=/service/mysql5.5/my.cnf--backup-dir=/backup/5.5/normal apply-logIMPORTANT: Please check that mysqlbackup run completes successfully.At the end of a successful 'apply-log' run mysqlbackupprints "mysqlbackup completed OK!".

datadir = /backup/5.5/normal/datadirinnodb_data_home_dir = /backup/5.5/normal/datadirinnodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = /backup/5.5/normal/datadirinnodb_log_files_in_group = 3innodb_log_file_size = 536870912innodb_page_size = 16384innodb_checksum_algorithm = innodbmysqlbackup: INFO: Uses posix_fadvise() for performance optimization.mysqlbackup: INFO: Creating 14 buffers each of size 65536.131122 23:59:39 mysqlbackup: INFO: Apply-log operation starts with following threads1 read-threads  1 process-threads131122 23:59:39 mysqlbackup: INFO: ibbackup_logfile's creation parameters:start lsn 3406087168, end lsn 3406090810,start checkpoint 3406087628.InnoDB: Doing recovery: scanned up to log sequence number 3406090810mysqlbackup: 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 Setting log file size to 536870912InnoDB: Progress in MB: 100 200 300 400 500Setting log file size to 536870912InnoDB: Progress in MB: 100 200 300 400 500Setting log file size to 536870912InnoDB: Progress in MB: 100 200 300 400 500131123 00:00:01 mysqlbackup: INFO: We were able to parse ibbackup_logfile up tolsn 3406090810.mysqlbackup: INFO: Last MySQL binlog file position 0 16178, file name ./ZWC-TEST.000024131123 00:00:01 mysqlbackup: INFO: The first data file is '/backup/5.5/normal/datadir/ibdata1'and the new created log files are at '/backup/5.5/normal/datadir'131123 00:00:01 mysqlbackup: INFO: Apply-log operation completed successfully.131123 00:00:01 mysqlbackup: INFO: Full backup prepared for recovery successfully.mysqlbackup completed OK![root@test bin]# ./mysqlbackup --defaults-file=/service/mysql5.5/my.cnf --backup-dir=/backup/5.5/normal copy-backMySQL Enterprise Backup version 3.8.2 [2013/06/18]Copyright (c) 2003, 2012, Oracle and/or its affiliates. All Rights Reserved.mysqlbackup: INFO: Starting with following command line ..../mysqlbackup --defaults-file=/service/mysql5.5/my.cnf--backup-dir=/backup/5.5/normal copy-backIMPORTANT: Please check that mysqlbackup run completes successfully.At the end of a successful 'copy-back' run mysqlbackupprints "mysqlbackup completed OK!".

datadir = /data/mysql5.5innodb_data_home_dir = /data/mysql5.5innodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = /data/mysql5.5innodb_log_files_in_group = 3innodb_log_file_size = 512Minnodb_page_size = Nullinnodb_checksum_algorithm = innodb

datadir = /backup/5.5/normal/datadirinnodb_data_home_dir = /backup/5.5/normal/datadirinnodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = /backup/5.5/normal/datadirinnodb_log_files_in_group = 3innodb_log_file_size = 536870912innodb_page_size = 16384innodb_checksum_algorithm = innodbmysqlbackup: INFO: Uses posix_fadvise() for performance optimization.mysqlbackup: INFO: Creating 14 buffers each of size 16777216.131123 00:00:44 mysqlbackup: INFO: Copy-back operation starts with following threads1 read-threads  1 write-threads131123 00:00:45 mysqlbackup: INFO: Copying /backup/5.5/normal/datadir/ibdata1.131123 00:00:45 mysqlbackup: INFO: Copying /backup/5.5/normal/datadir/common_schema/_global_qs_variables.ibd.131123 00:00:45 mysqlbackup: INFO: Copying /backup/5.5/normal/datadir/common_schema/_global_script_report_data.ibd.131123 00:00:45 mysqlbackup: INFO: Copying /backup/5.5/normal/datadir/common_schema/_global_split_column_names_table.ibd.131123 00:00:45 mysqlbackup: INFO: Copying /backup/5.5/normal/datadir/common_schema/_global_sql_tokens.ibd.131123 00:00:45 mysqlbackup: INFO: Copying /backup/5.5/normal/datadir/common_schema/_known_thread_states.ibd.131123 00:00:45 mysqlbackup: INFO: Copying /backup/5.5/normal/datadir/common_schema/_named_scripts.ibd.131123 00:00:45 mysqlbackup: INFO: Copying /backup/5.5/normal/datadir/common_schema/_script_statements.ibd.131123 00:00:45 mysqlbackup: INFO: Copying /backup/5.5/normal/datadir/common_schema/_waits.ibd.131123 00:00:45 mysqlbackup: INFO: Copying /backup/5.5/normal/datadir/common_schema/help_content.ibd.131123 00:00:45 mysqlbackup: INFO: Copying /backup/5.5/normal/datadir/common_schema/metadata.ibd.131123 00:00:45 mysqlbackup: INFO: Copying /backup/5.5/normal/datadir/common_schema/numbers.ibd.131123 00:00:45 mysqlbackup: INFO: Copying /backup/5.5/normal/datadir/zwc/t2.ibd.mysqlbackup: Progress in MB: 200 400131123 00:00:54 mysqlbackup: INFO: Copying the database directory 'common_schema'131123 00:00:55 mysqlbackup: INFO: Copying the database directory 'mysql'131123 00:00:55 mysqlbackup: INFO: Copying the database directory 'performance_schema'131123 00:00:55 mysqlbackup: INFO: Copying the database directory 'test'131123 00:00:55 mysqlbackup: INFO: Copying the database directory 'zwc'mysqlbackup: Progress in MB: 600 800131123 00:01:06 mysqlbackup: INFO: Completing the copy of all non-innodb files.131123 00:01:06 mysqlbackup: INFO: Copying the log file 'ib_logfile0'mysqlbackup: Progress in MB: 1000 1200 1400131123 00:01:16 mysqlbackup: INFO: Copying the log file 'ib_logfile1'mysqlbackup: Progress in MB: 1600 1800131123 00:01:27 mysqlbackup: INFO: Copying the log file 'ib_logfile2'mysqlbackup: Progress in MB: 2000 2200 2400131123 00:01:38 mysqlbackup: INFO: Copy-back operation completed successfully.131123 00:01:38 mysqlbackup: INFO: Finished copying backup files to '/data/mysql5.5'mysqlbackup completed OK!

[root@test mysql5.5]# chown -R mysql.mysql *[root@test mysql5.5]# /service/mysql5.5/bin/mysqld_safe --defaults-file=/service/mysql5.5/my.cnf --basedir=/service/mysql5.5 --datadir=/data/mysql5.5 --user=mysql &[1] 6525[root@test mysql5.5]# 131123 00:04:00 mysqld_safe Logging to '/data/mysql5.5/test.err'.131123 00:04:00 mysqld_safe Starting mysqld daemon with databases from /data/mysql5.5[root@test mysql5.5]# cat test.err131123 00:04:00 mysqld_safe Starting mysqld daemon with databases from /data/mysql5.5131123 0:04:00 InnoDB: The InnoDB memory heap is disabled131123 0:04:00 InnoDB: Mutexes and rw_locks use GCC atomic builtins131123 0:04:00 InnoDB: Compressed tables use zlib 1.2.3131123 0:04:00 InnoDB: Initializing buffer pool, size = 2.4G131123 0:04:00 InnoDB: Completed initialization of buffer pool131123 0:04:00 InnoDB: highest supported file format is Barracuda.InnoDB: Log scan progressed past the checkpoint lsn 3406091276131123 0:04:00 InnoDB: Database was not shut down normally!InnoDB: Starting crash recovery.InnoDB: Reading tablespace information from the .ibd files...InnoDB: Restoring possible half-written data pages from the doublewriteInnoDB: buffer...InnoDB: Doing recovery: scanned up to log sequence number 3406091710131123 0:04:01 InnoDB: Starting an apply batch of log records to the database...InnoDB: Progress in percents: 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 99InnoDB: Apply batch completedInnoDB: Last MySQL binlog file position 0 16178, file name ./ZWC-TEST.000024131123 0:04:01 InnoDB: Waiting for the background threads to start131123 0:04:02 InnoDB: 1.1.8 started; log sequence number 3406091710131123 0:04:02 [Note] Recovering after a crash using ZWC-TEST131123 0:04:02 [Note] Starting crash recovery...131123 0:04:02 [Note] Crash recovery finished.131123 0:04:02 [Note] Server hostname (bind-address): '0.0.0.0'; port: 30306131123 0:04:02 [Note] - '0.0.0.0' resolves to '0.0.0.0';131123 0:04:02 [Note] Server socket created on IP: '0.0.0.0'.131123 0:04:02 [Warning] 'user' entry 'root@test' ignored in --skip-name-resolve mode.131123 0:04:02 [Warning] 'user' entry '@test' ignored in --skip-name-resolve mode.131123 0:04:02 [Warning] 'proxies_priv' entry '@ root@test' ignored in --skip-name-resolve mode.131123 0:04:02 [Note] Event Scheduler: Loaded 0 events131123 0:04:02 [Note] /service/mysql5.5/bin/mysqld: ready for connections.Version: '5.5.25-log' socket: '/data/mysql5.5/mysql.sock' port: 30306 ZWC131123 0:04:02 [Note] Event Scheduler: scheduler thread started with id 1

[root@test mysql5.5]# /service/mysql5.5/bin/mysql -uroot -p -D zwcEnter password:Welcome to the MySQL monitor. Commands end with ; or /g.Your MySQL connection id is 6Server version: 5.5.25-log ZWCCopyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.root@linuxidc12:05:07>select count(*),now() from t1;+----------+---------------------+| count(*) | now()       |+----------+---------------------+| 10000000 | 2013-11-23 00:05:17 |+----------+---------------------+1 row in set (0.04 sec)root@linuxidc12:05:17>select count(*),now() from t2;+----------+---------------------+| count(*) | now()       |+----------+---------------------+| 10000000 | 2013-11-23 00:05:19 |+----------+---------------------+1 row in set (2.91 sec)