Laravel-backup: Access denied for user root@localhost

[baminou]

When I run
php artisan backup:run,
I get the following error:
Backup failed because The dump process failed with exitcode 2 : Misuse of shell builtins : mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

It seems that the mysqldump function is running without the -p argument. How can I fix that?

Thank you

[freekmurze]

Does the PHP process on the CLI have the right permissions to run the mysqldumpbinary?

[baminou]

I tried to look online to see how I can verify that, but I don't see anything. Do you know how I can check if the PHP process has the right permissions?

[freekmurze]

You can use PHP's exec function to check if you can call mysqldump from there (in a cli script).

The solution on how to fix this is something specific to your environment. Ask your hoster or sys admin.

[minutiae]

Any thoughts on this issue if exec seems to run from within php exec? I am having the issue where I'm getting the access denied for root error when the mysql user in my config is not root. I am on the 3.x branch for 5.1. Thanks

[sebastiaanluca]

Having this in production too when either executing the backup command as root or through the (default) root crontab and schedule:run. Kind of weird that it doesn't seem to take the app's database user, but root instead. Will investigate further.

Edit: switched to execute the cron jobs as non-root user. For some reason executing mysqldump as root ignores the credentials file, but haven't found a definitive answer as to _why_.

[jcdarwin]

Maybe a bit late, but probably the best way to get the cron job to work is to specify your DB_USERNAME env value in as an add_extra_option in the config/database.php, e.g.

//config/database.php
'connections' => [
    'mysql' => [
        'driver'    => 'mysql'
        ...,
        'dump' => [
                    'add_extra_option' => '-u ' . env('DB_USERNAME', 'forge'),
        ]  
    ],

There's more info at https://docs.spatie.be/laravel-backup/v4/installation-and-setup#dumping-the-database that alludes to this.

Note that the mysqldump command generated by laravel-backup looks something like:

'mysqldump' \
    --defaults-extra-file="/tmp/phpFcYmtt" \
    --skip-comments \
    --extended-insert \
    --default-character-set=utf8 \
    --result-file="/var/www/whatever/storage/app/laravel-backup/temp/db-dumps/mysql-whatever.sql" \
    whatever

The value of DB_USERNAME gets used to populate a temporary mysql config file by the associated spatie/db-dumper project, which is then passed in using the --defaults-extra-file parameter (/tmp/phpFcYmtt in the example above)

However, as others have discovered, when using cron the DB_USERNAME needs to be specified in the mysqldump command using the -u option, even though the DB_USERNAME already exists in the file specified by the --defaults-extra-file option.

[Blair2004]

@jcdarwin your solution worked for me. I've been putting the password on the "add_extra_option" :
'add_extra_option' => '--password=[password here]'
I think this issue is somehow specific to production environment.
Thank you for that.

[jpswade]

I found I had to delete ~/.my.cnf to make this work.