Title: syncing two mysql db table
Tags: mysql;database
Question: So, I have two mysql db tables: "table_original" and "table_copy"
Here is my current setup:
Every night, table_original gets updated from another server with information. It either removes the old db row or adds a new line it with unique post_id (there won't be no two identical post_id even if row is removed).
Then, certain information along with the post_id is copied into table_copy. Thus two are linked by the post_id.
So far, the table_original has ~20,000 rows while table_copy has ~40,000.
I just noticed that table_copy has not been being updated from the first table but only have been adding rows instead of removing anything that was removed from the first table.
In order to sync them, my initial approach was to check each row from table_original against the table_copy then if post_id exists, then do nothing, if not, remove the row from the table_copy.
My concern is that, the table_original will get updated every night: either older post_id row will be removed and/or add new rows. Unfortunately there is no way for me to know what is being done before it is done. Meaning, I have to wait till the db is updated to see what was removed and added.
Then, every time the first table is updated, then I have to check every rows against the second table to update them. The table will only get bigger and I am concerned that this approach might not be the best.
What do you guys suggest that I can do?
Thanks!
Comment: Hmm, I am not sure if I fully understand what you meant by it. But thank you for the reply. I will see if I can elaborate on it. Thanks.

Comment: put a trigger on the "original" table to set a flag somewhere else. That way when the updates come in, that flag will go up, and you can detect that elsewhere. when you're doing with your "post processing", you reset the flag.

Here is the accepted answer: To delete the rows in table_copy that are not present in table_original:
DELETE t1.* FROM table_copy AS t1
LEFT OUTER JOIN table_original AS t2 USING (post_id)
WHERE t2.post_id IS NULL;
I recommend you create a dummy table with a few rows so you can experiment with this kind of query and increase your confidence with it before you run it against your real data!
See http://dev.mysql.com/doc/refman/5.7/en/delete.html for more docs on the multi-table DELETE statement.
To keep them in sync every night automatically, use triggers:
CREATE TRIGGER copy_on_ins AFTER INSERT ON table_original
FOR EACH ROW
INSERT INTO table_copy SET post_id = NEW.post_id, other_columns = NEW.other_column;
CREATE TRIGGER copy_on_upd AFTER UPDATE ON table_original
FOR EACH ROW
UPDATE table_copy SET other_column = NEW.other_column
WHERE post_id = NEW.post_id;
CREATE TRIGGER copy_on_del AFTER DELETE ON table_original
FOR EACH ROW
DELETE FROM table_copy WHERE post_id = OLD.post_id;
See http://dev.mysql.com/doc/refman/5.7/en/create-trigger.html
Re your comment:
Knowing which post_ids were deleted is tricky. Since they are no longer in the database, you'd either need to:
Infer that they were once there, if you find a gap between post_id values that are still in the database. But this is no guarantee, because id values may never be used if an insert fails.
Check a log of some kind. Some people use a trigger to append to an audit table. Or else code their application that deletes posts to create a log.
Read through deletion events using the mysqlbinlog tool. This is kind of advanced.
Comment for this answer: Thank you for the through description. Much appreciate it ! =)
Comment for this answer: Just a quick question. Is there a way for me to know what `post_id` rows were deleted? (So I can do further clean up such as file deletion with the same `post_id`) Thanks!


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 2, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2016:00:10/06/16', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<mysql><database>', 'Title': 'syncing two mysql db table', 'ViewCount': 2513, 'id': 39906190, 'user_AboutMe': '<p>Currently loving the Flutter!</p>\n', 'user_AccountId': 5629337.0, 'user_DisplayName': 'Steve Kim', 'user_Reputation': 5096.0, 'user_Views': 518.0, 'user_id': 4456697.0}}