通过延时从库+binlog复制，规复误操作数据

通过延时从库+binlog复制，规复误操作数据

一、介绍环境

数据库版本实例脚色ip地点端口GreatSQL 8.0.32-26master192.168.134.1995725GreatSQL 8.0.32-26slave192.168.134.1995726二、主库设置

1. shell> /usr/local/greatsql/bin/mysql -S /tmp/mysql5725.sock -p
2. greatsql> create user 'repl'@'%' identified by '123';
3. greatsql> grant replication slave on . to 'repl'@'%';

复制代码

三、设置延时从库

1. greatsql> CHANGE MASTER TO
2.     master_host='192.168.134.199',
3.     master_port=5725,
4.     master_user='repl',
5.     master_password='123',
6.     master_auto_position=1,
7.     master_delay = 7200;
8. greatsql> START SLAVE;
9. greatsql> SHOW SLAVE STATUS\G

复制代码

四、模仿主库误删除数据表

1. shell> /usr/local/greatsql/bin/mysql -S /tmp/mysql5725.sock -p sysbench
2. greatsql> DROP TABLE sbtest2;

复制代码

五、延时从库规复数据到主库故障前

1、为了防止规复失败，先备份一下从库。
可以使用Xtrabackup/mysqldump，进行备份从库，这里演示使用 Xtrabackup 备份从库

1. $ xtrabackup --defaults-file=/data1/greatsql/greatsql5726/my5726.cnf -S /tmp/greatsql5726.sock --backup --slave-info \
2. --stream=xbstream --target-dir=/backup/full.xb

复制代码

2、我们找到主库误操作在哪个binlog里面，并需要确认误操作的binlog位置信息。

1. $ /usr/local/greatsql/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -vvv ./* | grep -rli 'drop'
2. $ /usr/local/greatsql/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -vvv mysql-bin.000002 |less

复制代码

3、停止sql_thread线程，设置不延时复制，设置复制停止在误操作binlog位置点。

1. shell> /usr/local/greatsql/bin/mysql -S /tmp/mysql5726.sock -p
2. greatsql> STOP SLAVE;
3. greatsql> CHANGE MASTER TO master_delay = 0;
4. greatsql> START SLAVE io_thread;
5. greatsql> START SLAVE sql_thread until SQL_BEFORE_GTIDS='2fc5a82c-2ac3-11ee-9f7f-00163e402951:187';
6. greatsql> SHOW SLAVE STATUS\G

复制代码

4、等待复制到需要的停止的位置点，sql_thread已经停止

5、查看从库误操作的表，备份出来规复到主库

1. greatsql> SHOW TABLES FROM sysbench;
2. greatsql> SELECT COUNT(*) FROM sysbench.sbtest2;
3. shell> /usr/local/greatsql/bin/mysqldump -S /tmp/mysql5726.sock --set-gtid-purged=OFF --single-transaction --master-data=2 --max-allowed-packet=32M -q sysbench sbtest2 > sbtest2.sql

复制代码

6、将sbtest2表备份数据规复到主库里

1. shell> /usr/local/greatsql/bin/mysql -S /tmp/mysql5725.sock -p -A sysbench
2. greatsql> SET sql_log_bin = off;
3. greatsql> source sbtest2.sql;
4. greatsql> EXIT;

复制代码

7、从库跳过误操作的gtid，重新设置延时从库，从库继承复制主库

1. shell> /usr/local/greatsql/bin/mysql -S /tmp/mysql5726.sock -p
3. greatsql> STOP SLAVE;
4. greatsql> SET gtid_next='2fc5a82c-2ac3-11ee-9f7f-00163e402951:187';
5. greatsql> BEGIN;COMMIT;
6. greatsql> SET gtid_next='automatic';
7. greatsql> CHANGE MASTER TO master_delay = 7200;
8. greatsql> START SLAVE;
9. greatsql> SHOW SLAVE STATUS\G

复制代码

六、总结防范误操作

怎样避免误删库、删表等误操作，以及怎样提高数据库的安全性。
推荐阅读GreatSQL运维管理手册：数据库防范误操作 https://greatsql.cn/docs/8.0.32-26/6-oper-guide/7-avoid-mistakes.html
1.常见危险误操作

在线上生产环境中的任何操作都要十分审慎，大概因为微小疏忽造成无法挽回的巨大丧失。
比较常见的线上误操作有几种：

• 想要删除当前目录下的文件，却不小心执行了 rm -fr /，把整个体系中的全部文件都给强行删了。
  
• 误以为是测试环境，想要删除某个数据对象，却把线上生产环境的数据库、表等数据对象给删除了。
  
• 误以为是测试环境，想要关闭或重启数据库实例，甚至是关闭或重启主机操作体系。
  
• 服务器更换硬盘等热插拔操作，现场工程师搞错信息，把正常的服务器给插拔了。
  
• 只想更新或删除部分数据，但由于还没来得及写好WHERE条件，不小心按下了回车键，导致全表被更新或删除。
  

可以防范的方法有几个：

• 总是确认每个数据库是否有可靠的备份策略，以及备份文件的有用性。
  
• 设置好一个耽误复制实例，避免在主节点上误操作删除数据后，还可以在从节点上实现快速规复。
  
• 避免层层跳转的服务器毗连方式，每跳转一次，就多了一分误操作的大概性。
  
• 完成操作后立即退出生产业务服务器，淘汰犯错误的机会。
  
• 经常性确认服务器、数据库和路径标示，而且在每次操作前都要反复确认服务器信息。
  
• 每个服务器主机体系上都要设置唯一的主机名，提高辨识度。
  
• 生产环境和测试环境要物理隔绝开，使之不能相互毗连。
  
• 毗连生产环境使用专门的操作机或必须先拨VPN等，多加一道防护门槛。
  
• 避免同时打开多个终端或操作窗口，这非常容易导致犯错。
  
• 全部重要操作执行前，都先在文档中写清晰，并逐一检查确认无误。
  
• 每个数据库的账号只授予必要的权限，避免权限过高而有了更多破坏的机会。
  
• 不要在生产环境执行删除操作，而是改成RENAME操作，先改名，确认无误后再删除，而不是直接删除。
  
• 在数据库中设置 sql_safe_updates=1，尽量避免被全表更新、删除的风险。
  

万一发生误删数据或者误操作大面积更新数据，可以参考下面几种方法进行闪回或挽救：

• [MySQL数据误删除的总结(opens new window)]：https://mp.weixin.qq.com/s/zMtgC24j7iIJ9xwbNo6AYQ
  
• [MySQL闪回工具binlog2sql(opens new window)]：https://mp.weixin.qq.com/s/hEE12-LeCUsC1zKH48hcag
  
• [my2sql工具之快速入门(opens new window)]：https://mp.weixin.qq.com/s/APgBs7MvJuxJvLwg5i7N_w
  
• [Slave被误写入数据怎样规复到主库(opens new window)]：https://mp.weixin.qq.com/s/yoUNEehE6eOBQ7uFqSR48A
  
• [耽误从库加上MASTER_DELAY，主库宕机后怎样快速规复服务(opens new window)]：https://mp.weixin.qq.com/s/qlAhbJq_ZPB5OXDd_zTQNw
  
• [一个耽误库规复的案例(opens new window)]：https://mp.weixin.qq.com/s/i8cvftodUhkcejswuLSQ3w
  
• [浅谈MySQL闪回的实现(opens new window)]：https://mp.weixin.qq.com/s/ZuXS2UcgGS2p2x3p9mTwQg
  

2.数据安全维护建议

为了让 GreatSQL 数据库运行更安全，建议遵照以下几点规范：

• 在应用端，全部用户哀求及输入数据都要做预处置惩罚，不能直接提交到数据库，避免被SQL注入。
  
• 定期扫描应用端用户哀求日志，扫描异常哀求并实时处置惩罚。
  
• 应用服务器端部署防火墙，阻断用户非法哀求。
  
• 应用程序上线前，都需要进行必要安全扫描，避免常见SQL注入等风险。
  
• 数据库端定期扫描哀求特征，判断是否有符合安全隐患的哀求，实时阻断处置惩罚。
  
• 数据库端启用审计（AUDIT）、SQL防火墙等组件，实时发现并阻断非法哀求。
  
• 数据库中存储的敏感数据，务必先进行单向加密，避免被破解、信息走漏。
  
• 生产环境中的数据，导入开发测试环境前，要先进行转码脱敏操作，避免信息走漏。
  
• 做好毗连哀求检测和监控，发现有异常频仍哀求时，实时阻断处置惩罚。
  

Enjoy GreatSQL
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。