|
Home > Archive > MySQL Server Forum > August 2005 > database syncing
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Ken Massey 2005-08-01, 8:23 pm |
| First let me say I'm not interested in replication.
I want to sync a target database with a source database at regular intervals (say weekly), but
in the intermediate time they may differ.
The sync process should essentially "make the remote copy of this database look exactly like the
local copy".
I have seen some tools online that claim to do something like this, but I was wondering if there
are any free tools, or other clever techniques. I run linux on both local and remote hosts.
Thanks,
Kenneth
| |
|
| Ken Massey wrote:
> First let me say I'm not interested in replication.
>
> I want to sync a target database with a source database at regular
> intervals (say weekly), but in the intermediate time they may differ.
>
> The sync process should essentially "make the remote copy of this
> database look exactly like the local copy".
>
> I have seen some tools online that claim to do something like this, but
> I was wondering if there are any free tools, or other clever
> techniques. I run linux on both local and remote hosts.
>
> Thanks,
> Kenneth
A recent discussion here or another group suggested implementing a
tracking system for every edit so those could be duplicated. Obviously
that has to be in every part of the system to work, which may be
painstaking depending on the complexity of the setup.
I'm curious why only once a week. If doing something like this you might
as we do it daily or in real time.
| |
| Gordon Burditt 2005-08-02, 3:23 am |
| >First let me say I'm not interested in replication.
>
>I want to sync a target database with a source database at regular
>intervals (say weekly), but
>in the intermediate time they may differ.
>
>The sync process should essentially "make the remote copy of this
>database look exactly like the
>local copy".
mysqldump piped into the 'mysql' command-line utility.
With the right options, it will drop all the tables (and
associated contents) and re-create them. Options to consider include:
--add-drop-table --lock-tables --opt
>I have seen some tools online that claim to do something like this, but
>I was wondering if there
>are any free tools, or other clever techniques. I run linux on both
>local and remote hosts.
You didn't say anything about network efficiency, so I'll assume
that keeping net traffic down when you resync with 0.2% of the
records changed by scrapping the destination database and re-sending
the whole thing isn't an issue.
It may also be possible to take snapshots of a database by setting
up replication (but never starting it) and then using LOAD DATA FROM
MASTER to get a snapshot. This worked in MySQL 4.1.* (with MyISAM
tables) and seems to no longer work in MySQL 5.* .
Gordon L. Burditt
| |
| Ken Massey 2005-08-05, 3:23 am |
| I have a virtual remote server for the website, and periodically I need to sync it with my home
computer, where I do most of the data calculations. I just got in the habit of syncing once a
week - currently by mysqldump, and recreating the entire database from scratch. I hate this, as
it's time consuming, and requires 30 minutes or so of downtime.
paul wrote:
> Ken Massey wrote:
>
>
>
>
> A recent discussion here or another group suggested implementing a
> tracking system for every edit so those could be duplicated. Obviously
> that has to be in every part of the system to work, which may be
> painstaking depending on the complexity of the setup.
>
> I'm curious why only once a week. If doing something like this you might
> as we do it daily or in real time.
| |
| Ken Massey 2005-08-05, 3:23 am |
| I'm using mysql 4.0, but I'll continue to look for a good solution. I may try that replication
snapshot idea. Thanks.
Gordon Burditt wrote:
>
>
> mysqldump piped into the 'mysql' command-line utility.
> With the right options, it will drop all the tables (and
> associated contents) and re-create them. Options to consider include:
> --add-drop-table --lock-tables --opt
>
>
>
>
> You didn't say anything about network efficiency, so I'll assume
> that keeping net traffic down when you resync with 0.2% of the
> records changed by scrapping the destination database and re-sending
> the whole thing isn't an issue.
>
> It may also be possible to take snapshots of a database by setting
> up replication (but never starting it) and then using LOAD DATA FROM
> MASTER to get a snapshot. This worked in MySQL 4.1.* (with MyISAM
> tables) and seems to no longer work in MySQL 5.* .
>
> Gordon L. Burditt
| |
| Gordon Burditt 2005-08-05, 3:23 am |
| >> A recent discussion here or another group suggested implementing a[color=darkred]
If you do it in real time, it is virtually guaranteed that a slip
of the fingers entering "delete from table;", leaving off the where
clause, will destroy all the copies.
It is also quite possible that if you do it daily and don't keep
all of the daily copies but just the last couple, that by the time
you find the problem, all the good copies are gone.
Gordon L. Burditt
|
|
|
|
|