SQL auto-backup addon?

Nuke Soldier Joined: Jan 24, 2004 Posts: 17

Is there some sort of addon for phpnuke that will automatically backup your site's SQL database every day or so?

If not, could someone tell me how I could go about making one, it should backup even the tables that were customly created for other modules/addons. It would be useful if it could send the generated SQL file to a remote FTP server or something, just in case the site gets hacked.

thanks Very Happy

Lieutenant Joined: Dec 30, 2003 Posts: 283

You can write a PHP program, which does a "system" call to mysql, and does a full back up like this:
http://www.mysql.com/doc/en/mysqldump.html

You could even "pipe" the output file to gzip to make it compressed.

Afterward, it ftp's the file, like this:
http://ca2.php.net/ftp

Then you put the php program into crontab.

If you have WHM, then it takes care of the htp, and you don't even need php wrapping. Just execute the mysql save, and then send it to php using WHM.

Lieutenant Joined: Dec 30, 2003 Posts: 283

Actually after further research, you can get mysqldump to directly write the backup file to another host. I have never used this myself, but you can try and let me know if it works.

So all you need is one mysqldump command saved in crontab, and you should be good to go. The only thing is that the backup file will be overwritten. There are other tricks to make the file name unique (e.g. timestamp in name, etc.) but that gets a bit more complex.. but if you use a php script to do a system call to mysqldump, you can easily accomplish stuff like that as well.

steve

Nuke Soldier Joined: Jan 24, 2004 Posts: 17

awesome. I'll try to figure this out.

Nuke Soldier Joined: Jan 24, 2004 Posts: 17

Thanks a lot!

I searched for premade mysqldump scripts, i found one that backs up the database (dumpv1-0.zip, got it here). However, it saves it to the same server.

How can I make this script execute itself on a daily basis? I'm a semi-noob and dont know about cron jobs or crontab.

Now I gotta see how to have it automatically FTP over.

Lieutenant Joined: Dec 30, 2003 Posts: 283

to execute periodically, read up on crontab. You will do a "crontab -e" to edit the crontab... plenty of docs on the web.

You should be able to do mysql dump using "-h" option to another host... try this first before attempting ftp.

Nuke Soldier Joined: Jan 24, 2004 Posts: 17

Ok, i wasn't able to get the -h part working. I looked around and it seems like it requires the remote server to have a database of its own. i just wanted to transfer the files over, so I'll try using FTP unless someone can clear up a misconception or knows a better way. I think i can get the cron job working if i just read a little more. Thanks a lot! Smile

this is what i made for backing it up:

Code:

<?php
/*
MySQL Database Backup Script
by zoid
1. Place this file in a world-writeable (CHMOD 777) directory.
2. Fill out the marked config variables (from $format to $database) below.
3. Run the file (go to www.your_site.com/your_dir/autosqlbackup.php) and
follow the errors. You may re-run the script to see if any of the errors were fixed.
Do whatever it may tell you to do.

If you setup the script correctly from the start, it should only give you errors the first time you run it.
*/
echo "<html><head><title>MySQL Database Backup Script</title><META NAME=\"generator\" CONTENT=\"MySQL Database Backup Script by zoid\"></head><body>";

// ***CONFIGURATION - EDIT THE BELOW LINES***
$format = "sql"; // The format to save your backups with, "sql" or "gzip"; gzip has smaller filesize & takes up less space
$path = "backup/"; // Directory to save backups to
$prefix = "backup_"; // Prefix for all backup files generated
$extsql = ".sql"; // Extension used when saving as an uncompressed sql file
$extgz = ".gz"; // Extension used when saving as a gzip compressed file
$username = ""; // Username for the SQL Database
$password = ""; // Password for the SQL Database
$database = ""; // SQL Database Name
// ***CONFIGURATION - EDIT THE ABOVE LINES***

if ( $username == "" || $database == "" ) {
echo "You did not completely modify the configuration for this script. Re-edit the configuration section of this file. It was found that you left the \$username and/or \$database values empty. ";
};

