How to backup mysql database?

[anita_86]

Hi again!!I wanted to know that is it possible to backup the mysql database just by running query?I am trying to find out the code so that if a user clicks backup button, the whole database will be stored somewhere.

[chris.upjohn]

These are some i found

http://www.daniweb.com/forums/thread277862.html
http://codecanyon.net/item/easy-mysq...-restore/58387

But this is the best one i have found that's nice and simple
http://css-tricks.com/snippets/php/mysql-backup-class/

[RishikeshJha]

use mysqldump

[anita_86]

Can you give me some example?I found following on net.It creates tt.sql file but I dont know how to modify this query and how to define file path.

<?php
$command="mysqldump -uroot --password='' lab >tt.sql";
$test=system($command);
?>

[RishikeshJha]

Can you give me some example?I found following on net.It creates tt.sql file but I dont know how to modify this query and how to define file path.

change root to the username
Change password accordingly

Change lab to your database name

tt.sql is the file name in which the query will be stored after dump

<?php
$command="mysqldump -uroot --password='' lab >tt.sql";
$test=system($command);
?>

[anita_86]

yes i am testing it on localhost and its username and passwords are actually "root" and "" and database name is lab.

[RishikeshJha]

yes i am testing it on localhost and its username and passwords are actually "root" and "" and database name is lab.

are you getting any error?

Also you have to specify the location where the file will be saved..

[anita_86]

no error is displayed.just a blank page.but no output either.tt.sql is created but blank

[RishikeshJha]

it means the script is running..

please check if the sql file is created or not..

[anita_86]

here is another script.but it is not giving output as well.
mysql_backup.class.php

<?php

/*
MySQL database backup class, version 1.0.0
Written by Vagharshak Tozalakyan <vagh@armdex.com>
Released under GNU Public license
*/


define('MSB_VERSION', '1.0.0');

define('MSB_NL', "\r\n");

define('MSB_STRING', 0);
define('MSB_DOWNLOAD', 1);
define('MSB_SAVE', 2);

