Link to home
Start Free TrialLog in
Avatar of M. Ahsan
M. Ahsan

asked on

MYSQL transfer changes to other server

i have logged the changes recorded in table "A" using triggers in a table log_tbl with the type UPDATE,DELETE,INSERT . how can i transfer these changes to another table (having same structure as changed table "A") on different server with the help of mysql scheduler OR other technique automatically without REPLICATION.
Avatar of David Favor
David Favor
Flag of United States of America image

I'd just run a nightly mysqldump to capture the data you desire, either full data or incremental data.

Transfer the output file (or compressed version) to your other server.

If full backup, drop the table + reload all data.

If incremental backup, just load all new data.
Avatar of skullnobrains
skullnobrains

you can obviously read the log table in a loop and replay statements. if you do that sequentially in the same order and the target has zero writes and the exact same initial data, you shoud be able to 'replicate' without using replication. just delete rows from the log tables as you replay the statements. but you'll need to write the ( 10 lines ) code yourself. and it will be very inefficient compared to statement based replication.

i've also used quite successfully a similar method based on timestamping each changed row in the source table and replicating the changed rows. adding the server that initially performed the replication allows replication in circles and not-so-acid multimaster setups. this works with different db servers such as replication from oracle to mysql. this allowed to perform aggregation at the time standard replication could not. but it does not replicate deletes ( which is feasible though ).

what is your actual goal ?
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.