How to backup your mysql based site using CRON in cpanel

[Kandevil]

Tweet

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

[trevHCS]

If it's Cpanel (as in the product called that) then what I do is use Getright and it's daily download feature to backup all the databases. It can do the naming you mentioned using special replace codes.

I just point it at the URL provided for dbase backups in Cpanel and it logs in and does the download for me. Handy when you're doing this across about 120 odd sites.

Actually that's not a bad idea replacing the download....might solve one of my current problems with the backups getting stupidly big!

Trev