<rt id="bn8ez"></rt>
<label id="bn8ez"></label>
  • 

    • <span id="bn8ez"></span>
    

    <label id="bn8ez"><meter id="bn8ez"></meter></label>
    

    
    

    




    







												


        

    qileilove
    

    blog已經(jīng)轉(zhuǎn)移至github,大家請(qǐng)?jiān)L問(wèn) http://qaseven.github.io/
    
			
    
				
					
	
    
		
    
			利用binlog進(jìn)行數(shù)據(jù)庫(kù)的還原
		
    
		
    前言：在學(xué)習(xí)mysql備份的時(shí)候，深深的感受到mysql的備份還原功能沒(méi)有oracle強(qiáng)大；比如一個(gè)很常見(jiàn)的恢復(fù)場(chǎng)景：基于時(shí)間點(diǎn)的恢復(fù)，oracle通過(guò)rman工具就能夠很快的實(shí)現(xiàn)數(shù)據(jù)庫(kù)的恢復(fù)，但是mysql在進(jìn)行不完全恢復(fù)的時(shí)候很大的一部分要依賴(lài)于mysqlbinlog這個(gè)工具運(yùn)行binlog語(yǔ)句來(lái)實(shí)現(xiàn)，本文檔介紹通過(guò)mysqlbinlog實(shí)現(xiàn)各種場(chǎng)景的恢復(fù)；
    　　一、測(cè)試環(huán)境說(shuō)明：使用mysqlbinlog工具的前提需要一個(gè)數(shù)據(jù)庫(kù)的完整性備份，所以需要事先對(duì)數(shù)據(jù)庫(kù)做一個(gè)完整的備份，本文檔通過(guò)mysqlbackup進(jìn)行數(shù)據(jù)庫(kù)的全備
    　　二、測(cè)試步驟說(shuō)明：
    　　數(shù)據(jù)庫(kù)的插入準(zhǔn)備工作
    　　2.1 在時(shí)間點(diǎn)A進(jìn)行一個(gè)數(shù)據(jù)庫(kù)的完整備份；
    　　2.2 在時(shí)間點(diǎn)B創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)BKT,并在BKT下面創(chuàng)建一個(gè)表JOHN，并插入5條數(shù)據(jù)；
    　　2.3 在時(shí)間點(diǎn)C往表JOHN繼續(xù)插入數(shù)據(jù)到10條；
    　　數(shù)據(jù)庫(kù)的恢復(fù)工作
    　　2.4 恢復(fù)數(shù)據(jù)庫(kù)到時(shí)間點(diǎn)A,然后檢查數(shù)據(jù)庫(kù)表的狀態(tài)；
    　　2.5 恢復(fù)數(shù)據(jù)庫(kù)到時(shí)間點(diǎn)B,檢查相應(yīng)的系統(tǒng)狀態(tài)；
    　　2.6 恢復(fù)數(shù)據(jù)庫(kù)到時(shí)間點(diǎn)C，并檢查恢復(fù)的狀態(tài)；
    　　三、場(chǎng)景模擬測(cè)試步驟（備份恢復(fù)是一件很重要的事情）
    　　3.1 執(zhí)行數(shù)據(jù)庫(kù)的全備份；
    　　[root@mysql01 backup]# mysqlbackup --user=root --password --backup-dir=/backup backup-and-apply-log //運(yùn)行數(shù)據(jù)庫(kù)的完整備份
    　　3.2 創(chuàng)建數(shù)據(jù)庫(kù)、表并插入數(shù)據(jù)
    mysql> SELECT CURRENT_TIMESTAMP;
    +---------------------+
    | CURRENT_TIMESTAMP |
    +---------------------+
    | 2014-11-26 17:51:27 |
    +---------------------+
    1 row in set (0.01 sec)
    mysql> show databases; //尚未創(chuàng)建數(shù)據(jù)庫(kù)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 | | | //當(dāng)前數(shù)據(jù)庫(kù)log的pos狀態(tài)
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)
    mysql> SELECT CURRENT_TIMESTAMP; //當(dāng)前的時(shí)間戳 當(dāng)前時(shí)間點(diǎn)A
    +---------------------+
    | CURRENT_TIMESTAMP |
    +---------------------+
    | 2014-11-26 17:54:12 |
    +---------------------+
    1 row in set (0.00 sec)
    mysql> create database BKT; //創(chuàng)建數(shù)據(jù)庫(kù)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條數(shù)據(jù)后數(shù)據(jù)庫(kù)的時(shí)間點(diǎn)B，記錄該點(diǎn)便于數(shù)據(jù)庫(kù)的恢復(fù)
    +---------------------+
    | 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 | | | //當(dāng)前binlog的pos位置
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)
    　3.3 設(shè)置時(shí)間點(diǎn)C的測(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 以上的操作完成之后，便可以執(zhí)行數(shù)據(jù)庫(kù)的恢復(fù)測(cè)試
    [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 //數(shù)據(jù)庫(kù)恢復(fù)完成
    　　授權(quán)并打開(kāi)數(shù)據(jù)庫(kù)
    　　[root@mysql02 data]# chmod -R 777 mysql //需要授權(quán)后才能打開(kāi)
    　　[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 //沒(méi)有BKT數(shù)據(jù)庫(kù)
    [root@mysql02 mysql]# service mysqld start //啟動(dòng)數(shù)據(jù)庫(kù)
    　　3.5 進(jìn)行數(shù)據(jù)庫(kù)的恢復(fù)到時(shí)間點(diǎn)B
    　　[root@mysql02 mysql2]# pwd //備份的時(shí)候，需要備份binlog日志，之前的binlog目錄為/data/mysql2
    　　/data/mysql2
    　　[root@mysql02 mysql2]# mysqlbinlog --start-position=107 --stop-position=1203 mysql-bin.000001| mysql -uroot -p //根據(jù)post的位置進(jìn)行恢復(fù)，當(dāng)前的pos位置為107，恢復(fù)到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) //查看數(shù)據(jù)庫(kù)恢復(fù)成功
    　　3.6 恢復(fù)數(shù)據(jù)庫(kù)到時(shí)間點(diǎn)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 //本次通過(guò)基于時(shí)間點(diǎn)的恢復(fù)，恢復(fù)到時(shí)間點(diǎn)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) //經(jīng)過(guò)檢查成功恢復(fù)到時(shí)間點(diǎn)C
    　　四、mysqlbinlog的其他總結(jié)：以上是利用binlog文件進(jìn)行基于時(shí)間點(diǎn)和binlog的POS位置恢復(fù)的測(cè)試，mysqlbinlog的使用還有很多功能，運(yùn)行mysqlbinlog --help可以查看相應(yīng)參數(shù)；
    　　4.1 查看binlog的內(nèi)容：[root@mysql02 mysql2]# mysqlbinlog mysql-bin.000001
    　　4.2 mysqlbinlog的其他常用參數(shù)：
    　　-h  根據(jù)數(shù)據(jù)庫(kù)的IP
    　　-P  根據(jù)數(shù)據(jù)庫(kù)所占用的端口來(lái)分
    　　-server-id 根據(jù)數(shù)據(jù)庫(kù)serverid來(lái)還原（在集群中很有用）
    　　-d  根據(jù)數(shù)據(jù)庫(kù)名稱(chēng)
    　　例如： [root@mysql02 mysql2]# mysqlbinlog -d BKT mysql-bin.000001 //還原BKT數(shù)據(jù)庫(kù)的信息
    
		
    
			posted on 2014-12-03 13:34 順其自然EVO 閱讀(1367) 評(píng)論(0)  編輯  收藏  所屬分類(lèi): 數(shù)據(jù)庫(kù) 
		
    
	
    
	
	


	


    
	    
    




    








    

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

    

					

    導(dǎo)航
    

					

    統(tǒng)計(jì)
    
	
					
					

    常用鏈接
    


    留言簿(55)
    


		
    隨筆分類(lèi)
    
		
				
			
	
		
    隨筆檔案
    
		
				
			
	
		
    文章分類(lèi)
    
		
				
			
	
		
    文章檔案
    
		
				
			
	



    搜索
    



    最新評(píng)論
	
    



    閱讀排行榜
    



    評(píng)論排行榜
    


				
			
    			
			

	

    







    
        
	



主站蜘蛛池模板：
xxxx日本在线播放免费不卡|
亚洲AV一二三区成人影片|
久久久久亚洲精品男人的天堂|
国产公开免费人成视频|
在线观看成人免费视频|
女性无套免费网站在线看|
在线不卡免费视频|
国产成人在线免费观看|
全部免费毛片在线|
久久精品国产精品亚洲下载|
日韩亚洲变态另类中文|
日韩亚洲欧洲在线com91tv|
久久久久亚洲AV成人无码|
日韩亚洲AV无码一区二区不卡|
少妇中文字幕乱码亚洲影视|
亚洲伊人久久大香线蕉在观|
91在线亚洲综合在线|
国产亚洲福利一区二区免费看
|
亚洲AV成人精品日韩一区18p|
亚洲国产成人精品91久久久
|
久视频精品免费观看99|
国内精品免费麻豆网站91麻豆
|
天黑黑影院在线观看视频高清免费|
成人免费区一区二区三区|
99re免费在线视频|
AV片在线观看免费|
xvideos亚洲永久网址|
亚洲中文字幕第一页在线|
亚洲一区二区在线免费观看|
亚洲伊人久久大香线蕉在观|
国产成人va亚洲电影|
中文字幕免费视频精品一|
最近中文字幕国语免费完整|
天天干在线免费视频|
亚洲午夜精品一级在线播放放|
亚洲AV日韩AV鸥美在线观看|
亚洲已满18点击进入在线观看|
黄网站在线播放视频免费观看|
毛片在线播放免费观看|
大地资源免费更新在线播放|
亚洲精品成人a在线观看|