MySQL backuping the database as table
Results 1 to 10 of 10

Thread: MySQL backuping the database as table

  1. #1
    Member
    Join Date
    Dec 2012
    Posts
    49

    MySQL backuping the database as table

    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 Code:
    <?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İH #################################################################
      
    setlocale(LC_ALL'tr_TR.UTF8');
      
    date_default_timezone_set('Europe/Istanbul');
      
    $yedekleme_tarihi strftime("%d_%m_%Y_%H_%M_%S"time());
    ######## TARİH #################################################################

    // Yedeği GZ ile sıkıştır evet true, hayı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ı: ' strftime("%d %B %Y, %H:%M:%S"time()) . "\n";
    $return .= "-- Veritabanı: " $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ısı `' $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($return9);
    $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ı Başarıyla Yedeklendi.</b></center>';
    }else{
    echo 
    '<center><b>Veritabanı Bir Hatadan Dolayı Yedeklenemedi.</b></center>';
    }
    return 
    $handle;
    }
    }
    ?>

    Thank you in advance
    Best Regards

  2. #2
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,840
    What are the problems?
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  3. #3
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    13,884
    Maybe just leverage the built-in MySQL backup tools, such as mysqldump?
    Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be." ~ from Nation, by Terry Pratchett

    "But the main reason that any programmer learning any new language thinks the new language is SO much better than the old one is because he’s a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html


    eBookworm.us

  4. #4
    Member
    Join Date
    Dec 2012
    Posts
    49
    Quote Originally Posted by Weedpacket View Post
    What are the problems?
    No problem for backup combine tables,
    But, not combine, backup tables as table

    Quote Originally Posted by NogDog View Post
    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

  5. #5
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,671
    Mysqldump is a built-in program that dumps a database to a text format suitable (well, mostly suitable) for restoration.

    Example command-line usage:

    Code:
    /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:

    PHP Code:
    $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.
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  6. #6
    Un Re Member cretaceous's Avatar
    Join Date
    Sep 2004
    Location
    London UK
    Posts
    935
    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

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

  7. #7
    Member
    Join Date
    Dec 2012
    Posts
    49
    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 Code:
    <?php
    @ini_set('memory_limit''-1');
    header('Content-Type: text/html; charset=utf-8');
    include 
    "config.php";
    $tables '*';
    $gz_enable='0';

    ######## TARİH #################################################################
      
    setlocale(LC_ALL'tr_TR.UTF8');
      
    date_default_timezone_set('Europe/Istanbul');
      
    $yedekleme_tarihi strftime("%d_%m_%Y_%H_%M_%S"time());
    ######## TARİ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ı: ' strftime("%d %B %Y, %H:%M:%S"time()) . "\n";
    $return .= "-- Veritabanı: " $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ısı `' $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($return9);
    $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ı Başarıyla Yedeklendi.</b></center>';
    }else{
    echo 
    '<center><b>Veritabanı Bir Hatadan Dolayı Yedeklenemedi.</b></center>';
    }
    return 
    $handle;

    ?>
    Last edited by gecekule; 10-02-2013 at 04:45 AM.

  8. #8

  9. #9
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,840
    So, like what mysqldump can do:
    Code:
    mysqldump --resultfile=table1.sql databasename table1
    mysqldump --resultfile=table2.sql databasename table2
    ...
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  10. #10
    Member
    Join Date
    Dec 2012
    Posts
    49
    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)
    .............
    ...........

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •