Creating command to backup MySql database in Laravel

There is one great package for creating backups in Laravel: spatie/laravel-backup.

Setup Your Database credential your .env file like this..

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

Then setup your controller function like this

    public function backup_database()
    {
        $mysqlHostName      = env('DB_HOST');
        $mysqlUserName      = env('DB_USERNAME');
        $mysqlPassword      = env('DB_PASSWORD'); 
        $DbName             = env('DB_DATABASE'); 
        $backup_name        = "backup.sql";
        $tables             = array("users", "villages", "migrations", "failed_jobs", "password_resets"); //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);
    }

Next create you a route for accessing controller. You can download .sql backup file now.

Rather than write own code just use spatie/laravel-backup.

because it provides us better approve and control. For example you can not only backup but also get notification when back success. Also you can send it to mail and compress file too.

For backup run command on console

  php artisan backup:run --only-db

i used --only-db statement because it only backup single file after compression. In my side my server Database file size becomes 400MB but it convert it only on 20MB. but if you use php artisan backup:run then your file size become more than 400MB.

So i am recommending you this... easy to install and use.

this works for me in a linux server setup running Laravel and MySql

$name = config('app.name');
$name = trim($name);
$name = Str::slug($name, '-');
$filename = $name . ".sql";

$DUMP_PATH = config('app.DUMP_PATH');
$DB_USERNAME = config('database.connections.mysql.username');
$DB_PASSWORD = config('database.connections.mysql.password');
$DB_HOST = config('database.connections.mysql.host');
$DB_PORT = config('database.connections.mysql.port');
$DB_DATABASE = config('database.connections.mysql.database');

$command = "".$DUMP_PATH." --user=" . $DB_USERNAME . " --password=" . $DB_PASSWORD . " --host=" . $DB_HOST . " " . $DB_DATABASE . "  > " . storage_path() . "/app/backup/" . $filename;
    $returnVar = NULL;
    $output = NULL;
    exec($command, $output, $returnVar);