Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cron job for MySQL backup

Posted on 2006-06-15
11
Medium Priority
?
795 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 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Network Interface Card (NIC) bonding, also known as link aggregation, NIC teaming and trunking, is an important concept to understand and implement in any environment where high availability is of concern. Using this feature, a server administrator …
I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
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.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Suggested Courses

715 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