假設每天XXX點用xtraceback進行一個全備。
截至到xxx點binlog寫到0009

[root@jonn mysql]# ls
ch ib_logfile1 mysqlbin.000001 mysqlbin.000004 mysqlbin.000007 mysqlbin.index xtrabackup_binlog_pos_innodb
ibdata1 jonn.com.err mysqlbin.000002 mysqlbin.000005 mysqlbin.000008 mysql.sock xtrabackup_checkpoints
ib_logfile0 mysql mysqlbin.000003 mysqlbin.000006 mysqlbin.000009 test zabbix

今天突然之間誤操作了，，需要進行不完全恢復

今天做的一些操作
mysql> select * from xs2;
+------+------+
| id | name |
+------+------+
| 2 | mary |
| 2 | mary |
| 3 | tom |
| 4 | tom |
+------+------+
4 rows in set (0.00 sec)

mysql> select * from xs;
+------+------+
| id | name |
+------+------+
| 2 | mary |
| 2 | mary |
| 3 | tom |
| 4 | tom |
+------+------+
4 rows in set (0.00 sec)
mysql> insert into xs select * from xs; ---存在09log中
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> insert into xs select * from xs; ---存在09log中
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0 ---此時xs有16條

mysql> flush logs; ----為了實驗需求產生新的log.10
Query OK, 0 rows affected (0.01 sec)

mysql> create table xs3 as select * from xs2;
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> flush logs; ---------為了實驗需求產生新的log。11
Query OK, 0 rows affected (0.02 sec)

mysql> create table xs4 as select * from xs2;
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> show tables;
+--------------+
| Tables_in_ch |
+--------------+
| xs |
| xs2 |
| xs3 |
| xs4 |
+--------------+
4 rows in set (0.00 sec)

mysql> select * from xs4;
+------+------+
| id | name |
+------+------+
| 2 | mary |
| 2 | mary |
| 3 | tom |
| 4 | tom |
+------+------+
4 rows in set (0.00 sec)

mysql> delete from xs4 where id=2; ---誤操作
Query OK, 2 rows affected (0.00 sec)

mysql> insert into xs select * from xs;
Query OK, 16 rows affected (0.01 sec)
Records: 16 Duplicates: 0 Warnings: 0 ---xs=32條
[root@jonn mysql999999]# ls ---今天又產生了一些log 已經到11了
ch ib_logfile1 mysqlbin.000001 mysqlbin.000004 mysqlbin.000007 mysqlbin.000010 test zabbix
ibdata1 jonn.com.err mysqlbin.000002 mysqlbin.000005 mysqlbin.000008 mysqlbin.000011 xtrabackup_binlog_pos_innodb
ib_logfile0 mysql mysqlbin.000003 mysqlbin.000006 mysqlbin.000009 mysqlbin.index xtrabackup_checkpoints

恢復

1，首先關庫，創造恢復環境
mv mysql mysql9999;
mkdir mysql；
2.恢復全備
[root@jonn 2015-10-21_15-44-45]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=ESBecs00 --apply-log /xback/full/2015-10-21_15-44-45/
[root@jonn 2015-10-21_15-44-45]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=ESBecs00 --copy-back /xback/full/2015-10-21_15-44-45/

3.開庫，全備恢復後binlog重置為01了
[root@jonn mysql]# ls
ch ib_logfile0 jonn.com.err mysqlbin.000001 mysql.sock xtrabackup_binlog_pos_innodb zabbix
ibdata1 ib_logfile1 mysql mysqlbin.index test xtrabackup_checkpoints

4進入到mysql99999備份，恢復binlog
mysqlbinlog --start-position=XXXXX mysqlbin.000009|mysql -u root -pESBecs00 ---地點可以透過檢視備份日誌來檢視

$ cat /path/to/backup/xtrabackup_binlog_info

mysql-bin.000003 57

如果使用了gtid，那麼記錄的就是
[root@HaoDai_App_DB02 2015-12-23_04-00-02]# more xtrabackup_binlog_info
6c8a10ed-ed0b-11e4-91eb-00163ec546ca:1-96083689