class MySQL_Backup
{

var $server = 'localhost';
var $port = 3306;
var $username = 'root';
var $password = '';
var $database = 'lab';
var $link_id = -1;
var $connected = false;
var $tables = array();
var $drop_tables = true;
var $struct_only = false;
var $comments = true;
var $backup_dir = '';
var $fname_format = 'd_m_y__H_i_s';
var $error = '';


function Execute($task = MSB_STRING, $fname = '', $compress = false)
{
if (!($sql = $this->_Retrieve()))
{
return false;
}
if ($task == MSB_SAVE)
{
if (empty($fname))
{
$fname = $this->backup_dir;
$fname .= date($this->fname_format);
$fname .= ($compress ? '.sql.gz' : '.sql');
}
return $this->_SaveToFile($fname, $sql, $compress);
}
elseif ($task == MSB_DOWNLOAD)
{
if (empty($fname))
{
$fname = date($this->fname_format);
$fname .= ($compress ? '.sql.gz' : '.sql');
}
return $this->_DownloadFile($fname, $sql, $compress);
}
else
{
return $sql;
}
}


function _Connect()
{
$value = false;
if (!$this->connected)
{
$host = $this->server . ':' . $this->port;
$this->link_id = mysql_connect($host, $this->username, $this->password);
}
if ($this->link_id)
{
if (empty($this->database))
{
$value = true;
}
elseif ($this->link_id !== -1)
{
$value = mysql_select_db($this->database, $this->link_id);
}
else
{
$value = mysql_select_db($this->database);
}
}
if (!$value)
{
$this->error = mysql_error();
}
return $value;
}


function _Query($sql)
{
if ($this->link_id !== -1)
{
$result = mysql_query($sql, $this->link_id);
}
else
{
$result = mysql_query($sql);
}
if (!$result)
{
$this->error = mysql_error();
}
return $result;
}


function _GetTables()
{
$value = array();
if (!($result = $this->_Query('SHOW TABLES')))
{
return false;
}
while ($row = mysql_fetch_row($result))
{
if (empty($this->tables) || in_array($row[0], $this->tables))
{
$value[] = $row[0];
}
}
if (!sizeof($value))
{
$this->error = 'No tables found in database.';
return false;
}
return $value;
}


function _DumpTable($table)
{
$value = '';
$this->_Query('LOCK TABLES ' . $table . ' WRITE');
if ($this->comments)
{
$value .= '#' . MSB_NL;
$value .= '# Table structure for table `' . $table . '`' . MSB_NL;
$value .= '#' . MSB_NL . MSB_NL;
}
if ($this->drop_tables)
{
$value .= 'DROP TABLE IF EXISTS `' . $table . '`;' . MSB_NL;
}
if (!($result = $this->_Query('SHOW CREATE TABLE ' . $table)))
{
return false;
}
$row = mysql_fetch_assoc($result);
$value .= str_replace("\n", MSB_NL, $row['Create Table']) . ';';
$value .= MSB_NL . MSB_NL;
if (!$this->struct_only)
{
if ($this->comments)
{
$value .= '#' . MSB_NL;
$value .= '# Dumping data for table `' . $table . '`' . MSB_NL;
$value .= '#' . MSB_NL . MSB_NL;
}
$value .= $this->_GetInserts($table);
}
$value .= MSB_NL . MSB_NL;
$this->_Query('UNLOCK TABLES');
return $value;
}


function _GetInserts($table)
{
$value = '';
if (!($result = $this->_Query('SELECT * FROM ' . $table)))
{
return false;
}
while ($row = mysql_fetch_row($result))
{
$values = '';
foreach ($row as $data)
{
$values .= '\'' . addslashes($data) . '\', ';
}
$values = substr($values, 0, -2);
$value .= 'INSERT INTO ' . $table . ' VALUES (' . $values . ');' . MSB_NL;
}
return $value;
}


function _Retrieve()
{
$value = '';
if (!$this->_Connect())
{
return false;
}
if ($this->comments)
{
$value .= '#' . MSB_NL;
$value .= '# MySQL database dump' . MSB_NL;
$value .= '# Created by MySQL_Backup class, ver. ' . MSB_VERSION . MSB_NL;
$value .= '#' . MSB_NL;
$value .= '# Host: ' . $this->server . MSB_NL;
$value .= '# Generated: ' . date('M j, Y') . ' at ' . date('H:i') . MSB_NL;
$value .= '# MySQL version: ' . mysql_get_server_info() . MSB_NL;
$value .= '# PHP version: ' . phpversion() . MSB_NL;
if (!empty($this->database))
{
$value .= '#' . MSB_NL;
$value .= '# Database: `' . $this->database . '`' . MSB_NL;
}
$value .= '#' . MSB_NL . MSB_NL . MSB_NL;
}
if (!($tables = $this->_GetTables()))
{
return false;
}
foreach ($tables as $table)
{
if (!($table_dump = $this->_DumpTable($table)))
{
$this->error = mysql_error();
return false;
}
$value .= $table_dump;
}
return $value;
}


function _SaveToFile($fname, $sql, $compress)
{
if ($compress)
{
if (!($zf = gzopen($fname, 'w9')))
{
$this->error = 'Can\'t create the output file.';
return false;
}
gzwrite($zf, $sql);
gzclose($zf);
}
else
{
if (!($f = fopen($fname, 'w')))
{
$this->error = 'Can\'t create the output file.';
return false;
}
fwrite($f, $sql);
fclose($f);
}
return true;
}


function _DownloadFile($fname, $sql, $compress)
{
header('Content-disposition: filename=' . $fname);
header('Content-type: application/octetstream');
header('Pragma: no-cache');
header('Expires: 0');
echo ($compress ? gzencode($sql) : $sql);
return true;
}

}

?>

execute_db_backup.php

<?
/*
|--------------------------------------------------|
| Example MySQL Backup File |
| |
| Written by: Justin Keller <kobenews@cox.net> |
| Released under GNU Public license. |
| |
| Only use with MySQL database backup class, |
| version 1.0.0 written by Vagharshak Tozalakyan |
| <vagh@armdex.com>. |
|--------------------------------------------------|
*/

require_once 'mysql_backup.class.php';
$backup_obj = new MySQL_Backup();

//----------------------- EDIT - REQUIRED SETUP VARIABLES -----------------------

$backup_obj->server = 'localhost';
$backup_obj->port = 3306;
$backup_obj->username = 'root';
$backup_obj->password = '';
$backup_obj->database = 'lab';

//Tables you wish to backup. All tables in the database will be backed up if this array is null.
$backup_obj->tables = array();

