Auto Backup SQL Database

AKnogood · 05-18-2008, 11:05 AM

Is there a way to automatically backup my SQL database lets say every day?

I know there's some program that are available if I search on Google. But anybody uses any particular program for specific reasons?

Thanks again!
Vincent

gameserpent · 05-18-2008, 11:29 AM

create a script called "datadump.sh" with the following info

Code:

#!/bin/bash
mysqldump -d -umyusername -pmypassword databasename >data.sql

-------

Edit myusername and mypassword and databasename accordingly in the above and chmod this file to 0755 or 0777

Next make a cronjob to run the above script , use the command "crontab -e" from a unix command line or use your control panel's cron editing cababilities and put the following info in it: (make sure to use your directory structure for the path of the script)

Code:

09 22 * * * /home/username/datadump.sh

You could add a line to the script to have it email you too but I'm just trying to keep it simple.

Doing this of course will overwrite your backup file everyday so if you want to save it to a newly named file you'll need some more logic .

Hope this helps!

AKnogood · 05-18-2008, 12:39 PM

Quote:

Originally Posted by gameserpent

create a script called "datadump.sh" with the following info

Code:

#!/bin/bash
mysqldump -d -umyusername -pmypassword databasename >data.sql

-------

Edit myusername and mypassword and databasename accordingly in the above and chmod this file to 0755 or 0777

Next make a cronjob to run the above script , use the command "crontab -e" from a unix command line or use your control panel's cron editing cababilities and put the following info in it: (make sure to use your directory structure for the path of the script)

Code:

09 22 * * * /home/username/datadump.sh

You could add a line to the script to have it email you too but I'm just trying to keep it simple.

Doing this of course will overwrite your backup file everyday so if you want to save it to a newly named file you'll need some more logic .

Hope this helps!

Thanks for yur help, I'll try to figure that out.

defcrash · 05-18-2008, 02:09 PM

Whats the point of creating a file and then call the file thru a cron job if you can run the command on the cron?

I'm doing like this (change the data on bold):

I've created a cron job to every day of the week:

mysqldump --opt -Q -u user --password=password database > /home/xxxxxxxx/public_html/whateverfolder/saturday.sql

mysqldump --opt -Q -u user --password=password database > /home/xxxxxxxx/public_html/whateverfolder/sunday.sql

mysqldump --opt -Q -u user --password=password database > /home/xxxxxxxx/public_html/whateverfolder/monday.sql

etc...
etc...
etc...

Now you'll have a backup of the database for every day of the week, if you want to restore the database to any day of the week, now thats easy!! Lets say you get your database "hacked" on Monday and you'll only noticed on Wednesday, now you can easily restore it to Sunday!

gameserpent · 05-18-2008, 04:48 PM

Quote:

Whats the point of creating a file and then call the file thru a cron job if you can run the command on the cron?

