Topic awaiting preservation: Database backups

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.

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

quote:

---

then a cron job that fires that at whatever interval you like

---

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

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:

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).]

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).]

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

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!

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.

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