1

I would like to recreate/copy a MySQL table (a actual new ibd file) - and most of the suggestions recommends the following :

CREATE TABLE mytbcopy LIKE mytb;
INSERT INTO mytbcopy SELECT * FROM mytb; 

The suggestion works well - but from my understanding (correct me if I'm wrong), it does not insert/update records in the new table during the period of copying/recreating.

Example : During the copying, if record #45 is already inserted into the new table, and there was an update on record #45 on the original table - it won't be replicated over

Is there another way to ensure that after the full copying of data over, the data on the new table will be in the most updated state? I'm not sure using TRIGGERS is the solution for this.

5
  • The concept of Atomicity is you can only observer the prior or final resulting state of the INSERT statement. You won't observe an uncommitted transaction.
    – danblack
    Commented Dec 13, 2019 at 2:41
  • You're copying a table and trying to keep it in sync while is copying? But it won't be in sync once it finished. What are you trying to achieve?
    – danblack
    Commented Dec 13, 2019 at 2:44
  • @danblack Yes, I am trying to keep the table in sync during the copying (as there'll be new updates/inserts during this period). I am just trying to ensure no downtime will be needed Commented Dec 13, 2019 at 2:46
  • I'm not sure using TRIGGERS is the solution for this. Why? triggers may solve your problem. But you need 2 separate AFTER trigger packs - 1st will fix all changes during copy process, 2nd will sync after. And you need 3rd (temp) table as a place where the 1st triggers pack will fix changes. The tables itself must have some primary/unique key, and copy/temp tables must have auto-utilized record update timestamp field.
    – Akina
    Commented Dec 13, 2019 at 4:42
  • One common approach for “synchronization”, where such applies, is to use a insert/last_updated column. As long as the synchronization values (eg. time) are atomically increasing then one can (repeatedly) always update a destination table to a changing source table. There is a small edge-case with grabbing instant-time with such approaches (eg. sync one period back) and deletions are a separate concern. Commented Jul 31, 2021 at 4:45

2 Answers 2

0

The way you provide is a very simple and basic level's solution to create a backup copy of the table. Once copy done and someone made a change into the main table backup won't be updated with original tables so you have to do it again.

As far I understand you want to keep backup of your whole database which should be up to date with all changes being done till now.

To make this you can create a master-slave replica of the database, which will work as a master-slave database. Changes being done in the master will automatically reflect in the slave database.

Thank you.

0

Don't re-invent the wheel. pt-online-query-digest takes care of all of that. It does require a TRIGGER to keep the new table up to date, as you suggested.

2
  • what options do I need to use for "pt-online-query-digest" ? Commented Dec 13, 2019 at 8:37
  • 2
    Hey Rick I think you meant pt-online-schema-change Commented Dec 16, 2019 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.