Originally posted by steveryan
Hi guys,
I have a large database which I need to backup every now and then. Do you guys have any scripts which allows me to automate the backup process, or have any links to a good script I can use?
I'm not looking for anything too fancy, maybe just dumps the contents of the database into a flat file with the timestamp as the file name.
Thanks in advance 🙂
I use the following shell script on my Solaris server. It dumps the DB to a file and then zips it up. I have cron execute it nightly at a specified time.
#!/bin/sh
databases="dbname"
backupdir=/export/home/sqlbackup
mysqldumpcmd=/opt/sfw/mysql/bin/mysqldump
userpassword=" --user=username --password=password"
dumpoptions="--quick --add-drop-table --add-locks --extended-insert --lock-tables"
echo "Dumping MySQL Databases"
for database in $databases
do
$mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${database}.sql
done
echo "Compressing Dump Files"
for database in $databases
do
rm -f ${backupdir}/${database}.sql.gz
gzip ${backupdir}/${database}.sql
done
ls -l ${backupdir}
echo "Dump Complete!"
exit
If you're using Windows then just write a batch file similar to this one and then use the Task Scheduller and tell it to run when you desire:
@echo off
mysqldump --quick -hlocalhost -uusername -ppassword dbname > "C:\sql_backup\dbname.sql"
exit