//------------------------ END - REQUIRED SETUP VARIABLES -----------------------

//-------------------- OPTIONAL PREFERENCE VARIABLES ---------------------

//Add DROP TABLE IF EXISTS queries before CREATE TABLE in backup file.
$backup_obj->drop_tables = true;

//Only structure of the tables will be backed up if true.
$backup_obj->struct_only = false;

//Include comments in backup file if true.
$backup_obj->comments = true;

//Directory on the server where the backup file will be placed. Used only if task parameter equals MSB_SAVE.
$backup_obj->backup_dir = '/';

//Default file name format.
$backup_obj->fname_format = 'm_d_Y';

//--------------------- END - OPTIONAL PREFERENCE VARIABLES ---------------------

//---------------------- EDIT - REQUIRED EXECUTE VARIABLES ----------------------

/*
Task:
MSB_STRING - Return SQL commands as a single output string.
MSB_SAVE - Create the backup file on the server.
MSB_DOWNLOAD - Download backup file to the user's computer.

*/
$task = MSB_DOWNLOAD;

//Optional name of backup file if using 'MSB_SAVE' or 'MSB_DOWNLOAD'. If nothing is passed, the default file name format will be used.
$filename = '';

//Use GZip compression if using 'MSB_SAVE' or 'MSB_DOWNLOAD'?
$use_gzip = true;

//--------------------- END - REQUIRED EXECUTE VARIABLES ----------------------

//-------------------- NO NEED TO ANYTHING BELOW THIS LINE --------------------

if (!$backup_obj->Execute($task, $filename, $use_gzip))
{
$output = $backup_obj->error;
}
else
{
$output = 'Operation Completed Successfully At: <b>' . date('g:i:s A') . '</b><i> ( Local Server Time )</i>';
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>MySQL Backup</title>
</head>
<body>
<?
echo $output;
?>
</body>
</html>

the error is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_details' at line 1

can anyone find '_details' here?atleast my eyes are burning.

[jhone007]

If you want to perform the mysql dump manually, without the assistance of your hosts control panel, then run SSH to your web server and do the following
* mysqldump --tab=/path/to/some/dir --opt db_name

[RishikeshJha]

shell_exec ("mysqldump cakephp > D:\\db.sql --user=root --password=");

[anita_86]

yes it is creating db.sql.but same problem is it is 0kb and blank

[RishikeshJha]

yes it is creating db.sql.but same problem is it is 0kb and blank

give the exact path for mysqldump

like c:/wamp/bin/mysql/

[anita_86]

do u mean:

<?
shell_exec ("mysqldump lab > C:\xampp\mysql\bin --user=root --password=");
?>

where will the backup files saved?and what will be its name??

[RishikeshJha]

do u mean:

where will the backup files saved?and what will be its name??

no it is for mysqldump path..

[RishikeshJha]

shell_exec ("C:\xampp\mysql\bin\mysqldump lab >  D:\\db.sql --user=root --password=");

[anita_86]

now wt i am supposed to do if i want to backup database named "lab" with username "root" and no password and i have copied mysqldump.exe in C:\xampp\htdocs\backup??
i wish i were as smart as u so i needn't ask such dumb questions.
but what to do?i have no idea how to do this thing.

[anita_86]

shell_exec ("C:\xampp\mysql\bin\mysqldump lab > D:\\db.sql --user=root --password=");

is not creating any .sql file in D drive.
am i going wrong somewhere?

[RishikeshJha]

why you have copied mysqldump.exe? Any specific reason..

shell_exec ("C:\xampp\mysql\bin\mysqldump lab > D:\\db.sql --user=root --password=");

you have to simply give the path of the mysqldump.exe , database name, location where the file will be created , user name and password.

[RishikeshJha]

now wt i am supposed to do if i want to backup database named "lab" with username "root" and no password and i have copied mysqldump.exe in C:\xampp\htdocs\backup??
i wish i were as smart as u so i needn't ask such dumb questions.
but what to do?i have no idea how to do this thing.

you can use this code

shell_exec ("C:\xampp\htdocs\backup\mysqldump lab > D:\\db.sql --user=root --password=");

[RishikeshJha]

is not creating any .sql file in D drive.
am i going wrong somewhere?

is the path is correct?

[anita_86]

God I rechecked everything at least 10 times.but still its not working.not even a 0kb blank file!!