Hey Brendan,
The way I do this is every night there's a cron job that runs and it
dumps every database to a file that it can (every database that the
mysql user the scripts uses can read). Here's the script that I use:
#!/bin/sh
MYSQLDUMP_DIR="/home/gardyneholt/mysqldump"
if [ ! -d $MYSQLDUMP_DIR ]; then
mkdir $MYSQLDUMP_DIR
fi
for db in `echo "show databases;" | mysql --user=mysqluser
--password=mysqlpassword mysql | grep -v "^Database$"`; do
dmp=$MYSQLDUMP_DIR"/"$db".mysql.dmp.bz2"
mysqldump --user=mysqluser --password=mysqlpassword
--add-drop-table -Q $db | bzip2 > $dmp
chmod 600 $dmp
done
Just replace 'mysqluser' with a database user, and 'mysqlpassword' with
the password for that user.
Then on the second server a couple of hours later there's a cron job
that rsyncs all the database dumps from the primary server. As long as
the second server trusts the primary server an scp or rsync command will
be able to pull all the files over and then just write a short script
that imports them all. I don't import them on our backup server so don't
have a script handy for that, sorry. On our backup server we store the
last 3 weeks database dumps so we can roll back if something bad happens
too.
- Mike
On Tue, Aug 17, 2010 at 8:30 AM, Brendan Brink <textvouchers.com
<http://textvouchers.com>@gmail.com <http://gmail.com>> wrote:
hi there,
Am wanting to automatically create a php script to create a copy of a
database onto a different server.
The script would run each evening at midnight.
Unfortunately, we are not able to simply write each DB transaction to
both databases on the fly.
The database has the following statistics:
127 tables
237,000 rows
30MB in size.
It is a MYSQL5 database.
Does anyone know of a script they have used that can handle this?
Ideally want the database to be backed up to a functional secondary
database, so that we can switch to it if the original server goes
down, and hence the data would be new as at the last midnight.
Looking forward to your advice / information regarding this.
Thanks
Brendan.
--
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
<mailto:[email protected]>
To unsubscribe, send email to
[email protected]
<mailto:nzphpug%[email protected]>
--
Aaron Barnes
= +64 27 469 5867
= http://twitter.com/spastk
= sry_not4sale on irc.freenode.net <http://irc.freenode.net>
--
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
--
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
