How do I create a crontab job to backup my MySQL db?
Moderator: General Moderators
How do I create a crontab job to backup my MySQL db?
I am looking have a cronrab to backup my MySQL db every night. I would the the file name to be something like backup_<todays date>.sql. I know how to get the crontab to work, I cannot figure out how to get the date fixed so that it will put the date the db was backed up. If there is a way to get it in the format mmddyyyy that would be ideal. Any ideas or knowledge that you can offer would be great! Thanks in advance!
-
kettle_drum
- DevNet Resident
- Posts: 1150
- Joined: Sun Jul 20, 2003 9:25 pm
- Location: West Yorkshire, England
...
Should be pretty simple to do in a shell script or a command line php script. Then just have cron run that file.
-
leenoble_uk
- Forum Contributor
- Posts: 108
- Joined: Fri May 03, 2002 10:33 am
- Location: Cheshire
- Contact:
Here's mine
Code: Select all
#!/bin/sh
########################################## MySQL Backup Script ##############################
### By Lee Noble
############################################# version 1.0 ###################################
###########################
### EDITABLE AREA - SERVER SPECIFIC INFORMATION
###########################
# And the location of the Backup Directory TYPE THE ABSOLUTE PATH TO THE FOLDER
BackupFolder=/www/DatabaseBackups
# Next I'd like to create an array of database names TYPE THE NAMES OF ALL DATABASES SEPARATED BY A SPACE
Databases="database_one database_two another_database"
#############################################################################################
###########################
### NON EDITABLE AREA, DO NOT ALTER BELOW HERE UNLESS YOU ARE SURE WHAT YOU'RE DOING
###########################
# First we need to get the day of the week like this...
# %a names the folder Mon, Tue, Wed, Thu, Fri, Sat or Sun
# use %b for Month, $e for date
directory=`date +%a`
# Pick a Backup directory
cd ${BackupFolder}
# Make a new Directory called the day of the week if it doesn't already exist
mkdir -p "${directory}"
# Now I need a for loop that will repeat this line for each name in the database array above
for database in $Databases
do
mysqldump -u username --password=yourpasswordhere ${database} > ${BackupFolder}/${directory}/${database}.sql
done
#################################################### END ###################################Code: Select all
cp -R -u /www/DatabaseBackups /www/LastWeeksDataLee
-
leenoble_uk
- Forum Contributor
- Posts: 108
- Joined: Fri May 03, 2002 10:33 am
- Location: Cheshire
- Contact:
Like this
You just copy and paste that text into a plain text file.
Lets say you call the file backup.sh
You can do this in BBEdit or in the Terminal in pico. If you use BBEdit you must make sure that line endings are set to UNIX.
When you have your plain text file on the desktop then in the terminal type:
chmod +x ~/Desktop/backup.sh
Now you can run the script in the terminal by typing:
~/Desktop/backup.sh
If you move the file to your bin directory (/usr/local/bin) or equivalent and issue the command rehash in the terminal then you can run the script just by typing:
backup.sh
anywhere you like.
Now you can add this command to your crontab.
Lets say you call the file backup.sh
You can do this in BBEdit or in the Terminal in pico. If you use BBEdit you must make sure that line endings are set to UNIX.
When you have your plain text file on the desktop then in the terminal type:
chmod +x ~/Desktop/backup.sh
Now you can run the script in the terminal by typing:
~/Desktop/backup.sh
If you move the file to your bin directory (/usr/local/bin) or equivalent and issue the command rehash in the terminal then you can run the script just by typing:
backup.sh
anywhere you like.
Now you can add this command to your crontab.