Determine new/changed data(rows) on a table during a table clone operation

Question

I'm trying to alter some heavy data tables tables, this operation may cause some impact on our production service, so to minimize outages the plan is:

• create a copy table that inherits table definitions.
• copy data to this copy table.
• Alter copy tables, while originals tables remain available for production service.
• Drop original tables and rename copy tables to original's name

So I'm wondering if it's possible (and if it is, how?) to identify which rows were added or updated to the original table since the copy operation started so after a insert is done I could refresh table, do an order by and continue with the next INSERT. I don't have a "last modified" column that could help me determine this I can't alter the structure of the tables. I read triggers may help, but I'm not to familiar with them.

Answer 1

The steps you mentioned are the high level plan executed by pt-online-schema-change

The tool can be used and configured with some options like monitor replica lag (with --max-lag), to pause if you need (with --pause-file), and you can even decide if you want the tool to perform the swap of the tables once it's done, or if you prefer to swap it during off-peak hours.

A quick example on how you could execute this would be:

pt-online-schema-change --pause-file=/tmp/pt-osc.pause --alter="YOUR REQUIRED ALTER HERE" --dry-run

With --dry-run you can check if everything is ok, and if you're cool with the output you can replace it with --execute.

One thing to keep in mind: If your table(s) have FK, you would have to choose between drop_swap (drop the old table and rename the new one) and rebuild_constraints (don't drop the old table, rename the tables and alter the child tables) (Further details can be found here: How pt-online-schema-change Handles Foreign Keys).

Answer 2

Assume that the table name is table1 and it have primary key expression (id).

1. We create a table which will store id values for rows which were inserted, updated or deleted, and also store the action type.

CREATE TABLE log (
    id INT PRIMARY KEY, 
    action CHAR(1)
    );

2. We create triggers which save the info about altered rows into this table.

CREATE TRIGGER tr_ai_log
AFTER INSERT ON table1
FOR EACH ROW
REPLACE INTO log (id, action) VALUES (NEW.id, 'I');

CREATE TRIGGER tr_au_log
AFTER UPDATE ON table1
FOR EACH ROW
REPLACE INTO log (id, action) VALUES (NEW.id, 'U');

CREATE TRIGGER tr_ad_log
AFTER DELETE ON table1
FOR EACH ROW
REPLACE INTO log (id, action) VALUES (OLD.id, 'D');

These triggers stores only the most last action for each altered row. If the record for some row exists then it will be overwritten.

3. Now we may create new table structure and copy the data into it.

4. When the copy is ready for to replace old table we must:

a. Disable external access to the server.

b. Drop all external links properties (foreign keys, triggers, permissions) from old table.

c. Rename old table.

d. Rename new table.

e. Copy those rows which are listed in log table with 'I' or 'U' action from old renamed table to new table overwriting existing one.

f. Delete rows which are listed in log table with 'D' action from old renamed table.

g. Restore all external links properties.

5. We may restore access to the server.

---

Create the structure for new table with caution. Especially - check charsets and collations for string-type data, if they differs then direct copy may cause in data damage. In some cases these damages are rare ("spitting into the data") and cannot be detected immediately with visual checking.