2020灾备演练
2020-09-01 本文已影响0人
与狼共舞666
默认我司的数据库备份频率是每周每天都按时备份，核心业务数据备份保留30天，电商数据保留7天；默认日志数据保留7天，方面利用binlog完成数据闪回。
image.png
此次数据灾备演练主要是误删数据库时，快速闪回，保证数据高可用。
1.安装Xtrabackup工具包
/*安装基础依赖包*/
[root@localhost ~]# yum -y install perl-CPAN perl-DBD-MySQL perl make gcc gcc-c++ patch libgcrypt libgcrypt-devel libaio libaio-devel automake autoconf bzr bison libtool ncurses5-devel zlib zlib-devel
已加载插件：fastestmirror
Determining fastest mirrors
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
bareos/signature | 833 B 00:00:00
bareos/signature | 4.8 kB 00:00:00 !!!
base | 3.6 kB 00:00:00
epel | 4.7 kB 00:00:00
extras | 2.9 kB 00:00:00
updates | 2.9 kB 00:00:00
(1/3): epel/x86_64/updateinfo | 1.0 MB 00:00:02
(2/3): updates/7/x86_64/primary_db | 4.5 MB 00:00:03
(3/3): epel/x86_64/primary_db | 6.9 MB 00:00:05
软件包 perl-CPAN-1.9800-295.el7.noarch 已安装并且是最新版本
软件包 perl-DBD-MySQL-4.023-6.el7.x86_64 已安装并且是最新版本
软件包 4:perl-5.16.3-295.el7.x86_64 已安装并且是最新版本
软件包 1:make-3.82-24.el7.x86_64 已安装并且是最新版本
软件包 gcc-4.8.5-39.el7.x86_64 已安装并且是最新版本
软件包 gcc-c++-4.8.5-39.el7.x86_64 已安装并且是最新版本
软件包 patch-2.7.1-12.el7_7.x86_64 已安装并且是最新版本
软件包 libgcrypt-1.5.3-14.el7.x86_64 已安装并且是最新版本
软件包 libgcrypt-devel-1.5.3-14.el7.x86_64 已安装并且是最新版本
软件包 libaio-0.3.109-13.el7.x86_64 已安装并且是最新版本
软件包 libaio-devel-0.3.109-13.el7.x86_64 已安装并且是最新版本
软件包 automake-1.13.4-3.el7.noarch 已安装并且是最新版本
软件包 autoconf-2.69-11.el7.noarch 已安装并且是最新版本
软件包 bzr-2.5.1-14.el7.x86_64 已安装并且是最新版本
软件包 bison-3.0.4-2.el7.x86_64 已安装并且是最新版本
软件包 libtool-2.4.2-22.el7_3.x86_64 已安装并且是最新版本
没有可用软件包 ncurses5-devel。
软件包 zlib-1.2.7-18.el7.x86_64 已安装并且是最新版本
软件包 zlib-devel-1.2.7-18.el7.x86_64 已安装并且是最新版本
无须任何处理
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
percona-release enable-only tools/*代表只启用tools仓库源*/
yum install percona-xtrabackup-24
二、备份目标数据库，备份结果以备份开始时间戳为目录
[root@localhost bin]# innobackupex --user=root --password=密码不能给你知道 /opt/bak/
xtrabackup: recognized server arguments: --datadir=/data/mysql/data --server-id=3306110 --log_bin=/data/mysql/log/binlog-mysql
xtrabackup: recognized client arguments:
200831 12:51:27 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
200831 12:51:27 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/data/mysql/tmp/mysql.sock' as 'root' (using password: YES).
200831 12:51:27 version_check Connected to MySQL server
200831 12:51:27 version_check Executing a version check against the server...
200831 12:51:27 version_check Done.
200831 12:51:27 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /data/mysql/tmp/mysql.sock
Using server version 5.6.34-log
innobackupex version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/data
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
200831 12:51:27 >> log scanned up to (281662226)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 1 for mysql/innodb_table_stats, old maximum was 0
200831 12:51:27 [01] Copying ./ibdata1 to /opt/bak/2020-08-31_12-51-27/ibdata1
200831 12:51:28 [01] ...done
200831 12:51:28 [01] Copying ./mysql/innodb_table_stats.ibd to /opt/bak/2020-08-31_12-51-27/mysql/innodb_table_stats.ibd
200831 12:51:28 [01] ...done
200831 12:51:28 >> log scanned up to (281662226)
200831 12:51:28 [01] Copying ./mysql/innodb_index_stats.ibd to /opt/bak/2020-08-31_12-51-27/mysql/innodb_index_stats.ibd
200831 12:51:28 [01] ...done
200831 12:51:28 [01] Copying ./mysql/slave_relay_log_info.ibd to /opt/bak/2020-08-31_12-51-27/mysql/slave_relay_log_info.ibd
200831 12:51:28 [01] ...done
200831 12:51:28 [01] Copying ./mysql/slave_master_info.ibd to /opt/bak/2020-08-31_12-51-27/mysql/slave_master_info.ibd
200831 12:51:28 [01] ...done
200831 12:51:28 [01] Copying ./mysql/slave_worker_info.ibd to /opt/bak/2020-08-31_12-51-27/mysql/slave_worker_info.ibd
200831 12:51:28 [01] ...done
200831 12:51:28 [01] Copying ./ipcis_mdm/t_area.ibd to /opt/bak/2020-08-31_12-51-27/ipcis_mdm/t_area.ibd
200831 12:51:28 [01] ...done
200831 12:51:28 [01] Copying ./ipcis_mdm/t_area_copy0.ibd to /opt/bak/2020-08-31_12-51-27/ipcis_mdm/t_area_copy0.ibd
200831 12:51:28 [01] ...done
200831 12:51:28 [01] Copying ./ipcis_mdm/t_area_copy20190425.ibd to /opt/bak/2020-08-31_12-51-27/ipcis_mdm/t_area_copy20190425.ibd
200831 12:51:28 [01] ...done
200831 12:51:28 [01] Copying ./ipcis_mdm/t_dict.ibd to /opt/bak/2020-08-31_12-51-27/ipcis_mdm/t_dict.ibd
200831 12:51:28 [01] ...done
200831 12:51:28 [01] Copying ./ipcis_mdm/t_seriano.ibd to /opt/bak/2020-08-31_12-51-27/ipcis_mdm/t_seriano.ibd
200831 12:51:28 [01] ...done
200831 12:51:28 [01] Copying ./ipcis_mdm/web_bas_comm_code.ibd to /opt/bak/2020-08-31_12-51-27/ipcis_mdm/web_bas_comm_code.ibd
200831 12:51:28 [01] ...done
200831 12:51:32 [01] Copying ./ipcis_mdm/web_mdm_phone_ver.frm to /opt/bak/2020-08-31_12-51-27/ipcis_mdm/web_mdm_phone_ver.frm
200831 12:51:32 [01] ...done
200831 12:51:32 [01] Copying ./ipcis_mdm/web_mdm_user.frm to /opt/bak/2020-08-31_12-51-27/ipcis_mdm/web_mdm_user.frm
200831 12:51:32 [01] ...done
200831 12:51:32 [01] Copying ./ipcis_mdm/web_mdm_user_log.frm to /opt/bak/2020-08-31_12-51-27/ipcis_mdm/web_mdm_user_log.frm
200831 12:51:32 [01] ...done
200831 12:51:32 [01] Copying ./ipcis_mdm/web_sys_sta_dict.frm to /opt/bak/2020-08-31_12-51-27/ipcis_mdm/web_sys_sta_dict.frm
200831 12:51:32 [01] ...done
200831 12:51:32 Finished backing up non-InnoDB tables and files
200831 12:51:32 [00] Writing /opt/bak/2020-08-31_12-51-27/xtrabackup_binlog_info
200831 12:51:32 [00] ...done
200831 12:51:32 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '281662226'
xtrabackup: Stopping log copying thread.
.200831 12:51:32 >> log scanned up to (281662226)
200831 12:51:32 Executing UNLOCK TABLES
200831 12:51:32 All tables unlocked
200831 12:51:32 Backup created in directory '/opt/bak/2020-08-31_12-51-27/'
MySQL binlog position: filename 'binlog-mysql.000002', position '3219'
200831 12:51:32 [00] Writing /opt/bak/2020-08-31_12-51-27/backup-my.cnf
200831 12:51:32 [00] ...done
200831 12:51:32 [00] Writing /opt/bak/2020-08-31_12-51-27/xtrabackup_info
200831 12:51:32 [00] ...done
xtrabackup: Transaction log of lsn (281662226) to (281662226) was copied.
200831 12:51:32 completed OK!
[root@localhost 2020-08-31_12-51-27]# ls -l
总用量 77856
-rw-r----- 1 root root 487 8月 31 12:51 backup-my.cnf
-rw-r----- 1 root root 79691776 8月 31 12:51 ibdata1
drwxr-x--- 2 root root 4096 8月 31 12:51 ipcis_mdm
drwxr-x--- 2 root root 4096 8月 31 12:51 mysql
drwxr-x--- 2 root root 4096 8月 31 12:51 performance_schema
drwxr-x--- 2 root root 20 8月 31 12:51 test
-rw-r----- 1 root root 25 8月 31 12:51 xtrabackup_binlog_info
-rw-r----- 1 root root 141 8月 31 12:51 xtrabackup_checkpoints
-rw-r----- 1 root root 473 8月 31 12:51 xtrabackup_info
-rw-r----- 1 root root 2560 8月 31 12:51 xtrabackup_logfile
灾备演练场景①，某时刻误更新数据，根据update操作的大概时间点过滤出update操作前一时刻position，主要参数有 start-datetime & stop-datetime，登陆数据库查看最新的binlog文件，根据误操作的大概时间段过滤出对应的操作语句
root@db 14:21: [ipcis_mdm]> delete root@db 14:21: [ipcis_mdm]> delete from web_mdm_user where c_addr_province=310000;
Query OK, 319 rows affected (0.19 sec)
- 通过binlog2sql反向解析出对应的回滚语句（开头简述binlog2sql安装）
1.安装python3、pip、git;
2.git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
3.pip install -r requirements.txt
root@db 14:23: [ipcis_mdm]> show master logs;
+---------------------+-----------+
| Log_name | File_size |
+---------------------+-----------+
| binlog-mysql.000001 | 4115 |
| binlog-mysql.000002 | 123427 |
+---------------------+-----------+
2 rows in set (0.00 sec)
[root@localhost binlog2sql]# python ./binlog2sql.py -uroot -p'密码不能给你知道' -d ipcis_mdm -t web_mdm_user --start-file='binlog-mysql.000002' --start-datetime='2020-09-01 14:20:00' --stop-datetime='2020-09-01 14:24:00' > /tmp/raw.sql
[root@localhost binlog2sql]# cd /tmp
[root@localhost tmp]# vim raw.sql
[root@localhost tmp]# wc -l raw.sql
319 raw.sql
DELETE FROM `ipcis_mdm`.`web_mdm_user` WHERE `T_CERTF_END_TM`='2038-07-05 00:00:00' AND `C_USER_ID`='47417I91E37CM9V0BD4652X2G8AE8T' AND `C_OFFICIAL_ACCOUNTS`='瑞华健康险' AND `T_CRT_TM`='2018-08-01 11:38:36' AND `C_USER_TYP` IS NULL AND `C_UPD_CDE`='47417I91E37CM9V0BD4652X2G8AE8T' AND `C_IDENTITY_FLAG`='1' AND `C_WECHAT_ID` IS NULL AND `C_CRT_CDE`='System' AND `C_OPEN_ID`='oAhYY0nGvjApxSEh4fNvh4kwKr9U' AND `C_REFEREE_CDE` IS NULL AND `T_UPD_TM`='2018-10-29 20:30:55' AND `C_REFEREE_CHA_CDE` IS NULL AND `C_ADDR_PROVINCE`='310000' AND `C_ADDR_CITY`='310100' AND `C_UNION_ID` IS NULL AND `C_STATUS`='1' AND `C_SOURCE`='01' AND `C_SEX`='m' AND `C_MODIFYPWD_FLAG`='0' AND `C_EMAIL_FLAG` IS NULL AND `C_ADDR_DETAIL`='莲安东路367弄18栋301室' AND `C_ADDR_DISTRICT`='310115' AND `T_WECHAT_BIND_TIME` IS NULL AND `C_CERTF_CDE`='429004199111271894' AND `C_PERSON_ID`='5065FW63711826169856' AND `C_PWD`='$2a$10$rM8KwOHz9KIivWs6T2qKWutyueNfOk4W/hrTiwNn/0xVMjwpNeEqi' AND `C_MOBILE`='083108310831' AND `C_NAME`='许冠杰' AND `i` IS NULL AND `T_REGIST_TM`='2018-08-01 11:38:36' AND `C_CERTF_CLS`='0' AND `C_CERTF_DATE_SIGN`='0' AND `C_EMAIL`='gjxu@isoftstone.com' AND `T_CERTF_BGN_TM` IS NULL AND `T_VERIFIED_TIME`='2018-11-22 19:36:30' AND `C_WECHAT_NAME` IS NULL AND `C_REL_NME_FLAG`='1' LIMIT 1; #start 12482 end 20803 time 2020-09-01 14:23:26
DELETE FROM `ipcis_mdm`.`web_mdm_user` WHERE `T_CERTF_END_TM`='2034-11-15 00:00:00' AND `C_USER_ID`='4742R0TXB5W8053POEX73370IKX368' AND `C_OFFICIAL_ACCOUNTS`='瑞华健康险' AND `T_CRT_TM`='2018-08-01 13:24:34' AND `C_USER_TYP` IS NULL AND `C_UPD_CDE`='4742R0TXB5W8053POEX73370IKX368' AND `C_IDENTITY_FLAG`='1' AND `C_WECHAT_ID` IS NULL AND `C_CRT_CDE`='System' AND `C_OPEN_ID`='oAhYY0nIDdr5SxNZeU_TBDFv3p0A' AND `C_REFEREE_CDE` IS NULL AND `T_UPD_TM`='2020-07-15 18:21:41' AND `C_REFEREE_CHA_CDE` IS NULL AND `C_ADDR_PROVINCE`='310000' AND `C_ADDR_CITY`='310100' AND `C_UNION_ID` IS NULL AND `C_STATUS`='1' AND `C_SOURCE`='02' AND `C_SEX`='m' AND `C_MODIFYPWD_FLAG`='0' AND `C_EMAIL_FLAG` IS NULL AND `C_ADDR_DETAIL`='保屯路379号' AND `C_ADDR_DISTRICT`='310101' AND `T_WECHAT_BIND_TIME` IS NULL AND `C_CERTF_CDE`='310230198609061478' AND `C_PERSON_ID`='M4742X06G2932RBT9K6SZP75R468K8' AND `C_PWD`='$2a$10$mAmzONw0PMv24S.zAn8MruHXYh3w6BFTuU1a4o3QM8Q4FL0IBQN7K' AND `C_MOBILE`='18916770118' AND `C_NAME`='孙锋' AND `i` IS NULL AND `T_REGIST_TM`='2018-08-01 13:24:34' AND `C_CERTF_CLS`='0' AND `C_CERTF_DATE_SIGN`='0' AND `C_EMAIL`='sf2046@163.com' AND `T_CERTF_BGN_TM` IS NULL AND `T_VERIFIED_TIME`='2018-11-22 19:36:30' AND `C_WECHAT_NAME` IS NULL AND `C_REL_NME_FLAG`='1' LIMIT 1; #start 12482 end 20803 time 2020-09-01 14:23:26
DELETE FROM `ipcis_mdm`.`web_mdm_user` WHERE `T_CERTF_END_TM` IS NULL AND `C_USER_ID`='47606AVAKD5TZ52U1CI01J01841I92' AND `C_OFFICIAL_ACCOUNTS`='微众银行' AND `T_CRT_TM`='2018-08-06 16:34:25' AND `C_USER_TYP` IS NULL AND `C_UPD_CDE`='47606AVAKD5TZ52U1CI01J01841I92' AND `C_IDENTITY_FLAG`='1' AND `C_WECHAT_ID` IS NULL AND `C_CRT_CDE`='System' AND `C_OPEN_ID`='oczxL5PZ5qZzolgDj5s_7RbGsDlY' AND `C_REFEREE_CDE` IS NULL AND `T_UPD_TM`='2020-07-09 11:23:47' AND `C_REFEREE_CHA_CDE` IS NULL AND `C_ADDR_PROVINCE`='310000' AND `C_ADDR_CITY`='310100' AND `C_UNION_ID` IS NULL AND `C_STATUS`='1' AND `C_SOURCE`='02' AND `C_SEX`='f' AND `C_MODIFYPWD_FLAG`='0' AND `C_EMAIL_FLAG` IS NULL AND `C_ADDR_DETAIL`='非天非地' AND `C_ADDR_DISTRICT`='310101' AND `T_WECHAT_BIND_TIME` IS NULL AND `C_CERTF_CDE`='620523199406191707' AND `C_PERSON_ID`='48732989806C2794W752' AND `C_PWD`='$2a$10$lgtM0OFw0wbvEVPSK1Y7UOvMgU1tMlO/OiIPThgGCPgiNyic6S2yu' AND `C_MOBILE`='18738182689' AND `C_NAME`='韩亚亚' AND `i` IS NULL AND `T_REGIST_TM`='2018-08-06 16:34:25' AND `C_CERTF_CLS`='0' AND `C_CERTF_DATE_SIGN` IS NULL AND `C_EMAIL`='1558642663@qq.com' AND `T_CERTF_BGN_TM` IS NULL AND `T_VERIFIED_TIME`='2018-11-27 15:53:58' AND `C_WECHAT_NAME` IS NULL AND `C_REL_NME_FLAG`='1' LIMIT 1; #start 12482 end 20803 time 2020-09-01 14:23:26
DELETE FROM `ipcis_mdm`.`web_mdm_user` WHERE `T_CERTF_END_TM`='2027-09-26 00:00:00' AND `C_USER_ID`='4810SITB442Y5772SE7J713KH2DV80' AND `C_OFFICIAL_ACCOUNTS`='瑞华健康险' AND `T_CRT_TM`='2018-08-20 10:18:09' AND `C_USER_TYP` IS NULL AND `C_UPD_CDE`='4810SITB442Y5772SE7J713KH2DV80' AND `C_IDENTITY_FLAG`='1' AND `C_WECHAT_ID` IS NULL AND `C_CRT_CDE`='System' AND `C_OPEN_ID`='oAhYY0qxZ5O1JvZ16regLA5whV3E' AND `C_REFEREE_CDE` IS NULL AND `T_UPD_TM`='2020-02-07 09:17:27' AND `C_REFEREE_CHA_CDE` IS NULL AND `C_ADDR_PROVINCE`='310000' AND `C_ADDR_CITY`='310100' AND `C_UNION_ID` IS NULL AND `C_STATUS`='1' AND `C_SOURCE`='02' AND `C_SEX`='m' AND `C_MODIFYPWD_FLAG`='0' AND `C_EMAIL_FLAG` IS NULL AND `C_ADDR_DETAIL`='未央区凤城九路文景西区27号楼0204' AND `C_ADDR_DISTRICT`='310101' AND `T_WECHAT_BIND_TIME` IS NULL AND `C_CERTF_CDE`='610526197907098218' AND `C_PERSON_ID`='49R239086035488358P4' AND `C_PWD`='$2a$10$vxLysJ3UAahKxS3sHtyCauFx1Idaqm26VE0Xue3c03GjHD4pSYzwm' AND `C_MOBILE`='15829299156' AND `C_NAME`='吴剑军' AND `i` IS NULL AND `T_REGIST_TM`='2018-08-20 10:18:09' AND `C_CERTF_CLS`='0' AND `C_CERTF_DATE_SIGN`='0' AND `C_EMAIL`='378796736@QQ.COM' AND `T_CERTF_BGN_TM` IS NULL AND `T_VERIFIED_TIME`='2018-11-22 19:36:30' AND `C_WECHAT_NAME` IS NULL AND `C_REL_NME_FLAG`='1' LIMIT 1; #start 12482 end 20803 time 2020-09-01 14:23:26
可以看到同一事务操作中的影响的行的start position是一样的，我们由此断定就是刚才误删除的数据（start position为12482，stop position为123396）。接着进一步用start position与stop position生成回滚语句。
[root@localhost binlog2sql]# python ./binlog2sql.py -uroot -p'密码不能给你知道' -d ipcis_mdm -t web_mdm_user --start-file='binlog-mysql.000002' --start-position=12482 --stop-position=123396 -B > /tmp/rollback0901.sql
[root@localhost binlog2sql]# cd /tmp
[root@localhost tmp]# wc -l rollback0901.sql
319 rollback0901.sql
INSERT INTO `ipcis_mdm`.`web_mdm_user`(`T_CERTF_END_TM`, `C_USER_ID`, `C_OFFICIAL_ACCOUNTS`, `T_CRT_TM`, `C_USER_TYP`, `C_UPD_CDE`, `C_IDENTITY_FLAG`, `C_WECHAT_ID`, `C_CRT_CDE`, `C_OPEN_ID`, `C_REFEREE_CDE`, `T_UPD_TM`, `C_REFEREE_CHA_CDE`, `C_ADDR_PROVINCE`, `C_ADDR_CITY`, `C_UNION_ID`, `C_STATUS`, `C_SOURCE`, `C_SEX`, `C_MODIFYPWD_FLAG`, `C_EMAIL_FLAG`, `C_ADDR_DETAIL`, `C_ADDR_DISTRICT`, `T_WECHAT_BIND_TIME`, `C_CERTF_CDE`, `C_PERSON_ID`, `C_PWD`, `C_MOBILE`, `C_NAME`, `i`, `T_REGIST_TM`, `C_CERTF_CLS`, `C_CERTF_DATE_SIGN`, `C_EMAIL`, `T_CERTF_BGN_TM`, `T_VERIFIED_TIME`, `C_WECHAT_NAME`, `C_REL_NME_FLAG`) VALUES ('2037-12-15 00:00:00', 'ZZ6OE6854TRT888S2GX83154022J4L', '瑞华健康险', '2020-01-19 20:14:35', NULL, 'System', '1', NULL, 'System', 'oAhYY0sfoUgCIh2sAoR6XpzNxWqE', NULL, '2020-01-21 20:07:34', NULL, '310000', '310100', NULL, '1', '02', 'f', '0', NULL, '广西省南宁市青秀区枫林路17-1号（恺泽口腔）', '310101', NULL, '450722198905011529', '66891591614A047846K4', '$2a$10$J4HQagsT9JeLhJSDAtXkU.Ker4znJVFKeN9OeK32zRpoDK2y5G9Xu', '13877113487', '马曼丽', NULL, '2020-01-19 20:14:35', '0', '0', '736292042@qq.com', NULL, '2020-01-21 20:07:34', NULL, '1'); #start 12482 end 123396 time 2020-09-01 14:23:26
INSERT INTO `ipcis_mdm`.`web_mdm_user`(`T_CERTF_END_TM`, `C_USER_ID`, `C_OFFICIAL_ACCOUNTS`, `T_CRT_TM`, `C_USER_TYP`, `C_UPD_CDE`, `C_IDENTITY_FLAG`, `C_WECHAT_ID`, `C_CRT_CDE`, `C_OPEN_ID`, `C_REFEREE_CDE`, `T_UPD_TM`, `C_REFEREE_CHA_CDE`, `C_ADDR_PROVINCE`, `C_ADDR_CITY`, `C_UNION_ID`, `C_STATUS`, `C_SOURCE`, `C_SEX`, `C_MODIFYPWD_FLAG`, `C_EMAIL_FLAG`, `C_ADDR_DETAIL`, `C_ADDR_DISTRICT`, `T_WECHAT_BIND_TIME`, `C_CERTF_CDE`, `C_PERSON_ID`, `C_PWD`, `C_MOBILE`, `C_NAME`, `i`, `T_REGIST_TM`, `C_CERTF_CLS`, `C_CERTF_DATE_SIGN`, `C_EMAIL`, `T_CERTF_BGN_TM`, `T_VERIFIED_TIME`, `C_WECHAT_NAME`, `C_REL_NME_FLAG`) VALUES ('2038-05-28 00:00:00', 'ZYE55LZ045832570X5P1H0B9J5NP04', '瑞华健康险', '2019-02-27 23:24:52', NULL, 'ZYE55LZ045832570X5P1H0B9J5NP04', '1', NULL, 'System', 'oAhYY0oAR3fdbhmAeaDCqzxCHyik', NULL, '2019-08-16 22:55:19', NULL, '310000', '310100', NULL, '1', '02', 'm', '0', NULL, '川沙路5122弄1号401', '310115', NULL, '152122198402085719', NULL, '$2a$10$Emu4fON5CQd5d7RMdKHcFO6eeF.HxCaCes45o.SzkZya4ODm6co0e', '18616806463', '杨宝成', NULL, '2019-02-27 23:24:52', '0', '0', 'ybch2008@163.com', NULL, '2019-03-04 10:53:51', NULL, '0'); #start 12482 end 123396 time 2020-09-01 14:23:26
INSERT INTO `ipcis_mdm`.`web_mdm_user`(`T_CERTF_END_TM`, `C_USER_ID`, `C_OFFICIAL_ACCOUNTS`, `T_CRT_TM`, `C_USER_TYP`, `C_UPD_CDE`, `C_IDENTITY_FLAG`, `C_WECHAT_ID`, `C_CRT_CDE`, `C_OPEN_ID`, `C_REFEREE_CDE`, `T_UPD_TM`, `C_REFEREE_CHA_CDE`, `C_ADDR_PROVINCE`, `C_ADDR_CITY`, `C_UNION_ID`, `C_STATUS`, `C_SOURCE`, `C_SEX`, `C_MODIFYPWD_FLAG`, `C_EMAIL_FLAG`, `C_ADDR_DETAIL`, `C_ADDR_DISTRICT`, `T_WECHAT_BIND_TIME`, `C_CERTF_CDE`, `C_PERSON_ID`, `C_PWD`, `C_MOBILE`, `C_NAME`, `i`, `T_REGIST_TM`, `C_CERTF_CLS`, `C_CERTF_DATE_SIGN`, `C_EMAIL`, `T_CERTF_BGN_TM`, `T_VERIFIED_TIME`, `C_WECHAT_NAME`, `C_REL_NME_FLAG`) VALUES ('2034-10-30 00:00:00', 'ZQ553633L437S742A3QC6SL2Q6UX24', '瑞华健康险', '2019-03-08 17:41:38', NULL, 'ZQ553633L437S742A3QC6SL2Q6UX24', '1', NULL, 'System', 'oAhYY0gl32twDQ4_J7OAAeKwsOnc', NULL, '2019-03-08 23:48:56', NULL, '310000', '310100', NULL, '1', '02', 'f', '0', NULL, '安亭镇新源路66弄21号世昶广场1206室', '310114', NULL, '142622198609072523', NULL, '$2a$10$4MnuDxON2vS61wwp4eXDp.zd98J55LWwk60RpsZL.x2R96V1djOQa', '15135377395', '陈东琴', NULL, '2019-03-08 17:41:38', '0', '0', '515992455@qq.com', NULL, '2019-03-08 23:48:56', NULL, '0'); #start 12482 end 123396 time 2020-09-01 14:23:26
INSERT INTO `ipcis_mdm`.`web_mdm_user`(`T_CERTF_END_TM`, `C_USER_ID`, `C_OFFICIAL_ACCOUNTS`, `T_CRT_TM`, `C_USER_TYP`, `C_UPD_CDE`, `C_IDENTITY_FLAG`, `C_WECHAT_ID`, `C_CRT_CDE`, `C_OPEN_ID`, `C_REFEREE_CDE`, `T_UPD_TM`, `C_REFEREE_CHA_CDE`, `C_ADDR_PROVINCE`, `C_ADDR_CITY`, `C_UNION_ID`, `C_STATUS`, `C_SOURCE`, `C_SEX`, `C_MODIFYPWD_FLAG`, `C_EMAIL_FLAG`, `C_ADDR_DETAIL`, `C_ADDR_DISTRICT`, `T_WECHAT_BIND_TIME`, `C_CERTF_CDE`, `C_PERSON_ID`, `C_PWD`, `C_MOBILE`, `C_NAME`, `i`, `T_REGIST_TM`, `C_CERTF_CLS`, `C_CERTF_DATE_SIGN`, `C_EMAIL`, `T_CERTF_BGN_TM`, `T_VERIFIED_TIME`, `C_WECHAT_NAME`, `C_REL_NME_FLAG`) VALUES ('2033-02-10 00:00:00', 'Z4WI8J648XZ8Q75098B962SV7392QU', '瑞华健康险', '2018-09-04 10:52:37', NULL, 'Z4WI8J648XZ8Q75098B962SV7392QU', '1', NULL, 'System', 'oAhYY0qP0OZhMFLwyGdy-1FRmEAM', NULL, '2019-02-09 22:18:28', NULL, '310000', '310100', NULL, '1', '02', 'f', '0', NULL, '金杨路220弄47号', '310101', NULL, '51020319811227082X', NULL, '$2a$10$7SWkNopy.hqmZ2cSf/bcc.dhj1kx4k7Cn4OCtC8ONDOA4cOxOKHBa', '18580062855', '王娅娜', NULL, '2018-09-04 10:52:37', '0', '0', '364708679@qq.com', NULL, '2018-11-22 19:36:30', NULL, '0'); #start 12482 end 123396 time 2020-09-01 14:23:26
检查回滚语句没有问题，开始回滚，最后最下验证，与前面误删除的数据行数吻合。
root@db 15:07: [ipcis_mdm]> source /tmp/rollback0901.sql
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
root@db 15:07: [ipcis_mdm]> select count(*) from web_mdm_user where c_addr_province=310000;
+----------+
| count(*) |
+----------+
| 319 |
+----------+
灾备演练场景②，针对DDL误操作，我司采用物理备份+binlog的方式，恢复起来耗时较长
- 模拟误删除某数据库，登陆数据库查看最新的binlog，drop操作前最后的stop-position为334004 ，所以我们从最近的一次物理备份为基线开始恢复。
drop database ipcis_mdm;
| binlog-mysql.000002 | 332671 | Xid | 3306110 | 332702 | COMMIT /* xid=1234 */ |
| binlog-mysql.000002 | 332702 | Query | 3306110 | 332779 | BEGIN |
| binlog-mysql.000002 | 332779 | Table_map | 3306110 | 332947 | table_id: 132 (ipcis_mdm.web_mdm_user) |
| binlog-mysql.000002 | 332947 | Write_rows | 3306110 | 333331 | table_id: 132 flags: STMT_END_F |
| binlog-mysql.000002 | 333331 | Xid | 3306110 | 333362 | COMMIT /* xid=1235 */ |
| binlog-mysql.000002 | 333362 | Query | 3306110 | 333439 | BEGIN |
| binlog-mysql.000002 | 333439 | Table_map | 3306110 | 333607 | table_id: 132 (ipcis_mdm.web_mdm_user) |
| binlog-mysql.000002 | 333607 | Write_rows | 3306110 | 334004 | table_id: 132 flags: STMT_END_F |
| binlog-mysql.000002 | 334004 | Xid | 3306110 | 334035 | COMMIT /* xid=1236 */ |
| binlog-mysql.000002 | 334035 | Query | 3306110 | 334142 | drop database ipcis_mdm
- Prepare 备份文件
[root@localhost bak]# xtrabackup --prepare --target-dir=/opt/bak/2020-08-31_12-51-27
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=3306110 --redo-log-version=0
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/opt/bak/2020-08-31_12-51-27
xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)
xtrabackup: cd to /opt/bak/2020-08-31_12-51-27/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(281662226)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence number 1625987 in the system tablespace does not match the log sequence number 281662226 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 3219, file name binlog-mysql.000002
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.26 started; log sequence number 281662226
InnoDB: xtrabackup: Last MySQL binlog file position 3219, file name binlog-mysql.000002
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 281664852
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=281664852
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 281665036
InnoDB: Doing recovery: scanned up to log sequence number 281665045 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 3219, file name binlog-mysql.000002
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.26 started; log sequence number 281665045
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 281665740
200901 16:09:36 completed OK!
- 关闭mysql数据库服务器，清空datadir目录
[root@localhost 2020-08-31_12-51-27]# ps -ef| grep mysql
root 12993 1 0 8月17 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
mysql 13223 12993 0 8月17 ? 00:13:04 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/log/error.log --pid-file=/data/mysql/tmp/mysql.pid --socket=/data/mysql/tmp/mysql.sock --port=3306
root 22065 21210 0 16:13 pts/4 00:00:00 grep --color=auto mysql
[root@localhost 2020-08-31_12-51-27]# mysqladmin -uroot -p shutdown
Enter password:
[root@localhost 2020-08-31_12-51-27]# ps -ef| grep mysql
root 22072 21210 0 16:16 pts/4 00:00:00 grep --color=auto mysql
[root@localhost 2020-08-31_12-51-27]# rm -rf /data/mysql/data
- 将备份文件拷贝到数据库的datadir目录，并重新更改对应目录权限
[root@localhost mysql]# xtrabackup --copy-back --target-dir=/opt/bak/2020-08-31_12-51-27/ --datadir=/data/mysql/dataxtrabackup: recognized server arguments: --datadir=/data/mysql/data --server-id=3306110 --log_bin=/data/mysql/log/binlog-mysql --datadir=/data/mysql/data
xtrabackup: recognized client arguments: --port=3306 --socket=/data/mysql/tmp/mysql.sock --copy-back=1 --target-dir=/opt/bak/2020-08-31_12-51-27/
xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)
200901 16:20:51 [01] Copying ib_logfile0 to /data/mysql/data/ib_logfile0
200901 16:20:51 [01] ...done
200901 16:20:51 [01] Copying ib_logfile1 to /data/mysql/data/ib_logfile1
200901 16:20:51 [01] ...done
200901 16:20:51 [01] Copying ibdata1 to /data/mysql/data/ibdata1
200901 16:20:52 [01] ...done
200901 16:20:52 [01] Copying ./mysql/innodb_table_stats.ibd to /data/mysql/data/mysql/innodb_table_stats.ibd
200901 16:20:52 [01] ...done
200901 16:20:52 [01] Copying ./mysql/innodb_index_stats.ibd to /data/mysql/data/mysql/innodb_index_stats.ibd
200901 16:20:52 [01] ...done
200901 16:20:56 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/data/xtrabackup_binlog_pos_innodb
200901 16:20:56 [01] ...done
200901 16:20:56 [01] Copying ./xtrabackup_master_key_id to /data/mysql/data/xtrabackup_master_key_id
200901 16:20:56 [01] ...done
200901 16:20:56 [01] Copying ./ibtmp1 to /data/mysql/data/ibtmp1
200901 16:20:56 [01] ...done
200901 16:20:56 completed OK!
[root@localhost mysql]# chown -R mysql:mysql data/
[root@localhost mysql]# ll
总用量 0
drwxr-xr-x 6 mysql mysql 232 9月 1 16:20 data
drwxr-xr-x 2 mysql mysql 103 8月 17 20:51 log
drwxr-xr-x 2 mysql mysql 6 9月 1 16:16 tmp
[root@localhost mysql]# cd data
[root@localhost data]# ll
总用量 188440
-rw-r----- 1 mysql mysql 79691776 9月 1 16:20 ibdata1
-rw-r----- 1 mysql mysql 50331648 9月 1 16:20 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 9月 1 16:20 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 9月 1 16:20 ibtmp1
drwxr-x--- 2 mysql mysql 4096 9月 1 16:20 ipcis_mdm
drwxr-x--- 2 mysql mysql 4096 9月 1 16:20 mysql
drwxr-x--- 2 mysql mysql 4096 9月 1 16:20 performance_schema
drwxr-x--- 2 mysql mysql 20 9月 1 16:20 test
-rw-r----- 1 mysql mysql 25 9月 1 16:20 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql 473 9月 1 16:20 xtrabackup_info
-rw-r----- 1 mysql mysql 1 9月 1 16:20 xtrabackup_master_key_id
- 重启mysql服务
[root@localhost log]# mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --datadir=/data/mysql/data &
[1] 22092
[root@localhost log]# 200901 16:29:49 mysqld_safe Logging to '/data/mysql/log/error.log'.
200901 16:29:49 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data
[root@localhost log]# ps -ef| grep mysql
root 22092 21210 0 16:29 pts/4 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cn --user=mysql --datadir=/data/mysql/data
mysql 22342 22092 7 16:29 pts/4 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/sr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/msql/log/error.log --pid-file=/data/mysql/tmp/mysql.pid --socket=/data/mysql/tmp/mysql.sock --port=3306
root 22365 21210 0 16:29 pts/4 00:00:00 grep --color=auto mysql
- 利用上次备份结束时的position与drop操作钱的postion做闪回恢复
[root@localhost ~]# mysqlbinlog --start-position=3219 --stop-position=334004 binlog-mysql.000002 | mysql -uroot -p'密码不能给你知道' ipcis_mdm
Warning: Using a password on the command line interface can be insecure.
root@db 16:30: [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ipcis_mdm |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
root@db 16:30: [(none)]> use ipcis_mdm;
Database changed
root@db 16:30: [ipcis_mdm]> select count(*) from web_mdm_user;
+----------+
| count(*) |
+----------+
| 22335 |
+----------+
1 row in set (0.04 sec)
root@db 16:31: [ipcis_mdm]> show master logs;
+---------------------+-----------+
| Log_name | File_size |
+---------------------+-----------+
| binlog-mysql.000001 | 4115 |
| binlog-mysql.000002 | 334165 |
| binlog-mysql.000003 | 120 |
+---------------------+-----------+
3 rows in set (0.00 sec)
root@db 16:35: [ipcis_mdm]> show binlog events in 'binlog-mysql.000003';
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+
| binlog-mysql.000003 | 4 | Format_desc | 3306110 | 120 | Server ver: 5.6.34-log, Binlog ver: 4 |
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+
1 row in set (0.01 sec)
root@db 16:36: [ipcis_mdm]> relect count(*) from web_mdm_user;
+----------+
| count(*) |
+----------+
| 22334 |
+----------+
1 row in set (0.01 sec)
