Automatic dump

[dreamwest]

Is there a query to create an sql dump of the database as a backup.

 

My web host dosnt have this feature.

 

I need to back it up once a week automatically

[jackpf]

Yeah, phpMyAdmin.

[Mchl]

Yeah, phpMyAdmin.

 

I'm interested how you can schedule backups in phpMyAdmin... did they add something in latest version?

 

dreamwest: this is job for cron + mysqldump

[jackpf]

Oh, didn't read the last sentence

My apologies. I was referencing the database dump feature in phpmyadmin.

Escuse my ignorance.

[fenway]

Version 6, I think....

[dreamwest]

dreamwest: this is job for cron + mysqldump

 

I dont know how to set this up. Also i dont have shell access if this is needed

 

I searched for a couple of hours and found a sql query (INTO OUTFILE) that might do it , but i think its missing something cause it wont work:

 

//Usually localhost
$host = "host";

//Database Username
$username = "user";

//Database Password
$dbpass = "pass";

//Database Name
$dbname = "name";

//Connect to DB.
mysql_connect("$host", "$username", "$dbpass") or die(mysql_error());
mysql_select_db("$dbname") or die(mysql_error());

$tableName  = 'Everton_Park';
$backupFile = 'Everton_Park.sql';
$query      = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);

echo "All Done!!";

 

Ive set the permissions of this file to 777 and the directory to 777, and placed it here:

 

http://site.com/backups/backup.php

[Gighalen]

Try googling "Webmin"

[dreamwest]

I finally work a way to do this.

 

SELECT all rows and tables and write that data to a file.

 

<?php

$mysql_host='localhost';
$mysql_database='name'; 
$mysql_username='user'; 
$mysql_password='pass';

$storing_dir = ""; 

$file_name = $mysql_database."_".date('YmdHis').".sql";

$link = mysql_connect($mysql_host, $mysql_username, $mysql_password);
if (!$link)
{
   fwrite($fh, 'Could not connect: ' . mysql_error());
   exit();
}

$db_selected = mysql_select_db($mysql_database, $link);
if (!$db_selected)
{
fwrite($fh, 'Can\'t use $mysql_database : ' . mysql_error());
exit();
}

$myFile = $storing_dir . $file_name;
$fh = fopen($myFile, 'w') or die("can't open file");
_mysqldump($mysql_database);
fclose($fh);

function _mysqldump($mysql_database)
{
global $fh;
_mysqldump_schema_structure($mysql_database);
$sql="show tables;";
$result= mysql_query($sql);
if( $result)
{

	while( $row= mysql_fetch_row($result))
	{
		_mysqldump_table_structure($row[0]);
		_mysqldump_table_data($row[0]);
	}
}
else
{
	$content =  "/* no tables in $mysql_database */\n";
	fwrite($fh, $content);
}
mysql_free_result($result);
}
function _mysqldump_schema_structure($schema)
{
global $fh;
fwrite($fh, "/* database : `$schema` */\n");
$sql="show create schema `$schema`; ";
$result=mysql_query($sql);
if( $result)
{
	if($row= mysql_fetch_assoc($result))
	{
		fwrite($fh, $row['Create Database'].";\n\n");
	}
}
fwrite($fh, "USE `$schema`; \n\n");
mysql_free_result($result);
}

function _mysqldump_table_structure($table)
{
global $fh;
fwrite($fh, "/* Table structure for table `$table` */\n");
fwrite($fh, "DROP TABLE IF EXISTS `$table`;\n\n");
$sql="show create table `$table`; ";
$result=mysql_query($sql);
if( $result)
{
	if($row= mysql_fetch_assoc($result))
	{
		fwrite($fh, $row['Create Table'].";\n\n");
	}
}
mysql_free_result($result);
}

function _mysqldump_table_data($table)
{
global $fh;
$sql="select * from `$table`;";
$result=mysql_query($sql);
if( $result)
{
	$num_rows= mysql_num_rows($result);
	$num_fields= mysql_num_fields($result);

	if( $num_rows > 0)
	{
		fwrite($fh, "/* dumping data for table `$table` */\n");

		$field_type=array();
		$i=0;
		while( $i < $num_fields)
		{
			$meta= mysql_fetch_field($result, $i);
			array_push($field_type, $meta->type);
			$i++;
		}

		//print_r( $field_type);
		fwrite($fh, "insert into `$table` values\n");
		$index=0;
		while( $row= mysql_fetch_row($result))
		{
			fwrite($fh, "(");
			for( $i=0; $i < $num_fields; $i++)
			{
				if( is_null( $row[$i]))
					fwrite($fh, "null");
				else
				{
					switch( $field_type[$i])
					{
						case 'int':
							fwrite($fh, $row[$i]);
							break;
						case 'string':
						case 'blob' :
						default:
							fwrite($fh, "'".mysql_real_escape_string($row[$i])."'");

					}
				}
				if( $i < $num_fields-1)
					fwrite($fh, ",");
			}
			fwrite($fh, ")");

			if( $index < $num_rows-1)
				fwrite($fh, ",");
			else
				fwrite($fh, ";");
			fwrite($fh, "\n");

			$index++;
		}
	}
}
mysql_free_result($result);
fwrite($fh, "\n");
}
?>