I want to backup mysql database in laravel 11 using a route. I tried some ways and all of them had problem:
using Spatie/db-dumper:
protected $signature = 'backup:database';
public function handle()
{
$filename = config('app.DB_DATABASE') . '.sql';
$path = storage_path("app/database/backup/");
$fnStorage = $path . $filename;
File::put($fnStorage, '');
MySql::create()
->setDbName(config('app.DB_DATABASE'))
->setUserName(config('app.DB_USERNAME'))
->setPassword(config('app.DB_PASSWORD'))
->setHost(config('app.DB_HOST'))
->setPort(config('app.DB_PORT'))
->dumpToFile($fnStorage);
}
It works fine by calling it in a terminal: php artisan backup:database
. But using a route (in controller: Artisan::call('backup:database');
It throws following error:
"mysqldump" is not recognized as an internal or external command, operable program or batch file.
Note: I added following code to config/database.php but error persists!
'mysql' => [
'driver' => 'mysql',
'dump' => [
'dump_binary_path' => 'D:/xampp/mysql/bin/',
'dump_command_path' => 'D:/xampp/mysql/bin/',
'use_single_transaction',
'timeout' => 60 * 5, // 5 minute timeout
],
...
],
It works fine by calling it in a terminal: php artisan backup:run --only-db
. But using a route (in controller: Artisan::call('backup:run', ['--only-db'=> true]);
It throws following error:
mysqldump: Got error: 2004: Can't create TCP/IP socket (10106) when trying to connect .
3.Using mysqldump
command directly:
$command1 = 'mysqldump --user=' . $dbUser . ' --password=' . $dbPassword . ' --host=' . $dbHost . ' ' . $dbName . ' > "' . $fnStorage . '"';
This command works fine in terminal. Using a route (exec($command1, $output, $returnVar);
It works also fine when I am in design mode in VSCode. But in production (Using xampp), it throws following error:
"mysqldump" is not recognized as an internal or external command, operable program or batch file.
And when I pass this command to a powershell command:
$command1 = 'mysqldump --user=' . $dbUser . ' --password=' . $dbPassword . ' --host=' . $dbHost . ' ' . $dbName . ' > \'' . $fnStorage . '\'';
$command1 = 'powershell -Command "' . $command1 . '"';
By calling it as exec($command1, $output, $returnVar);
, nothing happens!
So, what should I do to deal with this job?
Thanks to all. I found the answer and I share it for other people.
In the 3rd way, I put the absolute path for mysqldump:
$command1 = 'D:/xampp/mysql/bin/mysqldump --user=' . $dbUser . ' --password=' . $dbPassword . ' --host=' . $dbHost . ' ' . $dbName . ' > "' . $fnStorage . '"';
exec($command1, $output, $returnVar);
Now, it works for both design (VSCode) and production environments :)