qileilove
          

          blog已經轉移至github,大家請訪問 http://qaseven.github.io/
          
			
          
				
					
	
          
		
          
			利用binlog進行數據庫的還原
		
          
		
          前言：在學習mysql備份的時候，深深的感受到mysql的備份還原功能沒有oracle強大；比如一個很常見的恢復場景：基于時間點的恢復，oracle通過rman工具就能夠很快的實現數據庫的恢復，但是mysql在進行不完全恢復的時候很大的一部分要依賴于mysqlbinlog這個工具運行binlog語句來實現，本文檔介紹通過mysqlbinlog實現各種場景的恢復；
          　　一、測試環境說明：使用mysqlbinlog工具的前提需要一個數據庫的完整性備份，所以需要事先對數據庫做一個完整的備份，本文檔通過mysqlbackup進行數據庫的全備
          　　二、測試步驟說明：
          　　數據庫的插入準備工作
          　　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
          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
          　　四、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數據庫的信息
          
		
          
			posted on 2014-12-03 13:34 順其自然EVO 閱讀(1370) 評論(0)  編輯  收藏  所屬分類: 數據庫 
		
          
	
          
	
	


	


          
	    
    




          








          

				
			
          
			
          
				
					
          
	
          
		
          <2014年12月>
          
	
          日一二三四五六
          30123456
          78910111213
          14151617181920
          21222324252627
          28293031123
          45678910
          

          

					

          導航
          

					

          統計
          
	
					
					

          常用鏈接
          


          留言簿(55)
          


		
          隨筆分類
          
		
				
			
	
		
          隨筆檔案
          
		
				
			
	
		
          文章分類
          
		
				
			
	
		
          文章檔案
          
		
				
			
	



          搜索
          



          最新評論
	
          

          
	
			
		

          


          閱讀排行榜
          



          評論排行榜
          


				
			
          			
			

	

    







          
        
	




主站蜘蛛池模板：
万全县|
大姚县|
诸暨市|
阿勒泰市|
宝应县|
太仆寺旗|
台安县|
卓尼县|
开鲁县|
邹城市|
洛南县|
新晃|
图木舒克市|
湛江市|
思南县|
合江县|
江油市|
卢氏县|
三明市|
佛学|
南华县|
惠东县|
平山县|
安康市|
庐江县|
长阳|
恩平市|
锡林郭勒盟|
商南县|
沂南县|
于田县|
凤台县|
陇南市|
莲花县|
宁乡县|
刚察县|
碌曲县|
乐都县|
中宁县|
天祝|
阿坝|