Updating Mirror Database

Need a Backup Solution

[boxfan]

Hello,

I have a very busy ecommerce site that uses Redhat, php, and mysql. We have one database where transactions are stored and our customer service reps access to perform their work.

I would like to keep the original database for the ecommerce side and then use a second database that contains the exact data for the customer service reps to work in. This way if they delete a customer record or somehow screw up a record I can always restore from the original ecommerce record.

I'm thinking of setting up the second database and then using the binary logs from the original database to update the second database several times per day. The reps do not need real time updates so several times per day would be fine.

Is this the best way of doing this? If so, does anyone know of any scripts that are already written that will update a db from the binary logs?

I looked at replication but I'm not sure that's the way to go.

John

[physics]

One way to do this is to set up a cron job that grabs the db into a .sql file, copies it over to the other server, then imports it into the 'backup' db a few times a day.

On production server:
1)

mysqldump --opt yourdb -uusername -ppassword > yourdb.sql

2)
Set up an ssh client key so you can ssh between servers non-interactively, see:
[hacks.oreilly.com...]

rsync -ave ssh yourdb.sql [e-mail]:./

On remote server:
3)

mysql -uusername -ppass < yourdb.sql

Actually you can do this from the production server in the same cron script like

ssh example.com "mysql -uusername -ppass < yourdb.sql"

This is mostly from memory but I just want to get the idea across. I recommend the book Linux Server Hacks (see the link above) for more info on these tools.

[boxfan]

Importing the whole database several times a day is not an option as the customer service reps updates would be lost. I need to update the db several time a day with new records that's why I am considering the binary files.

[physics]

Ahh sorry. Maybe CVS could be used for this somehow (i.e. merge in the changes from the production to remote server).