Backup Database MySQL Menggunakan Script PHP

Sobatcoding.com - Backup Database MySQL Menggunakan Script PHP

Pada artikel kali ini kita akan mencoba membuat script PHP untuk backup database Mysql ke dalam format Gzip. GZIP adalah format file yang terbentuk karena proses kompresi dari suatu file agar ukurannya lebih kecil. Sebelumnya admin sudah mempunyai source function untuk backup database yang admin dapat dari salah satu sumber. Fungsi ini bisa digunakan untuk backup database menggunakan PHP atayupun framework Codeigniter.

Langsung saja coba kita aplikasikan source codenya.

Untuk source code backup database php kurang lebih seperti berikut, kita bisa namakan dengan nama backup.php.

<?php
  // set default timezone
  date_default_timezone_set("ASIA/JAKARTA");

  /**
   * mysql EXPORT TO GZIP 
   * exporting database to sql gzip compression data.
   * if directory writable will be make directory inside of directory if not exist, else wil be die
   *
   * @param string directory , as the directory to put file
   * @param $outname as file name just the name !, if file exist will be overide as numeric next ++ as name_1.sql.gz , name_2.sql.gz next ++
   *
   * @param string $dbhost database host
   * @param string $dbuser database user
   * @param string $dbpass database password
   * @param string $dbname database name
   *
   */
  function backup_database( $directory, $outname , $dbhost, $dbuser, $dbpass ,$dbname ) {

    // check mysqli extension installed
    if( ! function_exists('mysqli_connect') ) {
    die(' This scripts need mysql extension to be running properly ! please resolve!!');
    }

    $mysqli = @new mysqli($dbhost, $dbuser, $dbpass, $dbname);

    if( $mysqli->connect_error ) {
      print_r( $mysqli->connect_error );
      return false;
    }

    $dir = $directory;
    $result = '<p> Could not create backup directory on :'.$dir.' Please Please make sure you have set Directory on 755 or 777 for a while.</p>';  
    $res = true;
    if( ! is_dir( $dir ) ) {
      if( ! @mkdir( $dir, 755 )) {
        $res = false;
      }
    }

    $n = 1;
    if( $res ) {

      $name     = $outname;
      # counts
      if( file_exists($dir.'/'.$name.'.sql.gz' ) ) {

        for($i=1;@count( file($dir.'/'.$name.'_'.$i.'.sql.gz') );$i++){
          $name = $name;
          if( ! file_exists( $dir.'/'.$name.'_'.$i.'.sql.gz') ) {
            $name = $name.'_'.$i;
            break;
          }
        }
      }

      $fullname = $dir.'/'.$name.'.sql.gz'; # full structures

      if( ! $mysqli->error ) {
        $sql = "SHOW TABLES";
        $show = $mysqli->query($sql);
        while ( $r = $show->fetch_array() ) {
          $tables[] = $r[0];
        }

        if( ! empty( $tables ) ) {

          //cycle through
          $return = '';
          foreach( $tables as $table )
          {
            $result     = $mysqli->query('SELECT * FROM '.$table);
            $num_fields = $result->field_count;
            $row2       = $mysqli->query('SHOW CREATE TABLE '.$table );

            $row2       = $row2->fetch_row();
            $return    .= 
            "\n
            -- ---------------------------------------------------------
            --
            -- Table structure for table : `{$table}`
            --
            -- ---------------------------------------------------------

            ".$row2[1].";\n";

            for ($i = 0; $i < $num_fields; $i++) 
            {

              $n = 1 ;
              while( $row = $result->fetch_row() )
              { 
      

                if( $n++ == 1 ) { # set the first statements
                  $return .= 
                    "
                    --
                    -- Dumping data for table `{$table}`
                    --

                    ";  
                  /**
                   * Get structural of fields each tables
                   */
                  $array_field = array(); #reset ! important to resetting when loop 
                  while( $field = $result->fetch_field() ) # get field
                  {
                    $array_field[] = '`'.$field->name.'`';
                    
                  }
                  $array_f[$table] = $array_field;
                  // $array_f = $array_f;
                  # endwhile
                  $array_field = implode(', ', $array_f[$table]); #implode arrays

                    $return .= "INSERT INTO `{$table}` ({$array_field}) VALUES\n(";
                } else {
                    $return .= '(';
                }
                for($j=0; $j<$num_fields; $j++) 
                {
                  
                  $row[$j] = str_replace('\'','\'\'', preg_replace("/\n/","\\n", $row[$j] ) );
                  if ( isset( $row[$j] ) ) { $return .= is_numeric( $row[$j] ) ? $row[$j] : '\''.$row[$j].'\'' ; } else { $return.= '\'\''; }
                  if ($j<($num_fields-1)) { $return.= ', '; }
                }
                $return.= "),\n";
              }
              # check matching
              @preg_match("/\),\n/", $return, $match, false, -3); # check match
              if( isset( $match[0] ) )
              {
                $return = substr_replace( $return, ";\n", -2);
              }

            }

            $return .= "\n";

          }

          // "-- ---------------------------------------------------------
          // --
          // -- SIMPLE SQL Dump
          // -- 
          // --
          // -- Host Connection Info: ".$mysqli->host_info."
          // -- Generation Time: ".date('F d, Y \a\t H:i A ( e )')."
          // -- Server version: ".mysql_get_server_info()."
          // -- PHP Version: ".PHP_VERSION."
          // --
          // -- ---------------------------------------------------------\n\n

          // SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";
          // SET time_zone = \"+00:00\";


          // /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
          // /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
          // /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
          // /*!40101 SET NAMES utf8 */;
          // ".$return."
          // /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
          // /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
          // /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;";

          $return = 
          "-- ---------------------------------------------------------
          --
          -- SIMPLE SQL Dump
          -- 
          -- nawa (at) yahoo (dot) com
          --
          -- Host Connection Info: ".$mysqli->host_info."
          -- Generation Time: ".date('F d, Y \a\t H:i A ( e )')."
          -- PHP Version: ".PHP_VERSION."
          --
          -- ---------------------------------------------------------\n\n

          SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";
          SET time_zone = \"+00:00\";


          /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
          /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
          /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
          /*!40101 SET NAMES utf8 */;
          ".$return."
          /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
          /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
          /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;";

          # end values result

          @ini_set('zlib.output_compression','Off');
          $gzipoutput = gzencode( $return, 9);

          if(  @ file_put_contents( $fullname, $gzipoutput  ) ) { # 9 as compression levels

          $result = $name.'.sql.gz'; # show the name

          } else { # if could not put file , automaticly you will get the file as downloadable

            $result = false;   
            // various headers, those with # are mandatory
            header('Content-Type: application/x-gzip'); // change it to mimetype
            header("Content-Description: File Transfer");
            header('Content-Encoding: gzip'); #
            header('Content-Length: '.strlen( $gzipoutput ) ); #
            header('Content-Disposition: attachment; filename="'.$name.'.sql.gz'.'"');
            header('Cache-Control: no-cache, no-store, max-age=0, must-revalidate');
            header('Connection: Keep-Alive');
            header("Content-Transfer-Encoding: binary");
            header('Expires: 0');
            header('Pragma: no-cache');

            echo $gzipoutput;

          }

        } else {

          $result = '<p>Error when executing database query to export.</p>'.$mysqli->error;
              
          }
      }

    } else {
      $result = '<p>Wrong mysqli input</p>';
    }

    if( $mysqli && ! $mysqli->error ) {
      @$mysqli->close();
    }

    return $result;

  }

