Sync.\Maintaining updated data in 2 DATABASE TABLES(MYSQL)

Question

I have 2 Databases

Database 1, Database 2

Now Each Database has Table say Table 1(IN DATABASE 1) and Table 2(IN DATABASE 2).

Table 1 is Basically a Copy of Table 2(Just for Backup).

How can i Sync Table 2 if Table 1 is Updated?

I am using MYSQL,Storage Engine:InnoDBand in back-end programming i am using php.

Further i can check for update after every 15 minutes using php script but it takes too much time because each table has51000 rows.

So, How can i achieve something like if Administrator/Superuser updates table 1, that update should me immediately updated in Table 2.

Also, is there a way where Bi-Directional Update can work i.e Both can be Masters?

Instead Table 1 as the only master, Both Table 1 and Table 2 can be Master's? if any update is done at Any of the tables other one should update accordingly?

Answer 1

If not wrong, what you are looking for is Replication which does this exact thing for you. If you configure a Transnational Replication then every DML operation will get cascaded automatically to the mirrored DB. So, no need for you to do continuously polling from your application.

Quoted from MySQL Replication document

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous - slaves need not be connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

Per your comment, Yes Bi-Directional Replication can also be configured.

See Configuring Bi-Directional Replication

Answer 2

As Rahul stated, what you are looking for is replication. The standard replication of mysql is master -> slave which means that one of the databases is "master", the rest slaves. All changes must be written to the master db and will then be copied to the slaves. More info can be found in the mysql documentation on replication.

There is aslo an excellent guide on the digitaloceans community forums on master <-> master replication setup.

Answer 3

If the requirements for "Administrator/Superuser" weren't in your question, you could use the mysql's Replication functions on the databases.

If you want the data to be synced immediately to the Table2 upon inserting in Table1, you could use a trigger on the table. In that trigger you can check which user (if you have a column in that table specifying which user inserted the data) submitted data. If the user is an admin, configure the trigger to duplicate the data, if the user is a normal user, don't do anything.

Next for normal users entering data, you could keep an counter on each row, increasing by 1 if it's a new 'normal' user's data. Again in the same trigger, you could also check for what number the counter already is. Let's say if you reach 10, then duplicate all the rows to the other table and reset the counter + remove the old counter values from the just-duplicated-rows.