How to backup/export the connected Database database.sql file in laravel?

Question

I have viewed all the topics available for this question , but not getting any acurate answer... anybody got clear idea to backup the current database in .sql file and download it ?

Use your Database editor (MySQLWorkBench, PHPMyAdmin, etc.) and do an export. — Tim Lewis, Oct 02 '19 at 18:21
go to phpMyAdmin, click on export and then go. you will get your database in `.sql` file. — zahid hasan emon, Oct 02 '19 at 18:23
There is an excellent [package by spatie](https://github.com/spatie/laravel-backup) as well if you are interested. Can automate and dump folders as well pretty easily. — Watercayman, Oct 02 '19 at 18:24
database will be exported from database management panel like from `phpmyadmin` select database then export database there you will get options of format select which you require, and export it. — NomanJaved, Oct 02 '19 at 19:25

score 11 · Answer 1 · answered Dec 14 '19 at 14:47

// Code

public function our_backup_database(){

        //ENTER THE RELEVANT INFO BELOW
        $mysqlHostName      = env('DB_HOST');
        $mysqlUserName      = env('DB_USERNAME');
        $mysqlPassword      = env('DB_PASSWORD');
        $DbName             = env('DB_DATABASE');
        $backup_name        = "mybackup.sql";
        $tables             = array("users","messages","posts"); //here your tables...

        $connect = new \PDO("mysql:host=$mysqlHostName;dbname=$DbName;charset=utf8", "$mysqlUserName", "$mysqlPassword",array(\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
        $get_all_table_query = "SHOW TABLES";
        $statement = $connect->prepare($get_all_table_query);
        $statement->execute();
        $result = $statement->fetchAll();


        $output = '';
        foreach($tables as $table)
        {
         $show_table_query = "SHOW CREATE TABLE " . $table . "";
         $statement = $connect->prepare($show_table_query);
         $statement->execute();
         $show_table_result = $statement->fetchAll();

         foreach($show_table_result as $show_table_row)
         {
          $output .= "\n\n" . $show_table_row["Create Table"] . ";\n\n";
         }
         $select_query = "SELECT * FROM " . $table . "";
         $statement = $connect->prepare($select_query);
         $statement->execute();
         $total_row = $statement->rowCount();

         for($count=0; $count<$total_row; $count++)
         {
          $single_result = $statement->fetch(\PDO::FETCH_ASSOC);
          $table_column_array = array_keys($single_result);
          $table_value_array = array_values($single_result);
          $output .= "\nINSERT INTO $table (";
          $output .= "" . implode(", ", $table_column_array) . ") VALUES (";
          $output .= "'" . implode("','", $table_value_array) . "');\n";
         }
        }
        $file_name = 'database_backup_on_' . date('y-m-d') . '.sql';
        $file_handle = fopen($file_name, 'w+');
        fwrite($file_handle, $output);
        fclose($file_handle);
        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename=' . basename($file_name));
        header('Content-Transfer-Encoding: binary');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
           header('Pragma: public');
           header('Content-Length: ' . filesize($file_name));
           ob_clean();
           flush();
           readfile($file_name);
           unlink($file_name);


}

// routing

Route::get('/our_backup_database', 'YourControllerController@our_backup_database')->name('our_backup_database');

//View

        <form action="{{ route('our_backup_database') }}" method="get">
            <button style="submit" class="btn btn-primary"> download</button>
        </form>

Thanks for the great idea, I've modified your code above to use Laravel's existing DB connection, no need to re-enter credentials or use PDO extension if it's not installed (in my case it wasn't) — Amin, Apr 01 '20 at 19:42
sir this is working offline correctly. but online it is not working — Naeem Ijaz, Feb 03 '21 at 07:55

Amin · Answer 2 · 2020-05-22T15:44:05.950

I've tweaked @Abdelhakim Ezzahouri's code, to use Laravel's existing connection to DB instead of connecting again, entering credentials, and installing PDO, if it's not installed already.

Route::get('db_dump', function () {
    /*
    Needed in SQL File:

    SET GLOBAL sql_mode = '';
    SET SESSION sql_mode = '';
    */
    $get_all_table_query = "SHOW TABLES";
    $result = DB::select(DB::raw($get_all_table_query));

    $tables = [
        'admins',
        'migrations',
    ];

    $structure = '';
    $data = '';
    foreach ($tables as $table) {
        $show_table_query = "SHOW CREATE TABLE " . $table . "";

        $show_table_result = DB::select(DB::raw($show_table_query));

        foreach ($show_table_result as $show_table_row) {
            $show_table_row = (array)$show_table_row;
            $structure .= "\n\n" . $show_table_row["Create Table"] . ";\n\n";
        }
        $select_query = "SELECT * FROM " . $table;
        $records = DB::select(DB::raw($select_query));

        foreach ($records as $record) {
            $record = (array)$record;
            $table_column_array = array_keys($record);
            foreach ($table_column_array as $key => $name) {
                $table_column_array[$key] = '`' . $table_column_array[$key] . '`';
            }

            $table_value_array = array_values($record);
            $data .= "\nINSERT INTO $table (";

            $data .= "" . implode(", ", $table_column_array) . ") VALUES \n";

            foreach($table_value_array as $key => $record_column)
                $table_value_array[$key] = addslashes($record_column);

            $data .= "('" . implode("','", $table_value_array) . "');\n";
        }
    }
    $file_name = __DIR__ . '/../database/database_backup_on_' . date('y_m_d') . '.sql';
    $file_handle = fopen($file_name, 'w + ');

    $output = $structure . $data;
    fwrite($file_handle, $output);
    fclose($file_handle);
    echo "DB backup ready";
});

add `wordwrap()` to wrap the data like this: `$data .= "('" . wordwrap(implode("','", $table_value_array),400,"\n",TRUE) . "');\n";`. if the data gets too long that exceeded the limit of a line, it will be truncated and resulting error when you try to import it back into your database — Idris Akbar Adyusman, Nov 11 '20 at 20:09

yemenpoint · Answer 3 · 2020-07-17T03:38:29.927

Route::get('/backupdb', function () {
    $DbName             = env('DB_DATABASE');
    $get_all_table_query = "SHOW TABLES ";
    $result = DB::select(DB::raw($get_all_table_query));

    $prep = "Tables_in_$DbName";
    foreach ($result as $res){
        $tables[] =  $res->$prep;
    }



    $connect = DB::connection()->getPdo();

    $get_all_table_query = "SHOW TABLES";
    $statement = $connect->prepare($get_all_table_query);
    $statement->execute();
    $result = $statement->fetchAll();


    $output = '';
    foreach($tables as $table)
    {
        $show_table_query = "SHOW CREATE TABLE " . $table . "";
        $statement = $connect->prepare($show_table_query);
        $statement->execute();
        $show_table_result = $statement->fetchAll();

        foreach($show_table_result as $show_table_row)
        {
            $output .= "\n\n" . $show_table_row["Create Table"] . ";\n\n";
        }
        $select_query = "SELECT * FROM " . $table . "";
        $statement = $connect->prepare($select_query);
        $statement->execute();
        $total_row = $statement->rowCount();

        for($count=0; $count<$total_row; $count++)
        {
            $single_result = $statement->fetch(\PDO::FETCH_ASSOC);
            $table_column_array = array_keys($single_result);
            $table_value_array = array_values($single_result);
            $output .= "\nINSERT INTO $table (";
            $output .= "" . implode(", ", $table_column_array) . ") VALUES (";
            $output .= "'" . implode("','", $table_value_array) . "');\n";
        }
    }
    $file_name = 'database_backup_on_' . date('y-m-d') . '.sql';
    $file_handle = fopen($file_name, 'w+');
    fwrite($file_handle, $output);
    fclose($file_handle);
    header('Content-Description: File Transfer');
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename=' . basename($file_name));
    header('Content-Transfer-Encoding: binary');
    header('Expires: 0');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');
    header('Content-Length: ' . filesize($file_name));
    ob_clean();
    flush();
    readfile($file_name);
    unlink($file_name);

});

Please fix the code formatting and explain why your answer is different and better than others above and also voted higher — Mrinal Roy, Jul 17 '20 at 03:31
What I do like about this answer is that it actually exports all tables and not only two constant ones as in @Amins answer. — Martin Eckleben, Dec 03 '20 at 09:02

Hamza Qureshi · Answer 4 · 2021-01-25T19:05:48.070

one of the best solution

fisrt of all install spatie on local server https://spatie.be/docs/laravel-backup/v6/installation-and-setup
now check backup working or not php artisan backup:run
if backup success then u can also take it on cpanel for this go ro app/Console/Kernal.php and register this command
```
 protected function schedule(Schedule $schedule)
 {
     $schedule->command('backup:run')->everyMinute();
 }
```

now open your cpanel and open cron job and there register this command

/usr/local/bin/php /home/replaceyourdirectoryname/public_html/artisan backup:run > /dev/null 2>&1

now you are successful able to take backup on live server
now for on clcik download

<a href="{{ route('download') }}" class="btn btn-sm btn-primary"><i class="fa fa-download"></i>Download Backup</a>

register route Route::get('/download/','DownloadController@download')->name('download');

and finally controller

<?php
 namespace App\Http\Controllers;
 use Illuminate\Support\Facades\Artisan;
 class DownloadController extends Controller{


 public function download(){

     Artisan::call('backup:run');
     $path = storage_path('app/laravel-backup/*');
     $latest_ctime = 0;
     $latest_filename = '';
     $files = glob($path);
     foreach($files as $file)
     {
             if (is_file($file) && filectime($file) > $latest_ctime)
             {
                     $latest_ctime = filectime($file);
                     $latest_filename = $file;
             }
     }
     return response()->download($latest_filename);
 }

}

score 1 · Answer 5 · answered Apr 27 '21 at 14:25

Thanks for @Abdelhakim Ezzahraoui for your solution. i have just change some code. so here you can backup all table. but if you want specific then you set that table name.

function backupDatabase()
   {
       //ENTER THE RELEVANT INFO BELOW
       $mysqlHostName      = env('DB_HOST');
       $mysqlUserName      = env('DB_USERNAME');
       $mysqlPassword      = env('DB_PASSWORD');
       $DbName             = env('DB_DATABASE');
       $file_name = 'database_backup_on_' . date('y-m-d') . '.sql';


       $queryTables = \DB::select(\DB::raw('SHOW TABLES'));
        foreach ( $queryTables as $table )
        {
            foreach ( $table as $tName)
            {
                $tables[]= $tName ;
            }
        }
      // $tables  = array("users","products","categories"); //here your tables...

       $connect = new \PDO("mysql:host=$mysqlHostName;dbname=$DbName;charset=utf8", "$mysqlUserName", "$mysqlPassword",array(\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
       $get_all_table_query = "SHOW TABLES";
       $statement = $connect->prepare($get_all_table_query);
       $statement->execute();
       $result = $statement->fetchAll();
       $output = '';
       foreach($tables as $table)
       {
           $show_table_query = "SHOW CREATE TABLE " . $table . "";
           $statement = $connect->prepare($show_table_query);
           $statement->execute();
           $show_table_result = $statement->fetchAll();

           foreach($show_table_result as $show_table_row)
           {
               $output .= "\n\n" . $show_table_row["Create Table"] . ";\n\n";
           }
           $select_query = "SELECT * FROM " . $table . "";
           $statement = $connect->prepare($select_query);
           $statement->execute();
           $total_row = $statement->rowCount();

           for($count=0; $count<$total_row; $count++)
           {
               $single_result = $statement->fetch(\PDO::FETCH_ASSOC);
               $table_column_array = array_keys($single_result);
               $table_value_array = array_values($single_result);
               $output .= "\nINSERT INTO $table (";
               $output .= "" . implode(", ", $table_column_array) . ") VALUES (";
               $output .= "'" . implode("','", $table_value_array) . "');\n";
           }
       }

       $file_handle = fopen($file_name, 'w+');
       fwrite($file_handle, $output);
       fclose($file_handle);
       header('Content-Description: File Transfer');
       header('Content-Type: application/octet-stream');
       header('Content-Disposition: attachment; filename=' . basename($file_name));
       header('Content-Transfer-Encoding: binary');
       header('Expires: 0');
       header('Cache-Control: must-revalidate');
       header('Pragma: public');
       header('Content-Length: ' . filesize($file_name));
       ob_clean();
       flush();
       readfile($file_name);
       unlink($file_name);
   }

score 1 · Answer 6 · answered Jul 25 '21 at 09:39

1

If you wanted to download backup on the fly, use this code after your backup content.

$headers = [
        'Content-Disposition' => sprintf('attachment; filename="%s"', 'backup.sql'),
    ];

    return response()->make($output, 200, $headers);

answered Jul 25 '21 at 09:39

Muhammad Saifullah Muhammad Ja

75
1
7

score 0 · Answer 7 · answered Apr 13 '22 at 16:23

Using laravel export database backup

Here create a function for db backup

public function serverDBBackup()
    {
        
        try {

            $database = config('app.db');

            // dd(config('app.db'));
            $user = config('app.dbuser');
            $pass = config('app.dbpass');
            $host = config('app.dbhost');
            $dir = 'server_db_backup.sql';

            try {
                unlink($dir);
            } catch (\Throwable $th) {
            }

            // echo "<h3>Backing up database to `<code>{$dir}</code>`</h3>";
            // mysqldump -u [user name] –p [password] [options] [database_name] [tablename] > [dumpfilename.sql]
            // --add-drop-database --databases 
            // mysqldump --user=root --password=bismib_fashion@_mysql --host=localhost --events --routines --triggers elaravel_v2 --result-file=db_backup_new.sql 2>&1
            exec("mysqldump  --user={$user} --password={$pass} --host={$host} --events --routines --triggers  {$database}  --result-file={$dir} 2>&1", $output);

            $tableViewsCounts = DB::select('SELECT count(TABLE_NAME) AS TOTALNUMBEROFTABLES FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ?', [$database]);
            $tableViewsCounts = $tableViewsCounts[0]->TOTALNUMBEROFTABLES;
            
            $viewsCounts = DB::select('SELECT count(TABLE_NAME) AS TOTALNUMBEROFVIEWS FROM INFORMATION_SCHEMA.TABLES WHERE  TABLE_TYPE LIKE "VIEW" AND TABLE_SCHEMA = ?', [$database]);
            $viewsCounts = $viewsCounts[0]->TOTALNUMBEROFVIEWS;

            $tablesCount = $tableViewsCounts-$viewsCounts;


            $proceduresCounts = DB::select('SELECT count(TYPE) AS proceduresCounts FROM mysql.proc WHERE  TYPE="PROCEDURE" AND db = ?', [$database]);
            $proceduresCounts = $proceduresCounts[0]->proceduresCounts;

            $functionsCounts = DB::select('SELECT count(TYPE) AS functionsCounts FROM mysql.proc WHERE  TYPE="FUNCTION" AND db = ?', [$database]);
            $functionsCounts = $functionsCounts[0]->functionsCounts;

            $projectURL = url('/');
            $deviceIP = \Request::ip();

            $init_command = PHP_EOL.'-- '.$database.' Database Backup Generated time = '.YmdTodmYPm(\Carbon\Carbon::now()). PHP_EOL.PHP_EOL.
                            '-- Project URL = '.$projectURL.PHP_EOL.
                            '-- Device IP = '.$deviceIP.PHP_EOL.PHP_EOL.
                            '-- =============Objects Counting Start================= '.PHP_EOL.PHP_EOL.
                            '-- Total Tables + Views = '.$tableViewsCounts.PHP_EOL.
                            '-- Total Tables = '.$tablesCount.PHP_EOL.
                            '-- Total Views = '.$viewsCounts.PHP_EOL.PHP_EOL.
                            '-- Total Procedures = '.$proceduresCounts.PHP_EOL.
                            '-- Total Functions = '.$functionsCounts.PHP_EOL.
                            '-- =============Objects Counting End================= '.PHP_EOL.
                            PHP_EOL.PHP_EOL.
                            'SET FOREIGN_KEY_CHECKS=0; '. PHP_EOL.
                            'SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";'. PHP_EOL.
                            'START TRANSACTION;'. PHP_EOL.
                            'SET time_zone = "+06:00";'.PHP_EOL.
                            'drop database if exists '.$database.';'. PHP_EOL.
                            'CREATE DATABASE IF NOT EXISTS '.$database.' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'. PHP_EOL.
                            'use '.$database.';'.PHP_EOL; 
                
            $data = file_get_contents($dir);

            $append_command = PHP_EOL.'SET FOREIGN_KEY_CHECKS=1;'.PHP_EOL.'COMMIT;'.PHP_EOL;
            // dd($data);
            file_put_contents ( $dir , $init_command.$data.$append_command);

            return response()->download($dir);
        } catch (\Throwable $th) {
        }
    }

In .env put these lines

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=mfw
DB_USERNAME=root
DB_PASSWORD=root

In app\config.php

    'dbhost' => env('DB_HOST', ''),

    // mysql
    // mysql
    'db' => env('DB_DATABASE', ''),
    'dbuser' => env('DB_USERNAME', ''),
    'dbpass' => env('DB_PASSWORD', ''),

Now call the function

serverDBBackup()