MySQL backup

[0tter]

Thanks so far you've all been marvellous.

Right I'm currently getting on with the security checking of my site, but how about backing up the database. Currently I'm using a hosted service, so all my data and code is on the internet. I have only one database online with multiple tables, so I need to put them somewhere (on an daily basis preferably, with new backups each day). The code I can handle it's just the databases that are tricky.

Is there any tool out there that'll do this quick and smart and would I need a local MySQL database running in order to put them on a local machine.

Thanks in anticipation

Bill

[bodzan]

...phpMyAdmin's export database feature to backup my databases, but I do it manually...

[0tter]

bodzan wrote:

...phpMyAdmin's export database feature to backup my databases, but I do it manually...

Automated would be good though.

Does that backup to a file or do you have to have a separate MySQl structure to load it into and does it get really tricky if you have to restore it?

Thanks - off to bed now, nearly 1AM where I am now, so don't be surprised if you don't see a reply till tomorrow.

Thanks again.

Bill 🙂

[bodzan]

It can backup to a SQL that you can copy/paste to a Note Pad and then when you have to restore it just paste it to a SQL window of the same program or you can export it to a file and then restore your database from it as well...
Either way restoring your database is pretty easy because you get SQL statements needed to create a replica of a database you have on your system i.e. you have create table and insert statements to populate tables you want to back up or of the entire database...

Once I managed to restore a table with BLOB fields populated with pictures from this txt file... 😃

Not sure if it can be automated though...

[Drakla]

I dump to gzipped files on a cron using this which rolls over the old backups after 5 days

/*	backs up the database on a cron, with flush of older files
	HOME exists in $_SERVER in cron jobs and holds /home/yourhomedir

SCRIPT_FILENAME doesn't exist during cron, HOME is the closest you'll get
But __FILE__ is available - so is $_SERVER[argv][0] as the script name is the first arg to php
in the crontab

Run get_defined_vars() in a cron to see a full list
*/
$username = 'blah';
$password = 'foo';
$hostname = 'scaramanger';
$dbname	  = 'mylittledatabase';

$suffix = gmdate('y-m-d H:i');

// get rid of files over X days old
$expiry = time() - (5*60*60*24 - 30);
flush_cache($expiry);

// $directory = dirname(__FILE__);	// can use this if you like
$directory = '/home/mypath/public_html/mybackups';

$fileName = $directory.'/dump '.$suffix.'.gz';

echo "Dumpfile: $fileName<br>\n";
	// NO space after -p and the password
$command = "mysqldump -eQ -p$password -u $username -h $hostname $dbname";

//do -Q in dump to put `` round tables with reserved word names

$results = `$command`;

$oldVal = ignore_user_abort(true);
	$zp = gzopen($fileName, "w9");	//open file for writing with maximum compression 
	if(!$zp) die("TERMINATING: could not open file!"); 
	gzwrite($zp, $results);			// write string to file 
	gzclose($zp);					// close file 
ignore_user_abort($oldVal);

echo "Data dump complete<br>\n";



/**
*	Get rid of old backups
*/

function flush_cache($carousel)
{
	$directory = dirname(__FILE__);

echo "Flush dir: ".$directory.'<br>';

$fileMask = $directory . '/dump*';

$files = @glob($fileMask);

if (!$files) return;

foreach( $files as $file)
{
	if (filemtime($file) < $carousel)
	{
		echo "<br>\nUnlinking $file";
		unlink($file);
	}
}	
}

It assumes you've got write permissions on the directory and can run commands with backticks or whatever shell command you like. Read up on mysqldump for more info. You might want compatability mode if you're dumping for import into an older version of mysql.

[0tter]

That looks just the ticket, I'll give it a go over the weekend. You know that might just be the solution to all I need, though I'll still have to find out how to get it off the remote server onto my local computer to add a belt to the braces.

Thanks very much for that, really very much appreciated.

My bacon looks saved.

Bill

[0tter]

bodzan wrote:

It can backup to a SQL that you can copy/paste to a Note Pad and then when you have to restore it just paste it to a SQL window of the same program or you can export it to a file and then restore your database from it as well...
Either way restoring your database is pretty easy because you get SQL statements needed to create a replica of a database you have on your system i.e. you have create table and insert statements to populate tables you want to back up or of the entire database...

Once I managed to restore a table with BLOB fields populated with pictures from this txt file... 😃

Not sure if it can be automated though...

Thanks for that too, I'm going to be going through these solutions at length over this coming weekend. Looks as though it's going to be a mix and match affair and I'll cherry pick from what I have here.

Again very very much appreciated.

Thanks for the input, I'll let you all know what I get up to.

Thanks

Bill

[0tter]

Decided to go for the easy option and just use PHPAdmin export to a file, that gets it local as the file are on a hosted box, the cron job looked fantastic but I just couldn't get it to run on the hosted service.

Set some strftime options to change the file name every day, but apart from that isn't it a a nice easy routine.

Could you check though that I have the right settings, the only options I've enabled (took the defaults) are:

Structure:

Add AUTO_INCREMENT value
Enlclose table and filed names with backquotes

Data:

Use hexadecimal for binary fields

Export Type:

INSERT

Found as mentioned it is really simple to restore, just cut and past into a SQl query run and it's all done. Just best to remember to rename any tables first, just in case you might need them in the future.

That's about it apart from is there a "logout" in PHPAdmin beggared if I can find one.

Again thanks ever so much for all your help and support, I really don't know where I would have got without your help.

Thanks again

Bill