View Full Version : [ubuntu] Auto Mysql Dump
December 30th, 2010, 03:27 PM
I got this linux server ( ubuntu ) and it contains a mysql DB. I need to backup this daily. Now i know you can do a mysql dump trough command line but my linux skills are not as pro as some of the users here , better said most of the users here.
Can some one help me with doing this by clearly explaining this to a beginner? ( me )
Hope some one can help!
December 30th, 2010, 04:38 PM
You can use a cron and a small shell script to do this.
Create a script. I like to put custom root scripts in /usr/local/bin/, so something like this:
sudo gedit /usr/local/bin/dumpdbs.sh
# Dump and compress all mysql databases
if [[ $1 = "" ]] ; then
if [[ ! -d $out ]] ; then
mkdir -p $out
for db in `mysqlshow | cut -d " " -f 2 | grep -v "\-\-\+"` ; do
mysqldump --add-drop-table --single-transaction $db |gzip -c > $out/$db.sql.gz
Now you'll need to set the script executable and add a cronjob to run it on a nightly basis. Create a file with your cron entry describing the job:
00 19 * * * /usr/local/bin/dumpdbs.sh
Open a terminal to proceed.
These commands will dump your current crons, merge in the new line and re-import the whole thing:
sudo bash -c "crontab -l > current"
sudo bash -c "cat newcron.txt >> current"
sudo crontab current
And finally, run this command to make your script executable:
sudo chmod 755 /usr/local/bin/dumpdbs.sh
Lots of options can be changed in the script like specifying the root username and password for your mysql server in the mysqldump line or the destination or compression of the dumps.
December 30th, 2010, 05:05 PM
Wow thats some braincracking. Ok i can follow most of the steps but Where do i put in the database nam password username etc.
This is very new to me but i hope your willing to continue the explaining :)
December 30th, 2010, 05:14 PM
Just add them to the mysqldump line. Something like this:
mysqldump -u root -pyourpasswordhere --add-drop-table --single-transaction $db |gzip -c > $out/$db.sql.gz
*The lack of a space between the -p and the password is correct.
man mysqldump to learn about all the command line options
Powered by vBulletin® Version 4.2.2 Copyright © 2014 vBulletin Solutions, Inc. All rights reserved.