mysql backup

[ho66es]

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?

[ho66es]

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?

[James.W@AST]

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

[ho66es]

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.

[James.W@AST]

You are welcome, feel free to let me know if it works for you, okay?

[ho66es]

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

[James.W@AST]

Thanks for the update!

[ho66es]

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

Also running from cron daily.

[ho66es]

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.

[ho66es]

/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 )