Backup Database Menggunakan Script PHP

Untuk implementasi di php kita bisa buat sebuah file bernama index.html, karena kita akan membuat backup menggunakan Ajax jadi kita tambahkan juga Jquery melalui cdn. Perhatikan  kode berikut:

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Backup Database</title>
    </head>
    <body>
        <button id="backupdb"> Klik Untuk Backup DB</button>

        <script src="https://code.jquery.com/jquery-3.6.3.min.js" integrity="sha256-pvPw+upLPUjgMXY0G+8O0xUf+/Im1MZjXxxgOcBQBXU=" crossorigin="anonymous"></script>

        <script>
            $(document).ready(function(e) {
                $(document).on('click', '#backupdb', function(e) {
                    $.get('dobackup.php', function(result, success) {
                        $('.message').remove();
                        $('#backupdb').after('<p class="message"> Database berhasil di backup : '+ result +'</p>')
                    })
                })
            })
        </script>
    </body>
</html>

Kemudian buat sebuah file bernama dobackup.php dan masukkan kode berikut.

<?php

include("./backupdb.php");

$directory = __DIR__; //nama directory tempat menyimpan file backup
$outname = 'backupdb_'. time(); //nama file backup
$dbhost = 'localhost'; //nama host
$dbuser = 'root'; //username
$dbpass= ''; //password
$dbname = 'db_klinik'; //nama database

$backup = backup_database($directory, $outname , $dbhost, $dbuser, $dbpass ,$dbname);

echo $backup;

Untuk hasil tampilan di atas adalah sebagai berikut.

Backup Database MySQL Menggunakan Framework Codeigniter

Untuk implementasi di codeigniter 3 kita buat terlebih dahulu sebuah helper bernama backup_helper.php dan copy isi source code dari file backup.php.

Langkah selanjutnya buatlah sebuah controller bernama backupcontroller.php dan masukkan kode berikut.

<?php

class BackupController extends CI_Controller {
    public function index() {
       $this->load->view('upload');
    }
    
    public function backUpDb()
    {
       
        $date = gmdate("Ymd_His", time()+60*60*7);  // waktu backup
        $dir  = FCPATH . "database/";                   // direktori file hasil backup
        $name = $date.'_database';                  // nama file sql hasil backup
        
        // jalankan perintah backup database
        $backup = backup_database( $dir, $name, $this->db->hostname, $this->db->username,$this->db->password, $this->db->database);

        // mengecek proses backup database
        // jika backup database berhasil
        if ($backup) {
            // siapkan "data"
            $nama_file    = $name.".sql.gz";
           
         return $this->output
                ->set_content_type('application/json')
                 ->set_output(json_encode(['success' => true , 'message' => 'Database berhasil dibackup di folder '. $dir. $nama_file ]));
      
        }else{
          return $this->output
                ->set_content_type('application/json')
                 ->set_output(json_encode(['success' => false , 'message' => 'Database gagal dibackup ' ]));
        }
    }
}

Kemudian buat view bernama upload.php. Kalian bisa copy source code view dari file index.html yang kita buat di atas sebelumnya.

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Backup Database</title>
    </head>
    <body>
        <button id="backupdb"> Klik Untuk Backup DB</button>

        <script src="https://code.jquery.com/jquery-3.6.3.min.js" integrity="sha256-pvPw+upLPUjgMXY0G+8O0xUf+/Im1MZjXxxgOcBQBXU=" crossorigin="anonymous"></script>

        <script>
            $(document).ready(function(e) {
                $(document).on('click', '#backupdb', function(e) {
                    $.get('dobackup.php', function(result, success) {
                        $('.message').remove();
                        $('#backupdb').after('<p class="message">'+ result +'</p>')
                    })
                })
            })
        </script>
    </body>
</html>

Semoga bermanfaat.

Jika teman-teman memiliki pertanyaan atau saran mengenai artikel ini, jangan ragu untuk meninggalkan komentar pada form di bawah