How to generate a table backup programatically with Laravel?

Question

I wanna make a job in Laravel which will truncate the database and refill it. However, I wanna make sure I have a backup of the table first and I'm not sure how to create a file with all rows from that table from the same job. I tried using a csv file (using LaraCSV)

public function respaldaDevices() {
        $model = Device::get();
        $csvExport = new \Laracsv\Export();
        $campos = ['serial', 'code', 'rotulo', 'model_id', 
        'location_id', 'fecha_recepcion', 'guia_recepcion', 'fecha_instalacion',
        'fecha_reversa', 'guia_reversa', 'pep', 'status_id', 'serial_prev', 
        'reversa_prev', 'str_id', 'customer_id', 'technician_id', 'provider_id', 'obs'];
        $content = $csvExport->build($model,$campos)->getWriter()->getContent();
        $date = date('d-m-Y H-i-s');
        Storage::disk('public')->put('respaldo-devices-' . $date . '.csv', $content);
    }

but I'd rather have the insert statements.

EDIT: What I've tried so far:

Installed Symfony Process and

use Symfony\Component\Process\Process;

$process = new Process(array('mysqldump', sprintf('-u%s', getenv('DB_USERNAME')),'reportes','devices > devices-'.time().'.sql'));

Mysqldump returns an error 6.

EDIT 2:

Made it work with a deprecated option:

$process = new Process(
                    "mysqldump " . sprintf('-u%s', getenv('DB_USERNAME')) . " ".  " " . "reportes " . "devices > storage\app\devices-".time().".sql --no-create-info"
        );
        $process->run();

I'd like to know how to do the same but using the array.

You could generate the insert queries yourself from the query result you are getting. — Jerodev, Aug 01 '19 at 13:16
You can serialize model as JSON, save all records to .json file, then parse it. https://laravel.com/docs/5.8/eloquent-serialization#serializing-models-and-collections — EXayer, Aug 01 '19 at 13:22
I just tried it out with JSON but it takes way too long for the table I need to work with. — ffuentes, Aug 01 '19 at 15:25
it is bug free and easy to use and change. https://stackoverflow.com/questions/58207052/how-to-backup-export-the-connected-database-database-sql-file-in-laravel/67285015#67285015 — pankaj, Apr 27 '21 at 14:29

PtrTon · Answer 1 · 2019-08-01T16:53:26.310

3

You could leverage OS-specific tools like mysqldump instead. You can call this using a process.

Example usage of Process:

$process = new Process([
    'mysqldump',
    sprintf('-u%s', 'my-username'),
    sprintf('-p%s', 'my-password'),
    'my-database my-table', // 'my-table' can be left out for a backup of the entire database
    '--add-drop-database', // optional
]);

$process->run();

if (!$process->isSuccessful()) {
    throw new \Exception('Failed to create backup');
}

$date = date('d-m-Y H-i-s');
Storage::disk('public')->put('respaldo-devices-' . $date . '.csv', $process->getOutput());

edited Aug 01 '19 at 16:53

answered Aug 01 '19 at 13:21

PtrTon

3,705
2
14
24

I'm trying this but it's not working probably due to misuse $process = new Process(array( 'mysqldump', '-u'.getenv('DB_USERNAME'), '-p'.getenv('DB_PASSWORD'), 'reportes', 'devices > storage\app\devices-'.time().'.sql' )); $process->run(); – ffuentes Aug 01 '19 at 14:26
I'm trying to get the table, not the whole database and I get error 6 whereas using the same string on the cmd works with no issues. – ffuentes Aug 01 '19 at 14:49
You are right, I forgot that part. In the case you'd want to backup a single table you can [specify an additional argument after the database](https://dba.stackexchange.com/a/9309). – PtrTon Aug 01 '19 at 15:00
From a quick glance it would seem that you had issues with storing the actual output so I've updated my example with a general idea of how to do so. In case you have memory issues with big outputs, you can look into writing the process output as it's generated. You can do so by using `$process->start()` and then looping over the `$process`, more info on that can be found [here](https://symfony.com/doc/current/components/process.html) – PtrTon Aug 01 '19 at 16:54

How to generate a table backup programatically with Laravel?

1 Answers1

Linked