细粒度“最新” MySQL数据库数据的数据可恢复性
我使用 mySQL 后端运行一个基于网络的在线游戏。每天都会对数百个保存用户数据的相关表执行大量写入操作。
用户的帐户经常会受到威胁。我希望能够将用户的数据恢复到攻击之前的某个时间点，而不影响任何其他用户数据。
我知道 mySQL 中的二进制日志记录，但据我所知，这是到某个时间点的整个数据库恢复。我想要一个更细粒度的解决方案，即能够指定哪些表、哪些行等。 我应该调查什么？一般最佳实践是什么？
I operate a web-based online game with a mySQL backend. Every day many writes are performed against hundreds of related tables holding user data.
Frequently a user's account will become compromised. I would like the ability to restore the user's data to a certain point in time prior to the attack without affecting any other user data.
I'm aware of binary logging in mySQL, but as far as I know this is whole-database recovery up to a certain point in time. I would like a more granular solution, ie able to specify which tables, which rows etc.
What should I be looking into? What are the general best-practices?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您创建并使用审核表（通过触发器填充），您始终可以返回任何表中某一特定用户的数据。
不过，请务必在需要之前编写通用恢复脚本。将用户 ID 放入已有的脚本中比坐在那里查看审计表要容易得多，我该如何再次执行此操作。
If you create and use audit tables (populated through triggers) you can always get back to the data for one particular user in any table.
Be sure to write your general restore script before you need it though. Much easier to put in a userid into a script that you already have available than to sit there looking at the audit tables going, how do I do this again.
MySQL（或我所知道的任何其他 RDBMS）本身无法做到这一点。因此，您应该在应用程序层中自己实现它。
MySQL (or any other RDBMS that I'm aware of) is not able to do that by itself. Therefore you should implement that yourself in your application layer.
这是不可能的（没有外部模块）。
正如评论中的 thejh 所建议的，修订将是一个很好的解决方案。当您只需要使用用户数据时，创建一个类似于带有附加时间戳或类似内容的用户表的表，并每周/每天/..运行一次 cron 作业，复制最近修改的用户数据（附加标志/日期在实际用户表）放入此表中。
This is (without external modules) not possible.
As thejh in the comments suggested, revisions would be a good solution. When you only need to work with userdata, create a table that resembles the usertable with additional timestamp or similar and run a cron job once a week/day/.. that copies the userdata that has recently been modified (additional flags/dates in the actual user table) into this table.