if ( $format == "gzip" ) {
$ext = $extgz;
$database = "$database | gzip" ;
};
if ( $format == "sql" ) {
$ext = $extsql;
};

$timestamp = date(YmdHi); // date(YmdHis) by default, i removed the "s" because i'll only use it once a day
$filename = "$path$prefix$timestamp$ext";
if (file_exists($path)) {
if (!is_writable($path)) {
if (!chmod($path, 0777)) {
echo "Error: Directory $path is not writeable. Please CHMOD the directory to 777. ";
exit;
} };
} else {
echo "Error: Directory $path does not exist. -- Attempting to create directory. ";
if (!mkdir($path, 0777)) {
echo "Error: Directory $path failed to be created. ";
exit;
} else {
echo "Directory $path was created. Creating .htaccess and index.html files to help prevent directory listing. ";
// Create index.html and .htaccess files
$indexhtml = "index.html";
$file = "$path$indexhtml";
if (!$file_handle = fopen($file,"a")) { echo "Error: Cannot open file $file. "; };
if (!fwrite($file_handle, "<html><head><title></title></head><body></body></html>")) { echo "Error: Cannot write to file $file. "; };
echo "$file was successfully created. ";
fclose($file_handle);
$file = "$path.htaccess";
$data = "Options -Indexes\n";
$data .= "IndexIgnore *\n";
$data .= "DirectoryIndex index.html\n";
$data .= "ErrorDocument 400 http://www.google.com/search?q=error\n";
$data .= "ErrorDocument 401 http://www.google.com/search?q=error\n";
$data .= "ErrorDocument 403 http://www.google.com/search?q=error\n";
$data .= "ErrorDocument 404 http://www.google.com/search?q=error\n";
$data .= "ErrorDocument 500 http://www.google.com/search?q=error\n";
$data .= "ErrorDocument 501 http://www.google.com/search?q=error\n";
$data .= "ErrorDocument 502 http://www.google.com/search?q=error\n";
$data .= "ErrorDocument 503 http://www.google.com/search?q=error\n";
if (!$file_handle = fopen($file,"a")) { echo "Error: Cannot open file $file. "; };
if (!fwrite($file_handle, $data)) { echo "Error: Cannot write to file $file. "; };
echo "$file was successfully created. ";
fclose($file_handle);
// index.html and .htaccess files created
};
}

$command = "mysqldump --user $username --password=$password $database > $filename";
if (!system($command)) {
echo "Database Backup was created! ";
echo "<table border=\"1\" cellpadding=\"5\" cellspacing=\"2\"><tr><td>Path/Filename</td><td>Filesize</td></tr>";
$filesizeb = round(filesize($filename),2);
$filesizekb = round(filesize($filename) / 1024,2);
$filesizemb = round(filesize($filename) / 1024 / 1024,2);
echo "<tr><td><a href=\"$filename\">$filename</a></td><td>$filesizeb B $filesizekb KB $filesizemb MB</td></tr>";
echo "</table>";
};
if ( $filesizeb <= 250 ) {
echo "The File Generated is *NOT* a valid backup! Error: Database backup was less than 150 bytes! It is likely that you did NOT properly modify the configuration for this script! ";
};

echo "</body></html>";
?>

Nuke Soldier Joined: Jan 24, 2004 Posts: 17

I made the final version of the script,

you can download it here:
The original version
or
The Original plus a reduced FTP version that uses less resources when you run it.

I can't do cron jobs after all, since I'm not paying for my hosting. :/
oh well... there are (really ghetto) other ways to do the job. Wink

Thanks a lot for your help! Very Happy

Private Joined: Jan 14, 2004 Posts: 38

this is also a good script for ur database

http://sourceforge.net/projects/automysqlbackup

and this for ur files

http://www.funet.fi/pub/Linux/util/backup/

they work fine for me and the mail option is also verry handy Very Happy

Nuke Soldier Joined: Jan 24, 2004 Posts: 17

sweet, a script that'll back up your files... won't that take up a lot of space?

thanks! oh yeah and i got the cron jobs working Smile