然後到binlog去找96083689對應的log pos吧（這個地方我也不知道該選gtid開始前的一個pos還是結束時的pos，那就先試前面的，再試後面的）
#151221 9:24:37 server id 13307 end_log_pos 172914 CRC32 0xb33d7b00 Xid = 203873263
COMMIT/*!*/;
# at 172914
#151221 9:24:37 server id 13307 end_log_pos 172962 CRC32 0xa9599e57 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '6c8a10ed-ed0b-11e4-91eb-00163ec546ca:96083689'/*!*/;
# at 172962
#151221 9:24:37 server id 13307 end_log_pos 173025 CRC32 0x4a350fa8 Query thread_id=475472829 exec_time=0 error_code=0
SET TIMESTAMP=1450661077/*!*/;
BEGIN
/*!*/;
# at 173025
#151221 9:24:37 server id 13307 end_log_pos 173104 CRC32 0xa0d97a7f Table_map: `interface_hd_com`.`sms_log` mapped to number 193
35
# at 173104
#151221 9:24:37 server id 13307 end_log_pos 173210 CRC32 0xf16074ec Write_rows: table id 19335 flags: STMT_END_F
SET @@SESSION.GTID_NEXT= '6c8a10ed-ed0b-11e4-91eb-00163ec546ca:96083690'/*!*/;
#151221 9:24:37 server id 13308 end_log_pos 173219 CRC32 0xf16074ec Write_rows: table id 19335 flags: STMT_END_F

5.恢復其它binlog日誌
[root@jonn mysql999999]# mysqlbinlog mysqlbin.0000010|mysql -u root -pESBecs00 ---這個應該是恢復xs2表
[root@jonn mysql999999]# mysqlbinlog --stop-position=518 mysqlbin.000011|mysql -u root -pESBecs00 ---找到誤操作的時間，跳過去，恢復xs4，還有其它插入
[root@jonn mysql999999]# mysqlbinlog --start-position=637 mysqlbin.000011|mysql -u root -pESBecs00

分析一下：全備份前，xs=4條 xs2=4條
全備後：到最後xs=32條跳過誤操作 xs2=4條 xs3=4條 xs4=4條

核對一次：

mysql> select count(*) from xs;
+----------+
| count(*) |
+----------+
| 32 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from xs2;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from xs3;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from xs4;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
完全符合：不完全恢復成功

思路：需要知道每次全備的時間點，需要不完全恢復的話，先恢復全備，在分析一下全備前後連線的是哪個binlog日誌，
恢復連線的那個binlog日誌（指定--start-position=全備時間點，從全備份後開始恢復） ----該實驗為09
恢復其它的binlog日誌       ----恢復010日誌
恢復產生誤操作的那個日誌（跳過誤操作）   -----恢復011日誌

mysqldump異曲同工~！！！！~~~
背景

mysql> select count(*) from xs; --只有一張學生表
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)

[root@jonn xback]# mysqldump -u root -pESBecs00 -l -F --all-databases>all.sql ---全備完成後binlog為0000018

模擬操作

mysql> create table xs2 as select * from xs; ---18中
Query OK, 3 rows affected (0.04 sec) xs2=3條
Records: 3 Duplicates: 0 Warnings: 0

mysql> insert into xs select * from xs; xs=6條
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> create table xs3 as select * from xs; ----19中
Query OK, 6 rows affected (0.03 sec) xs3=6條
Records: 6 Duplicates: 0 Warnings: 0

mysql> insert into xs select * from xs; xs=12條
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> create table xs4 as select * from xs; ---20中
Query OK, 12 rows affected (0.04 sec) xs4=12條
Records: 12 Duplicates: 0 Warnings: 0

mysql> delete from xs where id=1; 誤操作
Query OK, 4 rows affected (0.01 sec)

全備恢復

