How to set the path of 'dump_binary_path' to backup database using spatie package in laravel 9?

Question

I have a couple of docker containers for my Laravel 9 app (Nginx, PHP, MySQL, PHPMyAdmin, etc). I use spatie package to backup my database. I don't know how to set the path to the mysqldump file as it's in the docker container! I tried many different paths mentioned on the internet (e.g. 'var/lib/mysql', 'mysql/bin', 'usr/bin', etc), but none of them works!

Fortunately, when I run the following command on the server, the backup file is generated successfully, so I think the mysqldump file is in the '\usr\lib':

docker exec MY-CONTAINER-NAME /usr/bin/mysqldump -u root --password=MY-PASSWORD DATABASE-NAME > storage/app/backups/backup-test.sql

The following is my MySQL service definition that I have in my docker-compose.yml file:

mysql:
     image: mysql:8.0
     container_name: mysql
     restart: unless-stopped
     tty: true
     volumes:
       - ./mysql:/var/lib/mysql
     environment:
       MYSQL_ROOT_PASSWORD: {{root-passowrd}}
       SERVICE_NAME: mysql
     networks:
       - laravel

---

Here is the MySQL settings in config/database.php:

     'mysql' => [
         'driver' => 'mysql',
         'url' => env('DATABASE_URL'),
         'host' => env('DB_HOST', '127.0.0.1'),
         'port' => env('DB_PORT', '3306'),
         'database' => env('DB_DATABASE', 'forge'),
         'username' => env('DB_USERNAME', 'forge'),
         'password' => env('DB_PASSWORD', ''),
         'unix_socket' => env('DB_SOCKET', ''),
         'charset' => 'utf8mb4',
         'collation' => 'utf8mb4_unicode_ci',
         'prefix' => '',
         'prefix_indexes' => true,
         'strict' => true,
         'engine' => null,
         'dump' => [
             'dump_binary_path' => '/usr/bin',
             'use_single_transaction',
             'timeout' => 60 * 5, 
         ],
         'options' => extension_loaded('pdo_mysql') ? array_filter([
             PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
         ]) : [],
     ],

---

That would be great, if someone can help me to resolve this issue!

Here is the error I get when I run

docker compose exec php php artisan backup:run

---

Backup failed because The dump process failed with a none successful exitcode. Exitcode ======== 127: Command not found

Output ======

Error Output ============ sh: 1: /usr/bin/mysqldump: not found . #0 /var/www/vendor/spatie/db-dumper/src/DbDumper.php(178): Spatie\DbDumper\Exceptions\DumpFailed::processDidNotEndSuccessfully(Object(Symfony\Component\Process\Process)) #1 /var/www/vendor/spatie/db-dumper/src/Databases/MySql.php(143): Spatie\DbDumper\DbDumper->checkIfDumpWasSuccessFul(Object(Symfony\Component\Process\Process), '/var/www/storag...') #2 /var/www/vendor/spatie/laravel-backup/src/Tasks/Backup/BackupJob.php(274): Spatie\DbDumper\Databases\MySql->dumpToFile('/var/www/storag...') #3 [internal function]: Spatie\Backup\Tasks\Backup\BackupJob->Spatie\Backup\Tasks\Backup{closure}(Object(Spatie\DbDumper\Databases\MySql), 'mysql') #4 /var/www/vendor/laravel/framework/src/Illuminate/Collections/Arr.php(560): array_map(Object(Closure), Array, Array) #5 /var/www/vendor/laravel/framework/src/Illuminate/Collections/Collection.php(739): Illuminate\Support\Arr::map(Array, Object(Closure)) #6 /var/www/vendor/spatie/laravel-backup/src/Tasks/Backup/BackupJob.php(277): Illuminate\Support\Collection->map(Object(Closure)) #7 /var/www/vendor/spatie/laravel-backup/src/Tasks/Backup/BackupJob.php(188): Spatie\Backup\Tasks\Backup\BackupJob->dumpDatabases() #8 /var/www/vendor/spatie/laravel-backup/src/Tasks/Backup/BackupJob.php(160): Spatie\Backup\Tasks\Backup\BackupJob->createBackupManifest() #9 /var/www/vendor/spatie/laravel-backup/src/Commands/BackupCommand.php(58): Spatie\Backup\Tasks\Backup\BackupJob->run() #10 /var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(36): Spatie\Backup\Commands\BackupCommand->handle() #11 /var/www/vendor/laravel/framework/src/Illuminate/Container/Util.php(41): Illuminate\Container\BoundMethod::Illuminate\Container{closure}() #12 /var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(93): Illuminate\Container\Util::unwrapIfClosure(Object(Closure)) #13 /var/www/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(37): Illuminate\Container\BoundMethod::callBoundMethod(Object(Illuminate\Foundation\Application), Array, Object(Closure)) #14 /var/www/vendor/laravel/framework/src/Illuminate/Container/Container.php(651): Illuminate\Container\BoundMethod::call(Object(Illuminate\Foundation\Application), Array, Array, NULL) #15 /var/www/vendor/laravel/framework/src/Illuminate/Console/Command.php(182): Illuminate\Container\Container->call(Array) #16 /var/www/vendor/symfony/console/Command/Command.php(312): Illuminate\Console\Command->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Illuminate\Console\OutputStyle)) #17 /var/www/vendor/laravel/framework/src/Illuminate/Console/Command.php(152): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Illuminate\Console\OutputStyle)) #18 /var/www/vendor/spatie/laravel-backup/src/Commands/BaseCommand.php(28): Illuminate\Console\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) #19 /var/www/vendor/symfony/console/Application.php(1020): Spatie\Backup\Commands\BaseCommand->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) #20 /var/www/vendor/symfony/console/Application.php(312): Symfony\Component\Console\Application->doRunCommand(Object(Spatie\Backup\Commands\BackupCommand), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) #21 /var/www/vendor/symfony/console/Application.php(168): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) #22 /var/www/vendor/laravel/framework/src/Illuminate/Console/Application.php(102): Symfony\Component\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) #23 /var/www/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(155): Illuminate\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) #24 /var/www/artisan(37): Illuminate\Foundation\Console\Kernel->handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) #25 {main} Sending notification failed Backup failed because: The dump process failed with a none successful exitcode. Exitcode ======== 127: Command not found

