Free Mysql Backup Script - GoFuckYourself.com

ServerGenius · 03-13-2006, 09:33 AM

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 :-)

quantum-x · 03-13-2006, 09:38 AM

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

ddfGandalf · 03-13-2006, 09:39 AM

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

ServerGenius · 03-13-2006, 09:45 AM

Quote:

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 · 03-13-2006, 09:55 AM

I already knew it all

Why · 03-13-2006, 10:55 AM

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 · 03-13-2006, 11:37 AM

Quote:

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 · 03-13-2006, 11:47 AM

we dutch ppl kick ass

rsync, ssh, dumps everything is in our blood

ServerGenius · 03-13-2006, 12:23 PM

Quote:

Originally Posted by PussyTeenies

we dutch ppl kick ass

rsync, ssh, dumps everything is in our blood

Damn Right we do!

ServerGenius · 03-13-2006, 01:40 PM

bump for the US folks

ServerGenius · 03-14-2006, 04:52 AM

BUMP for the EU folks

PussyTeenies · 03-14-2006, 05:15 AM

bump for the martians :D

03-13-2006, 09:33 AM	#1
ServerGenius Confirmed User Join Date: Feb 2002 Location: Amsterdam Posts: 9,377	Free Mysql Backup Script 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 :-) __________________ \| http://www.sinnerscash.com/ \| ICQ: 370820 \| Skype: SinnersCash \| AdultWhosWho \|

03-13-2006, 09:38 AM	#2
quantum-x Confirmed User Join Date: Feb 2002 Location: ICQ: 251425 Fr/Au/Ca Posts: 6,863	looks good, makes sense. I personally dupe all my mysql queries. __________________ PrettyInCash.com - BoozedGFs.com - TeenGFs.com - JizzGFs.com- MilfUploads.com -

03-13-2006, 09:39 AM	#3
ddfGandalf Confirmed User Join Date: Feb 2005 Location: Budapest, Hungary Posts: 534	Nice one ;) I got so used to pico -w that i made an alias for it using nano :P __________________

03-13-2006, 11:47 AM	#8
PussyTeenies Confirmed User Join Date: Feb 2005 Location: Haarlem and Amsterdam, capital of the porn world ;-) Posts: 6,496	we dutch ppl kick ass rsync, ssh, dumps everything is in our blood __________________ Need adult hosting? Contact us! WARM Hosting Need an IT solution? or someone to check your site and security? Nossie - IT Professional

03-13-2006, 01:40 PM	#10
ServerGenius Confirmed User Join Date: Feb 2002 Location: Amsterdam Posts: 9,377	bump for the US folks __________________ \| http://www.sinnerscash.com/ \| ICQ: 370820 \| Skype: SinnersCash \| AdultWhosWho \|

03-13-2006, 09:55 AM	#5
HorseShit Too lazy to set a custom title Join Date: Dec 2004 Posts: 17,513	I already knew it all

03-13-2006, 10:55 AM	#6
Why MFBA Industry Role: Join Date: Mar 2003 Location: PNW Posts: 7,230	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.

03-14-2006, 04:52 AM	#11
ServerGenius Confirmed User Join Date: Feb 2002 Location: Amsterdam Posts: 9,377	BUMP for the EU folks __________________ \| http://www.sinnerscash.com/ \| ICQ: 370820 \| Skype: SinnersCash \| AdultWhosWho \|

03-14-2006, 05:15 AM	#12
PussyTeenies Confirmed User Join Date: Feb 2005 Location: Haarlem and Amsterdam, capital of the porn world ;-) Posts: 6,496	bump for the martians :D __________________ Need adult hosting? Contact us! WARM Hosting Need an IT solution? or someone to check your site and security? Nossie - IT Professional