【Mysql】完全恢复与不完全恢复

最新推荐文章于 2022-09-02 13:58:51 发布

cml2016

最新推荐文章于 2022-09-02 13:58:51 发布

阅读量139

点赞数

文章标签：数据库运维

假设每天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 mysql> show tables;
+--------------+
| Tables_in_ch |
+--------------+
| xs |
| xs2 |
+--------------+
2 rows in set (0.00 sec)

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

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

再恢复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