Syncing data from one table to another

Question

I want to sync data from one table to another(only from 1 column) to another table.

Table A (
   col1 varchar(50) DEFAULT NULL,
   date_in timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
)

Table B (
   col1 varchar(50) DEFAULT NULL,
)

My strategry is to sync data that changed in timeframes of 5 minutes .

I sync with accuracy of 1 sec. Now there can be a situation where I want to sync data since 2020-05-03 00:05:00 until now (2020-05-03 00:10:00) and I receive x rows, but there are a few rows which are inserted to the database with for example date 2020-05-03 00:09:59 but they are not committed at the time I execute a SELECT query, so I miss them.

• Plan A

Add some grace time, for example 10 seconds to my SELECT query.

• Plan B

Have another thread which runs once a day and sync the "missed rows".

Are this plans legit? it there a better way of acomplishing this?

Answer 1

Plan Sound Cool but Depending on Overload on the Server for every 5 minutes will be huge. not to mention an overkill.

Use Triggers on Bulk or Inserts on The Table.

A trigger in SQL is a procedural code that is automatically executed in response to certain events on a specified table. It is important to understand how these small codes make such a huge difference in database performance.  

what you need is a DML Trigger on table on Each insert or delete.

here's a mention. https://www.sqlservertutorial.net/sql-server-triggers/sql-server-create-trigger/

Answer 2

pt-online-schema change does this trick when it copies rows from the original table to the new one. It creates three triggers: on insert, on update and on delete.

Actually, pt-online-schema can even create a copy table and leave it in that state.

pt-online-schema-change \
    --no-drop-triggers \
    --new-table-name actor_copy \
    --no-drop-new-table \
    --no-swap-tables \
    --alter "ENGINE InnoDB" \
    --execute D=sakila,t=actor

After this command the original table:

mysql> desc actor;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                 | Null | Key | Default           | Extra                       |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| actor_id    | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
| first_name  | varchar(45)          | NO   |     | NULL              |                             |
| last_name   | varchar(45)          | NO   | MUL | NULL              |                             |
| last_update | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

A copy of the original table:

mysql> desc actor_copy;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                 | Null | Key | Default           | Extra                       |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| actor_id    | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
| first_name  | varchar(45)          | NO   |     | NULL              |                             |
| last_name   | varchar(45)          | NO   | MUL | NULL              |                             |
| last_update | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

Now, changes to the original tables are reflected in the copy:

mysql> select * from actor where actor_id = 200;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      200 | THORA      | TEMPLE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql> select * from actor_copy where actor_id = 200;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      200 | THORA      | TEMPLE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql> delete from actor where actor_id = 200;
Query OK, 1 row affected (0.01 sec)

mysql> select * from actor_copy where actor_id = 200;
Empty set (0.00 sec)

mysql> INSERT INTO actor VALUES (200, 'THORA', 'TEMPLE', '2006-02-15 04:34:33');
Query OK, 1 row affected (0.00 sec)

mysql> select * from actor where actor_id = 200;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      200 | THORA      | TEMPLE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql> select * from actor_copy where actor_id = 200;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      200 | THORA      | TEMPLE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.01 sec)