Creating command to backup MySql database in Laravel

Question

I wanted to back up my database. I am using Xampp MySql. The username is root and the password is not set. I run the command using Command Prompt but it shows nothing. The Folder is created but there is nothing inside. The command prompt didn't print whether the process is a success or a failure.

Command

public function __construct()
{
    parent::__construct();

    $today = today()->format('Y-m-d');
    if (!is_dir(storage_path('backups')))
        mkdir(storage_path('backups'));

    $this->process = new Process(sprintf(
        'mysqldump --compact --skip-comments -u%s -p%s %s > %s',
        config('database.connections.mysql.username'),
        config('database.connections.mysql.password'),
        config('database.connections.mysql.database'),
        storage_path("backups/{today}.sql")
    ));
}

/**
 * Execute the console command.
 *
 * @return mixed
 */
public function handle()
{
    try {
        $this->process->mustRun();
        Log::info('Daily DB Backup - Success');
    } catch (ProcessFailedException $exception) {
        Log::error('Daily DB Backup - Failed');
    }
}

Answer 1

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

Answer 2

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.