Automatically Backing Up MySQL Dbs?

Status
Not open for further replies.
Enigmabomb

Enigmabomb

New member
Feb 26, 2007
2,035
66
0
Than Franthithco
Hey Guys,

I'm looking for a way, maybe in PHP, to automatically back up my DB's. I've got some somewhat critical Ecommerce stuff running, and Im starting to not feel good about my daily back ups. I'd like to ideally tie in back ups of certain tables to certain events. ie, a New order comes in, and the customer and orders table get backed up.

I could also deal with a cron job that backs up every .5 hours or something stupid.

I realize the quick and dirty way is to just write some PHP that dumps everything to a CSV file, but Im looking for something more elegant, hence, me asking people way smarter than myself.

Thanks in advance

Josh
 


I used to do PHP / sql stuff but on large sets of data its very slow e.g legally I must keep customer order data for 7 years :( so you can imagine.

A command called mysqldump is your friend its a bit lower level but well worth getting to grips with. Doing anything else on a regular basis will impact your sever big time depending on your DB size.
 
#!/bin/bash

PATH="/bin:/usr/bin:/usr/local/bin"
TODAY=`date +%a-%b-%d-%Y`
BACKUP_DIR="/home/backups/sql/"
MYSQLDUMP="/usr/local/mysql/bin/mysqldump"

DB_LIST="<DBNAMES>" (delimited by spaces)
DB_USER="<DBUSER>"
DB_PASSWORD="<PASSWORD>"

for DB_NAME in $DB_LIST
do
DB_BACKUP_FILE="$BACKUP_DIR/${DB_NAME}-${TODAY}.sql"
touch $DB_BACKUP_FILE
chmod 600 $DB_BACKUP_FILE
echo "Backing up $DB_NAME"
$MYSQLDUMP -u$DB_USER -p$DB_PASSWORD $DB_NAME -r $DB_BACKUP_FILE

chmod 600 $DB_BACKUP_FILE
gzip -9 $DB_BACKUP_FILE
done

/root/ftpsqlbackup.sh

rm -f $BACKUP_DIR*
 
ftpsqlbackup.sh

#!/bin/sh

cd /home/backups/sql/

ftp -nvi <<EOF
open <YOUR FTPSITE>
user <user> <password>
passive
cd SQL
cd Daily
mput *.gz

EOF
 
Status
Not open for further replies.
Top Bottom