Script De Backup

heldersc · February 23, 2004

Olá comunidade

Gostaria de saber se alguém tem ou sabe de algum script que faz backup do seu banco de dados mysql e envia direto pare seu email, numa determinada hora programada.

Agradeço a todos!

Fabyo · February 23, 2004

olha em baixo: no outro post

Fabyo · February 23, 2004

um script completo

index.php

<?
// vou usar a classe phpzip.inc.php
require 'phpzip.inc.php';
// variaveis globais (configure aqui) -------------------------
// variaveis de banco de dados
$db_name = 'nome_banco_dados';
$hostdb = 'localhost';
$userdb = 'usuario';
$passdb = 'senha';
// as tabelas que quero
$tabelas = array ('tabela1','tabela2', 'tabela3');
$tempdir = "/tmp"; // diretorio temporario
$filename = 'sql.'.time().'.txt';
// variaveis do sistema
$incluir_insert = 1; // imprime os INSERT's tambem
// ----------------------------------------------------
// BLOCO PRINCIPAL
// conectar ao banco de dados
$con = mysql_pconnect($hostdb,$userdb,$passdb);
mysql_select_db($db_name);
// imprimir tipo do documento na tela
// imprimir o dump do banco de dados
chdir($tempdir);
$fp = fopen($filename,"w");
for ($x=0; $x<count($tabelas); $x++) {
$saida = getTableDef($db_name, $tabelas[$x], "\n");
fputs($fp,$saida."\n\n");
if ($incluir_insert) {
getTableContentFast($db_name, $tabelas[$x], '', '');
fputs($fp,"\n\n");
}
}
fclose($fp);
// gerar o arquivo zipado
$zipname = ereg_replace("txt$","zip",$filename);
$zip = new PHPZip();
$files[]=$filename;
$zip -> Zip($files, $zipname);
$tamanho = filesize($zipname);
// imprimir arquivo p/ download
header("Content-Type: application/zip");
header("Content-Length: $tamanho");
header("Content-Disposition: attachment; filename=$zipname");
header("Content-Transfer-Encoding: binary");
// abrir e enviar o arquivo
$fp = fopen("$zipname", "r");
fpassthru($fp);
fclose($fp);
// remover os arquivos temporarios
unlink($filename);
unlink($zipname);
// FIM DO PROGRAMA
// --------------------------------------------------------
// --------------------------------------------------------
// PROCEDIMENTOS - Baseado no csdigo do phpmyadmin
function sqlAddslashes($a_string = '', $is_like = FALSE) {
if ($is_like) {
$a_string = str_replace('\\', '\\\\\\\\', $a_string);
} else {
$a_string = str_replace('\\', '\\\\', $a_string);
}
$a_string = str_replace('\'', '\\\'', $a_string);
return $a_string;
} // end of the 'sqlAddslashes()' function
function backquote($a_name, $do_it = TRUE) {
if ($do_it && PMA_MYSQL_INT_VERSION >= 32306 && !empty($a_name)
&& $a_name != '*') {
if (is_array($a_name)) {
$result = array();
reset($a_name);
while(list($key, $val) = each($a_name)) {
$result[$key] = '`' . $val . '`';
}
return $result;
} else {
return '`' . $a_name . '`';
}
} else {
return $a_name;
}
} // end of the 'backquote()' function
/**
* Returns $table's CREATE definition
*
* @param string the database name
* @param string the table name
* @param string the end of line sequence
*
* @return string the CREATE statement on success
*
* @global boolean whether to add 'drop' statements or not
* @global boolean whether to use backquotes to allow the use of special
* characters in database, table and fields names or not
*
* @see PMA_htmlFormat()
*
* @access public
*/
function getTableDef($db, $table, $crlf) {
global $drop;
global $use_backquotes;
global $con;
$schema_create = '';
if (!empty($drop)) {
$schema_create .= 'DROP TABLE IF EXISTS ' .
backquote($table) . ';' . $crlf;
}
// For MySQL < 3.23.20
$schema_create .= 'CREATE TABLE ' .
backquote($table) . ' (' . $crlf;
$local_query = 'SHOW FIELDS FROM ' . backquote($table) . ' FROM '
. backquote($db);
$result = mysql_query($local_query,$con);
while ($row = mysql_fetch_array($result)) {
$schema_create .= ' ' .
backquote($row['Field'])
. ' ' . $row['Type'];
if (isset($row['Default']) && $row['Default'] != '') {
$schema_create .= ' DEFAULT \'' .
sqlAddslashes($row['Default']) . '\'';
}
if ($row['Null'] != 'YES') {
$schema_create .= ' NOT NULL';
}
if ($row['Extra'] != '') {
$schema_create .= ' ' . $row['Extra'];
}
$schema_create .= ',' . $crlf;
} // end while
mysql_free_result($result);
$schema_create = ereg_replace(',' . $crlf . '$', '', $schema_create);
$local_query = 'SHOW KEYS FROM ' . backquote($table) . ' FROM '
. backquote($db);
$result = mysql_query($local_query,$con);
while ($row = mysql_fetch_array($result)) {
$kname = $row['Key_name'];
$comment = (isset($row['Comment'])) ? $row['Comment'] : '';
$sub_part = (isset($row['Sub_part'])) ? $row['Sub_part'] : '';
if ($kname != 'PRIMARY' && $row['Non_unique'] == 0) {
$kname = "UNIQUE|$kname";
}
if ($comment == 'FULLTEXT') {
$kname = 'FULLTEXT|$kname';
}
if (!isset($index[$kname])) {
$index[$kname] = array();
}
if ($sub_part > 1) {
$index[$kname][] = backquote($row['Column_name']) . '(' . $sub_part . ')';
} else {
$index[$kname][] = backquote($row['Column_name']);
}
} // end while
mysql_free_result($result);
while (list($x, $columns) = @each($index)) {
$schema_create .= ',' . $crlf;
if ($x == 'PRIMARY') {
$schema_create .= ' PRIMARY KEY (';
} else if (substr($x, 0, 6) == 'UNIQUE') {
$schema_create .= ' UNIQUE ' . substr($x, 7) . ' (';
} else if (substr($x, 0, 8) == 'FULLTEXT') {
$schema_create .= ' FULLTEXT ' . substr($x, 9) . ' (';
} else {
$schema_create .= ' KEY ' . $x . ' (';
}
$schema_create .= implode($columns, ', ') . ')';
} // end while
$schema_create .= $crlf . ');';
return $schema_create;
} // end of the 'getTableDef()' function
/**
* php >= 4.0.5 only : get the content of $table as a series of INSERT
* statements.
* After every row, a custom callback function $handler gets called.
*
* Last revision 13 July 2001: Patch for limiting dump size from
* vinay@sanisoft.com & girish@sanisoft.com
*
* @param string the current database name
* @param string the current table name
* @param string the 'limit' clause to use with the sql query
* @param string the name of the handler (function) to use at the end
* of every row. This handler must accept one parameter
* ($sql_insert)
*
* @return boolean always true
*
* @global boolean whether to use backquotes to allow the use of special
* characters in database, table and fields names or not
* @global integer the number of records
* @global integer the current record position
*
* @access private
*
* @see PMA_getTableContent()
*
* @author staybyte
*/
function getTableContentFast($db, $table, $add_query = '', $handler) {
global $use_backquotes;
global $rows_cnt;
global $current_row;
global $con;
global $fp;
$local_query = 'SELECT * FROM ' . backquote($db) . '.' . backquote($table)
. $add_query;
$result = mysql_query($local_query,$con);
if ($result != FALSE) {
$fields_cnt = mysql_num_fields($result);
$rows_cnt = mysql_num_rows($result);
// Checks whether the field is an integer or not
for ($j = 0; $j < $fields_cnt; $j++) {
$field_set[$j] = backquote(mysql_field_name($result, $j), $use_backquotes);
$type = mysql_field_type($result, $j);
if ($type == 'tinyint' || $type == 'smallint' ||
$type == 'mediumint' || $type == 'int' ||
$type == 'bigint' ||$type == 'timestamp') {
$field_num[$j] = TRUE;
} else {
$field_num[$j] = FALSE;
}
} // end for
// Sets the scheme
if (isset($GLOBALS['showcolumns'])) {
$fields = implode(', ', $field_set);
$schema_insert = 'INSERT INTO ' . backquote($table)
. ' (' . $fields . ') VALUES (';
} else {
$schema_insert = 'INSERT INTO ' .
backquote($table) . ' VALUES (';
}
$search = array("\x00", "\x0a", "\x0d", "\x1a"); //\x08\\x09, not required
$replace = array('\0', '\n', '\r', '\Z');
$current_row = 0;
@set_time_limit($GLOBALS['cfg']['ExecTimeLimit']);
// loic1: send a fake header to bypass browser timeout if data
// are bufferized - part 1
if (!empty($GLOBALS['ob_mode']) || (isset($GLOBALS['zip'])
|| isset($GLOBALS['bzip']) || isset($GLOBALS['gzip']))) {
$time0 = time();
}
while ($row = mysql_fetch_row($result)) {
$current_row++;
for ($j = 0; $j < $fields_cnt; $j++) {
if (!isset($row[$j])) {
$values[] = 'NULL';
} else if ($row[$j] == '0' || $row[$j] != '') {
// a number
if ($field_num[$j]) {
$values[] = $row[$j];
} else {
// a string
$values[] = "'" . str_replace($search, $replace,
sqlAddslashes($row[$j])) . "'";
}
} else {
$values[] = "''";
} // end if
} // end for
// Extended inserts case
if (isset($GLOBALS['extended_ins'])) {
if ($current_row == 1) {
$insert_line = $schema_insert . implode(', ', $values) . ');';
} else {
$insert_line = '(' . implode(', ', $values) . ');';
}
} else {
// Other inserts case
$insert_line = $schema_insert . implode(', ', $values) . ');';
}
unset($values);
// Call the handler
fputs($fp,$insert_line . "\n");
// loic1: send a fake header to bypass browser timeout if data
// are bufferized - part 2
if (isset($time0)) {
$time1 = time();
if ($time1 >= $time0 + 30) {
$time0 = $time1;
header('X-pmaPing: Pong');
}
} // end if
} // end while
} // end if ($result != FALSE)
mysql_free_result($result);
return TRUE;
} // end of the 'getTableContentFast()' function
?>

