One way sync

#11

Devin M wrote:
Can you recommend a efficient way to compare and send

Quote:

data?

Rsync.

If your data is slow moving, very little transfer.

Otherwise you ae down to using myql itself, or writing sql queries that
read two databases and update one with the differences.

Quote:

Regards,
Devin

#12

On 10/28/2010 5:25 PM, Devin M wrote:

Quote:

On Oct 28, 1:56 pm, Jerry Stuckle<jstuck... (AT) attglobal (DOT) net> wrote:
On 10/28/2010 4:28 PM, Devin M wrote:

On Oct 28, 1:25 pm, The Natural Philosopher<t... (AT) invalid (DOT) invalid
wrote:
Devin M wrote:
Hello, I am wondering what would be the most efficient way to
synchronize two slave databases to a master database.. Basically I
need an efficient way to find if a database is out of sync and a way
to sync it back to the master.

Any help would be appreciated,
Devin Morin

Real time? Or some sort of backup?

as far as backup goes, you can, as I have discovered, rsync ISAM files.
Ugly but it works, especially if you stop the mysqld process while you
do it.

Real time syncing is another matter.

Real time, would probably like to have it compare every 5 min with a
cron job and then sync if it needs to.

TNP is incorrect (as usual). You can't just rsync the files without
stopping MySQL. A lot of data may still be held in the buffers and not
written to disk yet. The result will be an inconsistent database.

Have you looked into replication?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

Looks like my reply got lost, Basically I have a few DNS servers that
need to pull the records from a master database server and keep them
on a local mysql server. I have not looked into replication but
basically what I am thinking about doing is writing a daemon in C that
can run on the two different servers, check for any missing or
incorrect data on the slave side and use the master server as the data
source. The only problem I have with this is a lack of experience with
the C library. Can you recommend a efficient way to compare and send
data?

Regards,
Devin

Sounds like a perfect case for replication.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#13

The Natural Philosopher wrote:

Quote:

Devin M wrote:
On Oct 28, 1:56 pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:
On 10/28/2010 4:28 PM, Devin M wrote:

On Oct 28, 1:25 pm, The Natural Philosopher<t... (AT) invalid (DOT) invalid
wrote:
Devin M wrote:
Hello, I am wondering what would be the most efficient way to
synchronize two slave databases to a master database.. Basically I
need an efficient way to find if a database is out of sync and a way
to sync it back to the master.
Any help would be appreciated,
Devin Morin
Real time? Or some sort of backup?
as far as backup goes, you can, as I have discovered, rsync ISAM
files.
Ugly but it works, especially if you stop the mysqld process while you
do it.
Real time syncing is another matter.
Real time, would probably like to have it compare every 5 min with a
cron job and then sync if it needs to.
TNP is incorrect (as usual). You can't just rsync the files without
stopping MySQL. A lot of data may still be held in the buffers and not
written to disk yet. The result will be an inconsistent database.

which is more or less what I said, of course....

No... not really.

--
Norman
Registered Linux user #461062
-Have you been to www.mysql.com yet?-

#14

Norman Peelman wrote:

Quote:

The Natural Philosopher wrote:
Devin M wrote:
On Oct 28, 1:56 pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:
On 10/28/2010 4:28 PM, Devin M wrote:

On Oct 28, 1:25 pm, The Natural Philosopher<t... (AT) invalid (DOT) invalid
wrote:
Devin M wrote:
Hello, I am wondering what would be the most efficient way to
synchronize two slave databases to a master database.. Basically I
need an efficient way to find if a database is out of sync and a way
to sync it back to the master.
Any help would be appreciated,
Devin Morin
Real time? Or some sort of backup?
as far as backup goes, you can, as I have discovered, rsync ISAM
files.
Ugly but it works, especially if you stop the mysqld process while
you
do it.
Real time syncing is another matter.
Real time, would probably like to have it compare every 5 min with a
cron job and then sync if it needs to.
TNP is incorrect (as usual). You can't just rsync the files without
stopping MySQL. A lot of data may still be held in the buffers and not
written to disk yet. The result will be an inconsistent database.

which is more or less what I said, of course....

No... not really.

I said "Ugly but it works, especially if you stop the mysqld process

while you do it. "

Two things to note. If its a linux or *nix host, which mine is. as long
as the data has been passed to the operating system, whether its been
actually written to the disk or not, is not an issue. The operating
system will still return the correct data been if its in the OS disk
buffers. Once more Stuckle ignorance is plain to see.

Secondly, the next time you sync, any inconsistent data will be corrected.

So if its purely a backup, it *is* adequate.

if you briefly STOP the mysqld process, thereby flushing any buffers it
has, the sync is correct. There are no issues whastoever. Iv'e done this
on many occasions. Zero corruption. .

I would also say that if mysqld does NOT flush its actual real file
update buffers at the end of every connection to the OS, its a pretty
unsafe bit of kit, unexpected machine power loss or program crash is bad
enough at the best of times. No point in making it worse..

There is absolutely no point in retaining disk write buffers anyway: the
OS caches those as well as or better than MYSQL can.
What Msqld needs to do is retain copies of what has been passed to the
OS, i.e. a record of what is in the OS cache or on the disk.

Which it certainly does. It wont update data it doesn't need to.

#15

The Natural Philosopher wrote:

Quote:

Norman Peelman wrote:
The Natural Philosopher wrote:
Devin M wrote:
On Oct 28, 1:56 pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:
On 10/28/2010 4:28 PM, Devin M wrote:

On Oct 28, 1:25 pm, The Natural Philosopher<t... (AT) invalid (DOT) invalid
wrote:
Devin M wrote:
Hello, I am wondering what would be the most efficient way to
synchronize two slave databases to a master database.. Basically I
need an efficient way to find if a database is out of sync and a
way
to sync it back to the master.
Any help would be appreciated,
Devin Morin
Real time? Or some sort of backup?
as far as backup goes, you can, as I have discovered, rsync ISAM
files.
Ugly but it works, especially if you stop the mysqld process
while you
do it.
Real time syncing is another matter.
Real time, would probably like to have it compare every 5 min with a
cron job and then sync if it needs to.
TNP is incorrect (as usual). You can't just rsync the files without
stopping MySQL. A lot of data may still be held in the buffers and
not
written to disk yet. The result will be an inconsistent database.

which is more or less what I said, of course....

No... not really.

I said "Ugly but it works, especially if you stop the mysqld process
while you do it. "

Two things to note. If its a linux or *nix host, which mine is. as long
as the data has been passed to the operating system, whether its been
actually written to the disk or not, is not an issue. The operating
system will still return the correct data been if its in the OS disk
buffers. Once more Stuckle ignorance is plain to see.

Secondly, the next time you sync, any inconsistent data will be corrected.

So if its purely a backup, it *is* adequate.

if you briefly STOP the mysqld process, thereby flushing any buffers it
has, the sync is correct. There are no issues whastoever. Iv'e done this
on many occasions. Zero corruption. .

I would also say that if mysqld does NOT flush its actual real file
update buffers at the end of every connection to the OS, its a pretty
unsafe bit of kit, unexpected machine power loss or program crash is bad
enough at the best of times. No point in making it worse..

There is absolutely no point in retaining disk write buffers anyway: the
OS caches those as well as or better than MYSQL can.
What Msqld needs to do is retain copies of what has been passed to the
OS, i.e. a record of what is in the OS cache or on the disk.

Which it certainly does. It wont update data it doesn't need to.

Further investigation unveiled this. (http://lists.mysql.com/mysql/177466)

">Does that imply that if we set this flush_time value to zero (ie no

Quote:

periodic flush to disk), then some of the data will not be committed to
disk, and if we had a subsequent power failure, then any data since the
last flush would be lost? We have innodb_flush_log_at_trx_commit set
to 1."

"MySQL will update the files on disk with system call after every
SQL statement and before the client is notified about the result. (This
is not true if you are running with --delay-key-write, in which case
data files are written but not index files.) This means that data file
contents are safe even if mysqld crashes, because the operating system
will ensure that the unflushed data is written to disk."

Now I cant guarantee the authority if the man who wrote that, but it
makes sense to me.

What it means is that any access to raw files will get the right data as
long as its not *exactly concurrent with a mysql update or insert*,
whether or not the daemon is stopped or not.

Obviously that doesn't mean that its a 100% reliable way to sync
databases in totally real time: replication is is the way to go thee -
but on a lightly trafficed database it will get you a very simple way to
get a 99.99% correct snapshot, and if its done regularly, any errors
will work themselves out.

Or simply script the backup to halt the daemons at both ends, take your
snapshot and restart.

In my case I haven't done that, because traffic at backup times is
essentially zero.

It avoided me having to open up sockets to remote myqsl processes that
represented a security hazard, and I discovered that rsync is very
intelligent about updates: Its sends delta changes, not complete files.

I think that pretty much refutes Stucklehead's ignorance, and justifies
my original statement, that doing it on live ISAM files is ugly, but
usually works 100%, and doing it on a stopped sql daemon is 100% reliable,.

#16

On 10/28/2010 2:17 PM, Devin M wrote:

Quote:

On Oct 28, 1:56 pm, Jerry Stuckle<jstuck... (AT) attglobal (DOT) net> wrote:
On 10/28/2010 4:28 PM, Devin M wrote:

On Oct 28, 1:25 pm, The Natural Philosopher<t... (AT) invalid (DOT) invalid
wrote:
Devin M wrote:
Hello, I am wondering what would be the most efficient way to
synchronize two slave databases to a master database.. Basically I
need an efficient way to find if a database is out of sync and a way
to sync it back to the master.

Have you looked into replication?

No I have not looked into replication.

This is exactly what MySQL replication is for - creating slave
databases which are copies of a master.

Wikipedia is an example of a very large system implemented that
way. There's a master MySQL database, several slave copies, and
web cache servers front-ending them.

Ordinary MySQL replication means that there are several read-only
copies of the database, and one read/write version, which is the
master. See

http://dev.mysql.com/doc/refman/5.0/...-scaleout.html

Note that the slave databases can get behind in updates;
you're not guaranteed that the slave databases are current.
Wikipedia sometimes gets 30 seconds to a minute behind during
heavy load.

There's also "MySQL Cluster", which is true synchronous
interlocked replication. That's more complex to set up and
run. Read-only slaves are straightforward.

John Nagle

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

One way sync

comp.databases.mysql comp.databases.mysql

Re: One way sync - 10-28-2010 , 07:38 PM

Re: One way sync - 10-28-2010 , 09:00 PM

Re: One way sync - 10-29-2010 , 05:17 AM

Re: One way sync - 10-29-2010 , 05:31 AM

Re: One way sync - 10-29-2010 , 08:05 AM

Re: One way sync - 11-02-2010 , 02:16 PM