Craft 3 cannot find mysqldump command, console works

Question

I want to use Feed Me with Craft to import my old database, but neither Feed Me nor Craft can find mysqldump.

I know this is a known problem, and I have successfully added the path to $PATH and verified that this is set in my .bash_profile.

When I execute mysqldump on the console I get the familiar usage hints. Executing which mysqldump gives me /usr/local/mysql/bin/mysqldump.

Yet when I go to Craft > Database > Backup it fails. In web.log I find:

2018-07-18 21:01:10 [::1][1][-][error][yii\base\Exception] yii\base\Exception: Could not create backup: The shell command "mysqldump --defaults-extra-file="/Users/xxx/mysite.com/code/craft/storage/runtime/temp/my.cnf" [snip] _180718_110110_rig2b5xl4z_v3.0.15.sql"" failed with exit code 127: sh: mysqldump: command not found in /Users/xxx/mysite.com/code/craft/vendor/craftcms/cms/src/controllers/UtilitiesController.php:383

Why can I run the command in the terminal but Craft cannot? I've checked the permissions, and they should be ok:

-rwxr-xr-x 1 root wheel 4062128 29 Sep 2016 mysqldump*

Answer 1

After a lot of digging I found an SO post which recommended updating the environment variable via PHP:

putenv("PATH={$_SERVER["PATH"]}:/usr/local/mysql/bin");

Adding that line into craft/config/general.php before the return statement did the trick for me. Craft does back up and Feed Me runs the feed successfully.

BUT: Is that a good solution?

Answer 2

If you are having this problem using MAMP, Craft has a guide that shows the recommended way to deal with this, which is to use the backupCommand and restoreCommand configuration parameters to specify the mysqldump that MAMP is bundled with.

It also says:

We recommend putting them in your .env file so they don't need to be shared in other environments such as production, staging, or your teammates' computers.

So add the following to you .env. This assumes your password is "root", change -proot to -pyourpassword if that's not the case.

BACKUP_COMMAND="/Applications/MAMP/Library/bin/mysqldump -h localhost -u root -proot --add-drop-table --comments --create-options --dump-date --no-autocommit --routines --set-charset --triggers --single-transaction --no-data --result-file=\"{file}\" {database} && /Applications/MAMP/Library/bin/mysqldump -h localhost -u root -proot --add-drop-table --comments --create-options --dump-date --no-autocommit --routines --set-charset --triggers --no-create-info --ignore-table={database}.assetindexdata --ignore-table={database}.assettransformindex --ignore-table={database}.cache --ignore-table={database}.sessions --ignore-table={database}.templatecaches --ignore-table={database}.templatecachecriteria --ignore-table={database}.templatecacheelements {database} >> \"{file}\""

RESTORE_COMMAND="/Applications/MAMP/Library/bin/mysql -h localhost -u root -proot {database} < \"{file}\""

And add the backupCommand and restoreCommand configs to your config/general.php

// Dev environment settings
    'dev' => [
        // existing settings ...
        'backupCommand' => getenv('BACKUP_COMMAND'),
        'restoreCommand' => getenv('RESTORE_COMMAND'),
    ],

Source: https://craftcms.com/guides/database-backups-in-craft-3-with-mamp

Answer 3

Very nice, for Valet Plus I changed the path to:

putenv("PATH={$_SERVER["PATH"]}:/usr/local/opt/mysql@5.7/bin");

this does this trick for local backups, I disabled it before pushing config back to production.