Mirroring database

software development

#1

Hi All,
I want to mirorring my mysql database on dreamhost with my local server, so my client cant access locally (wich is fast) and online (public can access too).
I have plan to use rsync to do that. The problem is:

  1. I know that in my local server (using xp pro sp2 with wamp server) database was saved in c:\wamp\mysql\data, but I dont know where dreamhost saved my database.
  2. Im not sure if I have access permission to my database directory to access with my rsync command.
  3. kchrist have other solution to this, below is his post,
    =====
    Posted by: kchrist
    Posted on: 2005-09-12 09:19:00

There’s no need to delete the database; look into mysqldump and mysqlimport.

A two-line shell script that dumps one and imports it into the other, run from cron, would do the trick. PHP seems like a strange choice of languages for something like this.

but I think that with rsync capabilities (only download any newer or changed files from the server) rsync will do the mirroring faster.

Anyone can help?
ps: Im sorry 4 my bad English, Im from Indonesia :slight_smile:


#2

You don’t have access. The mysql servers are on separate, dedicated machines. Hence the suggestion to use the mysql client programs.

Customer since 2000 :cool: openvein.org


#3

Even if you had access, backing up a MySQL database with rsync is a recipe for disaster. Only databases that have snapshot/freezing functionality can safely be backed up file-by-file. Otherwise, you could end up with internal inconsistencies between tables. And that’s assuming that rsync correctly detects when a file gets modified during the sync process. (I have no idea.)

Just use mysqldump. If you don’t have a mysql client on the other machine, run it locally through a pipe to ssh to the backup box, e.g.

mysqldump -h hostname -u username --password=“password” database_name | ssh username@hostname “cat > mydb_backup.$(date ‘+%s’)”