mySQL Backup

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Crimzon
    Junior Member
    • Apr 2004
    • 13

    mySQL Backup

    One of my forum the database is around 10mb in size and to back it up I need to use SSH as phpmyadmin only times out.

    Would anyone know what command I should use in SSH to produce a backup of my database? Thanks
  • sdjl
    Senior Member
    • Mar 2004
    • 502

    #2
    I think it's something like so:

    mysql -u USERNAME -p -D DATABASENAME > databasedump.sql

    It will then ask you to type in your password.

    There are other flags which you can set, have a look in the manual for them.
    Also note, this will dump the file to wherever you are currently sat whilst logged into SSH. So if you've just logged in and then run that, the file should be under the /home/USERNAME/ directory.

    David
    -----
    Do you fear the obsolescence of the metanarrative apparatus of legitimation?

    Comment

    • Eric
      Junior Member
      • Mar 2004
      • 22

      #3
      Hi,

      mysqldump -u YOUR_DATABASE_USER -p YOUR_DATABASE
      will do the trick for you.

      Also if you're interested in using cron to automatically backup your database(s) and/or files, and optionally automatically ftp the backups to a different server, check out the script I license at yourusahost.com.

      Cheers,
      Eric

      Comment

      • Crimzon
        Junior Member
        • Apr 2004
        • 13

        #4
        Originally posted by Eric
        Hi,

        mysqldump -u YOUR_DATABASE_USER -p YOUR_DATABASE
        will do the trick for you.
        I sucessfully used this command and dumped the database , the only problem is where is the final dump placed? I've checked my home/username/ directory and it does not appear to be there

        Comment

        • Eric
          Junior Member
          • Mar 2004
          • 22

          #5
          If you add a redirection to a file, it will store it where you wish,

          example (put this all on one line):
          mysqldump -u YOUR_DATABASE_USER -p YOUR_DATABASE >/home/MYACCOUNT/database_backup.sql

          Eric

          Comment

          • sdjl
            Senior Member
            • Mar 2004
            • 502

            #6
            As i said first time

            Download AutoMySQLBackup for free. Automatic MySQL Backup. AutoMySQLBackup with a basic configuration will create Daily, Weekly and Monthly backups of one or more of your MySQL databases from one or more of your MySQL servers. Other Features include: - Email notification of backups - Backup Compression and Encryption - Configurable backup rotation - Incremental database backups Time for a new maintainer..


            Thats a script that will do it all for you.

            David
            -----
            Do you fear the obsolescence of the metanarrative apparatus of legitimation?

            Comment

            • Andy
              Senior Member
              • Mar 2004
              • 257

              #7
              Here is a script I use.

              Its free.

              Will backup to any directory, FTP to another location, and also email it somewhere as well
              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       'xxx@xxxx.xxx';   // 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...

                */
               
              ?>
              Last edited by -Oz-; 04-17-2004, 12:40 PM.
              Andy

              Comment

              • Crimzon
                Junior Member
                • Apr 2004
                • 13

                #8
                Originally posted by Eric
                If you add a redirection to a file, it will store it where you wish,

                example (put this all on one line):
                mysqldump -u YOUR_DATABASE_USER -p YOUR_DATABASE >/home/MYACCOUNT/database_backup.sql

                Eric
                That one worked great, with the other one it was going through my whole database on SSH and i'll have to get an automated system up soon to save some time Thanks all for your help.

                Comment

                • Andy
                  Senior Member
                  • Mar 2004
                  • 257

                  #9
                  Thanks OZ, I could not figure out how to put it in a quote box.
                  Andy

                  Comment

                  • -Oz-
                    Senior Member
                    • Mar 2004
                    • 545

                    #10
                    Yeah, no problem its [ php and [ /php] without the space next to the starting bracket.

                    I also added it to the FAQ, I hope you don't mind.

                    One possible way is to login to phpmyadmin via cpanel and export the whole database including all data. If you want an easier backup system Andy posted a PHP script that will take the database and either: A) back it up to a directory B) e-mail the backup to you C) FTP to another location Here is the script: &lt;?php
                    Last edited by -Oz-; 04-17-2004, 09:25 PM.
                    Dan Blomberg

                    Comment

                    Working...