0

My problem statement is as below :

So, we have a system A where we get things from system C. Records coming in from system C are dumped into database of system A. One main aspect of that incoming record is it has Status field with values as (Synchronised, Not Available, Unable to synchronize).

Also, we have system B in place where records from system A are saved.

Now the main point here is as soon as there is update in system A's records status the same should be updated in system B's database table.

Hence whenever client queries for data from system B it will have the most recent status.

JUST FYI: I'm working in .NET Core Web API based projects both system A & B are based on that technology and databases for those individual projects are based on MySQL.

So far I'm still in research mode and till now i have explored on Dotmim sync framework, Trigger based approach , The best technology to synchronize data between different database schemas?

Haven't found such concrete and best solution as of now and looking for some fresh pointers or suggestions.

Any help is welcomed.

DarkBee
  • 16,592
  • 6
  • 46
  • 58
Aԃιƚყα Gυɾαʋ
  • 107
  • 1
  • 15

1 Answers1

1

Would be great to see the code piece or know about the things that you used

Note that below answer works for same definition of the table on both servers.

On Master Server.

Create a replication user with replication grant access:

mysql> select user,host from mysql.user where user='replication_ip_log_2';
+----------------------+---------------+
| user                 | host          |
+----------------------+---------------+
| replication_ip_log_2 | xxx.xx.xx.xx |
+----------------------+---------------+

mysql> show grants for `replication_ip_log_2`@`xxx.xx.xx.xx`;
+--------------------------------------------------------------------------+
| Grants for replication_ip_log_2@xxx.xx.xx.xx                            |
+--------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'replication_ip_log_2'@'xxx.xx.xx.xx' |
+--------------------------------------------------------------------------+


FLUSH TABLES WITH READ LOCK; --- Carefully this will lock all tables 
SHOW MASTER STATUS; --- And copy the values of the result of the last command somewhere.

You need the file and position

File             | Position
mysql-bin.091820 |  7039359

Without closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:

mysqldump db_name table_name > table_name.sql

Do not release the lock, if the dump has not ended yet.

UNLOCK TABLES;

Now copy the dump file to the slave using scp or your preferred tool.

On Slave Server.

Add replicate-do-table=db_name.table_name and after the change is made restart mysql service on Slave.

sudo grep -ir "ip_log2" /etc/mysql/
/etc/mysql/mysql.conf.d/mysqld.cnf:replicate-do-table=db_name.ip_log2

Open a connection to mysql and type:

STOP SLAVE;
RESET SLAVE;

CHANGE REPLICATION SOURCE TO
SOURCE_HOST='source_server_ip',
SOURCE_USER='replica_user',
SOURCE_PASSWORD='password',
SOURCE_LOG_FILE='mysql-bin.091820', ---the data tou saved from master 
SOURCE_LOG_POS=7039359;

Load masters data dump with this console command:

mysql -uroot -p < /root/mysqldump.sql
START SLAVE;
SHOW SLAVE STATUS;
Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
  • Thanks sir. Have one doubt after looking at this. Does this work automatically...i mean as soon as there is entry in one table of a database it immediately replicates change on another table of different database ? – Aԃιƚყα Gυɾαʋ Aug 03 '22 at 11:28
  • 1
    @THINKINGMACHINE yes, that is the point of replication. Every change made on the master automaticly will be reflected on the slave – Ergest Basha Aug 03 '22 at 11:36
  • 1
    @THINKINGMACHINE I made a change on `mysqldump db_name table_name > table_name.sql` this will backup only the table you need , not all the databases/tables – Ergest Basha Aug 03 '22 at 12:03