关键技术,数据库产生的二进制文件,在主库就是binlog在从库就是relay-log,用最新的物理备份可以新启动个新实例,可以模拟个从库,把主库的binlog复制到新的数据库实例上,利用主从复制和物理备份可以恢复到误操作前的数据
具体操作命令如下:
环境:
192.168.214.89 生产库
192.168.214.90 新数据库
1、在192.168.214.89上物理备份
innobackupex --defaults-file=/application/mysql56/etc/my.cnf --no-timestamp --user=root --password=123456 --host=192.168.214.89 --port=5621 --parallel=5 /data0/bak/full56
2、在192.168.214.89上多创建几个表,模拟业务增量
mysql> use employees;
mysql> create table employees_test as select * from employees;
Query OK, 300024 rows affected (7.18 sec)
Records: 300024 Duplicates: 0 Warnings: 0
mysql> insert into employees_test select * from employees_test;
Query OK, 300024 rows affected (5.19 sec)
Records: 300024 Duplicates: 0 Warnings: 0
mysql> insert into employees_test select * from employees_test;
Query OK, 600048 rows affected (7.97 sec)
Records: 600048 Duplicates: 0 Warnings: 0
#模拟binlog日志切换
mysql> flush logs;
Query OK, 0 rows affected (0.80 sec)
mysql> insert into employees_test select * from employees_test;
Query OK, 1200096 rows affected (25.22 sec)
Records: 1200096 Duplicates: 0 Warnings: 0
mysql> create table employees_test1 as select * from employees_test;
Query OK, 2400192 rows affected (59.35 sec)
Records: 2400192 Duplicates: 0 Warnings: 0
mysql> create table employees_test2 as select * from employees;
Query OK, 300024 rows affected (9.20 sec)
Records: 300024 Duplicates: 0 Warnings: 0
mysql> create table employees_test3 as select * from employees;
Query OK, 300024 rows affected (10.78 sec)
Records: 300024 Duplicates: 0 Warnings: 0
mysql> create table employees_test4 as select * from employees;
Query OK, 300024 rows affected (11.17 sec)
Records: 300024 Duplicates: 0 Warnings: 0
mysql> flush logs;
Query OK, 0 rows affected (0.49 sec)
mysql> create table employees_test5 as select * from employees;
Query OK, 300024 rows affected (10.67 sec)
Records: 300024 Duplicates: 0 Warnings: 0
mysql> flush logs;
Query OK, 0 rows affected (0.18 sec)
mysql> create table employees_test6 as select * from employees;
Query OK, 300024 rows affected (9.42 sec)
Records: 300024 Duplicates: 0 Warnings: 0
mysql> flush logs;
Query OK, 0 rows affected (0.31 sec)
mysql> create table employees_test7 as select * from employees;
Query OK, 300024 rows affected (8.83 sec)
Records: 300024 Duplicates: 0 Warnings: 0
mysql> create table employees_test8 as select * from employees;
Query OK, 300024 rows affected (9.83 sec)
Records: 300024 Duplicates: 0 Warnings: 0
查看数据库一共有多少个表
mysql> show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments |
| dept_emp |
| dept_manager |
| employees |
| employees_test |
| employees_test1 |
| employees_test2 |
| employees_test3 |
| employees_test4 |
| employees_test5 |
| employees_test6 |
| employees_test7 |
| employees_test8 |
| salaries |
| titles |
+---------------------+
15 rows in set (0.00 sec)
mysql> drop database employees;
Query OK, 15 rows affected (4.00 sec)
为了查找方便,误操作后切换日志
mysql> flush logs;
Query OK, 0 rows affected (0.35 sec)
3、恢复备份文件
innobackupex --defaults-file=/application/mysql56/etc/my.cnf --parallel=5 --apply-log /data0/bak/full56
4、在192.168.214.90上新安装个MySQL实例,启动新的MySQL实例,看启动是否成功,有异常的话,看错误日志解决,关闭MySQL数据库
5、把89恢复完的备份文件scp到90新安装的MySQL实例的数据目录下,启动数据库,让90成为一个从库
# cd /data0/bak/full56
# chown mysql:mysql * -R
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.88'; # MASTER_HOST可以随便指定
完了关闭数据库
6、把89的binlog文件scp到90新安装的MySQL实例的数据目录下,删除相关的文件,启动数据库
# scp mysql-bin.* 192.168.214.90:/data0/mysql/mysql56/var
# rm -rf mysql-bin.index
# rename mysql-bin relay-log mysql-bin.*
# ls ./relay-log.0* > relay-log.index
7、在主库上解析误操作前的binlog信息
# /application/mysql56/bin/mysqlbinlog mysql-bin.000042 > /tmp/42.sql
vi /tmp/42.sql
。。。。。。。。。。。。。。。。。。
# at 16494555
#181026 9:28:38 server id 19289 end_log_pos 16494586 CRC32 0xedfa848b Xid = 11451753
COMMIT/*!*/;
# at 16494586
#181026 9:29:55 server id 19289 end_log_pos 16494693 CRC32 0x8c072efe Query thread_id=3816657 exec_time=4 error
_code=0
SET TIMESTAMP=1540517395/*!*/;
drop database employees
/*!*/;
。。。。。。。。。。。。。。。。。
7、查看备份时候的binlog信息
# catxtrabackup_binlog_info
mysql-bin.000039 32987250
8、在90上做恢复
mysql> change master to relay_log_file='relay-log.000039',relay_log_pos=32987250;
Query OK, 0 rows affected (0.05 sec)
mysql> START SLAVE until RELAY_LOG_FILE = 'relay-log.000042', RELAY_LOG_POS = 16494555;
Query OK, 0 rows affected (0.04 sec)
查看验证表是否恢复正确
mysql> use employees;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments |
| dept_emp |
| dept_manager |
| employees |
| employees_test |
| employees_test1 |
| employees_test2 |
| employees_test3 |
| employees_test4 |
| employees_test5 |
| employees_test6 |
| employees_test7 |
| employees_test8 |
| salaries |
| titles |
+---------------------+
15 rows in set (0.00 sec)
再把employees数据库备份出来导入到192.168.214.89上
G
M
T
检测语言世界语中文简体中文繁体丹麦语乌克兰语乌兹别克语乌尔都语亚美尼亚语伊博语俄语保加利亚语信德语修纳语僧伽罗语克罗地亚语冰岛语加利西亚语加泰罗尼亚语匈牙利语南非祖鲁语卡纳达语卢森堡语印地语印尼巽他语印尼爪哇语印尼语古吉拉特语吉尔吉斯语哈萨克语土耳其语塔吉克语塞尔维亚语塞索托语夏威夷语威尔士语孟加拉语宿务语尼泊尔语巴斯克语布尔语(南非荷兰语)希伯来语希腊语库尔德语弗里西语德语意大利语意第绪语拉丁语拉脱维亚语挪威语捷克语斯洛伐克语斯洛文尼亚语斯瓦希里语旁遮普语日语普什图语格鲁吉亚语毛利语法语波兰语波斯尼亚语波斯语泰卢固语泰米尔语泰语海地克里奥尔语爱尔兰语爱沙尼亚语瑞典语白俄罗斯语科萨科西嘉语立陶宛语索马里语约鲁巴语缅甸语罗马尼亚语老挝语芬兰语苏格兰盖尔语苗语英语荷兰语菲律宾语萨摩亚语葡萄牙语蒙古语西班牙语豪萨语越南语阿塞拜疆语阿姆哈拉语阿尔巴尼亚语阿拉伯语韩语马其顿语马尔加什语马拉地语马拉雅拉姆语马来语马耳他语高棉语齐切瓦语 | 世界语中文简体中文繁体丹麦语乌克兰语乌兹别克语乌尔都语亚美尼亚语伊博语俄语保加利亚语信德语修纳语僧伽罗语克罗地亚语冰岛语加利西亚语加泰罗尼亚语匈牙利语南非祖鲁语卡纳达语卢森堡语印地语印尼巽他语印尼爪哇语印尼语古吉拉特语吉尔吉斯语哈萨克语土耳其语塔吉克语塞尔维亚语塞索托语夏威夷语威尔士语孟加拉语宿务语尼泊尔语巴斯克语布尔语(南非荷兰语)希伯来语希腊语库尔德语弗里西语德语意大利语意第绪语拉丁语拉脱维亚语挪威语捷克语斯洛伐克语斯洛文尼亚语斯瓦希里语旁遮普语日语普什图语格鲁吉亚语毛利语法语波兰语波斯尼亚语波斯语泰卢固语泰米尔语泰语海地克里奥尔语爱尔兰语爱沙尼亚语瑞典语白俄罗斯语科萨科西嘉语立陶宛语索马里语约鲁巴语缅甸语罗马尼亚语老挝语芬兰语苏格兰盖尔语苗语英语荷兰语菲律宾语萨摩亚语葡萄牙语蒙古语西班牙语豪萨语越南语阿塞拜疆语阿姆哈拉语阿尔巴尼亚语阿拉伯语韩语马其顿语马尔加什语马拉地语马拉雅拉姆语马来语马耳他语高棉语齐切瓦语 |
文本转语音功能仅限200个字符