• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 912
  • Last Modified:

Use Cpanel Cron jobs to schedule backup and email

Hi,
I'm using cpanel with have cron jobs funtion.
But i don't know write a script to schedule follow list:
- Back up database (all database of specific database), compress and email to me (require)
- Back up public_html and upload to remote ftp (optional)

All backup file must have datatime & db name.

Thank you so lots
0
BrendaMcMahon
Asked:
BrendaMcMahon
  • 7
  • 4
  • 4
  • +1
3 Solutions
 
ai_ja_naiCommented:
what db you have? they have different commands to dump data.
By the way, I hope you know what you are asking because mailing a db dump is like pushing ocean through a funnel...
0
 
BrendaMcMahonAuthor Commented:
Hi,
Thank you for your reply
I'm using Cpanel Hosting with MySQL Database.

My purpose is: Schedule backup MySQL database and email me. Because schedule backup local is ok. But what's happend if host crashed or something like that.
Thank you
0
 
ai_ja_naiCommented:
Then why don't you mail yourself the already created db backups, instead of creating again a dump and mailing it? DO you know where you can retrieve them?

anyway, making a script is really easy: just create a text file that begins with

#!/bin/sh

then add all the commands you would normally issue on a terminal. For example, in this case you can do

mysqldump [options] --databases db_name1 [db_name2 db_name3...] >outputfile.sql

or

mysqldump [options] --all-databases >outputfile.sql

For the mail part, you can use mail:

mail -s "db dump" user@mailhost.com < outputfile.sql

One you entered the commands, save and exit then issue chmod +x scriptname and add it to cron (you already know how to do it, don't you?)


Please, note that database dumps are as large as the data that's written inside. A db wil 100M rows will be several GB large, therefore impossible to mail. Be careful.
Your script will look like this



#!/bin/sh
mysqldump [options] --databases db_name1 [optionally specify other dbnames separated by space] >outputfile.sql
mail -s "db dump" user@mailhost.com < outputfile.sql

Open in new window

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
BrendaMcMahonAuthor Commented:
1. Then why don't you mail yourself the already created db backups, instead of creating again a dump and mailing it? DO you know where you can retrieve them?
- Because i want everything is scheduled and automatic without myself. In this case i don't need do manualy login cpanel - phpmyadmin - dump db. Everything will auto and email to myself.
2. Would you mind help me to provile complete command that can use in Cpanel Cron Jobs function. I'm noop with this script language
- Option: Which option i can use ?
- I want compress file to zip / gz before send it
- File name is datetime was dumped.

Thank you so so lots.
0
 
ai_ja_naiCommented:
This is a tutorial that explains how to add a job to cron (http://www.cyberciti.biz/faq/how-do-i-add-jobs-to-cron-under-linux-or-unix-oses/)

All the options you can use in mysqldump are described at http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html . But none of them are of your interests, except maybe --quick if it's a large db to avoid memory problems and -u to specify the username to access the db. Various examples are provided at the bottom of the page.
To compress the file, use this syntax: mysqldump < mysqldump options> | gzip > outputfile.sql.gz
To give the file a date/time name, use this refined syntax: mysqldump < mysqldump options> | gzip > "date +%Y.%m.%d-%H.%M.%S_dump.sql.gz"
0
 
Adam314Commented:
For getting a copy of the database, as ai_ja_nai said, you will probably not want to use e-mail, unless your database is very small.

Depending on what access you have to the server, you could use rsync or ftp.  If you can use rsync, it will be more efficient, as from one backup to the next, it will only transfer what has changed.  If you don't have rsync access, you can use ftp.  Either way, you can have a script on your local computer that is run through cron (or scheduled tasks if your local computer is windows), so it is automatic.
0
 
BrendaMcMahonAuthor Commented:
1. My database after compress (zip) take around < 1MB (until now)
2. hix ... Sorry all because i'm "stupid" guy in linux. So i need complete solution to work with Cpanel Hosting

Thank you so lots
0
 
Adam314Commented:
For files < 1MB, you can use mail as ai_ja_nai suggested.  But if your database gets much bigger (a few MB is typically the e-mail limit), you'll want something else.  Exactly what you use will depend on your specific needs.  Like I said, if it is available (contact your host), rsync is the most efficient.  If not, you can use FTP, which is almost always available.
0
 
ai_ja_naiCommented:
>Sorry all because i'm "stupid" guy in linux. So i need complete solution to work with Cpanel Hosting

ok, are you able to set a cron job in cPanel? If yes, simply use the script attached and you will be done
#!/bin/sh
 
mysqldump -u yourusername --databases db_name1 [optionally specify other dbnames separated by space]  | gzip > "date +%Y.%m.%d-%H.%M.%S_dump.sql.gz"
mail -s "db dump" user@mailhost.com < *.sql.gz

Open in new window

0
 
BrendaMcMahonAuthor Commented:
Sorry for late reply
I'll try it now and respond you soon
Thank you
0
 
BrendaMcMahonAuthor Commented:
1. this code still asked for password
2. my password have special chars like: $&' ...etc ... how can i put it in script ?
thank you
0
 
Maciej SsysadminCommented:
mysqldump -uusername -otheroptions -p'password' ...
0
 
BrendaMcMahonAuthor Commented:
1. Who to do in the case my password is: 'vKU$q`fH%B6!Sv\@

I cant not use 'password' in this case because the char ' already exist in password
2. With
gzip > "date +%Y.%m.%d-%H.%M.%S_dump.sql.gz"
return file name:  date\ +%Y.%m.%d-%H.%M.%S_dump.sql.gz (i use ls to view in ssh)
The date function does not return value date time.
3. mail function in my case does not attach file in mail. Look like it included file content into email content ??? !!!


Thank you
0
 
Adam314Commented:
Escape the single quote in the password with a backslash:

mysqldump -uusername -otheroptions -p'\'vKU$q`fH%B6!Sv\\@' ...

Open in new window

0
 
BrendaMcMahonAuthor Commented:
Thank you.
Done with case 1.
I'm watting for case 2 & 3 (filename & attach file into email)
0
 
Adam314Commented:
2. Use backticks instead of double quotes:
... | gzip > `date +%Y.%m.%d-%H.%M.%S_dump.sql.gz`
0
 
Maciej SsysadminCommented:
1. escape ` character as Adam314 wrote, or use " " instead of ' '.
2. Adam314 gave you correct answer
3. use some other appliacation to send email. I recommend mutt (echo "body of email" | mutt -x -a outputfile.sql -s subject recipient's@address < /dev/null)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now