We help IT Professionals succeed at work.

Mysqldump backup script and crontab error

jackal077
jackal077 asked
on
799 Views
Last Modified: 2014-01-15
Hi,

I've got the following mysqldumb backup scritpt:

#/bin/sh
mysqldump -u username -pmypass --all-databases > DB.sql
gzip DB.sql
NOW=$(date +"mysql-backup-%H%M-%d-%m-%y")
mv DB.sql.gz /home/user/BACKUPS/"$NOW.gz"

It works fine when I start it manually. When i put it in crontab, I get the following error:

mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'data\_29'': Got error 28 from storage engine (1030)

I reasearched that error 28 means there is not enough storage. That was the case but i free up some space and I'm still getting this error. Also under crontab the backup file is nearly half the size.
Comment
Watch Question

IT Manager
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT

Commented:
Hi!

To take a backup and compress the file in one line of code do this
#!/bin/sh
backfile=$(date +"mysql-backup-%H%M-%d-%m-%y")
myuser=yourusername
mypass=yourpassw
cd /home/user/BACKUPS
mysqldump -u $myuser -p$mypass --all-databases | gzip -9 > $backfile.sql.gz

Open in new window


This will compress your backup as much as possible on the fly and save storage.

Regards,
      Tomas Helgi

Author

Commented:
Hi,
yes, I must have missed the !. Cron runs under root. I didn't realize I had to define the path inside the script. That was it. Thx.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.