Mysql – “Access denied for user ‘root’@’servername’ (using password: NO)” – except I AM using a password

In order to connect to the mysql server you must provide credentials. You can specify them in a configuration file, pass them via the command line, or simply create account that doesn't require credentials.

Of course the no-password option should never be used, the pass-by command line isn't great because anyone who can run ps may be able to see the command line.

The recommended option is to create a mysql configuration file with the credentials in it and then protect that file with filesystem permissions so only your backup user can access it.

You being able to login to the mysql server while logged in interactively as root seems to suggest that you either don't have a root password set, or that you have a configuration file that is not being found by your script. If you have a .my.cnf you may need to manually point to it. If your root account doesn't have a password set then I would strongly encourage you to fix that.

Update (2016-06-29) If you are running mysql 5.6.6 or greater, you should look at the mysql_config_editor tool that allows you to store credentials in an encrypted file. Thanks to Giovanni for mentioning this to me.

You can start MySQL with the authentication disabled. From there you can create/delete the administrative account for MySQL.

The details you can find in the MySQL documentation: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

1. Shutdown MySQL

2. Start MySQL with: mysqld --skip-grant-tables --skip-networking

3. In mysql run:

   UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
   

   For newer MySQL versions:

   ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
   GRANT ALL PRIVILEGES ON \*.\* TO 'root'@'localhost' WITH GRANT OPTION;
   

4. Shutdown MySQL

5. Start MySQL as you usually do.