-
-
Save phillipsharring/fb500556e7173a1c222d to your computer and use it in GitHub Desktop.
<?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... | |
} |
<?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); | |
} | |
} |
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());
mysqldump is not recognized
How can i go to mysqldump directory and run the command from the artisan?
Help me