Auto Backup Mysql Database Using PHP Script

Introduction:

Yesterday I lost my Mysql Database deleted by a wrong SQL query.  I tried lots of ways to recover it back. Nothing helped me. There is no much data inside it, but most of the data in it were important. The thing I learned yesterday was "Always Take Backup" The problem here is, I need to access the PHPMYADMIN every time to take backup of the database. I searched for tactics and finally I found a PHP Program which helps to take backup of MYSQL Database without accessing PHPMYADMIN. Today, I am going to share the program with you, and I am damn sure that, this will help you.

How to Taking Backup of MySQL Database Using PHP Script:

To perform the Auto-Mysql backup, you need full permission to the PHPMYADMIN and need access to change the file permission. Below I am going to share the script that helps you to perform the backup system. Remember, Playing with a primary database is not a real activity. Test the program with any demo database and workout.

<?php
    //ENTER THE RELEVANT INFO BELOW
    $mysqlUserName      = "Database Username";
    $mysqlPassword      = "Password Here";
    $mysqlHostName      = "Hostname Here";
    $DbName             = "Database Name Here";
    $backup_name        = "mybackup.sql";
    $tables             = array("asset_branch", "asset_category", "asset_products", "asset_user", "login", "message", "replace_app");

   //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=false );

    function Export_Database($host,$user,$pass,$name,  $tables=false, $backup_name=false )
    {
        $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) . "\n\n".$TableMLine[1].";\n\n";

            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 .="\n\n\n";
        }
        //$backup_name = $backup_name ? $backup_name : $name."___(".date('H-i-s')."_".date('d-m-Y').")__rand".rand(1,11111111).".sql";
        $date = date("Y-m-d");
        $backup_name = $backup_name ? $backup_name : $name.".$date.sql";
        header('Content-Type: application/octet-stream');
        header("Content-Transfer-Encoding: Binary");
        header("Content-disposition: attachment; filename=\"".$backup_name."\"");
        echo $content; exit;
    }
?>

• From the above code, you need to enter the Database information. Also, you need to specify the tables name which you need to perform the backup. By specifying the name of the table, you could backup selected tables inside the database.
•  $table is in an array format So, you could enter the name of the tables in it.
• The date format will add a date with the name of the backup. So, You could find backup as per date wise.