mysql> SELECT CURRENT_TIMESTAMP; +---------------------+ | CURRENT_TIMESTAMP | +---------------------+ | 2014-11-26 17:51:27 | +---------------------+ 1 row in set (0.01 sec)
mysql> show databases; //尚未創建數據庫BKT +--------------------+ | Database | +--------------------+ | information_schema | | john | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.03 sec)
mysql> Ctrl-C -- Aborted [root@mysql02 data]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or //g. Your MySQL connection id is 2 Server version: 5.5.36-log Source distribution Copyright (c) 2000, 2014, 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 master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 107 | | | //當前數據庫log的pos狀態 +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> SELECT CURRENT_TIMESTAMP; //當前的時間戳 當前時間點A +---------------------+ | CURRENT_TIMESTAMP | +---------------------+ | 2014-11-26 17:54:12 | +---------------------+ 1 row in set (0.00 sec) mysql> create database BKT; //創建數據庫BKT Query OK, 1 row affected (0.01 sec) mysql> create table john (id varchar(32)); ERROR 1046 (3D000): No database selected mysql> use bkt; ERROR 1049 (42000): Unknown database /'bkt/' mysql> use BKT; Database changed mysql> create table john (id varchar(32)); Query OK, 0 rows affected (0.02 sec) mysql> insert into john values(/'1/'); Query OK, 1 row affected (0.01 sec) mysql> insert into john values(/'2/'); Query OK, 1 row affected (0.01 sec) mysql> insert into john values(/'3/'); Query OK, 1 row affected (0.00 sec) mysql> insert into john values(/'4/'); Query OK, 1 row affected (0.01 sec) mysql> insert into john values(/'5/'); Query OK, 1 row affected (0.01 sec) mysql> SELECT CURRENT_TIMESTAMP; //插入5條數據后數據庫的時間點B，記錄該點便于數據庫的恢復 +---------------------+ | CURRENT_TIMESTAMP | +---------------------+ | 2014-11-26 17:55:53 | +---------------------+ 1 row in set (0.00 sec)
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 1204 | | | //當前binlog的pos位置 +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 3.3 設置時間點C的測試
點擊(此處)折疊或打開
mysql> insert into john values(/'6/'); Query OK, 1 row affected (0.02 sec) mysql> insert into john values(/'7/'); Query OK, 1 row affected (0.01 sec) mysql> insert into john values(/'8/'); Query OK, 1 row affected (0.01 sec) mysql> insert into john values(/'9/'); Query OK, 1 row affected (0.01 sec) mysql> insert into john values(/'10/'); Query OK, 1 row affected (0.03 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 2125 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> SELECT CURRENT_TIMESTAMP; +---------------------+ | CURRENT_TIMESTAMP | +---------------------+ | 2014-11-26 17:58:08 | +---------------------+ 1 row in set (0.00 sec) 3.4 以上的操作完成之后，便可以執行數據庫的恢復測試
點擊(此處)折疊或打開
[root@mysql02 data]# mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql --backup-dir=/backup/ copy-back MySQL Enterprise Backup version 3.11.0 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 [2014/08/26] Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql --backup-dir=/backup/ 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!/". 141126 17:59:58 mysqlbackup: INFO: MEB logfile created at /backup/meta/MEB_2014-11-26.17-59-58_copy_back.log -------------------------------------------------------------------- Server Repository Options: -------------------------------------------------------------------- datadir = /data/mysql innodb_data_home_dir = /data/mysql 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 = /backup/datadir innodb_data_home_dir = /backup/datadir innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /backup/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. 141126 17:59:58 mysqlbackup: INFO: Copy-back operation starts with following threads 1 read-threads 1 write-threads mysqlbackup: INFO: Could not find binlog index file. If this is online backup then server may not have started with --log-bin. Hence, binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible. 141126 17:59:58 mysqlbackup: INFO: Copying /backup/datadir/ibdata1. mysqlbackup: Progress in MB: 200 400 600 141126 18:00:22 mysqlbackup: INFO: Copying the database directory /'john/' 141126 18:00:23 mysqlbackup: INFO: Copying the database directory /'mysql/' 141126 18:00:23 mysqlbackup: INFO: Copying the database directory /'performance_schema/' 141126 18:00:23 mysqlbackup: INFO: Completing the copy of all non-innodb files. 141126 18:00:23 mysqlbackup: INFO: Copying the log file /'ib_logfile0/' 141126 18:00:23 mysqlbackup: INFO: Copying the log file /'ib_logfile1/' 141126 18:00:24 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /data/mysql 141126 18:00:24 mysqlbackup: INFO: Copy-back operation completed successfully. 141126 18:00:24 mysqlbackup: INFO: Finished copying backup files to /'/data/mysql/' mysqlbackup completed //數據庫恢復完成 授權并打開數據庫
[root@mysql02 mysql2]# pwd //備份的時候，需要備份binlog日志，之前的binlog目錄為/data/mysql2 /data/mysql2 [root@mysql02 mysql2]# mysqlbinlog --start-position=107 --stop-position=1203 mysql-bin.000001| mysql -uroot -p //根據post的位置進行恢復，當前的pos位置為107，恢復到pos位置到1203 Enter password: [root@mysql02 mysql2]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or //g. Your MySQL connection id is 3 Server version: 5.5.36-log Source distribution Copyright (c) 2000, 2014, 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 | | BKT | | john | | mysql | | performance_schema | +--------------------+ 5 rows in set (0.02 sec) mysql> use BKT Database changed mysql> show tables; +---------------+ | Tables_in_BKT | +---------------+ | john | +---------------+ 1 row in set (0.00 sec) mysql> select * from john; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.01 sec) //查看數據庫恢復成功 3.6 恢復數據庫到時間點C
點擊(此處)折疊或打開
[root@mysql02 mysql2]# mysqlbinlog --start-date=/"2014-11-27 09:21:56/" --stop-date=/"2014-11-27 09:22:33/" mysql-bin.000001| mysql -uroot -p123456 //本次通過基于時間點的恢復，恢復到時間點C Warning: Using unique option prefix start-date instead of start-datetime is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix stop-date instead of stop-datetime is deprecated and will be removed in a future release. Please use the full name instead. [root@mysql02 mysql2]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or //g. Your MySQL connection id is 6 Server version: 5.5.36-log Source distribution Copyright (c) 2000, 2014, 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 | | BKT | | john | | mysql | | performance_schema | +--------------------+ 5 rows in set (0.00 sec) mysql> use BKT Database changed mysql> select * from john; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +------+ 10 rows in set (0.00 sec) //經過檢查成功恢復到時間點C
