MySQL backups

Anon

I know how to backup and individual database in MySQL, but is there quick way to back up all of the databases running on a MySQL server?

Anon

Do you have root access to the server, including the mysql data files?

[deleted]

Arg!

You're not going to suggest backing up the files are you?
That's extremely bad practice.

A simple script can see which databases exist on the host (show databases), and then dump them all into files correctly, using mysqldump.

Anon

Sounds good to me. Can you show me what this script would look like? Shell script? Can I run a PHP script as a cron job? I know that PERL is more common, but I am a lot more comfortable with PHP. Don't know much about cron either, new to the server biz.

Any input is appreciated!

superdave

here's the script i use to back up my database...it prompts me for my password, then dumps the tables:

#!/bin/bash
sql_dump=date +%Y%m%d%H%M%S.sql

SET THIS VARIABLE

database="DATABASENAME"
#NO_DATA="--no-data"
NO_DATA=""

REMOVE --no-data TO HAVE THE DUMP INCLUDE DATA,

AND NOT JUST THE STRUCTURE !!

/usr/local/mysql/bin/mysqldump -u dave -p \
--add-locks $NO_DATA \
--add-drop-table -l $database \
> $sql_dump
chmod 400 $sql_dump

Aaron wrote:

Any input is appreciated!

[deleted]

You can make that script in any scripting language, but as we're talking PHP, I'd do it in PHP.

Yes you can run PHP from the crontab, if you install the CGI version of PHP (which can be installed next to the apache module version, no problem)

For help on the crontab, try 'man cron'

As for the script itself:
just connect to the server (using some sort of admin account that can read all databases) and run a query like:

SHOW DATABASES;

that will give you a list of all databases on the system
Then simply loop through the results and run the mysqldump program for each database that is found.