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.

Answer 3

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.

Answer 4

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);