Free Mysql Backup Script

[ServerGenius]

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.

#!/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 :-)

[quantum-x]

looks good, makes sense. I personally dupe all my mysql queries.

[ddfGandalf]

Nice one ;)
I got so used to pico -w that i made an alias for it using nano :P

[ServerGenius]

Originally Posted by quantum-x

looks good, makes sense. I personally dupe all my mysql queries.

I do that too actually, but not everybody has that option, I run double db's
on at least 2 machines, I do the same with content actually through
rsync+ssh over second NIC with private LAN so all updating and mysql
queries go over the second nic using private ip space which doesn't get
routed over the internet. That way I can restrict mysql not to accept any
connections over the internet which increases security.

This I have 100% working mirrors of everything that I run, in case of an
emergency I can quickly add the ip of the server that is down and take my
time restoring the broken server while having less than a few minutes downtime.

It's the ideal setup but not everybody has multiple servers to do this, if you
have multiple server than this is defenitely the way to do things. Those of
you that have but don't know how to do this...........






















































SEE SIG

[HorseShit]

I already knew it all

[Why]

if you cant backup mysql you shouldnt even be allowed root. people should really pay for proper administration. but as you can see from looking around this board. most dont. and then bitch and moan when it kicks them in the ass later.

[ServerGenius]

Originally Posted by Why

if you cant backup mysql you shouldnt even be allowed root. people should really pay for proper administration. but as you can see from looking around this board. most dont. and then bitch and moan when it kicks them in the ass later.

I agree but obviously I'm biased For those that don't know and
have no admin with this they have 1 problem less to worry about

[PussyTeenies]

we dutch ppl kick ass

rsync, ssh, dumps everything is in our blood

[ServerGenius]

Originally Posted by PussyTeenies

we dutch ppl kick ass

rsync, ssh, dumps everything is in our blood

Damn Right we do!

[ServerGenius]

bump for the US folks

[ServerGenius]

BUMP for the EU folks

[PussyTeenies]

bump for the martians :D