Backing up database

[cjdj]

what is the best way to backup the mysql databases? (prefereably in a way that I could just restore them to a local MySQL database if necessary).

My main concern is that if PowWeb dissapears (God forbid), I want to ensure my data dosn't dissapear also.

Any pointers in the right direction will be appreciated.

Thankyou.

[sporter]

Here is my way of backing up my database. I would be very pleased if other people would post detailed instructions regarding how they do it, because it is always good to have a backup for your backup. All I wanted to do was dump to file and then be able to restore from the file. This is how I do it:

1. On the apparent recommendation in Powweb's FAQ, I purchased Mascon.

2. Mascon works on all the various Windows platforms, but has a bug when installed on Win 95/98/ME. The bug gives an error message "Cannot create file". To fix the bug:
> Download and install the http://download.scibit.com/Software/Mascon/Msc2.3.27-2.3.39.exe patch to fix this.

3. The dump to file does not seem to work if you select the box "Flush log files before starting dump", so leave it unchecked.

4. With all the above in place, you should be able to dump to file.

5. To restore the database, open your dump file with notepad, right click and "select all", copy the file and then open Mascon. In Mascon, open your database and click on SQL at the bottom right. Paste the file (Ctrl v) into the top box of the SQL view. Now execute the script by pressing (Ctrl e). Your database is now restored.

You would not believe how long it took me to figure out how to do this, but now it is easy.

Mascon has a detailed online Help section, and on reading it you will find that Mascon can be a wonderful tool to work with your database. It does far more than just help you back up and I do recommend it.

Good luck!

[cjdj]

After posting that question, I did a little research, and discovered a way to do it. I should have posted what I had found, but it didnt occur to me. Sorry.

My way is almost identicle to what you do, but my way of accessing my mySQL database is with the command line tools that are packaged with mySQL (you can download and install your own mySQL server on unix as well as windows).

I now do weekly backups of my database using the mysqldump command line tool that is provided.

A sample of this is:
mysqldump --host=pluto.powweb.com --password=pass --user=username database > backup.txt

replacing pass with your password, username with your username and database with your database.

This will then dump the contents of your database into a text file that can simply be pasted into the mysql command line to restore everything (after deleting all your tables).

It can also be used to create a dulpicate database on your local computer if you would like. It also allows me to view the records and retrieve individual items if necessary.

Thanks for your responce.

[sporter]

The above two ways to backup a MySQL database both seem to require that I be working from a computer with either Mascon or MySQL installed.

What happens when I am travelling and can only access the Internet through other people's computers or Internet Cafes. Are there ways to backup a MySQL database that are entirely online?

Also, my home computer is turned off when not using it and when travelling. It seems to me that using chron to schedule backups would work best if entirely online.

One idea might be to open an account with an ISP in Australia (a safe distance away) and use chron to back up my Powweb databases to that location. That way the databases would be backed up daily even when I do not have access to a computer.

Suggestions?

[Catweasel]

Originally posted by sporter
The above two ways to backup a MySQL database both seem to require that I be working from a computer with either Mascon or MySQL installed.

What happens when I am travelling and can only access the Internet through other people's computers or Internet Cafes. Are there ways to backup a MySQL database that are entirely online?

Install phpMyAdmin on your site, then you can dump your database/tables to CSV files. If you want to keep a backup copy away from Powweb, FTP the CSV's down to yourself.

[macgroup]

Sporter,
Did you figure out how to cron the dump? Would you mind sharing both the cron and script code?

I have PHPmyadmin but would also like a cron to run the backup while I'm away.

Thanks!

[devinemke]

0 0 * * * /usr/local/bin/mysqldump --user=xxx --password=xxx databasename >/www/u/username/backup.sql 2>/www/u/username/backup.err

Paste the above code into a plain ASCII text file. Change the "xxx" to your username and password and change the "databasename" to your database name. Change the paths to your own root directory. Save as "crontab" and upload to your /etc/ directory. This will backup the schema of your database every midnight (PST) to the file "backup.sql". Errors will be spooled out to "backup.err".

[macgroup]

Thanks! I'll give that a try.

I don't suppose you know how to make it save to a different file each night?

:}

[devinemke]

well, you could modify the cron to:

0 0 * * * /usr/local/bin/mysqldump --user=xxx --password=xxx databasename >>/www/u/username/backup.sql 2>>/www/u/username/backup.err

which would append the new backup to the old backup, but this file could get very big very fast.

another way is to add a separate line to your crontab that backs up the databse to a different file each night of the week. that way you would have 7 backups going back each week. something like this:

0 0 0 * * /usr/local/bin/mysqldump --user=xxx --password=xxx databasename >/www/u/username/backup0.sql 2>/www/u/username/backup0.err

0 0 1 * * /usr/local/bin/mysqldump --user=xxx --password=xxx databasename >/www/u/username/backup1.sql 2>/www/u/username/backup1.err

...and so on for each week day. (please note that each command should be on a separate line of your contab file.)

[macgroup]

My database is still not being backed up.

