Fitur Backup database merupakan fitur yang sangat penting dalam engembangkan suatu sistem. Backup database diperlukan untuk membuat cadangan database ataupun untuk memperbarui database dengan data yang terbaru. Proses backup database sangat berguna saat ditemukan data yang rusak, korup, ataupun saat sedang terjadi perbaikan sistem. Backup database melalui PHP mempunyai kelebihan sendiri yaitu kita tidak harus menggunakan phpmyadmin. Yaitu cukup dengan menyediakan tombol download/backup pada suatu halaman, maka database dalam bentuk file sql akan terdownload. Karena tidak menggunakan phpmyadmin, kita tidak perlu mengakses cPanel hosting yang biasanya membutuhkan waktu akses yang lebih lama untuk sampai pada halaman phpmyadmin.
Proses backup database dengan PHP prosesnya hampir sama dengan proses export pada phpmyadmin. Yang mana code PHP akan menyalin struktur dan isi data dari tiap-tiap tabel pada database kemudian diubah dalam bentuk file sql. Isi dari file sql merupakan kumpulan SQL command untuk membuat tabel sekaligus mengisinya sesuai dengan data pada database sebelum proses backup terjadi. File tersebut dapat langsung digunakan dengan cara import pada phpmyadmin untuk pembaruan data.
Berikut Source Code untuk proses backup database.
1. Halaman Index.php
<!DOCTYPE html> <html> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"> <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css"> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.1.1/css/all.min.css"/> <style> body{ padding:10px 15px; } </style> <title>Backup / Export Database</title> </head> <body> <div class="jumbotron text-center"> <h3>Backup / Export Database</h3> </div> <div class="container"> <div class="row"> <div class="col-md-12"> <div class="card"> <div class="card-header bg-secondary"> <h4 class="card-title" style="color:white">Backup Database</h4> </div> <div class="card-body"> <p>Proses backup atau export database MySQL.</p> <p>Silahkan klik tombol backup untuk mengunduh file .sql hasil backup dari database</p> <p><a href="backup.php" class="btn btn-primary"><i class="fa fa-download"></i> Backup Database </a></p> </div> </div> </div> </div> </div> </body> <script src="https://cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.slim.min.js"></script> <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js"></script> <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/js/bootstrap.bundle.min.js"></script> </html>
2. Mendapatkan Daftar Tabel
//get table list $queryTables = $mysqli->query('SHOW TABLES'); while($row = $queryTables->fetch_row()) { $target_tables[] = $row[0]; }
3. Membuka Struktur Tabel Dan Mengambil Isi Data
//get table structure foreach($target_tables as $table) { $result = $mysqli->query('SELECT * FROM '.$table); $fields_amount = $result->field_count; $rows_num=$mysqli->affected_rows; $res = $mysqli->query('SHOW CREATE TABLE '.$table); $TableMLine =$res->fetch_row(); $content =(!isset($content) ? '' : $content) . "\n\n".$TableMLine[1].";\n"; for ($i = 0, $st_counter = 0; $i < $fields_amount; $i++, $st_counter=0) { while($row = $result->fetch_row()) { if ($st_counter%100 == 0 || $st_counter == 0 ) { $content .= "\nINSERT INTO ".$table." VALUES"; } $content .= "\n("; for($j=0; $j<$fields_amount; $j++) { $row[$j] = str_replace(array("\r\n\r\n","\n\r\n","\r\n","\n\n","\n"),array("\\r\\n","\\r\\n","\\r\\n","\\r\\n","\\r\\n"), addslashes($row[$j]) ); if (isset($row[$j])) { $content .= '"'.$row[$j].'"' ; } else { $content .= '""'; } if ($j<($fields_amount-1)) { $content.= ','; } } $content .=")"; //every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num) { $content .= ";"; } else { $content .= ","; } $st_counter=$st_counter+1; } } }
4. Menyimpan / Download SQL
// save as .sql file $content_="\n-- Database Backup --\n"; $content_ .="-- Ver. : 1.0.1\n"; $content_ .="-- Host : 127.0.0.1\n"; $content_ .="-- Generating Time : ".date("M d").", ".date("Y")." at ".date("H:i:s:").date("A")."\n"; $content_ .=$content; //save the file $backup_file_name = $db_name." ".date("Y-m-d H-i-s").".sql"; $fp = fopen($backup_file_name ,'w+'); $result = fwrite($fp, $content_); fclose($fp); //download file directly from browser $file_path = $backup_file_name; if(!empty($file_path) && file_exists($file_path)){ header("Pragma:public"); header("Expired:0"); header("Cache-Control:must-revalidate"); header("Content-Control:public"); header("Content-Description: File Transfer"); header("Content-Type: application/octet-stream"); header("Content-Disposition:attachment; filename=\"".basename($file_path)."\""); header("Content-Transfer-Encoding:binary"); header("Content-Length:".filesize($file_path)); flush(); readfile($file_path); exit(); }
Source Code Lengkap File backup.php
<?php //connect to DB $host = "localhost"; $root = "root"; $pass = ""; $db_name = "db_coba"; $mysqli = new mysqli($host,$root,$pass,$db_name); $mysqli->select_db($db_name); $mysqli->query("SET NAMES 'utf8'"); //get table list $queryTables = $mysqli->query('SHOW TABLES'); while($row = $queryTables->fetch_row()) { $target_tables[] = $row[0]; } //get table structure foreach($target_tables as $table) { $result = $mysqli->query('SELECT * FROM '.$table); $fields_amount = $result->field_count; $rows_num=$mysqli->affected_rows; $res = $mysqli->query('SHOW CREATE TABLE '.$table); $TableMLine =$res->fetch_row(); $content =(!isset($content) ? '' : $content) . "\n\n".$TableMLine[1].";\n"; for ($i = 0, $st_counter = 0; $i < $fields_amount; $i++, $st_counter=0) { while($row = $result->fetch_row()) { if ($st_counter%100 == 0 || $st_counter == 0 ) { $content .= "\nINSERT INTO ".$table." VALUES"; } $content .= "\n("; for($j=0; $j<$fields_amount; $j++) { $row[$j] = str_replace(array("\r\n\r\n","\n\r\n","\r\n","\n\n","\n"),array("\\r\\n","\\r\\n","\\r\\n","\\r\\n","\\r\\n"), addslashes($row[$j]) ); if (isset($row[$j])) { $content .= '"'.$row[$j].'"' ; } else { $content .= '""'; } if ($j<($fields_amount-1)) { $content.= ','; } } $content .=")"; //every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num) { $content .= ";"; } else { $content .= ","; } $st_counter=$st_counter+1; } } } // save as .sql file //give additional description $content_="\n-- Database Backup --\n"; $content_ .="-- Ver. : 1.0.1\n"; $content_ .="-- Host : 127.0.0.1\n"; $content_ .="-- Generating Time : ".date("M d").", ".date("Y")." at ".date("H:i:s:").date("A")."\n"; $content_ .=$content; //save the file $backup_file_name = $db_name." ".date("Y-m-d H-i-s").".sql"; $fp = fopen($backup_file_name ,'w+'); $result = fwrite($fp, $content_); fclose($fp); //download file directly from browser $file_path = $backup_file_name; if(!empty($file_path) && file_exists($file_path)){ header("Pragma:public"); header("Expired:0"); header("Cache-Control:must-revalidate"); header("Content-Control:public"); header("Content-Description: File Transfer"); header("Content-Type: application/octet-stream"); header("Content-Disposition:attachment; filename=\"".basename($file_path)."\""); header("Content-Transfer-Encoding:binary"); header("Content-Length:".filesize($file_path)); flush(); readfile($file_path); exit(); } ?>
Pengujian
Halaman Utama
Hasil Download
Demikian tutorial backup database dengan PHP, semoga bermanfaat.
0 Komentar