0

I am using MySQL 5.7.17 on Debian Jessie and wanted to dump the database using mysqldump with mysql_config_editor.

The steps I did for the mysql_config_editor. They are:

mysql_config_editor set --login-path=local --host=localhost --user=root --password
mysql_config_editor print --all

The user would be root.

Now change the root password:

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables &

A new terminal is opened and the following is typed:

mysql -u root -p
use mysql;
select user,host from user where user='root';
UPDATE user SET authentication_string=PASSWORD('123456') WHERE user='root';
FLUSH PRIVILEGES;
exit
sudo service mysql stop
sudo service mysql start

Now I execute the following:

mysqldump --login-path=local parana > parana.sql

And I get the following message:

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

How could you solve the problem to be able to backup cpn mysqldump and --login-path or if they have another alternative to be able to backup. Thank you very much

0

I suffered the same strange behaviour.

When I use in my script the command:

/usr/bin/mysqldump -h localhost -u root -pmy#rootpassword mysql --tables db >> mysql-db_2017-03-17_09-50-35.sql

I get the Warning:

Warning: Using a password on the command line interface can be insecure.

So I changed my Backup Scripts to

/usr/bin/mysqldump --login-path=backups mysql --tables db >> mysql-mysql-db_2017-03-17_08-24-07.sql

But that gave me the Error:

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

The official MySQL Documentation recommends to try the Configuration with the "mysql" command
https://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html

To connect to the local server, use this command:
shell> mysql --login-path=client

And actually I found that I get the same Error this way:

$ mysql --login-path=backups
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I could not make any sense of it until I stumbled over this comment at the bottom of the MySQL Documentation:

Password string with character "#" in it will cause authentication failure because when strings are read, hash characters are treated as the start of a comment.

That took me to the MySQL Bug Page:
https://bugs.mysql.com/bug.php?id=74691

There I found the suggested Workaround that worked for me:

A workaround is to enter the password in quotes "pass#pass".
Suggested fix:
Enclose all strings in quotes.

So I retyped my Password into the "mysql_config_editor":

$ mysql_config_editor set --login-path=scripts --host=localhost --user=root --password
Enter password: -> typeing: "my#rootpassword" <- including the character >"<
WARNING : 'scripts' path already exists and will be overwritten. 
 Continue? (Press y|Y for Yes, any other key for No) : y

And finally the Login succeeded:

$ mysql --login-path=scripts
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 139
Server version: 5.6.35-log Distributed by The IUS Community Project
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit
Bye

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.