MySQL Dump Using Linux CRON Job
If you are a database administrator who would like to automate you tasks. Here is a simple and very basic task that can be automated,
MySQL Database Dumps are the very basic task every administrator does, no matter how simple it sounds, it is most useful in failure scenarios. Hence you would have to perform this task very often.
It is very likely to miss on taking dumps on daily routine, hence you can come up with an alternative to dump your databases by scheduling it to run automatically. This will let you concentrate on your other task which might need more attention.
There are several ways to dump a database, you have many utilities and tools to do so. Also many tools give you the option to schedule the dumps through a GUI.
Follow the below steps to automate your MySQL dump.
Firstly, you need create a .sh file with these entries,
>vi MySQLdump.sh
mysqldump -u root -pmysql123 –all-databases –routines| gzip > /root/MySQLDB_`date ‘+%m-%d-%Y’`.sql.gz
mysqldump -h 172.16.21.3 -u root -pmysql123 –all-databases –routines | gzip > /root/MySQLDB.3_`date ‘+%m-%d-%Y’`.sql.gz
In above two lines, I’m scheduling the cron job to dump my database on the same machine, also the second line is dumping a databsase from a remote host.
You need to provide access to the local system on the remote host to perform the task.
Secondly, create a CRON job to schedule the dumps.
>crontab -e
30 15 * * * /root/MySQLdump.sh 2>&1>> /root/MySQLdump.log
The above will dump the database every day at 15:30.
You can schedule it your way.
Mark R
Jun 11, 2010 @ 15:48:11
next, you need to also do the following:
1. Instruct your monitoring system to check that this script has run when it is supposed to
2. Monitor disc space usage on the backup volume (but of course, you presumably monitor all of your production systems’ discs anyway)
3. Consider backing it up to another system
Another thing to note is that by default mysqldump will lock the database out completely during a dump. As for a reasonable sized database, mysqldump may take a long time, if this is not acceptable, you should consider taking backups from a replication slave instead.
erkules
Jun 11, 2010 @ 17:36:28
Using –all-databases, the –routines option has no use.
Death
Jun 14, 2010 @ 06:59:14
Yes very true, the –routines has no effect with –all-databases.
-Thanks
24/7 EBay Auction on “Look for Job”
Jun 12, 2010 @ 06:36:59
24/7 EBay Auction on
Jun 12, 2010 @ 06:55:10
Matt Reid
Jun 20, 2010 @ 23:05:37
While you’re at it you can replace the simple script you’re using in the example with a partition space aware, exception handling, email reporting, configurable script from Kontrollkit: http://code.google.com/p/kontrollkit/
kt-backup-ai -> runs mysql backups standard mysqldump method but adds nice features like email reporting and backup completion checking as well as compression, also adds disk space checking and size analysis before running the backup to ensure you don’t use up 100% partition space with a backup.
Or there are plenty of other great CLI backup scripts out on the internet as well. 🙂
herupriadi
Jun 29, 2010 @ 05:13:27
please sent me this articles i need!!!!!!!!
» cron mysql backup – Tom Altman’s Wedia Conversation
Aug 29, 2010 @ 02:09:15
Rupert Wuori
Apr 24, 2011 @ 20:45:06
Good site! I really love how it is simple on my eyes and the data are well written. I am wondering how I could be notified when a new post has been made. I have subscribed to your RSS feed which must do the trick! Have a nice day!
Izrada Sajta
Sep 14, 2011 @ 13:35:41
Hi, Like Your post and have one Q.
I want to make Demo site and restore database on every one hour.
Tried few solutions with CronJos but everytime I get error
ERROR 1062 (23000) at line 73: Duplicate entry…
So I need to dump tables before, but what command to put in Cron Jobs
I did it with command:
mysql –host=localhost –user=user –password=pass –database base < /home/full/path/mysite.com/database_folder/dbu_filename.sql
Thanks
sam
Nov 30, 2011 @ 02:53:11
Izrada Sajta, I think the “=” equal to operator that you place between ‘-u’ and ‘root’ is causing the problem. none of the options should have the ‘=’ operator.
use -h for hostname
use -u for user
use -p for password
[I think you can have a space in place of the ‘=’]
So your command should be like
mysql –hlocalhost –uuser –ppass –database base < /home/full/path/mysite.com/database_folder/dbu_filename.sql
I guess by this time you would have resolved the problem.
Alex Vallejo
Mar 07, 2012 @ 16:50:09
Side comment, where did you get that thumbs up/thumbs down rating system? Did you build it yourself?
Question 2: It would be even more helpful if you could delete/overwrite the existing .sql dumps.
How do I back up a MySQL database? | Nathan Caldwell
Jan 31, 2013 @ 02:31:57
tony
May 05, 2014 @ 17:02:12
here is my backup.sh
#!/bin/bash
date=`date -I`
mysqldump -h localhost -u root -root helpdesk1 | gzip > /home/hthought/backup-$date.sql.gz
my crontab -e
0 0 * * * /bin/sh /home/hthought/backupPlace/backup.sh > /dev/null 2>&1
Whats wrong with this code, what should i do its not backing the database
Favourite Blogs to Follow | Database Learners
Sep 04, 2020 @ 13:03:33
Favourite Blogs To Follow | Database Learners
Sep 08, 2020 @ 13:52:06