Results 1 to 11 of 11
  1. #1
    Join Date
    May 2002
    Posts
    638

    how to export mysql table db (i.e. compress it and save it as a file?)

    i know how to do it with phpmysqladmin (using the export function).

    but i would like to code it myself using php.

    is there any tutorials that can help me with this.

    i want to save some (not all) of the tables in my database regularly. so if i can code it myself, i can simply use lynx to automate the backup process daily.

    CAn anyone help?

  2. #2
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,099
    Read the mysqldump manual page.
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  3. #3
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    2,123
    mysqldump -u username database_name > db_backup.sql -p

  4. #4
    Join Date
    Dec 2002
    Location
    NY, NY
    Posts
    3,975
    mysqldump -u USER -p PASS DB TABLE > FILE TO DUMP TO
    [this is not a sql query]
    run that from telnet/bash

  5. #5
    Join Date
    May 2002
    Posts
    638
    but i wish to do it via a webpage, instead of telnet/bash

    how to do it with php? what is the mysql query code?

  6. #6
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,099
    use system(), exec(), etc. to run that command.

    And -p PASS doesn't work. its -pPASS
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  7. #7
    Join Date
    Sep 2003
    Posts
    125
    Here is a script that I have used ... can be run from cron to automate it all.

    Mike

    PHP Code:
    <?php 
      
    /* HMS_BACKUP.PHP 
         Holomarcus MySQL Database Backup    
         
         Version 1.0 - August 15th, 2003 
         Changelog: see bottom of script. 
          
         (c)2003 Holomarcus ([url]http://www.holomarcus.nl[/url]) 
         You can distribute this script and use it freely as 
         long as this header is not edited in the script. 
          
         With HMS_BACKUP you can make a backup of your MySQL-database. 
         This backup can be sent by e-mail or uploaded through FTP. 
          
         This script doesn't need privileges to execute *nix commands. 
         It's 100% pure PHP. 
          
         The script needs write-privileges on the directory it resides in! 
          
         Change the necessary settings below... 
      */ 

      
    $dbhost        'localhost';  // Server address of your MySQL Server 
      
    $dbuser        'xxx_xxx';      // Username to access MySQL database 
      
    $dbpass        'xxxx';    // Password to access MySQL database 
      
    $dbname        'xxx_xxx';      // Database Name 

      
    $use_gzip      'yes';        // Set to No if you don't want the files sent in .gz format 

      
    $remove_file   'no';        // Set this to yes if you want to remove the file after sending. Yes is recommended. 

      
    $use_email     'yes';          // Set to 'yes' if you want the backup to be sent throug email. Fill out next 3 lines. 
      
    $send_to       [email protected]';   // E-mail to send the mail to 
      
    $send_from     'DB_BAckp'// E-mail the mail comes from 
      
    $subject       "MySQL Database ($dbname) Backup - " date("j F Y"); // Subject in the email to be sent. 

      
    $use_ftp       'no'// Do you want this database backup uploaded to an ftp server? Fill out the next 4 lines 
      
    $ftp_server    '';   // FTP hostname 
      
    $ftp_user_name '';   // FTP username 
      
    $ftp_user_pass '';   // FTP password 
      
    $ftp_path      "/";  // This is the path to upload on your ftp server! 

      
    $echo_status 'no';   // Set to 'no' if the script should work silently (no output will be sent to the screen) 


    # You probably don't need to edit below this line.... 
    #------------------------------------------------------------------------------- 

      
    $db mysql_connect("$dbhost","$dbuser","$dbpass"); 
        
    mysql_select_db("$dbname",$db); 

      
    $path make_dir(); 
       
      if (
    $echo_status == 'yes') { 
        print 
    "Dumpfile will be written to $path<br>"
      } 

      
    $result mysql_query("show tables from $dbname"); 
      while (list(
    $table) = mysql_fetch_row($result)) { 
        
    $newfile .= get_def($table); 
        
    $newfile .= "\n\n"
        
    $newfile .= get_content($table); 
        
    $newfile .= "\n\n"
        
    $i++; 
        if (
    $echo_status == 'yes') { 
          print 
    "Dumped table $table<br>"
        } 
      } 

        
    $file_name $dbname "-" date("Ymd-Hi") . ".sql"
        
    $file_path $path $file_name

      if (
    $use_gzip == "yes") { 
        
    $file_name .= ".gz"
        
    $file_path .= ".gz"
        
    $zp gzopen($file_path"wb9"); 
        
    gzwrite($zp,$newfile); 
        
    gzclose($zp); 

        if (
    $echo_status == 'yes') { 
          print 
    "<br>Gzip-file is created...<br>"
        } 
      } else { 
        
    $fp fopen($file_path"w"); 
        
    fwrite($fp$newfile); 
        
    fclose($fp); 

        if (
    $echo_status == 'yes') { 
          print 
    "<br>SQL-file is created...<br>"
        } 
      } 

      if (
    $use_email == 'yes') { 
        
    $fileatt_type filetype($file_path); 
       
        
    $headers "From: $send_from"
       
        
    // Read the file to be attached ('rb' = read binary) 
        
    $fp fopen($file_path,'rb'); 
        
    $data fread($fp,filesize($file_path)); 
        
    fclose($fp); 
       
        
    // Generate a boundary string 
        
    $semi_rand md5(time()); 
        
    $mime_boundary "==Multipart_Boundary_x{$semi_rand}x"
       
        
    // Add the headers for a file attachment 
        
    $headers .= "\nMIME-Version: 1.0\n" ."Content-Type: multipart/mixed;\n" ." boundary=\"{$mime_boundary}\""
       
        
    // Add a multipart boundary above the plain message 
        
    $message "This is a multi-part message in MIME format.\n\n" ."--{$mime_boundary}\n" ."Content-Type: text/plain; charset=\"iso-8859-1\"\n" ."Content-Transfer-Encoding: 7bit\n\n" 
        
    $message "\n\n"
       
        
    // Base64 encode the file data 
        
    $data chunk_split(base64_encode($data)); 
       
        
    // Add file attachment to the message 
        
    $message .= "--{$mime_boundary}\n" ."Content-Type: {$fileatt_type};\n" ." name=\"{$file_name}\"\n" ."Content-Disposition: attachment;\n" ." filename=\"{$file_name}\"\n" ."Content-Transfer-Encoding: base64\n\n" 
        
    $data "\n\n" ."--{$mime_boundary}--\n"
       
        
    // Send the message 
        
    $ok = @mail($send_to$subject$message$headers); 
         
        if (
    $echo_status == 'yes') { 
          print 
    "<br>Mail is sent...<br>"
        } 
      } 
       
      if (
    $use_ftp == 'yes') { 
        if (
    $use_gzip == 'yes') { 
          
    $mode FTP_BINARY
        } else { 
          
    $mode FTP_ASCII
        } 
        
    $ftp_id       ftp_connect($ftp_server); 
        
    $login_result ftp_login($ftp_id$ftp_user_name$ftp_user_pass); 
        
    $upload       ftp_put($ftp_id$ftp_path $file_name$file_path$mode); 
        
    ftp_close($ftp_id); 

        if (
    $echo_status == 'yes') { 
          print 
    "<br>Backup is uploaded to $ftp_user_name@$ftp_server...<br>"
        } 
      } 

      if (
    $remove_file == "yes") { 
        
    unlink($file_name); 
        if (
    $echo_status == 'yes') { 
          print 
    "<br>File is deleted...<br>"
        } 
      } 

      if (
    $echo_status == 'yes') { 
        print 
    "<br>I am done!<br>"
      } 


      function 
    make_dir() { 
        
    $page split("/"getenv('SCRIPT_NAME')); 
        
    $n count($page)-1
        
    $page $page[$n]; 
        
    $page split("\."$page2); 
        
    $extension $page[1]; 
        
    $page $page[0]; 
        
    $script     "$page.$extension"
        
    $base_url     "http://".$_SERVER['SERVER_NAME']; 
        
    $directory     $_SERVER['PHP_SELF']; 
        
    $url_base "$base_url$directory"
        
    $url_base ereg_replace("$script"''"$_SERVER[PATH_TRANSLATED]"); 

        
    $path $url_base

        return 
    $path
      } 

      function 
    get_def($table) { 
        
    $def ""
        
    $def .= "DROP TABLE IF EXISTS $table;\n"
        
    $def .= "CREATE TABLE $table (\n"
        
    $result mysql_query("SHOW FIELDS FROM $table") or die("Table $table not existing in database"); 
        while(
    $row mysql_fetch_array($result)) { 
          
    $def .= "    $row[Field] $row[Type]"
          if (
    $row["Default"] != ""$def .= " DEFAULT '$row[Default]'"
          if (
    $row["Null"] != "YES"$def .= " NOT NULL"
          if (
    $row[Extra] != ""$def .= $row[Extra]"
          
    $def .= ",\n"
        } 
        
    $def ereg_replace(",\n$",""$def); 
        
    $result mysql_query("SHOW KEYS FROM $table"); 
        while(
    $row mysql_fetch_array($result)) { 
          
    $kname=$row[Key_name]; 
          if((
    $kname != "PRIMARY") && ($row[Non_unique] == 0)) $kname="UNIQUE|$kname"
          if(!isset(
    $index[$kname])) $index[$kname] = array(); 
          
    $index[$kname][] = $row[Column_name]; 
        } 
        while(list(
    $x$columns) = @each($index)) { 
          
    $def .= ",\n"
          if(
    $x == "PRIMARY"$def .= "   PRIMARY KEY (" implode($columns", ") . ")"
          else if (
    substr($x,0,6) == "UNIQUE"$def .= "   UNIQUE ".substr($x,7)." (" implode($columns", ") . ")"
          else 
    $def .= "   KEY $x (" implode($columns", ") . ")"
        } 
        
    $def .= "\n);"
        return (
    stripslashes($def)); 
      } 

      function 
    get_content($table) { 
        
    $content=""
        
    $result mysql_query("SELECT * FROM $table"); 
        while(
    $row mysql_fetch_row($result)) { 
          
    $insert "INSERT INTO $table VALUES ("
          for(
    $j=0$j<mysql_num_fields($result);$j++) { 
            if(!isset(
    $row[$j])) $insert .= "NULL,"
            else if(
    $row[$j] != ""$insert .= "'".addslashes($row[$j])."',"
            else 
    $insert .= "'',"
          } 
          
    $insert ereg_replace(",$","",$insert); 
          
    $insert .= ");\n"
          
    $content .= $insert
        } 
        return 
    $content
      } 

      
    /* Changelog 
       
         Version 1.0 - August 15th, 2003 
         =============================== 
         Created this beautiful script... 

      */ 
    ?>

  8. #8
    Join Date
    Nov 2001
    Posts
    551
    edit: next time I would try and read the original post and not recommend something they already know about. sorry

  9. #9
    Join Date
    May 2002
    Posts
    638
    foogee, instead of selecting all the tables in the database, is it possible to select some of them?

  10. #10
    Join Date
    May 2002
    Posts
    638

    *

    Originally posted by lobaloba9
    foogee, instead of selecting all the tables in the database, is it possible to select some of them?

  11. #11
    Join Date
    Sep 2003
    Posts
    125
    Originally posted by lobaloba9
    foogee, instead of selecting all the tables in the database, is it possible to select some of them?
    Yes it is. I modified the while loop after the show tables from sql query to test if a table is in the list of tables I want to back up.
    PHP Code:
      $result mysql_query("show tables from $dbname");
      while (list(
    $table) = mysql_fetch_row($result))
      {
        if ((
    $table=='IWantThisTable')||
            (
    $table=='IWantThisTable2')||
            (
    $table=='AndThis1')||
            (
    $table=='AndThis2')||
            (
    $table=='etc') )
        {
          
    $newfile .= get_def($table);
          
    $newfile .= "\n\n";
          
    $newfile .= get_content($table);
          
    $newfile .= "\n\n";
          
    $i++;
          if (
    $echo_status == 'yes') {
            print 
    "Dumped table $table<br>";
          }
        }
      } 
    Mike

Posting Permissions

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