Portal Home > Knowledgebase > Articles Database > Is it possible to create a cron to backup mysql data daily ?
Is it possible to create a cron to backup mysql data daily ?
| Posted by kimkim2, 05-15-2009, 09:26 AM |
| Hello,
Sorry if i'm wrong. Is there any way to create a cron to backup mysql data daily ( or weekly )? I mean an "auto script" to run this command daily
mysqldump -u usernam -p password dataname > file.sql
I'm sorry if this is a stupid question.
|
| Posted by fwaggle, 05-15-2009, 10:08 AM |
| It's not a stupid question.
Don't put the password on the command line, instead consider tightening up the permissions on the user's ~/.my.cnf file and add this to it:
[mysqldump]
password=yourpasswordgoeshere
Then mysqldump -u username -p databasename > databasename.sql should work automatically.
Last edited by fwaggle; 05-15-2009 at 10:08 AM.
Reason: typo
|
| Posted by sahsanu, 05-15-2009, 10:33 AM |
| Take a look to automysqlbackup.
It does all you need.
|
| Posted by kimkim2, 05-18-2009, 07:57 AM |
| Thank you, where to create it ?
4. Create the following directory.
/backups
/etc/cron.daily/backups
or
/backups
|
| Posted by softtech, 05-18-2009, 12:58 PM |
| Well do not forget to make some script move that backup off site. ON site backups are fast and convenient but bad if you need a backup from say a disk or major backbone failure causing you to relocate the server. No need to offsite every backup but maybe every week or 5th backup moved off site will help.
|
| Posted by softtech, 05-18-2009, 01:03 PM |
| Create it anywhere you like. /backups makes sense to me but anything you like should be fine. Just so long as you know where to find it when you need it. Its also good to have a written restore procedure on hand and practice it to make sure you know how to do it in a pinch.
As for the /etc/cron.daily/backups this is likely the script that you run backups from. Any file in that cron.daily will be run atr some point in the middle of the night. Makes ure its executable and test the script by running it manually to make sure its works. Keep in mind that some cron environments might not have a full $PATH you are accustom to in your ssh sessions. So if it were me I always put in full paths to any binary file.
mysqldump would likely be /usr/bin/mysqldumb or wherever you find it on your system.
|
| Posted by Rekhatitus, 06-03-2009, 03:22 AM |
| Here is the cronjob to backup mysql database
You can also do it by using Mysqldumper.
Last edited by Rekhatitus; 06-03-2009 at 03:27 AM.
Reason: correction
|
| Posted by digitalpioneer, 06-03-2009, 04:07 AM |
| should mysql or the web server be turned off when backing up?
|
| Posted by Rekhatitus, 06-03-2009, 04:57 AM |
| No need to turn off Mysql or webserver while backing up.
|
| Posted by Lightwave, 06-03-2009, 06:28 AM |
| You do need to take into consideration how big your database is, how much utilization it's under, and how often you're going to back it up.
If your stuff is highly utilized, or with rather large tables/databases and such... some surfers may experience unexpected errors as the mysql server will lock tables as it performs the backup.
|
| Posted by ddwebguru, 06-03-2009, 06:47 AM |
| Yes it will run but if you try to change the code ,it will give an error.
|
| Posted by blue44, 06-03-2009, 10:09 AM |
| 2Big2Send.com have an automated offsite backup solution for MySQL databases.
Give me a shout on PM if you wish to have a try.
|
Add to Favourites 
Print this Article
Also Read
