Search code examples
mysqlcron-task

mysqldump Cron Job Password less command line scripts with MySQL 5.6

I login into SSH as the Account I will want to eventually run a Cron Job with. (ACCOUNT_NAME)

I then run:

mysql_config_editor set --login-path=local --host=localhost --user=MYSQL_USERNAME --password

I then type in the password for this username.

I then run:

mysql_config_editor print --all

It shows this:

[local]
user = MYSQL_USERNAME
password = *****
host = localhost

I then try:

mysql --login-path=local

I get this error:

Error 1045 (28000); Access denied for user 'MYSQL_USERNAME'@'localhost' (using password: YES)
Solution

  • It seems my real problem was that I was calling the cronjob from a cpanel account and it had no permissions to access the .mylogin.cnf file in the root.

    What I finally did was copy my .mylogin.cnf from the root folder to the account folder (/home/ACCOUNTNAME/), and then change the owner:group permissions to the cpanel account user (ACCOUNTNAME:ACCOUNTNAME) . Then the cronjob from that account was able access the --login-path.

    You could probably just login into SSH using the cpanel account instead of root when you create the file (using mysql_config_editor ) and avoid copying the file and changing permissions. For some reason this wasn't working for me.

    The final CronJob call that worked was:

    mysqldump --login-path=CREDSREFERENCE DATABASENAME | gzip > "/home/ACCOUNTFOLDERNAME/backup/DATABASENAME-$(date).sql.gz"

    Thanks to @wchiquito. His command lines and discussion put me on the right track.