利用binlog進行數據庫的還原

前言：在學習MySQL備份的時候，深深的感受到mysql的備份還原功能沒有oracle強大；比如一個很常見的恢復場景：基于時間點的恢復，oracle通過rman工具就能夠很快的實現數據庫的恢復，但是mysql在進行不完全恢復的時候很大的一部分要依賴于mysqlbinlog這個工具運行binlog語句來實現，本文檔介紹通過mysqlbinlog實現各種場景的恢復；

一、測試環境說明：使用mysqlbinlog工具的前提需要一個數據庫的完整性備份，所以需要事先對數據庫做一個完整的備份，本文檔通過mysqlbackup進行數據庫的全備（mysqlbackup的使用：http://blog.itpub.net/12679300/viewspace-1329578/）；

二、測試步驟說明：

數據庫的插入準備工作

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
+---------------------+
| 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 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
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
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

四、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數據庫的信息

參數的組合使用：

點擊(此處)折疊或打開

[root@mysql02 mysql2]# mysqlbinlog --start-date=\"2014-11-27 09:21:56\" --stop-date=\"2014-11-27 09:22:33\" -d BKT -h 127.0.0.1 /var/lib/mysql/mysql-bin.000001 ｜mysql -u root -p
#如果有多個binlog文件，用逗號隔開；

4.4 恢復是一件很重要的事情，如果不知道具體要恢復的時間點，請把binlog文件先轉換成文本文件，詳細查看完相應的內容再進行恢復；

[root@mysql02 mysql2]# mysqlbinlogmysql-bin.000001 > /tmp/00001.sql

總結：備份有時候永遠都用不上，但是你永遠也不知道什么時候會用上，正所謂養兵千日用兵一時，作為一個合格的DBA有個可用的備份，就可以做到胸有成竹；

網頁名稱：利用binlog進行數據庫的還原
URL標題：http://m.kartarina.com/article32/pgogsc.html

成都網站建設公司_創新互聯，為您提供Google、定制開發、網站設計公司、品牌網站設計、網站制作、網站排名

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源：創新互聯

猜你還喜歡下面的內容