模拟实现MySQL从库_my14_mysql指定时间恢复之模拟从库

场景

*********************************

线上库数据误删除，存在几天前的一份全备数据，现需要恢复这些误删除的数据

本例方案：在另外一台服务器上，恢复全备，搭建binlog server复制从全备时的binlog日志，然后模拟从库使用SQL_THEAD追加数据

方案特点：可恢复到全备到当前之间的任何时刻；使用模拟从库的方式，不用担心需要追加的binlog太多会引发异常

常用使用binlog恢复的异常有

Max_allowed_packet问题

Blob/Binary/text字段问题

特殊字符的转义问题

没有”断点恢复”：执行出错后，没有足够的报错，也很难从失败的地方继续恢复

操作简记

**********************************

现库环境(非GTID环境)

---------------------------------------------

mysqld_safe --defaults-file=/etc/my_bakdb.cnf --user=mysql &

mysql -uautomng -p********* -P3319 -h127.0.0.1

mysql> select count(*) from sbtest5;

+----------+

| count(*) |

+----------+

| 300000 |

+----------+

1 row in set (1.30 sec)

全备数据准备

-----------------------------------------------

vim xbakdb.sh

#!/usr/bin/env bash

rm -rf /data0/backup/dbbak_20180813/*

innobackupex --defaults-file=/etc/my_bakdb.cnf --no-timestamp --user automng --host=192.168.56.82 --port=3319 --password ******** /data0/backup/dbbak_20180813

chmod +x xbakdb.sh

使用screen记录日志输出，默认输出到/tmp/screenlog_bakdb.log

[root@red4 mysql]# screen -L -t bakdb -dmS backup /opt/scripts/backup/mysql/xbakdb.sh

[root@red4 mysql]# tailf /tmp/screenlog_bakdb.log

备份完成后应用日志

innobackupex --user automng --host=192.168.56.82 --port=3319 --password ********* --apply-log /data0/backup/dbbak_20180813

模拟事务

----------------------------------------------

备份过程执行两次删除

mysql> delete from sbtest5 limit 10;

Query OK, 10 rows affected (0.45 sec)

备份完成后再执行两次删除

mysql> delete from sbtest5 limit 10;

Query OK, 10 rows affected (0.11 sec)

此时库中该表删除了40条记录，预计全备恢复时为删除20记录

mysql> select count(*) from sbtest5;

+----------+

| count(*) |

+----------+

| 299960 |

+----------+

1 row in set (0.04 sec)

使用sysbench清除现在的表，再插入80张表，每张表5万条记录，意思就是在恢复的过程中，原库一直很繁忙，会产生大量的binlog日志

全备恢复

----------------------------------------------------

将备份数据复制到另外一个服务器上

scp /etc/my_bakdb.cnf mysql01:/etc/

scp -r dbbak_20180813/ mysql01:/data0/backup

在mysql01服务器上创建bakdb使用的目录与目录

mkdir -p /data0/mysql/log/bakdb

mkdir -p /data0/mysql/data/bakdb

touch /data0/mysql/log/bakdb/error.log

chown -R mysql.mysql /data0/mysql/log/bakdb

注意：由于是在新服务器上恢复，xtrabackup不备份日志相关文件，所以需要手工创建一个日志文件并对日志目录授权

直接将应用日志后的备份文件复制到库的位置

cp -r dbbak_20180813/* /data0/mysql/data/bakdb/

chown -R mysql.mysql /data0/mysql/data/bakdb/

mysqld_safe --defaults-file=/etc/my_bakdb.cnf --user=mysql &

mysql -uautomng -p********* -P3319 -h127.0.0.1

查看数据发现少了20条记录，与前面的预计相符

mysql> select count(*) from sbtest5;

+----------+

| count(*) |

+----------+

| 299980 |

+----------+

1 row in set (1.96 sec)

搭建binlog server

-------------------------------------------------------

注意磁盘空间，要寻找一个足够放得下全备至恢复点日志的目录

mkdir -p /data0/backup/bakdb_binlog/

mysqlbinlog --no-defaults -h192.168.56.82 -P3319 -uautomng -p******** -R --raw --stop-never mysql-bin.000043 -r /data0/backup/bakdb_binlog/ --stop-never-slave-server-id=33119 &

确认恢复位置

-----------------------------------------------------

模拟的业务是创建80张表，每张表5万数据，将位置定在第31张表，即恢复到第31张表创建之后但插入数据之前的位置，通过几次尝试确定语句在mysql-bin.000048 中

mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /data0/backup/bakdb_binlog/mysql-bin.000048 > ./tmp/mysql_48.sql

mysql_48.sql 1.7G太大，先拆分成小文件

split -b 300m mysql_48.sql

/*!*/;