Output ======

Answer 1

I managed to resolve the issue differently! I installed MySQL client in my PHP container via Dockerfile as follows:

Dockerfile :

...
# install default-mysql-client
RUN apt-get update && apt-get install -y default-mysql-client && rm -rf /var/lib/apt
...

Then, I used the following command (in my controller) to generate the backup. You can trigger this via a cron job to generate backup periodically:

mysql -u root --password=DB_PASSWORD -h mysql /usr/bin/mysqldump your-mysql-container-name > storage/app/backups/backup_filename.sql;

In my case, I put a button in the UI for admins to generate and download backups whenever needed. So when the button is clicked, an ajax call is triggered to run the command in the shell of my PHP container. The command I used in my controller is as follows:

My Controller:

...
// create a dynamic name for the backup-file
$backup_filename  = date('d-m-Y_h-ia') . '_yourapp-DB-backup.sql';
$shell_command = 'cd .. && mysql -u root --password=' . env('DB_PASSWORD');
$shell_command .= ' -h mysql /usr/bin/mysqldump your-db-name > storage/app/backups/' . $backup_filename;

exec($shell_command);
...

At the end, I put my docker-compose.yml file in case it can help someone!

version: '3.6'

services:

  nginx:
    image: nginx
    container_name: nginx-books
    hostname: nginx-books
    volumes:
      - ./src:/var/www/
      - ./nginx.conf:/etc/nginx/nginx.conf
    networks:
      - books
      - nginx-web-proxy

  mysql:
    image: mysql:8.0
    container_name: mysql-books
    restart: unless-stopped
    tty: true
    volumes:
      - ./mysql:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: MYPASSWORD
      SERVICE_NAME: mysql
    networks:
      - books

  php:
    build:
      context: .
      dockerfile: Dockerfile
    container_name: php-books
    hostname: php-books
    volumes:
      - ./src:/var/www/
      - ./php.ini:/usr/local/etc/php/php.ini
    networks:
      - books

  phpmyadmin:
    container_name: phpmyadmin-books
    hostname: phpmyadmin-books
    depends_on:
      - mysql
    image: phpmyadmin/phpmyadmin
    environment:
      - PMA_HOST=mysql
      - PMA_PORT=3306
    networks:
      - nginx-web-proxy
      - books


networks:
  books:
    name: books
  nginx-web-proxy:
    name: nginx-web-proxy
    external: true

Answer 2

you can use dump_binary_path like this

'connections' => [
...
'mysql' => [
    ...
    'dump' => [
        'dump_binary_path' => 'C:\xampp\mysql\bin',
        'use_single_transaction',
        'timeout' => 60 * 5,
    ],
],
...

]