database backup - how to?

[siliconmike]

How do I synchronize MySQL table data of my home PC with latest data
from a remote server ?

My home PC is on a very slow internet connection, so implementing
replication will cause long time read lock on remote server, which is
not desirable.

What are the tools for manual sync of tables ?

I'm running FreeBSD on my home PC.

Mike

[Aggro]

siliconmike wrote:

What are the tools for manual sync of tables ?

If the size of database is not too big:

Use mysqldump to create dump of the whole database. Use gzip or similar
tool to make it smaller for the transfering. On your computer, unzip it
and read it in using mysql-command line tool.

You can use mysqldump to get only given tables, in case you habe very
large static table and few small dynamic tables.

[siliconmike]

Aggro wrote:

siliconmike wrote:

What are the tools for manual sync of tables ?

If the size of database is not too big:

Use mysqldump to create dump of the whole database.

Oh O! The tables are Biiiiiiiiiiiiiiiigggggggg!!

[jerry gitomer]

siliconmike wrote:

How do I synchronize MySQL table data of my home PC with latest data
from a remote server ?

My home PC is on a very slow internet connection, so implementing
replication will cause long time read lock on remote server, which is
not desirable.

What are the tools for manual sync of tables ?

I'm running FreeBSD on my home PC.

Mike

Mike,

If you can modify the programs on the server have them write the
SQL statements that insert or update your server database to a
flat file. Download the flat file and run it against your home
PC database.

As long as every SQL statement is terminated with a semi-colon
you can execute the contents of your flat file using:

mysql -u user_name database_name < flat_file_name

HTH

Jerry

[Olivier M.]

<si*********@yahoo.com> ecrivait/schrieb/wrote:

Use mysqldump to create dump of the whole database.

Oh O! The tables are Biiiiiiiiiiiiiiiigggggggg!!

even if you compress them with bzip2/gzip? (generaly work
quite well on dumps).

Olivier

--
__________________________________________________ _______________
Olivier M. - sp**********@8304.ch - PGP: 0E84D2EA - Switzerland

[siliconmike]

Sounds good. But I would prefer another approach - putting a column
called NEEDS_BACK_UP in each remote table to keep track of whether a
record needs to be backed up.

In what you advise, probably a failed sql statement in a flat file
might leave the rest of the sql statements meaningless.

Although, my scheme I guess needs considerable change in my code.

Or there is another scheme - updated-timestamp in each remote table...

Mike

jerry gitomer wrote:

Mike,

If you can modify the programs on the server have them write the
SQL statements that insert or update your server database to a
flat file. Download the flat file and run it against your home
PC database.

As long as every SQL statement is terminated with a semi-colon
you can execute the contents of your flat file using:

mysql -u user_name database_name < flat_file_name

HTH

Jerry

[siliconmike]

Olivier M. wrote:

<si*********@yahoo.com> ecrivait/schrieb/wrote:

Use mysqldump to create dump of the whole database.

Oh O! The tables are Biiiiiiiiiiiiiiiigggggggg!!

even if you compress them with bzip2/gzip? (generaly work
quite well on dumps).

The site will initially have 2 GB tables, which will grow..

[Aggro]

siliconmike wrote:

In what you advise, probably a failed sql statement in a flat file
might leave the rest of the sql statements meaningless.

No, if you use --force parameter (if I remember the syntax correctly),
which will force it continue after errors also.

[siliconmike]

Aggro wrote:

siliconmike wrote:

In what you advise, probably a failed sql statement in a flat file
might leave the rest of the sql statements meaningless.

No, if you use --force parameter (if I remember the syntax correctly),
which will force it continue after errors also.

What I mean is that there is no meaning to continue backup in case a
sql command fails. Because some of the future sql commands might be
depending on the result of the one that failed. The backup-data then
wouldn't be equivalent to the original one.

[steve]

"" wrote:

How do I synchronize MySQL table data of my home PC with
latest data
from a remote server ?