# at 411781429

#180815 11:11:40 server id 3319 end_log_pos 411781490 Anonymous_GTID last_committed=409 sequence_number=410 rbr_only=yes

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 411781490

#180815 11:11:40 server id 3319 end_log_pos 411781558 Query thread_id=18 exec_time=0 error_code=0

SET TIMESTAMP=1534302700/*!*/;

BEGIN

/*!*/;

# at 411781558

#180815 11:11:40 server id 3319 end_log_pos 412305775 Rows_query

# INSERT INTO sbtest31(k, c, pad) VALUES(247741, '46161912814-94771992086-29465110879-96852384793-12688454296-19046139779-19533481261-64495786388-42440185851-37409777004',

'88689078905-06893547748-69501204574-82481850912-44422599985'),

411781429标记时，设置事务级别为RC(相比默认的RR级别，RC级别可以减少并发事务--此处为并发insert 所造成的锁等待，sysbench支持多线程)，后面开始插入sbtest31的数据，本次恢复的位置就定为411781429

恢复前的数据备份

------------------------------------------------------

开始操作数据了，操作前先对原数据做一次备份，操作失败了还有一份备份

mysqladmin shutdown -uautomng -p******* -S /data0/mysql/log/bakdb/mysql_bakdb.sock

cp -r /data0/mysql/data/bakdb/ /data0/backup/

手工制作 realy_log

------------------------------------------

mysql> show variables like 'relay_log_basename';

+--------------------+-------------------------------------------+

| Variable_name | Value |

+--------------------+-------------------------------------------+

| relay_log_basename | /data0/mysql/data/bakdb/mysql02-relay-bin |

+--------------------+-------------------------------------------+

1 row in set (1.22 sec)

mysql> show variables like 'relay_log_index';

+-----------------+-------------------------------------------------+

| Variable_name | Value |

+-----------------+-------------------------------------------------+

| relay_log_index | /data0/mysql/data/bakdb/mysql02-relay-bin.index |

+-----------------+-------------------------------------------------+

1 row in set (0.00 sec)

# cat relay_log.sh

#!/usr/bin/env bash

for i in $(ls mysql-bin.00*)

do

nn=$(echo $i|cut -d'.' -f2);

cp $i mysql02-relay-bin.$nn;

echo "./mysql02-relay-bin.$nn" >> mysql02-relay-bin.index;

done

执行脚本前，先停止一下binlog server，因为最后一个文件还在不断地增加，或者恢复时不使用最后一个文件

# cat mysql02-relay-bin.index

./mysql02-relay-bin.000043

./mysql02-relay-bin.000044

./mysql02-relay-bin.000045

./mysql02-relay-bin.000046

./mysql02-relay-bin.000047

./mysql02-relay-bin.000048

./mysql02-relay-bin.000049

./mysql02-relay-bin.000050

./mysql02-relay-bin.000051

./mysql02-relay-bin.000052

./mysql02-relay-bin.000053

./mysql02-relay-bin.000054

./mysql02-relay-bin.000055

./mysql02-relay-bin.000056

./mysql02-relay-bin.000057

./mysql02-relay-bin.000058

./mysql02-relay-bin.000059

./mysql02-relay-bin.000060

# ls -ltrh | grep mysql02

-rw-r-----. 1 root root 1.1G Aug 16 11:27 mysql02-relay-bin.000043

-rw-r-----. 1 root root 1.1G Aug 16 11:28 mysql02-relay-bin.000044

-rw-r-----. 1 root root 1.1G Aug 16 11:30 mysql02-relay-bin.000045

-rw-r-----. 1 root root 1.1G Aug 16 11:31 mysql02-relay-bin.000046

-rw-r-----. 1 root root 1.1G Aug 16 11:32 mysql02-relay-bin.000047

-rw-r-----. 1 root root 1.1G Aug 16 11:33 mysql02-relay-bin.000048

-rw-r-----. 1 root root 1.1G Aug 16 11:35 mysql02-relay-bin.000049

-rw-r-----. 1 root root 1.1G Aug 16 11:36 mysql02-relay-bin.000050

-rw-r-----. 1 root root 1.1G Aug 16 11:37 mysql02-relay-bin.000051

-rw-r-----. 1 root root 1.1G Aug 16 11:39 mysql02-relay-bin.000052

-rw-r-----. 1 root root 1.1G Aug 16 11:40 mysql02-relay-bin.000053

-rw-r-----. 1 root root 1.1G Aug 16 11:41 mysql02-relay-bin.000054

-rw-r-----. 1 root root 1.1G Aug 16 11:42 mysql02-relay-bin.000055

-rw-r-----. 1 root root 1.1G Aug 16 11:44 mysql02-relay-bin.000056

