Solved

Cron job for MySQL backup

Posted on 2006-06-15
11
771 Views
Last Modified: 2013-12-16
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
Comment
Question by:marky9074
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
11 Comments
 
LVL 25

Assisted Solution

by:Cyclops3590
Cyclops3590 earned 65 total points
ID: 16910994
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
 
LVL 22

Accepted Solution

by:
pjedmond earned 60 total points
ID: 16911205
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
 

Author Comment

by:marky9074
ID: 16933656
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
Are You Using the Best Web Development Editor?

The worlds of web hosting and web development are constantly evolving. Every year we see design trends change, coding standards adapt and new frameworks/CMS created. With such a quick pace of change it’s easy to get lost trying to keep up.

See if your editor made the list.

 
LVL 25

Expert Comment

by:Cyclops3590
ID: 16933689
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
 

Author Comment

by:marky9074
ID: 16940391
But if I stop myql how can I run the mysqldump command?
0
 
LVL 22

Expert Comment

by:pjedmond
ID: 16940687
mysqldump

does not require the database to be running...so you'd run it as normal! :)
0
 

Author Comment

by:marky9074
ID: 16940723
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
 
LVL 22

Expert Comment

by:pjedmond
ID: 16940800
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
 
LVL 25

Expert Comment

by:Cyclops3590
ID: 16941223
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
 
LVL 22

Expert Comment

by:pjedmond
ID: 16942212
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
 

Author Comment

by:marky9074
ID: 16943577
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

Application Discovery Service in AWS

In the era of the cloud, customers migrating away from their existing on-premise infrastructure. This requires lots of planning, strategies, and effort to identify their existing resources and determine how best to migrate.  Datacenter migrations happen in four phases -

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How many times have you wanted to quickly do the same thing to a list but found yourself typing it again and again? I first figured out a small time saver with the up arrow to recall the last command but that can only get you so far if you have a bi…
Linux users are sometimes dumbfounded by the severe lack of documentation on a topic. Sometimes, the documentation is copious, but other times, you end up with some obscure "it varies depending on your distribution" over and over when searching for …
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question