CODEWITHSUNDEEP
mysqlcron-task
user2032807
user2032807

Reputation: 59

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)

Upvotes: 1

Views: 1723

Answers (3)

user2032807
user2032807

Reputation: 59

I recently had a problem with this regarding some Linux bug saving path passwords that include hash-marks. Apparently, when you enter the password with a hash-mark you need to enclose in quote-marks.

Upvotes: 0

user2032807
user2032807

Reputation: 59

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.

Upvotes: 1

wchiquito
wchiquito

Reputation: 16551

I can't reproduce the problem. Check the credentials of the user (in my case mysql_user) to access MySQL:

username@machine $ mysql_config_editor set \
                   --login-path=mysqldump \
                   --host=localhost \
                   --user=mysql_user \
                   --password
Enter password:

username@machine $ ls -l .mylogin.cnf 
-rw------- 1 username username 120 Jan 10 00:01 .mylogin.cnf

username@machine $ mysql_config_editor print --all
[mysqldump]
user = mysql_user
password = *****
host = localhost

username@machine $ mysqldump tbl_name | \
                   gzip > "/path/username/tbl_name-$(date).sql.gz"

username@machine $ ls -l *.sql.gz
-rw-rw-r-- 1 username username 1276 Jan 10 00:02 tbl_name-Tue Jan 10 00:02:01 UTC 2017.sql.gz

username@machine $ crontab -l -u username
# Edit this file to introduce tasks to be run by cron.
# For more information see the manual pages of crontab(5) and cron(8)
# 
# m h  dom mon dow   command
0 1 * * * mysqldump tbl_name | gzip > "/path/username/tbl_name-$(date).sql.gz"

Upvotes: 0

Related Questions