Posted by Paul Bramscher on 01/17/07 17:39

kenoli wrote:
> What is the best way to backup a MySQL database aside from what the seb
> server does on its own?
>
> Is there an effective way to write a php script that does this
> periodically, maybe saves the database (in an SQL file??) somewhere
> either on the web server or for downloading?
>
> --Kenoli

Backing up a database is arguably a command-line or OS-level function.
It's relatively trivial to write a bash or Perl script (under 10 lines
or so, and a good learning exercise) that does this:

1) Determine the current date/time.
2) Execute a "mysqldump" and aim its contents into a file called
something like {dbname}_timestamp.sql
3) tar/gzip it.

Then you can add this to your crontab, or just run it manually on an
occasional basis.

I've done this, and more (including php.ini, my.cnf, all http confs, all
log files, all web servable directories, etc.) to basically backup an
entire LAMP environment for relatively easy restore -- with a single
command.

The basics are very easy, and I'd strongly suggest trying it out
yourself -- you'll quickly find ways to expand it (and easily). Mine is
under 50 lines of code or so.

[Back to original message]