利用binlog進行資料庫的還原

　　二、測試步驟說明：

　　2.1 在時間點A進行一個資料庫的完整備份；

　　2.2 在時間點B建立一個資料庫BKT,并在BKT下面建立一個表JOHN，并插入5條資料；

　　2.3 在時間點C往表JOHN繼續插入資料到10條；

　　資料庫的恢複工作

　　2.4 恢複資料庫到時間點A,然後檢查資料庫表的狀态；

　　2.5 恢複資料庫到時間點B,檢查相應的系統狀态；

　　2.6 恢複資料庫到時間點C，并檢查恢複的狀态；

　　三、場景模拟測試步驟（備份恢複是一件很重要的事情）

　　3.1 執行資料庫的全備份；

　　[root@mysql01 backup]# mysqlbackup --user=root --password --backup-dir=/backup backup-and-apply-log //運作資料庫的完整備份

　　3.2 建立資料庫、表并插入資料

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

| 2014-11-26 17:54:12 |

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

Query OK, 0 rows affected (0.02 sec)

mysql> insert into john values(\'1\');

mysql> insert into john values(\'2\');

mysql> insert into john values(\'3\');

Query OK, 1 row affected (0.00 sec)

mysql> insert into john values(\'4\');

mysql> insert into john values(\'5\');

mysql> SELECT CURRENT_TIMESTAMP; //插入5條資料後資料庫的時間點B，記錄該點便于資料庫的恢複

| 2014-11-26 17:55:53 |

| mysql-bin.000001 | 1204 | | | //目前binlog的pos位置

　3.3 設定時間點C的測試

mysql> insert into john values(\'6\');

Query OK, 1 row affected (0.02 sec)

mysql> insert into john values(\'7\');

mysql> insert into john values(\'8\');

mysql> insert into john values(\'9\');

mysql> insert into john values(\'10\');

Query OK, 1 row affected (0.03 sec)

| mysql-bin.000001 | 2125 | | |

| 2014-11-26 17:58:08 |

　　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_log_group_home_dir = /backup/datadir

innodb_page_size = 16384

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 data]# chmod -R 777 mysql //需要授權後才能打開

　　[root@mysql02 data]# cd mysql

　　[root@mysql02 mysql]# ll

　　總用量 733220

-rwxrwxrwx. 1 root root 305 11月 26 18:00 backup_variables.txt

-rwxrwxrwx. 1 root root 740294656 11月 26 18:00 ibdata1

-rwxrwxrwx. 1 root root 5242880 11月 26 18:00 ib_logfile0

-rwxrwxrwx. 1 root root 5242880 11月 26 18:00 ib_logfile1

drwxrwxrwx. 2 root root 4096 11月 26 18:00 john

drwxrwxrwx. 2 root root 4096 11月 26 18:00 mysql

drwxrwxrwx. 2 root root 4096 11月 26 18:00 performance_schema

-rwxrwxrwx. 1 root root 8488 11月 26 18:00 server-all.cnf

-rwxrwxrwx. 1 root root 1815 11月 26 18:00 server-my.cnf //沒有BKT資料庫

[root@mysql02 mysql]# service mysqld start //啟動資料庫

　　3.5 進行資料庫的恢複到時間點B

　　[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

[root@mysql02 mysql2]# mysql -uroot -p

Your MySQL connection id is 3

mysql> show databases;

| BKT |

5 rows in set (0.02 sec)

mysql> use BKT

mysql> show tables;

+---------------+

| Tables_in_BKT |

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.

Your MySQL connection id is 6

5 rows in set (0.00 sec)

| 6 |

| 7 |

| 8 |

| 9 |

| 10 |

10 rows in set (0.00 sec) //經過檢查成功恢複到時間點C

　　四、mysqlbinlog的其他總結：以上是利用binlog檔案進行基于時間點和binlog的POS位置恢複的測試，mysqlbinlog的使用還有很多功能，運作mysqlbinlog --help可以檢視相應參數；

　　4.1 檢視binlog的内容：[root@mysql02 mysql2]# mysqlbinlog mysql-bin.000001

　　4.2 mysqlbinlog的其他常用參數：

　　-h  根據資料庫的IP

　　-P  根據資料庫所占用的端口來分

　　-server-id 根據資料庫serverid來還原（在叢集中很有用）

　　-d  根據資料庫名稱

　　例如： [root@mysql02 mysql2]# mysqlbinlog -d BKT mysql-bin.000001 //還原BKT資料庫的資訊

最新内容請見作者的GitHub頁：http://qaseven.github.io/