mySQL back-up question

[pdstein]

I'm trying to set-up an automated mySQL back-up that will run nightly.[nbsp][nbsp]I'm familiar with the mysqldump command and have no trouble using it from the command line or calling it as a cron job to back up a single table or a whole database.[nbsp][nbsp]The problem is if I dump the whole database to one file, the file is too big.[nbsp][nbsp]I'd like to dump each table to its own file and then zip them all into one compressed file.

I can get a list of the tables in the database with a little PHP script, but I don't seem to be able to use mysqldump from within PHP.[nbsp][nbsp]And I can run mysqldump as a cron job, but I can't get a list of the existing tables or loop to dump each one seperately.

any ideas???

[SneakyDave]

"urban" posted a Perl script that backed up and zipped MySQL table dumps. I've used it a few times where I need a consistent backup.

I don't know if you want a specific PHP script, but this one isn't too bad to figure out.

Search for "backup" and "urban" and you should be able to find the thread.

edited to add: I realized that you wanted to backup only certain tables, so I don't know if this script would do that.
[This message has been edited by SneakyDave (edited 12-01-00@5:14 pm)]

[pdstein]

Thanks.[nbsp][nbsp]I found the pearl script.[nbsp][nbsp]I will try to decipher and apply it to my situation, but if anyone has a way to do this with PHP, I'd much prefer to do it that way.

[outline]

I was under the impression that PHP wouldn't have permission to do this, b/c anything PHP executes, executes with the permissions of PHP.[nbsp][nbsp]In other words, I think you have to use some shell script (such as PERL) with permissions to execute the mysqldump command and then write it to a file on the server.

I would assume you'd set the CRON job to run this perl script which would do all the dirty work for you.[nbsp][nbsp]Please let me know your final solution, I would like to do something like this to and would of course prefere PHP if it was doable.

[SneakyDave]

Also, you'd only be able to use PHP as the scripting language if it was set up as as CGI process on the server (I think). I think FQ still has both the Apache Mod and CGI versions installed, but I don't know if that's the case with PHP4 servers.

[Justin]

Yes, both the Apache module and the binary are available on all servers. PHP only runs as user 'apache' when it is used as an Apache module -- running it from the command line, it runs under your UID.

Another thing to keep in mind however, is that in a cron, you don't have your PATH environment -- so it is best to specify a full path to any files accessed.

------------------
Justin Nelson
FutureQuest (http://www.FutureQuest.net/index.php) Support

[pdstein]

After looking over the Pearl script written by urban, I'm not much closer to a solution.[nbsp][nbsp]Maybe it's because I don't know Pearl, but there's a lot of stuff in there I just don't understand.[nbsp][nbsp]I'm going to keep working on it, but if anyone has an alternative suggestion, that would be great!

[mike_w]

You can get around using absolute paths in your script (for cron jobs) by setting $PATH in either your script or your crontab file.

perl syntax example -

$ENV{"PATH"} = "/bin:/sbin:/usr/bin";

crontab example -

PATH=/bin:/sbin:/usr/bin

[pdstein]

O.K.[nbsp][nbsp]This is turning out to be more time-consuming than it ought simply because I don't understand Pearl.[nbsp][nbsp]I'm trying to avoid learning the entire language just to write one little script.[nbsp][nbsp]Would someone be able to translate my pseudo-code to Pearl to make this work for me?

Thanks in advance![nbsp][nbsp]I really appreciate it!


$myUser = "-uusername";
$myPass = "-ppassword";
$myHost = "-hhost";
$myDB = "database";
$localDir = "/big/dom/yada/yada/yada/";

// query database for the names of all tables in the DB
$result = getTableNames($myDB);

// For all the tables...
while (tableName = result(tableName)) {

[nbsp][nbsp][nbsp][nbsp][nbsp]// dump each table into a file tableName.sql
[nbsp][nbsp][nbsp][nbsp][nbsp]mysqldump $myUser $myPass $myHost $myDB $tableName > $localDir.$tableName.".sql";
}

// zip all .sql files in the directory into one file
zip myBackUp.zip *.sql

[Terra]

You don't really need perl to do this, as it can be done with a regular Bash script...

This is a little something I whipped up for Deb so that she could view the structure of her tables from the command line...

With a slight bit of modification, you should be able to mold it to what you are trying to accomplish with mysqldump...


#!/bin/sh
#I added the dir path since you will use this in a CRON
m_dir='/usr/local/mysql/bin'
#just a small DBI perlism
DSN='-hmysql.yourdomain.com -uyourusername -pyourpassword'
#use tail to kill the first line which is garbage
for i in $($m_dir/mysql $DSN xyourdatabase -e 'show tables' | tail +2);
do
[nbsp][nbsp][nbsp][nbsp]echo $i
[nbsp][nbsp][nbsp][nbsp]$m_dir/mysqlshow $DSN xyourdatabase $i
[nbsp][nbsp][nbsp][nbsp]echo
done


It's that easy and doesn't require the Perl DBI::DBD libraries... ;)[nbsp][nbsp]

One important note that I cannot stress heavily enough...[nbsp][nbsp]When you are performing the mysqldump - make sure you are compressing it on the fly...
e.g.
mysqldump ... | gzip -9 > my_table.gz

After which you can just TAR the files together, or zip them w/o using zip compression since it's already gzip'd...

*If you don't do it this way, you can consume a tremendous amount of diskspace rather quickly...[nbsp][nbsp]I am lenient on disk space, for the most part, but not when a amicable solution presents itself yet one still dumps to an uncompressed file and subsequently compresses afterwords...[nbsp][nbsp]That is just plain silly...

--
Terra
--Always explore alternative and inventive ways to solve problems--
FutureQuest

[This message has been edited by ccTech (edited 12-11-00@09:55 am)]

[Arthur]

Here's something I whipped up. I'm not really good at Perl, but it does work :)
For each table you get one file named tablename.gz. If you want you could make a tarball of those.

#!/usr/bin/perl

use DBI;

my $database = "xyourdatabase";
my $host = "yourhost";
my $data_source = "DBI:mysql:database=$database:host=$host";
my $username[nbsp][nbsp][nbsp][nbsp]= 'xyourusername';
my $password[nbsp][nbsp][nbsp][nbsp]= 'yourpassword';

my $dbh = DBI->connect($data_source,$username,$password) or die "Can't connect to $data_source: $dbh->errstr\n";

my $tablesquery = "show tables";
my $sth = $dbh->prepare($tablesquery);
if (!defined $sth) {
[nbsp][nbsp]die "Cannot prepare statement: $DBI::errstr\n";
}

$sth->execute;

my @tableinfo;
while ($tableinfo = $sth->fetchrow_arrayref) {
[nbsp][nbsp]system("/usr/local/mysql/bin/mysqldump -h $host $database $tableinfo->[0] -u$username -p$password | /usr/bin/gzip -9 > $tableinfo->[0].gz");
}

$sth->finish;
$dbh->disconnect;

---
Arthur
[This message has been edited by arthur (edited 12-11-00@1:47 pm)]

[pdstein]

Thanks so much Terra & Arthur.[nbsp][nbsp]Terra - I'm not sure how you use/call a bash script.[nbsp][nbsp]Arthur, I was able to get your perl script working.[nbsp][nbsp]Whoohoooh![nbsp][nbsp]