GoFuckYourself.com - Adult Webmaster Forum

I get asked many times how to backup mysql databases on a server. So here
is a free little shell script that will take care of it.

INSTALL INSTRUCTIONS

MySQL database backup script. This small script will do a backup of all
the MySQL databases that are controled by the user + password provided
in the script. Make sure /home/backup exists or change the path info
to the path where you want to store your backups.

The script will create /home/backup/todaysdate/mysql.databasename.dump.gz
Additionaly it will keep the backups stored for the amount of days that
is filled out in the KEEP_DAYS=x variable. This means it will also clean
up all backups that are older than the number provided in the variable.

Before you try to install and run this script please verify the location
of mysqldump as that may vary depending on how mysql was configured and
installed. The easy way to find the location of mysqldump is to login to
the shell of your server and issue the following command: whereis mysqldump <enter>
If the path is different than /usr/bin/mysqldump please update it in the code
for every instance it is being used.

On a linux based system open an editor nano is an easy one.
nano -w /path/to/backup.sh
paste the code into the editor (shift + insert)
CTRL + X to save the script

Now make it executable
chmod 755 /path/to/script

To have this run every day we have to add a cronjob, on linux issue the following
command: crontab -e
press the i key for insert mode and move the cursor to first empty line.
add the following line after you changed /path/to/backup.sh to the correct location \
where you stored backup.sh:
0 0 * * * /path/to/backup.sh /dev/null 2>&1

this will make the script run every day at midnight, if you want a different time
the first 0 is for minutes the second one for hours.

Press the Escape key to go out of insert mode.
Press SHIFT+ZZ (The Z key twice while holding shift) to save the new crontab
When crontab exits it will say something similar to this

"crontab.8fofJ3/crontab" 4L, 135C written
crontab: installing new crontab

4L means 4 lines, 135C means number of characters in the crontab list.

you're now done and your mysql databases will get backup. I recommend to download
a copy of the backups on your workstation or another server in case of hdd failure.
Leaving them on the same server and the same hdd is not very smart.

Last but not least. Use of this script is at your own risk. I cannot be
held liable if it causes any damage what so ever. If you don't feel comfortable
using it then DON'T. If you understand the code you'll see that it is harmless
but I never endorse people to try code if they don't understand what it will do
in case it does fuck up something then tough luck, you have been warned.

Code:

#!/bin/bash



TODAY=`/bin/date +%d-%m-%y`



BACKUP_MYSQL=1



DEST_DIR="/home/backup"



DESTINATION_DIR="$DEST_DIR/$TODAY"

KEEP_DAYS=1



# edit below PASSWD="password" and USER="username" to your mysql username and password 



if [ $BACKUP_MYSQL -eq 1 ]; then

  PASSWD="password"

  USER="username"

  DATABASES=`mysql -BN -u $USER --password=$PASSWD -e 'SHOW DATABASES;'`



  if [ -x /usr/bin/mysqldump ]; then

    for db in $DATABASES; do

      mkdir -p $DESTINATION_DIR

      cd $DESTINATION_DIR

      /usr/bin/mysqldump --opt -u $USER --password=$PASSWD $db | gzip - | cat - > mysql.$db.dump.gz

    done

  fi

fi



/usr/bin/find $DEST_DIR/ -type d -mtime +$KEEP_DAYS | xargs rm -rf 2>&1 > /dev/null

That's all folks enjoy :-)