I am NOT getting any error messages.

Here is my code. Each code fits on one line. There are no hard returns or extra characters in it. It is saved in notepad.
x = user name
xx=password
databasenamehere = my actual database name
everything else is exactly as written.

Anyone see something wrong or have any thoughts?


0 0 0 * * /usr/local/bin/mysqldump --user=x --password=xx databasenamehere >/www/u/ui/backups/backup0.sql 2>/www/u/ui/logs/backup0.err
0 0 1 * * /usr/local/bin/mysqldump --user=x --password=xx databasenamehere >/www/u/ui/backups/backup1.sql 2>/www/u/ui/logs/backup1.err
0 0 2 * * /usr/local/bin/mysqldump --user=x --password=xx databasenamehere >/www/u/ui/backups/backup2.sql 2>/www/u/ui/logs/backup2.err
0 0 3 * * /usr/local/bin/mysqldump --user=x --password=xx databasenamehere >/www/u/ui/backups/backup3.sql 2>/www/u/ui/logs/backup3.err
0 0 4 * * /usr/local/bin/mysqldump --user=x --password=xx databasenamehere >/www/u/ui/backups/backup4.sql 2>/www/u/ui/logs/backup4.err
0 0 5 * * /usr/local/bin/mysqldump --user=x --password=xx databasenamehere >/www/u/ui/backups/backup5.sql 2>/www/u/ui/logs/backup5.err
0 0 6 * * /usr/local/bin/mysqldump --user=x --password=xx databasenamehere >/www/u/ui/backups/backup6.sql 2>/www/u/ui/logs/backup6.err

[cjdj]

What server is your website on, and what server is your database on? You may need a --host parameter. Although I would expect that would give you an error.

[macgroup]

I believe both on lunar

[cmschool]

cjdj...do you need to create the backups folder? or will the cronjob do that automatically? I used the same 7day backup system, but I haven't tested it since it occurs at midnight. Please advise if there's something special I have to do as I have never set up a cronjob before.

Also, how would I go about copying the backup off? Just as an extra precaution...

[cjdj]

Originally posted by cmschool
cjdj...do you need to create the backups folder? or will the cronjob do that automatically?

Yes, you need to create it yourself. It will automatically create a new file, but the directory must exist.


Also, how would I go about copying the backup off? Just as an extra precaution...

FTP. Just download the file using FTP. If you are putting the backups under a htdocs folder, then you can use your browser to get it, but I wouldnt recommend doing that.

[cjdj]

Originally posted by macgroup
I believe both on lunar

I dont see anything wrong with it quite frankly. I suggest that you create a script that will execute a shell command, and put

/usr/local/bin/mysqldump --user=x --password=xx databasenamehere >/www/u/ui/backups/backup0.sql 2>/www/u/ui/logs/backup0.err

in there and have it tell you what is wrong...
If that executes fine, then at least the command is ok. If not, then you might have to troubleshoot the crontask some more.

To this effect I would remove the lines from the crontab and put in a dummy one that will just write to a file. That way you can see if the cron is doing its thing.

[cmschool]

I was wondering...I went and put in the username info (c/cmschool) instead of u/ui on the lines. Are you supposed to leave it as u/ui? Or do you change that. I waited for two nights and since it didn't work either of them, I am resorting to calling for backup one more time. :) Thanks.

[the_radix]

yes you must use your user directory..

ie.. mine is /t/the_radix

you obviously use /c/cmschool


;)

[Cessna177]

Originally posted by devinemke
0 0 * * * /usr/local/bin/mysqldump --user=xxx --password=xxx databasename >/www/u/username/backup.sql 2>/www/u/username/backup.err

Paste the above code into a plain ASCII text file. Change the "xxx" to your username and password and change the "databasename" to your database name. Change the paths to your own root directory. Save as "crontab" and upload to your /etc/ directory. This will backup the schema of your database every midnight (PST) to the file "backup.sql". Errors will be spooled out to "backup.err".

does "crontab" need an extension?

[macgroup]

No. It's just crontab. Don't create it is Wordpad. Wordpad sometimes adds extra characters. Do it in Notepad. Before you FTP it, make sure Notepad didn't add .txt at the end. If it did, delete the .txt extension.

[cmschool]

0 0 0 * * /usr/local/bin/mysqldump --user=u--password=pw cmsdatabase >/www/c/cmschool/backups/backup0.sql 2>/www/c/cmschool/logs/backup0.err
0 0 1 * * /usr/local/bin/mysqldump --user=u --password=pw cmsdatabase >/www/c/cmschool/backups/backup1.sql 2>/www/c/cmschool/logs/backup1.err
0 0 2 * * /usr/local/bin/mysqldump --user=u --password=pw cmsdatabase >/www/c/cmschool/backups/backup2.sql 2>/www/c/cmschool/logs/backup2.err
0 0 3 * * /usr/local/bin/mysqldump --user=u --password=pw cmsdatabase >/www/c/cmschool/backups/backup3.sql 2>/www/c/cmschool/logs/backup3.err
0 0 4 * * /usr/local/bin/mysqldump --user=u --password=pw cmsdatabase >/www/c/cmschool/backups/backup4.sql 2>/www/c/cmschool/logs/backup4.err
0 0 5 * * /usr/local/bin/mysqldump --user=u --password=pw cmsdatabase >/www/c/cmschool/backups/backup5.sql 2>/www/c/cmschool/logs/backup5.err
0 0 6 * * /usr/local/bin/mysqldump --user=u --password=pw cmsdatabase >/www/c/cmschool/backups/backup6.sql 2>/www/c/cmschool/logs/backup6.err

