[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 812
  • Last Modified:

Cron job for MySQL backup

Hi there,

At the moment I am using the following script to back up my databases:

mysqldump \
  -uroot \
  --password='password' \
  -hlocalhost \
  --all-databases \
  --opt \
  --allow-keywords \
  --flush-logs \
  --hex-blob \
  --master-data \
  --max_allowed_packet=16M \
  --quote-names \
  --result-file=BACKUP_MYSQL_4.0.SQL

How can I run this as a daily cron job with a file name corresponding to the date or just incrementing in number?

Thanks,

Mark

0
marky9074
Asked:
marky9074
  • 4
  • 4
  • 3
2 Solutions
 
Cyclops3590Commented:
not too hard really

mysql_backup.sh
-----------------------------------------------------------------
#!/bin/sh
date=`date +'%Y-%m-%d'`

mysqldump -uroot -password='password' -hlocalhost -all-databases -opt -allow-keywords -flush-logs -hex-blob -master-date -max_allowed_packet=16M -quote-names -result-file=$date.bak

-----------------------------------------------------------------
then
crontab -e
will most likely open in vi

type "i" to go to insert mode, and get to a new line, then type
0 0 * * * /path/to/mysql_backup.sh

column   what means
1            minute of hour
2            hour of day
3            day of month
4            month of year
5            day of week (0=sunday)

haven't tried it though, so after you create the script, run it from the command line to make sure it works as expecteed.  also, replace mysqldump with the full path of that cmd
0
 
pjedmondCommented:
Couple of things that you need to be aware of here!

Firstly, if the database is being accessed during the backup, there is the potential for discrepancies in the database back up. In order for this approach to be successful, you need to prevent connections to the database during the back up process. (Also mySQL transactional support is minimal, so it is possible that as a result of the recovery process you end up with an invalid database.....Not what you want to find out in an 'emergency' situation)

If you cannot take your database off line, then you need to use mysqlhotcopy.pl. This used to be available free on the mySQL website. I can't find it there any more, but I can post it if you're interested (28K). Alternatively, you can try the commercial alternative:

http://www.arkeia.com/hotbackup/mysql/

As with all of these types of solutions for databases. Test the restore process works in a 'hot-test' enviroment, *BEFORE* you have to rely on the capability.

Then cron script that I use with mysqlbackup is (also offers email notification of success etc):

---------------8X-----------------------
#!/bin/bash
###################################################
# Script to 'hot backup' the bugszilla and mysql  #
# databases via the loacal mysql socket. In order #
# to restore the databases, stop the mysqldatabase#
# and copy them back to the appropriate directory #
# Then chown -R mysql.mysql.......or use the      #
# hotcopy script the other way through the socket #
#                                                 #
# Written 1 Sep 04                                #
# by Peter Edmond                                 #
# me@mydomain.com                                   #
###################################################

admin="me@mydomain.com"  #Person at mydomain to notify once script has run
BACKUPDIR="/home/scripts/mysqlbu"
USER="root"
PASS="iwf2M("
SQLSOCKET="/var/lib/mysql/mysql.sock"
HOTCOPYSCRIPT="/home/scripts/mysqlbu/mysqlhotcopy.pl"

#One days cycling of backups
rm -Rf $BACKUPDIR/old
mv $BACKUPDIR/dbs $BACKUPDIR/old
mkdir $BACKUPDIR/dbs

# Backup the databases required
$HOTCOPYSCRIPT bugs $BACKUPDIR/dbs -S $SQLSOCKET -u $USER -p $PASS

if [ $? -ne 0 ]; then
        ERROR = $?
        cat <<EOF |/usr/lib/sendmail -t -oi
To: $admin
Reply-to: $admin
From: scripts@mydomain.com
Subject: Error in mySQL Backup
Note that this is an automated email,
and replying to it will get no response

The mySQL backup script returned the
following error:
 $ERROR
and HAS NOT
completed successfully.
EOF
exit 0
fi

$HOTCOPYSCRIPT mysql $BACKUPDIR/dbs -S $SQLSOCKET -u $USER -p $PASS

# And notify admin
if [ $? -ne 0 ]; then
      ERROR = $?
      cat <<EOF |/usr/lib/sendmail -t -oi
To: $admin
Reply-to: $admin
From: scripts@mydomain.com
Subject: Error in mySQL Backup
Note that this is an automated email,
and replying to it will get no response

The mySQL backup script returned the
following error:
$ERROR
 and HAS NOT
completed successfully.
EOF
exit 0
fi

cat <<EOF |/usr/lib/sendmail -t -oi
To: $admin
Reply-to: $admin
From: scripts@mydomain.com
Subject: Notification of mySQL backup completion
Note that this is an automated email,
and replying to it will get no response

The mySQL backup script has completed
running.
EOF

--------------8X---------------------------------

HTH:)
0
 
marky9074Author Commented:
I hadnt thought of access during the backup.  To be honest I had been doing it manually whilst it was up anyway, as I dont get a lot of traffic.  But I guess I should think about doing it properly.  I like the simplicity of the first method.  How do I go about taking the database offline in the script?

Mark
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Cyclops3590Commented:
just stop it before the script.  assume it'd be like this
/etc/init.d/mysqld stop
then start it up again after you're done with the backup
/etc/init.d/mysqld start

of course you may want to build a couple of protection in there to
/etc/init.d/mysqld stop || mail -s mysql_did_not_stop <your email address>
this way if the daemon doesn't stop properly, it'll email you that fact, also
/etc/init.d/mysqld start || mail -s mysql_did_not_start <your email address>

This way if an error occurs you'll be notified and won't have to worry about it (I'd still check it once in a while though just to double check everything is working correctly in the script)
0
 
marky9074Author Commented:
But if I stop myql how can I run the mysqldump command?
0
 
pjedmondCommented:
mysqldump

does not require the database to be running...so you'd run it as normal! :)
0
 
marky9074Author Commented:
Why does it do this then?

mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) whe             n trying to connect
0
 
pjedmondCommented:
Sorry - my mistake! I hadn't looked at my backup script for a long time! The script I was looking at stops anyone doing anything by locking the table:

mysqldump -a -l -u mambo -p mambo > backupfile.mysql

(the -l option which prevents any 'write' operations during the backup' The website gives a custom error under these circumstances.

HTH:)
0
 
Cyclops3590Commented:
oops my bad...obviously been a while since I've had to backup a mysql database.  pjedmond is right you need to lock the databases.  However I'd give pjedmond's script a shot and customize it if you have to.  Piece of mind is priceless when running backups and being notified when something goes wrong is worth the extra effort.
0
 
pjedmondCommented:
Another idea if locking the database causes problems is to stop the database and just copy the database table files whilst it cannot be accessed:)

/var/lib/mysql/name_of_database

Really easy:) and probably the fastest way to do the backup:)
0
 
marky9074Author Commented:
Have used this instead to lock all tables...

--lock-all-tables, -x

Seemed smaller (and simpler) to use dump, when backup up all my databases so will try the simple script and see how it goes...

Thanks,

Mark
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now