Hello,
The code below is the database backup code.
I am running the code from another page with ajax.
The code works, but there is a memory issue.
Small size database backup is ok. “I get the message saying successful with ajax”
However, even though a 350MB database backup is over, memory usage is increasing, cpu usage is increasing, ajax is on standby.
I look at the file size in the directory that the backup has finished, and when it reaches the size, I open the file and see that it has been backed up without any problems.
However, ajax is pending, memory increase continues, cpu increase continues, apache usage is high.
I run these tests on my local computer with Wampserver64
//echo '<pre>' . print_r($_POST, true) . '</pre>';
//exit;
@ini_set('memory_limit', '-1');
@ignore_user_abort(1);
@ini_set("magic_quotes_runtime", 0);
@ini_set('max_execution_time', 60*60*24);
require_once('connect.php');
################################################################################
if(!file_exists(BACKUPDIR)){
if (!mkdir(BACKUPDIR, 0777, true)) {
die('Failed to create folders...');
}
}
$content = 'deny from all';
$file = new SplFileObject(BACKUPDIR . '/.htaccess', "w") ;
$file->fwrite($content);
################################################################################
if(isset($_POST['onek']) && !empty($_POST['onek'])){ // File prefix
$onek = $_POST['onek']."-";
$tarih_onek = $onek.datetime;
}else{
$tarih_onek = datetime; // Date file prefix
}
################################################################################
$tables = array();
################################################################################
if(isset($_POST['combine']) && $_POST['combine']=='1'){ // Tam tek dosya olarak yedekleme // Backup as a consolidated single file
$tabloadi='-Tam';
$tables = '*';
}
################################################################################
if(isset($_POST['combine']) && $_POST['combine']=='2'){ // Tabloları Ayrı Ayrı yedekleme // Backing up individual tables
$tables = '*';
define('SUBBACKUPDIR', './'.BACKUPDIR.'/'.$tarih_onek ) ;
if(!file_exists(SUBBACKUPDIR)){
if (!mkdir(SUBBACKUPDIR, 0777, true)) {
die('Failed to create folders...');
}
}
$content = 'deny from all';
$file = new SplFileObject(SUBBACKUPDIR . '/.htaccess', "w") ;
$file->fwrite($content) ;
}
################################################################################
if(isset($_POST['combine']) && $_POST['combine']=='3' && isset($_POST['elle']) && $_POST['elle']=='1'){ // Tabloları elle seçme // Backing up manually selected tables
$toplam_tablo = count($_POST['tablolar']);
if($toplam_tablo==1){
$tables = $_POST['tablolar'];
sort($tables);
$tabloadi="-".$tables[0];
}else{
$tables = $_POST['tablolar'];
sort($tables);
$tabloadi='-Elle';
}
}
################################################################################
if(isset($_POST['combine']) && $_POST['combine']=='3' && isset($_POST['elle']) && $_POST['elle']=='2'){ // Tabloları elle seçme // Backing up manually selected tables
define('SUBBACKUPDIR', './'.BACKUPDIR.'/'.$tarih_onek ) ;
if(!file_exists(SUBBACKUPDIR)){
if (!mkdir(SUBBACKUPDIR, 0777, true)) {
die('Failed to create folders...');
}
}
$content = 'deny from all';
$file = new SplFileObject(SUBBACKUPDIR . '/.htaccess', "w") ;
$file->fwrite($content) ;
$tables = $_POST['tablolar'];
sort($tables);
}
################################################################################
if(isset($_POST['group']) && $_POST['group']=='1'){
$return = null;
$mysql_version = $PDOconn->query('select version()')->fetchColumn();
$mysqlcharacter = $PDOconn->query("SHOW VARIABLES LIKE 'character_set_connection'");
$mysql_character = $mysqlcharacter->fetchColumn(1);
$return .= "\n-- Karakter Seti: ".$mysql_character."\n";
$return .= "-- PHP Sürümü: ".phpversion()."\n";
$return .= "-- Sunucu sürümü: ".$mysql_version."\n";
$return .= "-- Anamakine: ".$_SERVER['HTTP_HOST']."\n";
$return .= '-- Üretim Zamanı: ' . date_tr('j F Y, H:i', time() ) . "\n";
$return .= "-- Veritabanı: " . $db_name . "\n";
$return .= "--\n";
$return .= "-- --------------------------------------------------------\n";
$return .= 'SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";' ."\n" ;
$return .= 'SET AUTOCOMMIT = 0;' ."\n";
$return .= 'START TRANSACTION;' ."\n" ;
$return .= 'SET time_zone = "+00:00";' ."\n" ;
$return .="-- --------------------------------------------------------\n\n";
$return .="--\n";
$return .="-- Veritabanı: `{$db_name}`\n";
$return .="--\n\n";
$lock_write = 'LOCK TABLES';
$lock_read = 'LOCK TABLES';
// BASE TABLE SAVE
// get all of the tables
if($tables == '*'){
$tables = array();
$result = $PDOconn->query("SHOW TABLES");
while($row = $result->fetch(PDO::FETCH_NUM)){
$tables[] = $row[0];
$lock_write .= ' '.$row[0].' WRITE,';
$lock_read .= ' '.$row[0].' READ,';
}
}else{
$tables = is_array($tables) ? $tables : explode(',',$tables);
foreach ($tables AS $table){
$lock_write .= ' '.$table.' WRITE,';
$lock_read .= ' '.$table.' READ,';
}
}
$tablosayisi = count($tables);
##############################################################################################################################
// Repair & Optimize Tables
function repairTables($PDOconn)
{
// Predefined Variables
$tables = array();
$lock = 'LOCK TABLES';
// Get Table List
$result = $PDOconn->query('SHOW tables');
// Store Table List
while ($table = $result->fetch(PDO::FETCH_NUM))
{
$tables[] = $table[0];
$lock .= ' `'.$table[0].'` WRITE,';
}
// Remove Ending of LockList
$lock = rtrim($lock,",").';';
// Lock Tables
if ($_POST['lock']=='1')
{
$PDOconn->query($lock);
}
// Loop Tables
foreach ($tables AS $table){
// Check Table
$check = $PDOconn->query("CHECK TABLE `$table`")->fetch(PDO::FETCH_NUM);
// Repair Table
$repair = $PDOconn->query("REPAIR TABLE `$table`")->fetch(PDO::FETCH_BOTH);
// Optimize Table
$optimize = $PDOconn->query("OPTIMIZE TABLE `$table`")->fetch(PDO::FETCH_BOTH);
} // foreach ($tables AS $table)
// Unlock Tables
if ($_POST['lock']=='1')
{
$PDOconn->query('UNLOCK TABLES;');
}
}
if($_POST['bakim']=='1'){
repairTables($PDOconn);
}
##############################################################################################################################
// array of column number types only
//sadece sayı türlerin dizisi
$numtypes = array('tinyint', 'smallint', 'mediumint', 'int', 'bigint', 'float', 'double', 'decimal', 'real', 'int unsigned');
$handle = "";
$basarili = false;
$t = 0;
foreach($tables as $table){
$t++;
$sutun_sayisi = $PDOconn->query('SELECT * FROM '.$table);
$num_fields = $sutun_sayisi->columnCount();
$numrow = $sutun_sayisi->rowCount();
################################################################################
// Path and filename to backup tables separately
// Tabloları ayrı ayrı yedekleme yolu ve dosya adı
if($_POST['combine']=='2' OR $_POST['combine']=='3' AND $_POST['elle']=='2'){
$handle = fopen(SUBBACKUPDIR.'/'.trim($table).'.sql','a');
// Path and filename for GZip
// GZip için dosya yolu ve dosya adı
$dosya = SUBBACKUPDIR.'/'.trim($table).'.sql';
}
// Backup path and filename in one concatenated file
// Tek dosyada yedekleme yolu ve dosya adı
if($_POST['combine']=='1' OR $_POST['combine']=='3' AND $_POST['elle']!='2'){
$handle = fopen('./'.BACKUPDIR.'/'.$tarih_onek.$tabloadi.'.sql','a');
// Path and filename for GZip
// GZip için dosya yolu ve dosya adı
$dosya = './'.BACKUPDIR.'/'.$tarih_onek.$tabloadi.'.sql';
}
$type = array();
################################################################################
$return .= "--\n" ;
$return .= '-- Tablonun yapısı `' . $table . '`' . "\n" ;
$return .= "--\n\n";
$return .= 'DROP TABLE IF EXISTS '.$table.';';
$pstm2 = $PDOconn->query('SHOW CREATE TABLE '.$table);
$row2 = $pstm2->fetch(PDO::FETCH_NUM);
$ifnotexists = str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $row2[1]);
$return .= "\n".$ifnotexists.";\n";
$return .= "\n--\n" ;
$return .= '-- Tablonun veri dökümü `' . $table . '`' . "\n" ;
$return .= "--\n\n" ;
##############################################################################################
// We get the column types of the table
//Tablonun sutün tiplerini alıyoruz ki sayı formatlı mı metin formatlı mı
if ($numrow) {
$pstm3 = $PDOconn->query("SHOW COLUMNS FROM $table");
$type = array();
while ($rows = $pstm3->fetch(PDO::FETCH_NUM)) {
if (stripos($rows[1], '(')) {
$type[$table][] = stristr($rows[1], '(', true);
} else {
$type[$table][] = $rows[1];
}
}
}
##############################################################################################
##############################################################################################
$sutunozellikleri = $PDOconn->query(' SHOW COLUMNS FROM '.$table );
$sutun_ozellikleri = $sutunozellikleri->fetchAll(PDO::FETCH_NUM);
##############################################################################################
$satirlar_dizisi = $sutun_sayisi->fetchAll(PDO::FETCH_NUM);
@set_time_limit(0);
$s = 0;
foreach($satirlar_dizisi AS $satirlardizi){
$s++;
$return .= 'INSERT INTO `' . trim($table) . '` VALUES(';
foreach($satirlardizi AS $key => $value){
// Checking if data exists
// Veri olup olmadığını kontrol ediyoruz
if (strlen((string) $value)>0) {
// Sutün tipi sayı formatlı ise '15', gibi yerine kesmeyi kaldırıp 15, sadece sayı değeri ekle
if ((in_array($type[$table][$key], $numtypes)) && (strlen((string) $value)>0)) {
$return .= $value;
} else {
$return .= $PDOconn->quote($value); // Sutün tipi sayı formatlı olmadığı için 'veri' gibi veriyi kesme içine alarak ekle
}
} else {
if( $sutun_ozellikleri[$key][2] == 'YES' && empty($sutun_ozellikleri[$key][4]) ){ // Veri yok ve "Tanımlandığı gibi" de yok ise NULL ekle
$return .= 'NULL';
}else{ // Veri yok ve "Tanımlandığı gibi" de veri var ise ekle
if ((in_array($type[$table][$key], $numtypes))) {
$return .= $sutun_ozellikleri[$key][4];
} else {
// Sutün tipi NOT NULL olduğu halde veri yoksa burada hata verecektir.
// Çözümü, sutün tipi NOT NULL ise boş olmayacak, boş olacaksa DEFAULT NULL olacak
if(empty($sutun_ozellikleri[$key][4])){
$return .= '\'\'';
}else{
$return .= $PDOconn->quote( $sutun_ozellikleri[$key][4] );
}
}
}
}
if ($key < ($num_fields - 1)) {
$return .= ', ' ;
}
}
$return .= ");\n";
// Add table data below dump
// Tablo verinin dökümün altına ekler
if ( $s == ( $numrow - 0 ) ){
$return .="\n--\n";
$return .="-- TABLO_ADI {$table} {$numrow}\n";
$return .="--\n";
$return .="\n-- --------------------------------------------------------\n\n";
}
// Write read data to file
// Okunan veriyi dosyaya yaz
fwrite($handle, $return);
$return=null;
}
// If there is no data in the table, just write the table structure to the file
// Tabloda veri yoksa sadece tablo yapısını dosyaya yaz
if($numrow == '0'){
$return .="--\n";
$return .="-- TABLO_ADI {$table} {$numrow}\n";
$return .="--\n";
$return .="\n-- --------------------------------------------------------\n\n";
fwrite($handle, $return);
$return=null;
}
####################################################################################################
// Trigger section
// Tetikleyici bölümü
$trigger = $PDOconn->query(" SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = '".$db_name."' AND EVENT_OBJECT_TABLE = '".$table."' ");
$trigger_dizi = $trigger->fetchAll(PDO::FETCH_ASSOC);
if(count($trigger_dizi)>0){
$tri = 1;
foreach($trigger_dizi AS $trigger){
if($tri == 1){
$return .= "--\n";
$return .= "-- Tetikleyiciler `".$trigger['EVENT_OBJECT_TABLE']."`";
$return .= "\n--\n\n";
}
$return .= "DROP TRIGGER IF EXISTS `".$trigger['TRIGGER_NAME']."`;\n";
$return .= "DELIMITER $$\n";
$return .= 'CREATE TRIGGER `'.$trigger['TRIGGER_NAME'].'` '.$trigger['ACTION_TIMING'].' '.$trigger['EVENT_MANIPULATION'].' ON `'.$trigger['EVENT_OBJECT_TABLE'].'` FOR EACH ROW ';
$return .= $trigger['ACTION_STATEMENT'];
$return .= "\n$$\n";
$return .= "DELIMITER ;\n";
$tri++;
}
$return .= "COMMIT;\n\n";
// Write read data to file
// Okunan veriyi dosyaya yaz
fwrite($handle, $return);
$return=null;
}
####################################################################################################
// Add to the end of the file
// Dosyanın en sonuna ekle
if ( $t == ( $tablosayisi - 0 ) AND $tablosayisi > 1){
$return .= "\n";
$return .= 'SET FOREIGN_KEY_CHECKS = 1 ; ' . "\n" ;
$return .= 'COMMIT ; ' . "\n" ;
$return .= 'SET AUTOCOMMIT = 1 ; ' . "\n" ;
fwrite($handle, $return);
$return=null;
}
// Close opened file
// Açılmış dosyayı kapat
if($_POST['gz']=='0'){
fclose($handle);
}
// Compress individual backups of each table with GZip
// Tablo Tablo yedekleri GZip ile sıkıştırır
if($_POST['gz']=='1' AND ($_POST['combine']=='2' OR @$_POST['elle']=='2')){
fclose($handle);
$input = $dosya;
$output = $input.".gz";
$basarili = file_put_contents("compress.zlib://$output", file_get_contents($input));
if($basarili){@unlink($dosya);}
}
}//foreach($tables as $table){
// Combined single file backup Compress with GZip
// Tek dosyada yedeği GZip ile sıkıştırır
if($handle AND $_POST['gz']=='1' AND ($_POST['combine']=='1' OR @$_POST['elle']=='1')){
fclose($handle);
$input = $dosya;
$output = $input.".gz";
$basarili = file_put_contents("compress.zlib://$output", file_get_contents($input));
if($basarili){@unlink($dosya);}
}
// Unlock if Tables are locked during backup
// Yedekleme sırasında Tablolar kilitlendi ise kilitleri açar
if ($_POST['lock'] == 1){
$PDOconn->query('UNLOCK TABLES;');
unset($lock_write,$lock_read);
}
if($handle != "" OR $basarili){
echo 'Veritabanı Başarıyla Yedeklendi'; // Database Backed Up Successfully
}else{
echo 'Veritabanı Bir Hatadan Dolayı Yedeklenemedi'; // The Database Could Not Be Backed Up Due To An Error
}
}//if($_POST['group']=='1'){
This code is amateurishly coded
I request your help
Thank you