My home PC is on a very slow internet connection, so
implementing
replication will cause long time read lock on remote server,
which is
not desirable.

What are the tools for manual sync of tables ?

I'm running FreeBSD on my home PC.

Mike

I have followed this thread...

but have a basic question..

Why do you want to ’synchronize’ db with your home pc? Since you
mention slow connection, you obvisouly only using your home pc for
back. Then there are no stringent requiremetns for synch’ing. All
you need is simple backups, which have been amply explained.

By the way, on a 2Gig DB, the best approach is mysqlhotcopy, if you
have root access. All the other approaches can be problematic:
mysqldump can fail on big db’s, and taring the files directly fails
too, since the files may be updating in middle of tar.

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/mySQL-databa...ict237213.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=824569

[siliconmike]

steve wrote:

"" wrote:

> How do I synchronize MySQL table data of my home PC with
> latest data
> from a remote server ?
>
> My home PC is on a very slow internet connection, so
> implementing
> replication will cause long time read lock on remote server,
> which is
> not desirable.
>
> What are the tools for manual sync of tables ?
>
> I'm running FreeBSD on my home PC.
>
> Mike
I have followed this thread...

but have a basic question..

Why do you want to 'synchronize' db with your home pc?

To keep the new data posted by people on the website server, so that in
case the server crashes, I have a copy.

Since you mention slow connection, you obvisouly only using your home pc for
back. Then there are no stringent requiremetns for synch'ing. All
you need is simple backups, which have been amply explained.

By the way, on a 2Gig DB, the best approach is mysqlhotcopy, if you
have root access.
Correct me- but mysqlhotcopy acquires read locks, and since I'm on a
slow connection, long time read locks would prevent other threads from
reading the tables while it is being backed up.. right?

All the other approaches can be problematic: mysqldump can fail on big db's, and taring the files directly fails
too, since the files may be updating in middle of tar.

[Aggro]

siliconmike wrote:

Correct me- but mysqlhotcopy acquires read locks, and since I'm on a
slow connection, long time read locks would prevent other threads from
reading the tables while it is being backed up.. right?

Can you not create the copy to the server. After copy is ready, then
transfer it to your home computer?

[steve]

"" wrote:

steve wrote:

"" wrote:

> How do I synchronize MySQL table data of my home PC with
> latest data
> from a remote server ?
>
> My home PC is on a very slow internet connection, so
> implementing
> replication will cause long time read lock on remote server, > which is
> not desirable.
>
> What are the tools for manual sync of tables ?
>
> I'm running FreeBSD on my home PC.
>
> Mike

I have followed this thread...

but have a basic question..

Why do you want to 'synchronize' db with your home pc?

To keep the new data posted by people on the website server,
so that in
case the server crashes, I have a copy.

Since you

mention slow connection, you obvisouly only using your home

pc for

back. Then there are no stringent requiremetns for

synch'ing. All

you need is simple backups, which have been amply explained.

By the way, on a 2Gig DB, the best approach is mysqlhotcopy,

if you

have root access.

Correct me- but mysqlhotcopy acquires read locks, and since
I'm on a
slow connection, long time read locks would prevent other
threads from
reading the tables while it is being backed up.. right?

All the other approaches can be problematic:

mysqldump can fail on big db's, and taring the files

directly fails

too, since the files may be updating in middle of tar.

mysqlhotcopy works on the remote server itself (again assuming you
have root access). It takes care of all the locking, so that a live
database can be backed up. If you, instead, use TAR or mysqldum, you
will notice that the db will be brought down to its feet.

Backups can be had for very cheap these days, a few dollars a month.
There is no reason to back up over slow lines to your local PC. Just
backup over high speed to a 3rd party location. Try GNAX, you can
back up your 2Gig for $1.00 !! a month
http://www.gnax.net/backup/index.php

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/mySQL-databa...ict237213.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=824634