增量MySQL
我在同一个 Linux 机器上有 2 个 MySQL 数据库。它们并不是很大，但有些表可容纳大约 500,000 行，并且每月增加大约 40,000 行。
我需要做的是每天一次将部分备份从一个数据库写入另一个数据库。此部分备份是快照，除了备份之外，不会写入任何新数据。它仅包含主数据库的一些表，以及这些表中的一些字段。
编写一个删除备份数据库的 PHP 脚本很容易，然后使用所需的数据重新创建它以获得新的快照，但是我想知道是否有一种方法可以使用 PHP 增量地执行此操作，并且只写入新的或更改的数据数据。
I have 2 MySQL databases on the same Linux box. They aren't super large, but some tables hold around 500,000 rows, increasing by about 40,000 rows per month.
What I need to do is to write a partial backup from one database to the other once per day. This partial backup is a snapshot and apart from the backups will not have any fresh data written to it. It contains only some of the tables of the main db, and from those tables only some of the fields.
It is easy enough to write a PHP script that deletes the backup database, and then recreates it with the desired data to get a fresh snapshot, however i am wondering if there is a way to do this incrementally with PHP and only write new or changed data.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您的数据库中有时间戳字段吗？使用带有 ON UPDATE CURRENT_TIMESTAMP 子句的时间戳可以让您了解每行的修改时间。这样，您可以轻松地对时间戳大于给定值的行执行 SELECT 查询。
Do you have a timestamp field on your database? Using a timestamp with ON UPDATE CURRENT_TIMESTAMP clause would allow you to know the modification time of each row. That way, you could easily do a SELECT query on rows WHERE timestamp is greater than a given value.
如果您要检查那么多记录中的新数据或更改数据，那么机器的负担将会很重。每条记录都必须与其他数据库中的记录进行比较。会非常慢并且对性能影响巨大。
最好的方法是使用专为此任务设计的备份软件。如果您确实想要一个 PHP 解决方案，我会向数据库添加一个名为“changes”的新表。它将保存表名和键索引。
然后在每个需要备份的表上添加一个触发器，该触发器将使用表名和键在更改表中输入一个新条目。然后，您的 PHP 脚本可以查询该表以查找更改和新项目，并且仅查询这些项目。
您可以随时向现有表添加一个名为lastAction 或其他内容的微小int 列，而不是使用单独的表，并存储0 表示不更改，1 表示更新，2 表示新记录。
然后您再次可以只查询所需的数据。
请记住，具有两个数据库的 PHP 解决方案永远不可靠。您需要这样的结构：
完成步骤 2 后，您很可能会失去与旧数据库的连接，并且无法再将其设置为已完成。这将导致记录在下次运行时被更新或再次添加。
另一个问题可能是在步骤 1 和步骤 2 之间，有人再次更改了记录。然后，您在备份中插入较旧的值并将其标记为已完成。而最新的更新将不再备份。
If you are checking for new or changed data on that many records, it will be heavy on the machine. Each record will have to be compared to a record in the other database. Will be very slow and huge performance impact.
The best way to go would be to use software for backup that is designed for this task. If you realy want a PHP solution I would add a new table to the database called changes. It will hold a table name and key index.
Then add a trigger on each table that needs a backup that will enter a new entry in the changes table with the table name and the key. Then your PHP script can query this table to find changes and new items and only query these.
Instead of using a seperate table you can always add a tiny int column to the existing tables called lastAction or something and store 0 for no change, 1 for update and 2 for new record.
Then again you can query only the needed data.
Keep in mind that a PHP solution with two database is never reliable. You would need a structure like:
It could very well be that after you are done with step 2, that you lose connection to the old database and can no longer set it to completed. This will result in the record being updated or added again on the next run.
Another issue could be that between step 1 and 2, somebody alters the record again. You then insert an older value in the backup and mark it as completed. While the newest update will no longer be backed up.