Tutorial PHP

Auto Backup Database MySql dengan PHP Tanpa Membuka PhpMyadmin

Backup Database MySql dengan PHP MySql

Tutorial Backup Database MySql dengan PHP MySql ini akan menjelaskan cara membackup database mysql tanpa harus masuk ke phpmyadmin, dengan menggunakan PHP. Develindo.com

Perhatikan script dibawah ini.

<?php
    //anda hanya perlu merubah di konfigurasi ke database dibawah ini
    $mysqlUserName      = "root";
    $mysqlPassword      = "";
    $mysqlHostName      = "localhost";
    $DbName             = "nama_database_anda";
    $backup_name        = $DbName.'_'.date("d-m-Y").'_'.date("H:i:s").'.sql';

    $tables             = "*";

   //or add 5th parameter(array) of specific tables:    array("mytable1","mytable2","mytable3") for multiple tables

    Export_Database($mysqlHostName,$mysqlUserName,$mysqlPassword,$DbName,  $tables=false, $backup_name);

    function Export_Database($host,$user,$pass,$name,  $tables=false, $backup_name)
    {
        $mysqli = new mysqli($host,$user,$pass,$name);
        $mysqli->select_db($name);
        $mysqli->query("SET NAMES 'utf8'");

        $queryTables    = $mysqli->query('SHOW TABLES');
        while($row = $queryTables->fetch_row())
        {
            $target_tables[] = $row[0];
        }
        if($tables !== false)
        {
            $target_tables = array_intersect( $target_tables, $tables);
        }
        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) . "nn".$TableMLine[1].";nn";

            for ($i = 0, $st_counter = 0; $i < $fields_amount;   $i++, $st_counter=0)
            {
                while($row = $result->fetch_row())
                { //when started (and every after 100 command cycle):
                    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("n","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;
                }
            } $content .="nnn";
        }
        //$backup_name = $backup_name ? $backup_name : $name."___(".date('H-i-s')."_".date('d-m-Y').")__rand".rand(1,11111111).".sql";
        $backup_name = $backup_name ? $backup_name : $name.".sql";
        header('Content-Type: application/octet-stream');
        header("Content-Transfer-Encoding: Binary");
        header("Content-disposition: attachment; filename="".$backup_name.""");
        echo $content; exit;
    }
?>

 

Script diatas dapat anda terapkan di website anda, dapat digunakan dengan menggunakan elemen <a href=""></a> untuk menuju file tersebut. Hasil export database akan terdownload dengan format .sql


selamat mencoba..
Terimakasih

Download Source Code

Develindo Web | Tutorial Pemrograman Website