Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Cron job for MySQL backup

Posted on 2006-06-15
11
Medium Priority
?
802 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
  • 4
  • 4
  • 3
11 Comments
 
LVL 25

Assisted Solution

by:Cyclops3590
Cyclops3590 earned 260 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 240 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
Google Drive is extremely cheap offsite storage, and it's even possible to get extra storage for free for two years.  You can use the free account 15GB, and if you have an Android device..when you install Google Drive for the first time it will give…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
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
Course of the Month9 days, 22 hours left to enroll

926 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