Closed Thread Icon

Topic awaiting preservation: Database backups Pages that link to <a href="https://ozoneasylum.com/backlink?for=13101" title="Pages that link to Topic awaiting preservation: Database backups" rel="nofollow" >Topic awaiting preservation: Database backups\

 
Author Thread
DL-44
Maniac (V) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 03-21-2004 00:26

Something I haven't had to worry about really, and have no idea where to start looking -

how do I set things up to automatically generate a backup of my MySQL database on my server at regularly scheduled intervals?

I do it every couple weeks manually, since it's been a relatively small and slow growing DB, but would like to move up to an automatic process.

Am I looking at just making a PHP script with the appropriate SQL, or chron jobs, or............?

Any tips appreciated.


JKMabry
Maniac (V) Inmate

From: out of a sleepy funk
Insane since: Aug 2000

posted posted 03-21-2004 02:09

heck yeah, php/sql dump with the timestamp for a filename, then a cron job that fires that at whatever interval you like

DL-44
Maniac (V) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 03-21-2004 03:40
quote:
then a cron job that fires that at whatever interval you like



Well, that's the part I'm clueless on really....

JKMabry
Maniac (V) Inmate

From: out of a sleepy funk
Insane since: Aug 2000

posted posted 03-21-2004 04:13

I see your host is running Linux, so if you have shell access you're set. Stolen from my host's knowledgebase:

quote:
crontab will get you fired up

crontab -l will show you your currently set up cron jobs on the server.

crontab -r will delete your current cron jobs.

crontab -e will allow you to add or edit your current cron jobs by using your default text editor to edit your "crontab file".

Your crontab includes all the cron jobs you'd like, with one cron entry per line. A cron entry looks like this:

45 2 * * * /home/user/script.pl

The first number is the minute of the hour for the command to run on.
The second number is the hour of the day for the command to run on.
The third number is the day of the month for the command to run on.
The fourth number is the month of the year for the command to run on.
The fifth number is the day of the week for the command to run on.

Here are some examples to help you learn the syntax for the numbers:

32 * * * * : will be run every hour on the 32nd minute.
12,42 * * * * : will be run twice an hour on the 12th and 42nd minutes.
*/15 */2 * * *: will be run at 0:00, 0:15, 0:30, 0:45, 2:00, 2:15, 2:30, ...
43 18 * * 7: will be run at 6:43pm every Sunday.



this is how I do it with a php script:

code:
23,53 * * * * lynx -dump [url=http://yourdomain.com/mysql_dump_script.php]http://yourdomain.com/mysql_dump_script.php[/url]



calls the linux web browser lynx to open the page, thus running the script




edit: expounding after posting a stumper

[This message has been edited by JKMabry (edited 03-21-2004).]

Tyberius Prime
Paranoid (IV) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 03-21-2004 13:56

note that the php version usually only works if safe mode isn't turned on and you can extend the timeout.(*)

Otherwise, I'd suggest you use "mysqldump --opt %datebasename%" (possible with the appropriate --user= and --password= and --host strings= directly.


Edit:
* - Except if your database is pretty small to begin with.

[This message has been edited by Tyberius Prime (edited 03-21-2004).]

DL-44
Maniac (V) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 03-21-2004 17:00

Thanks guys - that gives me plenty to work with =)



JKMabry
Maniac (V) Inmate

From: out of a sleepy funk
Insane since: Aug 2000

posted posted 03-21-2004 19:46

thanks TP! I recommended the php script based on my success with using cron to fire php scripts that cache news feeds, I've never actually used it to backup a DB. Your suggestion got me to looking into doing it with shell scripting and I think I might be able to dump DBs, name them sequentially, gzip them, mail them to me, whatever and etc, excellent!

Tyberius Prime
Paranoid (IV) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 03-21-2004 20:13

just remember: mysqldump can also dump all of your dbs into a dumb, though you'll have to look up the switch for that yourself.

Pugzly
Paranoid (IV) Inmate

From: 127.0.0.1
Insane since: Apr 2000

posted posted 03-22-2004 12:59

DL - I have a shell script that does everything you need. Contact me offline.

« BackwardsOnwards »

Show Forum Drop Down Menu