Solved

Cron job for MySQL backup

Posted on 2006-06-15
11
747 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 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Choosing CentOS 16 82
Linux haproxy: stop temporary haproxy service 9 44
Penetration Testing home based work 3 75
AWS Central Authentication 1 66
This is the error message I got (CODE) Error caused by incompatible libmp3lame 3.98-2 with ffmpeg I've googled this error message and found out sometimes it attaches this note "can be treated with downgrade libmp3lame to version 3.97 or 3.98" …
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 …
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

803 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