1. 恢復前flush logs一下
[root@jonn xback]# mysql -uroot -pESBecs00<all.sql<all.sql< all.sql<="" all.sql<all.sql</all.sql<all.sql<>
此時xs表資料應該為3條
mysql> select count(*) from xs;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
恢復binlog18
[root@jonn mysql]# mysqlbinlog mysqlbin.000018 | mysql -uroot -pESBecs00
ERROR 1050 (42S01) at line 27: Table 'xs2' already exists ---報錯，因為建立xs2的語句存在日誌中，而你全備恢復時xs2是沒刪除掉的，只刪除掉了xs表，也就是全備份之後建立的表還存在庫中，檢視試試
mysql> show tables;
+--------------+
| Tables_in_ch |
+--------------+
| xs |
| xs2 |
| xs3 |
| xs4 |
+--------------+
4 rows in set (0.00 sec)
---所以比較麻煩，恢復日誌0019 00020同樣會暴這種錯！，解決方法也比較簡單，那就是恢復全備前幹掉整個庫
mysql> drop database ch;
Query OK, 4 rows affected (0.04 sec)

mysql> flush logs; ---全備恢復前重新整理一下日誌
Query OK, 0 rows affected (0.02 sec)

[root@jonn xback]# mysql -uroot -pESBecs00<all.sql

mysql> use ch;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------+
| Tables_in_ch |
+--------------+
| xs |
+--------------+
1 row in set (0.00 sec)
1. 此時xs表資料應該為3條
2. mysql> select count(*) from xs;
  +----------+
  | count(*) |
  +----------+
  | 3 |
  +----------+
  1 row in set (0.00 sec)
</all.sql
再次恢復00018日誌
[root@jonn mysql]# mysqlbinlog mysqlbin.000018 | mysql -uroot -pESBecs00

再恢復00019日誌
[root@jonn mysql]# mysqlbinlog mysqlbin.000019 | mysql -uroot -pESBecs00
mysql> show tables;
+--------------+
| Tables_in_ch |
+--------------+
| xs |
| xs2 |
| xs3 |
+--------------+
3 rows in set (0.00 sec)

mysql> select count(*) from xs2;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from xs;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from xs3;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec

再恢復 00020日誌並跳過誤刪除的地方
[root@jonn mysql]# mysqlbinlog --stop-position=638 mysqlbin.000020 | mysql -uroot -pESBecs00
[root@jonn mysql]# mysqlbinlog --start-position=978 mysqlbin.000020 | mysql -uroot -pESBecs00
mysql> select count(*) from xs;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from xs2;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from xs3;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from xs4;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)
恢復成功

只要有全備之後新建的表，都會報上面個錯，真是麻煩，還是用innobackupex來做備份恢復吧~~
如果只是單純的誤刪了一些資料。。將備份日誌都複製到測試機器上去，，恢復成功後再將資料匯入到生產上去~~~~·

其實在測試機器上可以快速恢復誤刪的表的資料就行了，其他的表都可以忽略不計啊！
假設全備之後所有的操作都寫在一個binlog中,，恢復全備之後
[root@node2 mysql22222]# mysqlbinlog --start-position=373859 --stop-position=441471 mysqlbin.000001 |mysql -uroot -pESBecs00
--start-position=373859 --全備之後對test表做的第一個操作的位置
--stop-position=441471 --勿刪之前對test表做的最後一個操作的位置

補充一下常用的

根據pos 範圍來提取相關的sql 語句，並儲存到檔案當中：
mysqlbinlog --start-position=98 --stop-position=117 /var/lib/mysql2/mysql-bin.000001 --result-file=/home/binlog.1
查詢的同時匯入資料庫：
mysqlbinlog --start-position=98 --stop-position=117 /var/lib/mysql2/mysql-bin.000001 |mysql -uroot -p
或者：
mysql> source /home/binlog.1
mysql -u使用者名稱 -p 資料庫名 </home/binlog.1
根據時間來進行恢復：
mysqlbinlog --start-datetime="2009-12-01 12:00:00" --stop-datetime="2009-12-01 19:00:00" /var/lib/mysql2/mysql-bin.000001 --result-file=/home/binlog.1
結合系統命令可以只對某個表的操作進行恢復。
比如我只恢復對cdr 表的插入動作進行恢復：
#>grep "insert into cdr " /home/binlog.1 >/home/binlog.2
#>mysql -uroot -ppassword asterisk </home/binlog.2

【Mysql】完全恢復與不完全恢復

相關文章