How to backup and download Database using PHP

Learn How to backup and download Database using PHP. Most of the time we develop some applications, the most important thing is our database.
And most of people need the download database option inside their application for taking a backup.

creating database backup Dynamically with PHP program will be hand in many situations. We can download the backup process periodically.
Taking the mysql database backup using PHP programming is Simple. we are going to learn how to backup MySQL database as .sql with a simple core PHP code.

First, I get the database connection object to get the table schema and data to be dumped into a file. I read all the table names from the database and store into an array.

In this tutorial, we are going to learn how we can make a database backup with PHP. This will work with PHP 7 and higher versions of it

I wrote a function for export database here it is

<?php
    
    $mysqlUserName      = 'databaseusername';
    $mysqlPassword      = 'databasepassword';
    $mysqlHostName      = 'localhost';
    $DbName             = 'mydatabasename';
   
    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 = $name.".sql";
        header('Content-Type: application/octet-stream');   
        header("Content-Transfer-Encoding: Binary"); 
        header("Content-disposition: attachment; filename=\"".$backup_name."\"");  
        echo $content; exit;
    }



?>

 



Woot. that’s all.. just run this function and your database will download as .SQL file

checkout more PHP related tutorials 

 

Download Source Code : 

Post Your Questions on our forum

Post a question on Forum

Ajith Jojo Joseph

Self taught, dedicated young entrepreneur with many licensed products under his sleeve. Passionate about technology, business and excellence in general.

Share with your friends:

Comments are closed.

How to get the next value of an array and loop it in the array in PHP

Do you know How to get the next value of an array and loop it in the array in PHP […]

July 28, 2023

New Open Source CRM for project Management and Invoicing

I’m excited to announce the launch of my new open source project: Gmax CRM. an invoicing and project management tool […]

December 31, 2022

Post View Counts WordPress plugin (Documentation)

The Post View Counts plugin is a simple tool for tracking and displaying the number of views for each post […]

December 30, 2022

How to create Laravel Flash Messages

Laravel flash messages are a convenient way to display one-time notifications to the user after a form submission or other […]

December 22, 2022

Digital Marketing Toolkit

Get Free Access to Digital Marketing Toolkit. You can use all our tools without any limits

Get Free Access Now