Laravel Artisan command to perform MySQL Dump using database connection information in the .env file. Posted 2016 Jan. Unsupported. Forked from https://gist.github.com/kkiernan/bdd0954d0149b89c372a

Kernel.php

<?php

namespace App\Console;

use Illuminate\Console\Scheduling\Schedule;
use Illuminate\Foundation\Console\Kernel as ConsoleKernel;

class Kernel extends ConsoleKernel
{
    /**
     * The Artisan commands provided by your application.
     *
     * @var array
     */
    protected $commands = [
        \App\Console\Commands\Inspire::class,
        
        // add the MySqlDump command here
        \App\Console\Commands\MySqlDump::class,
    ];

    // etc...
}

MySqlDump.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;

class MySqlDump extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'db:dump';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Runs the mysqldump utility using info from .env';

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $ds = DIRECTORY_SEPARATOR;

        $host = env('DB_HOST');
        $username = env('DB_USERNAME');
        $password = env('DB_PASSWORD');
        $database = env('DB_DATABASE');
        
        $ts = time();

        $path = database_path() . $ds . 'backups' . $ds . date('Y', $ts) . $ds . date('m', $ts) . $ds . date('d', $ts) . $ds;
        $file = date('Y-m-d-His', $ts) . '-dump-' . $database . '.sql';
        $command = sprintf('mysqldump -h %s -u %s -p\'%s\' %s > %s', $host, $username, $password, $database, $path . $file);

        if (!is_dir($path)) {
            mkdir($path, 0755, true);
        }

        exec($command);
    }
}

Extra credit: add checks/smart defaults for the .env variables (like 'forge' or 'homestead' for the db name); throw if any info is missing; allow overriding those values from the command line with optional arguments; check for the mysql path; throw if the command fails; add some success output; drop in a snippet to schedule it in app/Console/Kernel schedule function; make it a composer package. Go forth!

2022 Nov update - I have not updated this code since it was written 2016 Jan. It may not be compatible with recent versions of Laravel and it is not supported. Feel free to fork it and build out the extra credit (above) or make any fixes necessary.

Ooo brilliant!

Beatiful! Thanks!!

sorry, my problem is ..... don't execute the command, use xampp in window, show nothing...... I use laravel 5.4

mysqldump is not recognized
How can i go to mysqldump directory and run the command from the artisan?
Help me

Thanks!

I need to create an SQL dump from an artisan command in Laravel 4.2, how can I achieve this?

Thanks!

Without wanting to spoil anyone's fun, you should probably check what goes in the logs if this code fails for any reason. I have something similar and found it logging the full credentials to my database when an exception occurs. Even worse is that I have my site coded to email me when there's an exception, and when this dump code throws an exception I get an email containing the error, which contains the full database credentials in the clear. Oops!

Refactoring tip for lines 43, 44 and 45: shorthand Ternary Operator (?:)

is_dir($path) ?: mkdir($path, 0755, true);

Short isn't always better. In this case, I would hesitate to condense a conditional + method into a one liner just because we can; it's not as readable.

Thanks a lot!!!
Just changed env() with config() in order to avoid caching issues

$host = config('database.connections.mysql.host'); $username = config('database.connections.mysql.username'); $password = config('database.connections.mysql.password'); $database = config('database.connections.mysql.database');

Very nice!!

but my backup file ended like this:
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

What do i need to change in order to get especific tables?

@maureknob looks like you've been using config:cache. Laravel won't read the .env file when config:cache is used. So you need to use the config helper instead to access cached environment variables

From the docs
https://laravel.com/docs/9.x/helpers#method-env
https://laravel.com/docs/9.x/helpers#method-config

I'm geeting 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces when I try to use this with laravel 9 in sail on dev

Edit:
With $command = sprintf('mysqldump -h %s -u %s -p\'%s\' %s --no-tablespaces > %s', $host, $username, $password, $database, $path . $file); it is working

Thanks @philharmonie !

Curiously I had the same error that you were getting (Access Denied) but when I switched to using the Symfony process control it worked as expected 🤷‍♀️ This does mean I load the whole file into memory, which could be an issue with large databases.

My slightly different version, using config instead of env and adding the port (because I have a custom port).

A bonus of using Process is that we could also check if it worked, and do something differently if it didn't.

use Symfony\Component\Process\Process;


        $host = config('database.connections.mysql.host');
        $username = config('database.connections.mysql.username');
        $password = config('database.connections.mysql.password');
        $database = config('database.connections.mysql.database');
        $port = config('database.connections.mysql.port');
        
        $process = new Process([
            'mysqldump',
            '-h', $host,
            '-P', $port,
            '-u', $username,
            '-p'.$password,
            $database,
        ]);
        $process->run();

         // Note: I actually use Storage::put() and a fixed filename to output, but for consistency with the rest of this Gist I've
         // put this untested version in:
         
        $ts = time();

        $path = database_path() . $ds . 'backups' . $ds . date('Y', $ts) . $ds . date('m', $ts) . $ds . date('d', $ts) . $ds;
        $file = date('Y-m-d-His', $ts) . '-dump-' . $database . '.sql';

        file_put_contents($path, $process->getOutput());