Script De Backup

heldersc · Fevereiro 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 · Fevereiro 23, 2004

olha em baixo: no outro post

Fabyo · Fevereiro 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 · Fevereiro 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 · Fevereiro 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 · Fevereiro 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);
?>

Entrar

Script De Backup

Pergunta

heldersc

Link para o comentário

Compartilhar em outros sites

5 respostass a esta questão

Posts Recomendados

Fabyo

Link para o comentário

Compartilhar em outros sites

Fabyo

Link para o comentário

Compartilhar em outros sites

heldersc

Link para o comentário

Compartilhar em outros sites

heldersc

Link para o comentário

Compartilhar em outros sites

Fabyo

Link para o comentário

Compartilhar em outros sites

Participe da discussão

Estatísticas dos Fóruns

Navegação

Atividades