How to backup your mysql based site using CRON in cpanel

[Kandevil]

I haven't actually cracked this yet, but hopefully with the input of some forum members we can get to a solution that others will find useful in the future.

I backup my wordpress blogs with: WordPress Database Backup

But I have a wpmu site which I haven't found a suitable solution for and also some other sites which are mysql based that I want to backup, I think the best way is to set up a CRON job, which I can do on some sites via ssh or via the cpanel menu on some others. So he are some codes snippets I have found so far:

/usr/local/bin/mysqldump --default-character-set=utf8 --user=username --password=password --quote-names --complete-insert --extended-insert --quick --compact --lock-tables=false --skip-add-locks --all-databases | gzip > /home/path/backup/`date "+%Y.%m.%d"`.gz

I can't say I have much of a clue how to use the code snippets yet, or how to even get started emailing myself a backup, but I'll get there eventually, maybe even with some of your help, so if you know what you are doing can you help me out

My idea is to have 7 backups on my site at any one time. Number 1 through 7 they get overwritten weekly, so as to not been storing too much. I also want to email myself the backup on a weekly or daily basis depending on which of my sites it is.

I think that if I can use `date "+%d"`it will give me a backup for each day of the month, I know in php w gives me a numeric representation of the day of the week, which is what I am after here, not sure if it is the same for a cron job though.

Anyway I'm off to do some more digging, if you can help with any of this, let me know, I'm learning as I go here.

[jezaff]

cant help, but would be thankful if you post a quick 'how to' when you get it sorted!!

[Kandevil]

that is the plan jezaff will post updates on how I get on and solution here at the end

[Kandevil]

ok I've already realised I was trying to be too clever, to backup the whole database daily, I can go with:


0 3 * * 0 mysqldump --user=username --password=xxxx databasename /home/sitename/public_html/backups/Sundaybackup.sql
0 3 * * 1 mysqldump --user=username --password=xxxx databasename /home/sitename/public_html/backups/Mondaybackup.sql
0 3 * * 2 mysqldump --user=username --password=xxxx databasename /home/sitename/public_html/backups/Tuesdaybackup.sql
0 3 * * 3 mysqldump --user=username --password=xxxx databasename /home/sitename/public_html/backups/Wednesdaybackup.sql
0 3 * * 4 mysqldump --user=username --password=xxxx databasename /home/sitename/public_html/backups/Thursdaybackup.sql
0 3 * * 5 mysqldump --user=username --password=xxxx databasename /home/sitename/public_html/backups/Fridaybackup.sql
0 3 * * 6 mysqldump --user=username --password=xxxx databasename /home/sitename/public_html/backups/Saturdaybackup.sql


which will backup to the /backups/ directory which needs to exist at 3am each day of the week. (/home/sitename/public_html/ is my base directory, yours could be different such as just /public_html/ or /var/www/html).

more to come soon and also a report back after the first 3am trial run

not sure if I need the --password=xxxx bit, think as it is local it will work without, will find out....

[Kandevil]

this link is very useful: Backup all your MySQL databases with one line of cron | SkyMinds.Net

[tbp]

If you have PHP available, you can have a more advanced solution, which backs up your database and sends it by FTP to another computer. This can be another server or your home computer if its on a static ip.

If you're interested then let me know, as I wrote one a while back which I can dig out.

[Kandevil]

I do have php available, sounds very good, I would be interested in the code

[tbp]

Ok, I`ll dig it out for you when I get a chance.

I actually did an article about it as well which explained the development, so if I`ve still got a copy of that i`ll pass that on too.

[Barry]

Yep I would be interested in that too please tbp.

Thanks

Baz

[tbp]

No problem!

Its not actually on my PC at the moment since I upgraded earlier in the year, so i`ll have to dig out my backup disks. I`ll do this in the next couple of days and then PM you both details of how to get it.

[Barry]

Thanks.

Baz