Here is my code for my crontab. I created a backups folder and put it in the root directory. All the lines are on their individual row. If anywas can see what I am doing wrong, please let me know. I sub'd u for username and pw for the password.

[aza]

Can anyone provide here working crontab file with MySQL backup function? I've also tried to do like you folks here say and like written in help.powweb.com (main difference is there used two symbols ">" before the pathes) but neither ones work! Maybe you also need to put some special restrictions to folder where backup file should be created? But there no words were about this anywhere ....

[macgroup]

I put in a help ticket but am still waiting for a response. If I ever get this taken care of, I'll post the actual crontab file here.

[aza]

I would appreciate it. Thanks in advance!

[the_radix]

what is the format of entries for cron files??

I have just assumed from a few posts here that it is:
mins hours days weeks months cmd

[aza]

Here is my etc/crontab file (it's supposed to backup my DB once per hour):

0 * * * * /usr/local/bin/mysqldump --user=azaXX --password=XXXX dkmmerchandisecom >>/www/a/azaXX/logs/backup.sql 2>>/www/a/azaXX/logs/backup.err

(everything on *one*line). I've also tried writing cron this way:

0 * * * * /usr/local/bin/mysqldump --user=azaXX --password=XXXX dkmmerchandisecom >/www/a/azaXX/logs/backup.sql 2>/www/a/azaXX/logs/backup.err

But it didn't work either! :(

[HalfaBee]

I tried backing up my DB today using cron and it worked fine.

Its only small 200K of sql.
I used the same cronjob above using the correct paths :)

I tried it using phpshell.php before updating the crontab to test if would work.

HalfaBee

[LLaffer]

You are wanting to use this in the start of your crontab lines:

0 0 * * 0
0 0 * * 1
0 0 * * 2
.
.
0 0 * * 6

What you told it to do was at midnight at the 1st, 2nd, 3rd, 4th, 5th, and 6th of each month run the script.

a b c d e

a = minute of the hour specified to run
b = hour to run
c = date of specified month to run
d = month to run
e = day of week to run

[Paul E]

Originally posted by devinemke
another way is to add a separate line to your crontab that backs up the databse to a different file each night of the week. that way you would have 7 backups going back each week. something like this:

0 0 0 * * /usr/local/bin/mysqldump --user=xxx --password=xxx databasename >/www/u/username/backup0.sql 2>/www/u/username/backup0.err

0 0 1 * * /usr/local/bin/mysqldump --user=xxx --password=xxx databasename >/www/u/username/backup1.sql 2>/www/u/username/backup1.err

...and so on for each week day. (please note that each command should be on a separate line of your contab file.)

devinemke, you are great, man!! I had zero knowledge about cron before yesterday, but after reading your posts in this thread I was able to successfully use 'crontab' to backup my SLQ database on the first try!! WOOHOO!!

I liked the idea of the separate nighly backups, so I am using 6 lines as you described above.

I just wanted to say....

THANK YOU

... from a new cron user. :)

[HalfaBee]

The post before the last one was a little incorrect from LLaffer

0 0 * * 0 does the command on the first day of the week not the day of the month.

If you want to do the same command every night just put

0 0 * * *


Keep on croning
HalfaBee

[firebolt]

Anyone try executing a PHP script w/a cron task? How would one do something like that?

I've tried:
0 * * * * * /www/f/firebolt/htdocs/crontest.php >/www/f/firebolt/logs/cron.log 2>/www/f/firebolt/logs/cron.err

which didn't work, but I think that may be because I put one too many * in there at the end. I'll try it the other way and let you all know if it works.

[HalfaBee]

You will have to add as the very first line

#!/usr/local/bin/php

and CHMOD the file to 755.

It will work then.
Also make sure there is a newline or two after the command.

HalfaBee

[firebolt]

Got the database backup to work tonight... woohoo:
0 0 * * * /usr/local/bin/mysqldump --user=user --password=password dbasename >/www/f/firebolt/backup.sql 2>>/www/f/firebolt/logs/dbbackup.err

Originally posted by HalfaBee
You will have to add as the very first line

#!/usr/local/bin/php

and CHMOD the file to 755.

It will work then.
Also make sure there is a newline or two after the command.

HalfaBee

Had it w/o a few newlines at the end and it made my log file named cron.err[#funkychar] which I now can't delete through FTP. Oh well. Adding newlines should fix it hopefully.

Thanks for your help!