I put every thing in extra files so that they can be saved with my regular backups then (when switching servers or starting new sites all I have to do is nice clean crontab entries to run everything.

Either way works......I just do what's easiest for me.

Another thing to think about with daily backups......you should be downloading it after you make it........leaving them on the server inevitably will prove to be no good if the server is hacked.

defcrash · 05-18-2008, 05:03 PM

Quote:

Originally Posted by gameserpent

I put every thing in extra files so that they can be saved with my regular backups then (when switching servers or starting new sites all I have to do is nice clean crontab entries to run everything.

The problem with that is that the files are accessible via browser revealing the destination/name where the files will be saved and the user/pass of the database!

Quote:

Originally Posted by gameserpent

Another thing to think about with daily backups......you should be downloading it after you make it........leaving them on the server inevitably will prove to be no good if the server is hacked.

Yep, thats true, being daily or not you should always save backups in places other than the server.

gameserpent · 05-18-2008, 05:09 PM

Quote:

The problem with that is that the files are accessible via browser revealing the destination/name where the files will be saved and the user/pass of the database!

What makes you think the files are accessible via the web browser? Sure if you put them in www or public_html then yeah that would be bad but in /home/username or /bin its fine.

mysqldump runs just fine without being in a web accessible directory.

Edit......yes I see using your path to the dumps would be bad......because they go under the public_html...so potentially people could just have your data

But I was never saying to do that.....

defcrash · 05-18-2008, 05:26 PM

Quote:

Originally Posted by gameserpent

What makes you think the files are accessible via the web browser? Sure if you put them in www or public_html then yeah that would be idiotic but in /home/username or /bin its fine.

mysqldump runs just fine without being in a web accessible directory.

Sure, thats correct, but can you assume that a person that don't know how to "automatically backup" a database have the correct security notion to not save the "datadump.sh" where will not be accessible to other users with malicious intentions?

Anyway, as you said before either way works, and with the right knowledge both ways can be equal secure. Theres no need to start discussion!

Edit...... perhaps i should'ed mention to password protect the folder...

gameserpent · 05-18-2008, 05:45 PM

You are right they probably don't that is why in my original post I put it under /home/username for them just trying to give him something that would work out of the box. No need to start a discussion that is for sure.

RickyG · 05-18-2008, 10:35 PM

If you host is good, they do daily/weekly/momnthly, backups of the whole site and stash it some where.

RickyG

05-18-2008, 11:05 AM	#1 (permalink)
AKnogood Contributing Member Join Date: May 2008 Posts: 33	Auto Backup SQL Database Is there a way to automatically backup my SQL database lets say every day? I know there's some program that are available if I search on Google. But anybody uses any particular program for specific reasons? Thanks again! Vincent __________________ TG Motocross 3 \| Max Dirt Bike \| Worlds Hardest Game \| Worlds Hardest Game 2 \| Miniclips \| Paper Mario World

05-18-2008, 11:29 AM	#2 (permalink)
gameserpent Senior Member Join Date: Apr 2007 Posts: 351	create a script called "datadump.sh" with the following info Code: #!/bin/bash mysqldump -d -umyusername -pmypassword databasename >data.sql ------- Edit myusername and mypassword and databasename accordingly in the above and chmod this file to 0755 or 0777 Next make a cronjob to run the above script , use the command "crontab -e" from a unix command line or use your control panel's cron editing cababilities and put the following info in it: (make sure to use your directory structure for the path of the script) Code: 09 22 * * * /home/username/datadump.sh You could add a line to the script to have it email you too but I'm just trying to keep it simple. Doing this of course will overwrite your backup file everyday so if you want to save it to a newly named file you'll need some more logic . Hope this helps! __________________ ~

05-18-2008, 02:09 PM	#4 (permalink)
defcrash Contributing Member Join Date: Jan 2008 Posts: 37	Whats the point of creating a file and then call the file thru a cron job if you can run the command on the cron? I'm doing like this (change the data on bold): I've created a cron job to every day of the week: mysqldump --opt -Q -u user --password=password database > /home/xxxxxxxx/public_html/whateverfolder/saturday.sql mysqldump --opt -Q -u user --password=password database > /home/xxxxxxxx/public_html/whateverfolder/sunday.sql mysqldump --opt -Q -u user --password=password database > /home/xxxxxxxx/public_html/whateverfolder/monday.sql etc... etc... etc... Now you'll have a backup of the database for every day of the week, if you want to restore the database to any day of the week, now thats easy!! Lets say you get your database "hacked" on Monday and you'll only noticed on Wednesday, now you can easily restore it to Sunday! __________________ arcadefromhell.com YOUR SOUL WILL BE MINE

05-18-2008, 05:45 PM	#9 (permalink)
gameserpent Senior Member Join Date: Apr 2007 Posts: 351	You are right they probably don't that is why in my original post I put it under /home/username for them just trying to give him something that would work out of the box. No need to start a discussion that is for sure. __________________ ~

05-18-2008, 10:35 PM	#10 (permalink)
RickyG Moderator Senior Member Join Date: Dec 2006 Location: Under Your Mouse! Posts: 1,207	If you host is good, they do daily/weekly/momnthly, backups of the whole site and stash it some where. RickyG __________________ Arcade Banner Exchange, game packs, top site, arcade script and forum for all of your arcade needs http://www.mibbi.com This site stinks!! www.farting.us Coming soon MibbiJr!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode