Today working on a client project, a shell script which uses MySQL to dump the database, has got me into this error

mysqldump: Got error: 1045: Access denied for user

I was typically amazed getting this error because all my configurations were correct. I have created proper users to access MySQL database and correctly configured the MySQL server settings. FYI, my script was on one server and the MySQL was running on a separate dedicated box. The connection between them were TCP based.

After few minutes of head hunching, I was able to figure out the problem. So if you got into the same problem any time, follow the following guidelines to get over with it:

1. Check that whether you have created the proper MySQL user and gave them the required permission. You can check the user permission by following command.

SHOW GRANTS FOR 'user'@'localhost';

SHOW GRANTS FOR 'user'@'192.168.1.1';

If the permissions are not proper, you might think of providing it properly by

GRANT ALL ON database.* TO 'user'@'localhost' identified by 'password';

2. Check whether you are including the host properly in case of remote MySQL connections.

mysql -h host -u user -p database

3. Finally, if you have created user and didn’t restarted your MySQL server or ran following command, your MySQL user permissions will not take effect in-spite of creating them. (Ps. This was the issue I was having.)

FLUSH PRIVILEGES

After running the above command, my script was able to connect to database properly.