An example Laravel app command to create and load database snapshots using S3
<?php | |
namespace App\Console\Commands; | |
use Illuminate\Console\Command; | |
use Illuminate\Support\Facades\DB; | |
use Illuminate\Support\Facades\Storage; | |
class SnapshotCommand extends Command | |
{ | |
/** | |
* The name and signature of the console command. | |
* | |
* @var string | |
*/ | |
protected $signature = 'app:snapshot {action}'; | |
/** | |
* The console command description. | |
* | |
* @var string | |
*/ | |
protected $description = 'Create or load a production snapshot'; | |
/** | |
* Execute the console command. | |
* | |
* @return mixed | |
*/ | |
public function handle() | |
{ | |
switch ($this->argument('action')) { | |
case 'create': | |
if (!app()->environment('production')) { | |
$this->error('"create" should only be run in the production environment'); | |
return; | |
} | |
$this->createSnapshot(); | |
break; | |
case 'load': | |
if (!app()->environment('local')) { | |
$this->error('"create" should only be run in the local environment'); | |
return; | |
} | |
$this->loadSnapshot(); | |
break; | |
default: | |
$this->error('{action} must be either "create" or "load"'); | |
} | |
} | |
protected function createSnapshot() | |
{ | |
$this->runCommandWithCredentials( | |
'mysqldump --defaults-extra-file="{credentials_file}" --single-transaction {database} ' | |
. '> /app/storage/app/snapshot-data.sql; gzip -f /app/storage/app/snapshot-data.sql' | |
); | |
Storage::cloud()->put('db-snapshots/snapshot-data.sql.gz', fopen('/app/storage/app/snapshot-data.sql.gz', 'r+')); | |
unlink('/app/storage/app/snapshot-data.sql.gz'); | |
} | |
protected function loadSnapshot() | |
{ | |
DB::getSchemaBuilder()->dropAllTables(); | |
Storage::disk()->put( | |
'snapshot-data.sql.gz', | |
Storage::cloud()->getDriver()->readStream('db-snapshots/snapshot-data.sql.gz') | |
); | |
$this->runCommandWithCredentials( | |
'zcat /app/storage/app/snapshot-data.sql.gz' | |
. ' | mysql --defaults-extra-file="{credentials_file}" {database}' | |
); | |
unlink('/app/storage/app/snapshot-data.sql.gz'); | |
} | |
protected function runCommandWithCredentials($command) | |
{ | |
$dbConfig = config('database.connections.' . config('database.default')); | |
$disk = Storage::disk('local'); | |
$disk->put('mysql-credentials.txt', implode(PHP_EOL, [ | |
'[client]', | |
"user = '{$dbConfig['username']}'", | |
"password = '{$dbConfig['password']}'", | |
"host = '{$dbConfig['host']}'", | |
"port = '{$dbConfig['port']}'", | |
])); | |
$command = str_replace( | |
['{credentials_file}', '{database}'], | |
[$disk->path('mysql-credentials.txt'), $dbConfig['database']], | |
$command | |
); | |
exec($command); | |
$disk->delete('mysql-credentials.txt'); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment