MySQL backuping the database as table

[gecekule]

Hello,
There are some problems when backuping very large database. What should I change to backup the code below as table (not conbine)?

Can you help, please?

<?php
session_start();
error_reporting(E_ALL & ~E_NOTICE);
header('Content-Type: text/html; charset=utf-8');

include('../includes/baglantii.php');
@ini_set('memory_limit', '-1');
 backup_tables($mysqlibag,$db_name);

/* backup the db OR just a table */
function backup_tables($baglanti, $dbname, $tables = '*'){

######## TAR&#304;H #################################################################
  setlocale(LC_ALL, 'tr_TR.UTF8');
  date_default_timezone_set('Europe/Istanbul');
  $yedekleme_tarihi = strftime("%d_%m_%Y_%H_%M_%S", time());
######## TAR&#304;H #################################################################

// Yede&#287;i GZ ile s&#305;k&#305;&#351;t&#305;r evet true, hay&#305;r false
$gz_enable=true; // true / false

if($_POST['rolu']=='1' AND !empty($_SESSION['uye']))
{
$first_table_veriable=$tables;

$return=null;

$mysql = $baglanti->query("SELECT VERSION() AS version")->fetch_row();
$mysql_character = $baglanti->query("SHOW VARIABLES LIKE '%character%'")->fetch_row();

// BASE TABLE SAVE  

//get all of the tables
if($tables == '*')
{
$return .= "\n-- Karakter: ".$mysql_character[1]."\n";
$return .= "-- PHP Sürümü: ".phpversion()."\n";
$return .= "-- Sunucu sürümü: ".$mysql[0]."\n";
$return .= "-- Anamakine: ".$_SERVER['HTTP_HOST']."\n";
$return .= '-- Üretim Zaman&#305;: ' . strftime("%d %B %Y, %H:%M:%S", time()) . "\n";
$return .= "-- Veritaban&#305;: " . $dbname . "\n";
$return .= "--\n";
$return .= "-- --------------------------------------------------\n\n";
$return .= 'SET AUTOCOMMIT = 0 ;' ."\n" ;
$return .= 'SET FOREIGN_KEY_CHECKS=0 ;';
$tables = array();
$result = $baglanti->query("SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'");
while($row = $result->fetch_row())
{
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(',',$tables);
}

//cycle through
foreach($tables as $table)
{
$result = $baglanti->query('SELECT * FROM '.$table);
$num_fields = $result->field_count;
$return .= "\n\n--\n" ;
$return .= '-- Tablo için tablo yap&#305;s&#305; `' . $table . '`' . "\n" ;
$return .= "--\n\n" ;
$return.= 'DROP TABLE IF EXISTS '.$table.';';
$row2 = $baglanti->query('SHOW CREATE TABLE '.$table)->fetch_row();
$return.= "\n".$row2[1].";\n";
$return .= "\n--\n" ;
$return .= '-- Tablo döküm verisi `' . $table . '`' . "\n" ;
$return .= "--\n\n" ;
for ($i = 0; $i < $num_fields; $i++)
{
@set_time_limit(0);
while($row = $result->fetch_row())
{
$return.= 'INSERT INTO '.$table.' VALUES(';
for($j=0; $j<$num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = preg_replace("/\r\n/","\\r\\n",$row[$j]);
if (isset($row[$j])) { $return.= "'".$row[$j]."'" ; } else { $return.= "''"; }
if ($j<($num_fields-1)) { $return.= ','; }
}
$return.= ");\n";
}
}
$return.="\n-- --------------------------------------------------------";
}

// WIEW SAVE

//get all of the tables
$tables = $first_table_veriable;

if($tables == '*')
{
$tables = array();
$result = $baglanti->query("SHOW FULL TABLES WHERE Table_type = 'VIEW'");
while($row = $result->fetch_row())
{
$tables[] = $row[0];
}

}
else
{
$tables = is_array($tables) ? $tables : explode(',',$tables);

}

//cycle through
foreach($tables as $table)
{

$result = $baglanti->query('SELECT * FROM '.$table);

$return.= 'DROP TABLE IF EXISTS '.$table.';';
$row2 = $baglanti->query('SHOW CREATE VIEW '.$table)->fetch_row();
$return.= "\n".$row2[1].";\n";

$return.="\n\n\n";
}
// Close the connection
$baglanti->close();
$return.="\n\n";
$return .= 'SET FOREIGN_KEY_CHECKS = 1 ; '  . "\n" ; 
$return .= 'COMMIT ; '  . "\n" ;
$return .= 'SET AUTOCOMMIT = 1 ; ' . "\n"  ; 
//save file

if($gz_enable){
$gzdata = gzencode($return, 9);
$handle = fopen('tarih-'.$yedekleme_tarihi.'.sql.gz','w+');
fwrite($handle, $gzdata);
fclose($handle);
}else{
$handle = fopen('tarih-'.$yedekleme_tarihi.'.sql','w+');
fwrite($handle,$return);
fclose($handle);
}

if($handle !=""){
echo '<center><b>Veritaban&#305; Ba&#351;ar&#305;yla Yedeklendi.</b></center>';
}else{
echo '<center><b>Veritaban&#305; Bir Hatadan Dolay&#305; Yedeklenemedi.</b></center>';
}
return $handle;
}
}
?>

Thank you in advance
Best Regards

[Weedpacket]

What are the problems?

[NogDog]

Maybe just leverage the built-in MySQL backup tools, such as mysqldump?

[gecekule]

Weedpacket;11033697 wrote:

What are the problems?

No problem for backup combine tables,
But, not combine, backup tables as table

NogDog;11033703 wrote:

Maybe just leverage the built-in MySQL backup tools, such as mysqldump?

I beginner, many do not understand

I did not write the above code, organized just for myself.

Regards

[dalecosp]

Mysqldump is a built-in program that dumps a database to a text format suitable (well, mostly suitable) for restoration.

Example command-line usage:

/usr/local/bin/bash:me@somebox>mysqldump my_sql_db > sql.backup.sql

(Incidentally, as you say you're a beginner, "/usr/local/bin/bash:me@somebox>" is the shell prompt in the example above, and "mysqldump my_sql_db > sql.backup.sql" is the actual command to be run at the prompt.

Of course, it might ask for a password, etc. on the command line. You can script this:

$backup = system("/path/to/mysqldump -u username -pMyP@ssw0rd my_sql_db > /path/to/backups/sql.backup.sql");

Perhaps this will help? I hope so. 🙂

[cretaceous]

Also mysql dump can be set to ignore some tables - you may have some that are less important
- but not really sure what your problem is

$command = "mysqldump -u$dbUserLogin -h$dbHost --password=$dbPassword $dbName --ignore-table=$dbName.history_archive | gzip > $filename";

[gecekule]

Thank you very much for your answer
I'm sorry, my English is bad

Separately tables making backups

Not full-database-backup.sql

table1.sql
table2.sql
table3.sql
table4.sql
table5.sql
............
........
.....
like

More regular

<?php
@ini_set('memory_limit', '-1');
header('Content-Type: text/html; charset=utf-8');
include "config.php";
$tables = '*';
$gz_enable='0';

######## TAR&#304;H #################################################################
  setlocale(LC_ALL, 'tr_TR.UTF8');
  date_default_timezone_set('Europe/Istanbul');
  $yedekleme_tarihi = strftime("%d_%m_%Y_%H_%M_%S", time());
######## TAR&#304;H #################################################################

$return=null;

$mysql = $mysqlibag->query("SELECT VERSION() AS version")->fetch_row();
$mysql_character = $mysqlibag->query("SHOW VARIABLES LIKE 'character_set_connection'")->fetch_assoc();

$return .= "--\n";
$return .= "-- Karakter: ".$mysql_character['Value']."\n";
$return .= "-- PHP Sürümü: ".phpversion()."\n";
$return .= "-- Sunucu sürümü: ".$mysql[0]."\n";
$return .= "-- Anamakine: ".$_SERVER['HTTP_HOST']."\n";
$return .= '-- Üretim Zaman&#305;: ' . strftime("%d %B %Y, %H:%M:%S", time()) . "\n";
$return .= "-- Veritaban&#305;: " . $db_name . "\n";
$return .= "--\n";
$return .= "-- --------------------------------------------------\n\n";
$return .= 'SET AUTOCOMMIT = 0 ;' ."\n" ;
$return .= 'SET FOREIGN_KEY_CHECKS=0 ;';
$tables = is_array($tables) ? $tables : explode(',',$tables);


//cycle through
foreach($tables as $table)
{
$result = $mysqlibag->query('SELECT * FROM '.$table);
$num_fields = $result->field_count;
$return .= "\n\n--\n" ;
$return .= '-- Tablo için tablo yap&#305;s&#305; `' . $table . '`' . "\n" ;
$return .= "--\n\n" ;
$return.= 'DROP TABLE IF EXISTS '.$table.';';
$row2 = $mysqlibag->query('SHOW CREATE TABLE '.$table)->fetch_row();
$return.= "\n".$row2[1].";\n";
$return .= "\n--\n" ;
$return .= '-- Tablo döküm verisi `' . $table . '`' . "\n" ;
$return .= "--\n\n" ;
for ($i = 0; $i < $num_fields; $i++)
{
@set_time_limit(0);
while($row = $result->fetch_row())
{
$return.= 'INSERT INTO '.$table.' VALUES(';
for($j=0; $j<$num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = preg_replace("/\r\n/","\\r\\n",$row[$j]);
if (isset($row[$j])) { $return.= "'".$row[$j]."'" ; } else { $return.= "''"; }
if ($j<($num_fields-1)) { $return.= ','; }
}
$return.= ");\n";
}
}
$return.="\n-- --------------------------------------------------------";
}//foreach($tables as $table)

// Close the connection
$mysqlibag->close();
$return.="\n\n";
$return .= 'SET FOREIGN_KEY_CHECKS = 1 ; '  . "\n" ; 
$return .= 'COMMIT ; '  . "\n" ;
$return .= 'SET AUTOCOMMIT = 1 ; ' . "\n"  ; 
//save file

if($gz_enable=='1'){
$gzdata = gzencode($return, 9);
$handle = fopen('tarih-'.$yedekleme_tarihi.'.sql.gz','w+');
fwrite($handle, $gzdata);
fclose($handle);
}elseif($gz_enable=='0'){
$handle = fopen('tarih-'.$yedekleme_tarihi.'.sql','w+');
fwrite($handle,$return);
fclose($handle);
}
if($handle !=""){
echo '<center><b>Veritaban&#305; Ba&#351;ar&#305;yla Yedeklendi.</b></center>';
}else{
echo '<center><b>Veritaban&#305; Bir Hatadan Dolay&#305; Yedeklenemedi.</b></center>';
}
return $handle;

?>

[gecekule]

Like this backup

[Weedpacket]

So, like what mysqldump can do:

mysqldump --resultfile=table1.sql databasename table1
mysqldump --resultfile=table2.sql databasename table2
...

[gecekule]

Thank you for your interest in everyone, I solved the problem

.............
.............
$row[$j] = addslashes($row[$j]);
$row[$j] = preg_replace("/\r\n/","\r\n",$row[$j]);
if (isset($row[$j])) { $return.= "'".$row[$j]."'" ; } else { $return.= "''"; }
if ($j<($num_fields-1)) { $return.= ','; }
}
$return.= ");\n";
}
$handle = fopen('./yedek/'.$date.$table'.sql','a');
fwrite($handle,$return);
fclose($handle);
unset($return);
}
$return.="\n-- --------------------------------------------------------";
}//foreach($tables as $table)
.............
...........