It is currently Wed Jan 20, 2021 7:18 pm
All times are UTC + 8 hours

mysql backup

Share your awesome tips and tricks here.

mysql backup

Postby ho66es » Tue Apr 09, 2013 3:17 am

Hi I want to do a daily backup of the databases in mysql. I would prefer a script and crontab entry.

Does anyone have such a script before I try to reinvent the wheel?
608t
ho66es
 
Posts: 476
Joined: Wed Mar 13, 2013 5:38 am

Re: mysql backup

Postby ho66es » Thu Apr 11, 2013 4:08 am

ok started looking at writing my own script but most seem to use mysqldump and I can't find it? can we get a version to use?
608t
ho66es
 
Posts: 476
Joined: Wed Mar 13, 2013 5:38 am

Re: mysql backup

Postby James.W@AST » Thu Apr 11, 2013 2:08 pm

Hi ho66es,

Sure, we will put mysqldump in the next minor release (1.0.9) which should be released very soon. ;)

or if you would like to give it a try in advance, you can download the attached file, unzip and save it to /volume0/usr/builtin/sbin/

JW
Attachments
mysqldump.zip
(769.21 KiB) Downloaded 537 times
To Infinity and Beyond!

Friends of My AS-606T: (RAID 5 with WD Black 2TB * 4, WD Red 2TB * 2)
MacBook Air (10.8) & Self-assembled Windows PC (Windows 7)
iPhone 4S (iOS 6) & iPad 2 (iOS 6)
Boxee Box, Logitech Squeezebox Duet
SONY HT-CT150 Sound Bar (Connected directly to the NAS via HDMI)
CISCO Linksys E4200
User avatar
James.W@AST
 
Posts: 189
Joined: Wed Jun 06, 2012 12:50 am

Re: mysql backup

Postby ho66es » Thu Apr 11, 2013 4:19 pm

James.W@AST wrote:Hi ho66es,

Sure, we will put mysqldump in the next minor release (1.0.9) which should be released very soon. ;)

or if you would like to give it a try in advance, you can download the attached file, unzip and save it to /volume0/usr/builtin/sbin/

JW


what a hero! many thanks.
608t
ho66es
 
Posts: 476
Joined: Wed Mar 13, 2013 5:38 am

Re: mysql backup

Postby James.W@AST » Thu Apr 11, 2013 5:05 pm

You are welcome, feel free to let me know if it works for you, okay?
To Infinity and Beyond!

Friends of My AS-606T: (RAID 5 with WD Black 2TB * 4, WD Red 2TB * 2)
MacBook Air (10.8) & Self-assembled Windows PC (Windows 7)
iPhone 4S (iOS 6) & iPad 2 (iOS 6)
Boxee Box, Logitech Squeezebox Duet
SONY HT-CT150 Sound Bar (Connected directly to the NAS via HDMI)
CISCO Linksys E4200
User avatar
James.W@AST
 
Posts: 189
Joined: Wed Jun 06, 2012 12:50 am

Re: mysql backup

Postby ho66es » Thu Apr 11, 2013 9:11 pm

James.W@AST wrote:You are welcome, feel free to let me know if it works for you, okay?


it works :) 1st script runs and creates backup, now to tweak :)
608t
ho66es
 
Posts: 476
Joined: Wed Mar 13, 2013 5:38 am

Re: mysql backup

Postby James.W@AST » Fri Apr 12, 2013 2:05 am

Thanks for the update! :)
To Infinity and Beyond!

Friends of My AS-606T: (RAID 5 with WD Black 2TB * 4, WD Red 2TB * 2)
MacBook Air (10.8) & Self-assembled Windows PC (Windows 7)
iPhone 4S (iOS 6) & iPad 2 (iOS 6)
Boxee Box, Logitech Squeezebox Duet
SONY HT-CT150 Sound Bar (Connected directly to the NAS via HDMI)
CISCO Linksys E4200
User avatar
James.W@AST
 
Posts: 189
Joined: Wed Jun 06, 2012 12:50 am

Re: mysql backup

Postby ho66es » Fri Apr 12, 2013 4:05 pm

now logging to system log , archiving old backups and dumping each database to its own sql file.

Also running from cron daily.
608t
ho66es
 
Posts: 476
Joined: Wed Mar 13, 2013 5:38 am

Re: mysql backup

Postby ho66es » Sun Apr 14, 2013 4:38 am

Ok this is the script I am using from cron

cron entry is

Code: Select all
# backup prep
10 1 * * * /volume1/misc/mysqlbackup.sh


this is designed to be run before my backups to remote location so that the database is fully backed up daily and previous backups are archived. The backup dumps each database to a separate sql file and is commented where your install may differ from mine (I run the script from a share called misc, the backups go to a share called backup and the password for the backup user is stored in a file /etc/backuppass the only contents of that file are the password.

*updated script*
mysqlbackup.zip
(1.45 KiB) Downloaded 471 times


please feel free to read and get back to me with any ideas for changes / fixes. If you choose to try this please understand I make no guarantees other than it works for me and on my austor and I will not be responsible if it damages the contents of your nas in any way etc etc.
Last edited by ho66es on Tue Oct 22, 2013 12:25 am, edited 1 time in total.
608t
ho66es
 
Posts: 476
Joined: Wed Mar 13, 2013 5:38 am

Re: mysql backup

Postby ho66es » Wed May 01, 2013 1:12 am

/etc/backuppass is lost after reboot so i thought I would test startup scripts instead of moving to a different location

so I created a script called S75mysqlbackup under /usr/local/etc/init.d/ and for the contents inserted

Code: Select all
echo -e "password" >> /etc/backuppass


where password is you chosen password

then make the script executable and it recreates the backup password file on reboot (theres easier ways to do this but I wanted to test startup scripts :) )
608t
ho66es
 
Posts: 476
Joined: Wed Mar 13, 2013 5:38 am

Next

Return to Tips & Tricks

  • You cannot post new topics in this forum
    You cannot reply to topics in this forum
    You cannot edit your posts in this forum
    You cannot delete your posts in this forum
    You cannot post attachments in this forum
  • Who is online

    Users browsing this forum: No registered users and 1 guest