phpzip.inc.php

<?php
#
# PHPZip v1.1 by Sext (sext@neud.net) 2002-11-18
#
# Makes zip archive
#
# Based on "Zip file creation class", uses zLib
#
# Examples in sample1.php and sample2.php
#
class PHPZip
{
function Zip($dir, $zipfilename)
{
if (@function_exists('gzcompress'))
{
$curdir = getcwd();
if (is_array($dir))
{
$filelist = $dir;
}
else
{
$filelist = $this -> GetFileList($dir);
}

if ((!empty($dir))&&(!is_array($dir))&&(file_exists($dir))) chdir($dir);
else chdir($curdir);
if (count($filelist)>0)
{
foreach($filelist as $filename)
{
if (is_file($filename))
{
$fd = fopen ($filename, "r");
$content = fread ($fd, filesize ($filename));
fclose ($fd);
if (is_array($dir)) $filename = basename($filename);
$this -> addFile($content, $filename);
}
}
$out = $this -> file();
chdir($curdir);
$fp = fopen($zipfilename, "w");
fwrite($fp, $out, strlen($out));
fclose($fp);
}
return 1;
}
else return 0;
}
function GetFileList($dir)
{
if (file_exists($dir))
{
$args = func_get_args();
$pref = $args[1];

$dh = opendir($dir);
while($files = readdir($dh))
{
if (($files!=".")&&($files!=".."))
{
if (is_dir($files))
{
$curdir = getcwd();
chdir($files);
$file = array_merge($file, $this -> GetFileList("", "$pref$files/"));
chdir($curdir);
}
else $file[]=$pref.$files;
}
}
closedir($dh);
}
return $file;
}
var $datasec = array();
var $ctrl_dir = array();
var $eof_ctrl_dir = "\x50\x4b\x05\x06\x00\x00\x00\x00";
var $old_offset = 0;
/**
* Converts an Unix timestamp to a four byte DOS date and time format (date
* in high two bytes, time in low two bytes allowing magnitude comparison).
*
* @param integer the current Unix timestamp
*
* @return integer the current date in a four byte DOS format
*
* @access private
*/
function unix2DosTime($unixtime = 0) {
$timearray = ($unixtime == 0) ? getdate() : getdate($unixtime);
if ($timearray['year] < 1980) {
$timearray['year'] = 1980;
$timearray['mon'] = 1;
$timearray['mday'] = 1;
$timearray['hours'] = 0;
$timearray['minutes'] = 0;
$timearray['seconds'] = 0;
} // end if
return (($timearray['year'] - 1980) << 25) | ($timearray['mon'] << 21) | ($timearray['mday'] << 16) |
($timearray['hours'] << 11) | ($timearray['minutes'] << 5) | ($timearray['seconds'] >> 1);
} // end of the 'unix2DosTime()' method
/**
* Adds "file" to archive
*
* @param string file contents
* @param string name of the file in the archive (may contains the path)
* @param integer the current timestamp
*
* @access public
*/
function addFile($data, $name, $time = 0)
{
$name = str_replace('\\', '/', $name);
$dtime = dechex($this->unix2DosTime($time));
$hexdtime = '\x' . $dtime[6] . $dtime[7]
. '\x' . $dtime[4] . $dtime[5]
. '\x' . $dtime[2] . $dtime[3]
. '\x' . $dtime[0] . $dtime[1];
eval('$hexdtime = "' . $hexdtime . '";');
$fr = "\x50\x4b\x03\x04";
$fr .= "\x14\x00"; // ver needed to extract
$fr .= "\x00\x00"; // gen purpose bit flag
$fr .= "\x08\x00"; // compression method
$fr .= $hexdtime; // last mod time and date
// "local file header" segment
$unc_len = strlen($data);
$crc = crc32($data);
$zdata = gzcompress($data);
$zdata = substr(substr($zdata, 0, strlen($zdata) - 4), 2); // fix crc bug
$c_len = strlen($zdata);
$fr .= pack('V', $crc); // crc32
$fr .= pack('V', $c_len); // compressed filesize
$fr .= pack('V', $unc_len); // uncompressed filesize
$fr .= pack('v', strlen($name)); // length of filename
$fr .= pack('v', 0); // extra field length
$fr .= $name;
// "file data" segment
$fr .= $zdata;
// "data descriptor" segment (optional but necessary if archive is not
// served as file)
$fr .= pack('V', $crc); // crc32
$fr .= pack('V', $c_len); // compressed filesize
$fr .= pack('V', $unc_len); // uncompressed filesize
// add this entry to array
$this -> datasec[] = $fr;
$new_offset = strlen(implode('', $this->datasec));
// now add to central directory record
$cdrec = "\x50\x4b\x01\x02";
$cdrec .= "\x00\x00"; // version made by
$cdrec .= "\x14\x00"; // version needed to extract
$cdrec .= "\x00\x00"; // gen purpose bit flag
$cdrec .= "\x08\x00"; // compression method
$cdrec .= $hexdtime; // last mod time & date
$cdrec .= pack('V', $crc); // crc32
$cdrec .= pack('V', $c_len); // compressed filesize
$cdrec .= pack('V', $unc_len); // uncompressed filesize
$cdrec .= pack('v', strlen($name) ); // length of filename
$cdrec .= pack('v', 0 ); // extra field length
$cdrec .= pack('v', 0 ); // file comment length
$cdrec .= pack('v', 0 ); // disk number start
$cdrec .= pack('v', 0 ); // internal file attributes
$cdrec .= pack('V', 32 ); // external file attributes - 'archive' bit set
$cdrec .= pack('V', $this -> old_offset ); // relative offset of local header
$this -> old_offset = $new_offset;
$cdrec .= $name;
// optional extra field, file comment goes here
// save to central directory
$this -> ctrl_dir[] = $cdrec;
} // end of the 'addFile()' method
/**
* Dumps out file
*
* @return string the zipped file
*
* @access public
*/
function file()
{
$data = implode('', $this -> datasec);
$ctrldir = implode('', $this -> ctrl_dir);
return
$data .
$ctrldir .
$this -> eof_ctrl_dir .
pack('v', sizeof($this -> ctrl_dir)) . // total # of entries "on this disk"
pack('v', sizeof($this -> ctrl_dir)) . // total # of entries overall
pack('V', strlen($ctrldir)) . // size of central dir
pack('V', strlen($data)) . // offset to start of central dir
"\x00\x00"; // .zip file comment length
} // end of the 'file()' method
} // end of the 'PHPZip' class
?>

heldersc · February 23, 2004

cara o scrip que quero é esse mesmo, mas gostaria que me explicasse um pouco mais dele, pois analisando o mesmo, vi que tenho que configurar os dados do bd e as tabelas e um diretorio , gostaria de saber sobre o diretorio temporario que tenho que criar e se ele envia os dados para um email a cada determinada hora, ou se os backups, ficam na pasta mencionada.

Velew

heldersc · February 23, 2004

fabyo além desse comentario aí em cima, o scrip esta aperecendo isso, será se é por causa de alguma configuração que esta faltando?

Obs: Testei ele em localhost 1º

Warning: ChDir: No such file or directory (errno 2) in C:\apache\htdocs\script\index.php on line 29
Warning: Cannot add header information - headers already sent by (output started at C:\apache\htdocs\script\index.php:29) in C:\apache\htdocs\script\index.php on line 51
Warning: Cannot add header information - headers already sent by (output started at C:\apache\htdocs\script\index.php:29) in C:\apache\htdocs\script\index.php on line 52
Warning: Cannot add header information - headers already sent by (output started at C:\apache\htdocs\script\index.php:29) in C:\apache\htdocs\script\index.php on line 53
Warning: Cannot add header information - headers already sent by (output started at C:\apache\htdocs\script\index.php:29) in C:\apache\htdocs\script\index.php on line 54
Warning: fopen("sql.1077557193.zip","r") - No such file or directory in C:\apache\htdocs\script\index.php on line 57
Warning: Supplied argument is not a valid File-Handle resource in C:\apache\htdocs\script\index.php on line 58
Warning: Supplied argument is not a valid File-Handle resource in C:\apache\htdocs\script\index.php on line 59
Warning: Unlink failed (No such file or directory) in C:\apache\htdocs\script\index.php on line 63

Fabyo · February 24, 2004

Fazendo backup do banco mysql via script php

O ideal seria usar o mysqldump

mysqldump

No exemplo abaixo ao final será criado um arquivo script.sql com o codigo sql necessario para recriar as tabelas.

Se você quiser que saia no browser, basta trocar os fwrite por echo.

<?php
$dbname = "seu database";
// coloque aqui seus parametros
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db($dbname) or die(mysql_error());
$back = fopen("script.sql","w");
// Pega a lista de todas as tabelas
$res = mysql_list_tables($dbname) or die(mysql_error());
while ($row = mysql_fetch_row($res)) {
$table = $row[0]; // cada uma das tabelas
$res2 = mysql_query("SHOW CREATE TABLE $table");
while ( $lin = mysql_fetch_row($res2)){ // Para cada tabela
fwrite($back,"-- Criando tabela : $table\n");
fwrite($back,"$lin[1]\n--Dump de Dados\n");
$res3 = mysql_query("SELECT * FROM $table");
while($r=mysql_fetch_row($res3)){ // Dump de todos os dados das tabelas
$sql="INSERT INTO $table VALUES ('";
$sql .= implode("','",$r);
$sql .= "')\n";
fwrite($back,$sql);
}
}
}
fclose($back);
?>

Sign In

Script De Backup

Question

heldersc

Link to comment

Share on other sites

5 answers to this question

Recommended Posts

Fabyo

Link to comment

Share on other sites

Fabyo

Link to comment

Share on other sites

heldersc

Link to comment

Share on other sites

heldersc

Link to comment

Share on other sites

Fabyo

Link to comment

Share on other sites

Join the conversation

Forum Statistics

Browse

Activity