-rw-r-----. 1 root root 362M Aug 16 11:44 mysql02-relay-bin.000057

-rw-r-----. 1 root root 193 Aug 16 11:44 mysql02-relay-bin.000058

-rw-r-----. 1 root root 169 Aug 16 11:44 mysql02-relay-bin.000059

-rw-r-----. 1 root root 150 Aug 16 11:44 mysql02-relay-bin.000060

-rw-r--r--. 1 root root 486 Aug 16 11:44 mysql02-relay-bin.index

复制制作的relay_log到指定目录，默认在数据目录下，此处单独创建一个目录作为relay log使用

mkdir -p /data0/mysql/log/bakdb_relay_log

cp mysql02-relay-bin.* /data0/mysql/log/bakdb_relay_log

chown -R mysql.mysql /data0/mysql/log/bakdb_relay_log/

恢复前数据再次确认

-------------------------------------------

mysql> show tables;

+----------------+

| Tables_in_txdb |

+----------------+

| sbtest1 |

| sbtest10 |

| sbtest11 |

| sbtest12 |

| sbtest13 |

| sbtest14 |

| sbtest15 |

| sbtest2 |

| sbtest3 |

| sbtest4 |

| sbtest5 |

| sbtest6 |

| sbtest7 |

| sbtest8 |

| sbtest9 |

+----------------+

15 rows in set (0.00 sec)

修改配置文件relay相关参数

-------------------------------------------------------------

修改server-id，使从库与主库的server-id不同

配置relay_log 指定relay_log地址

配置文件中定义，relay_log_basename是自动计算的，不需要在配置文件中定义

relay_log = /data0/mysql/log/bakdb_relay_log/mysql02-relay-bin

relay_log_index = /data0/mysql/log/bakdb_relay_log/mysql02-relay-bin.index

relay_log_info_file = /data0/mysql/log/bakdb_relay_log/relay-log.info

relay_log_info_repository = FILE

relay_log_purge = 0

skip-slave-start

重启服务器查看参数定义，此处再检查一次relay log的权限是否为mysql，否则就授权

chown -R mysql.mysql /data0/mysql/log/bakdb_relay_log/

mysql -uautomng -p******** -S /data0/mysql/log/bakdb/mysql_bakdb.sock

mysql> show variables like 'relay%';

+---------------------------+----------------------------------------------------------+

| Variable_name | Value |

+---------------------------+----------------------------------------------------------+

| relay_log | /data0/mysql/log/bakdb_relay_log/mysql02-relay-bin |

| relay_log_basename | /data0/mysql/log/bakdb_relay_log/mysql02-relay-bin |

| relay_log_index | /data0/mysql/log/bakdb_relay_log/mysql02-relay-bin.index |

| relay_log_info_file | /data0/mysql/log/bakdb_relay_log/relay-log.info |

| relay_log_info_repository | FILE |

| relay_log_purge | OFF |

| relay_log_recovery | OFF |

| relay_log_space_limit | 0 |

+---------------------------+----------------------------------------------------------+

8 rows in set (0.01 sec)

启动SQL_THREAD恢复数据

---------------------------------------------------------------

# cat xtrabackup_binlog_info

mysql-bin.0000434526

mysql -uroot -p********* -S /data0/mysql/log/bakdb/mysql_bakdb.sock

mysql> ? change master to

mysql> ? start slave

CHANGE MASTER TO

RELAY_LOG_FILE='mysql02-relay-bin.000043',

RELAY_LOG_POS=4526;

预计恢复完毕后，sbtest31已创建但无数据

start slave sql_thread until RELAY_LOG_FILE = 'mysql02-relay-bin.000048', RELAY_LOG_POS = 411781429;

验证结果，最终的确创建了sbtest31这张表，并且表中数据为空

mysql> show tables;

+----------------+

| Tables_in_txdb |

+----------------+

| sbtest1 |

| sbtest10 |

| sbtest11 |

| sbtest12 |

| sbtest13 |

| sbtest14 |

| sbtest15 |

| sbtest16 |

| sbtest17 |

| sbtest18 |

| sbtest19 |

| sbtest2 |

| sbtest20 |

| sbtest21 |

| sbtest22 |

| sbtest23 |

| sbtest24 |

| sbtest25 |

| sbtest26 |

| sbtest27 |

| sbtest28 |

| sbtest29 |

| sbtest3 |

| sbtest30 |

| sbtest31 |

| sbtest32 |

| sbtest4 |

| sbtest5 |

| sbtest6 |

| sbtest7 |

| sbtest8 |

| sbtest9 |

+----------------+

32 rows in set (0.00 sec)

mysql> select count(*) from sbtest31;

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

至此，恢复成功。