Mysqldump got error 1045 access denied for user root

Human errors can occur at any time, especially when we are in a hurry to do something.

But, sometimes it can lead to problems. One such error is mysqldump error 1045.

At Bobcares, we help server owners resolve MySQL errors as part of out Server Management Services.

Today, let’s discuss the top 4 reasons for this error and how we fix them.

mysqldump error 1045 – What’s this?

Before we move on to the reasons for this error, let’s first get an idea of this error.

Server owners often face this error when they try to use mysqldump utility for taking backup of the databases or transfer it to another server.

For instance, users see the error message like this:

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

This means that MySQL server disallows the user to connect it from localhost.

mysqldump error 1045 – Causes & Fixes

Now, let’s see the main reasons for this error and how our Dedicated Engineers fix them.

1) Wrong mysqldump syntax

One of the common mistakes that we see is that customers use wrong format of mysqldump command. For example, this is the general syntax for taking database backup using mysqldump.

mysqldump -u username -p'password' database > dump.sql

However, we’ve seen instances where customers put extra space after -p switch and give the password. But, MySQL considers this syntax as invalid and throws this error.

How we fix?

Here, our Support Engineers check the syntax of the mysqldump command and correct the errors.

We often recommend customers not to paste the MySQL password as plain text in their commands. Instead, we suggest them to use -p switch alone, and then give the password when it prompts for.

mysqldump -u username -p

In addition to that, some database servers won’t accept passwords with special characters such as $, &, etc. So, we reset the password and update it to the customer.

In some cases, we need to explicitly specify the hostname to which the user is connecting using the -h switch.

mysqldump -h hostname.com -u username -p'password' database > dump.sql

2) Wrong user credentials

Likewise, customers may use wrong username and password while connecting to the database. Therefore, MySQL can’t verify the authenticity of the account and throws this error.

Similarly, some customers create custom file .my.cnf and add their MySQL username and password in this file. So, they can easily use the mysql, mysqldump commands without giving the password. However, wrong information in this file can break the connection and lead to this error.

How we fix?

In such cases, our Database Experts first check whether correct username and password is used by the customer. If not, we reset the password of the database user. In addition to that, for database driven websites like WordPress, Drupal, etc.  we update the new password in the website configuration files.

Moreover, if customer uses any custom .my.cnf file for the user, we update the new password there also.

3) Insufficient privileges to the database user

Another common reason for this error is that the user trying to connect to the database doesn’t have sufficient privileges. And, they receive the error as given below.

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

This error means that the database user ‘test-user’ doesn’t have enough privileges to run the mysqldump command on the database.

How we fix?

In such cases, our Support Experts assign proper privileges to the user to access the database. For instance, see the below command.

GRANT ALL PRIVILEGES ON *.* TO test-user@'localhost' IDENTIFIED BY 'password' with grant option;

This will grant all privileges to the user test-user on the selected database. Most importantly, we use the below command to save the privileges.

flush privileges;

Similarly, on cPanel servers, we manage user privileges from cPanel > Mysql databases > Current databases > Privileged users > Click on the database user.

[Missing database user privileges? Click here, and get one of our Database Experts to fix it for you.]

4) Remote host not allowed in server

Similarly, users commonly see this error when they try to push and pull the databases remotely.

mysqldump: Got error: 1045: Access denied for user 'username'@'161.22x.1xx.2xx' (using password: YES) when trying to connect

This is because most web hosts disallow external connections and they only allow to connect from localhost. So, server owners must allow the IP address of the server from which they are accessing the database.

How we fix?

Our Support Engineers get the remote IP address of the customer and add it to the allowed hosts list of the server, so that the IP can connect to the database remotely.

For example, on cPanel servers, we whitelist an IP address from cPanel > Databases > Remote MySQL > Add Access Host for remote connection. If the customer’s IP address changes regularly, we allow the IP address using a wildcard(%). This will add all the IP addresses in that range.

[Are you facing mysqldump error 1045? No worries, Our Support Experts can help you. ]

Conclusion

In short, mysqldump error 1045 can occur due to mysqldump syntax errors, insufficient privileges for the database user, and more. Today, we’ve discussed the top 4 reasons for this error and how our Dedicated Engineers fix them.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

• 

  JFEB

  
  

  This person is a verified professional.

  Verify your account
  to enable IT peers to see that you are a professional.

  habanero

  This is how I type mine up, using just -p will create a prompt for the password where you can enter it in the terminal without it being visible:
  Aside from that it could be a permissions issue.

  BASH

  mysqldump -u root -p -h ‘localhost’ database > /foo/bar/database.sql
  

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Problem is I am trying to automate my backups.  Waiting for a password would not work very well.  And I also get the same results.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  JFEB

  
  

  This person is a verified professional.

  Verify your account
  to enable IT peers to see that you are a professional.

  habanero

  Log into mysql and type:

  BASH

  show grants for root@localhost;
  

  
  1 found this helpful
  thumb_up
  thumb_down
  

• 

  JFEB

  
  

  This person is a verified professional.

  Verify your account
  to enable IT peers to see that you are a professional.

  habanero

  It should tell you what databases root has permissions for, *.* would be all.

  (sorry for double post, it wouldn’t let me continue typing below the code box)

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  *.* is what it shows

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  JFEB

  
  

  This person is a verified professional.

  Verify your account
  to enable IT peers to see that you are a professional.

  habanero

• 

  Followed the article and everything is as it should be.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  are you accessing the correct mysql server / instance?  I have seen machines with separate instances of mysql. (one for MAMP, one for ruby on rails, …)  if you’re using a .sock file, maybe it is pointing to the wrong one?

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Only one instance on the machine.  I am kinda wishing WordPress would use SQL Server instead.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  JFEB

  
  

  This person is a verified professional.

  Verify your account
  to enable IT peers to see that you are a professional.

  habanero

  TigrisMagnus wrote:

  Only one instance on the machine.  I am kinda wishing WordPress would use SQL Server instead.

  I take it your wordpress is working then? You’ve probably tried this mysql user, but if you go to /var/www/wordpress (or wherever you may have it) and open wp-config.php, it will have the database, user, and password in there, if that is one you haven’t tried yet.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Same user and password

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  can you try a «SHOW DATABASES» command from the CLI?  does it give you what you expect?

  
  Was this post helpful?
  thumb_up
  thumb_down
  

• 

  Show databases gives me what I expect to see.  We ended up going a different route to backup the databases.  Built 2 scripts, one to shut the MySQL instance service down and another to bring it back up.  Specified these 2 scripts in the respective application specific pre and post thaw fields in Veeam’s application specific fields.  The service is down maybe half a second as Veeam takes it’s snapshot.

  
  Was this post helpful?
  thumb_up
  thumb_down
  

Hello..

and thanks for a cool gem.

I’m testing out this gem and I’m having an issue with the dump.sql. I’m going to delete this test server as soon as I’m finished writing this.

Here is the error I’m getting:

Russells-iMac:jjlaw Home$ wordmove push --all -e live

▬▬ ✓ Using Movefile: ./Movefile ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

▬▬ ✓ Pushing wordpress core ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
   remote | put_directory: /Applications/MAMP/htdocs/jjlaw /var/www/html/jjlaw .git/ .gitignore .sass-cache/ bin/ tmp/* Gemfile* Movefile wp-config.php wp-content/*.sql wp-content/*
I, [2015-07-27T13:46:35.031210 #52357]  INFO -- : rsync --progress -e 'ssh root@###.###.##.###' -rlpt --compress --omit-dir-times --delete --exclude .git/ --exclude .gitignore --exclude .sass-cache/ --exclude bin/ --exclude 'tmp/*' --exclude 'Gemfile*' --exclude Movefile --exclude wp-config.php --exclude 'wp-content/*.sql' --exclude 'wp-content/*' /Applications/MAMP/htdocs/jjlaw/ :/var/www/html/jjlaw
building file list ... 
1096 files to consider

sent 22137 bytes  received 20 bytes  14771.33 bytes/sec
total size is 16314178  speedup is 736.30

▬▬ ✓ Pushing Uploads ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
   remote | put_directory: /Applications/MAMP/htdocs/jjlaw/wp-content/uploads /var/www/html/jjlaw/wp-content/uploads .git/ .gitignore .sass-cache/ bin/ tmp/* Gemfile* Movefile wp-config.php wp-content/*.sql wp-content/*
I, [2015-07-27T13:46:36.439542 #52357]  INFO -- : rsync --progress -e 'ssh root@###.###.##.###' -rlpt --compress --omit-dir-times --delete --exclude .git/ --exclude .gitignore --exclude .sass-cache/ --exclude bin/ --exclude 'tmp/*' --exclude 'Gemfile*' --exclude Movefile --exclude wp-config.php --exclude 'wp-content/*.sql' --exclude 'wp-content/*' /Applications/MAMP/htdocs/jjlaw/wp-content/uploads/ :/var/www/html/jjlaw/wp-content/uploads
building file list ... 
rsync: link_stat "/Applications/MAMP/htdocs/jjlaw/wp-content/uploads/." failed: No such file or directory (2)
0 files to consider

sent 178 bytes  received 20 bytes  132.00 bytes/sec
total size is 0  speedup is 0.00
rsync error: some files could not be transferred (code 23) at /SourceCache/rsync/rsync-45/rsync/main.c(992) [sender=2.6.9]

▬▬ ✓ Pushing Themes ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
   remote | put_directory: /Applications/MAMP/htdocs/jjlaw/wp-content/themes /var/www/html/jjlaw/wp-content/themes .git/ .gitignore .sass-cache/ bin/ tmp/* Gemfile* Movefile wp-config.php wp-content/*.sql wp-content/*
I, [2015-07-27T13:46:37.613782 #52357]  INFO -- : rsync --progress -e 'ssh root@###.###.##.###-rlpt --compress --omit-dir-times --delete --exclude .git/ --exclude .gitignore --exclude .sass-cache/ --exclude bin/ --exclude 'tmp/*' --exclude 'Gemfile*' --exclude Movefile --exclude wp-config.php --exclude 'wp-content/*.sql' --exclude 'wp-content/*' /Applications/MAMP/htdocs/jjlaw/wp-content/themes/ :/var/www/html/jjlaw/wp-content/themes
building file list ... 
255 files to consider

sent 5688 bytes  received 20 bytes  3805.33 bytes/sec
total size is 3586162  speedup is 628.27

▬▬ ✓ Pushing Plugins ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
   remote | put_directory: /Applications/MAMP/htdocs/jjlaw/wp-content/plugins /var/www/html/jjlaw/wp-content/plugins .git/ .gitignore .sass-cache/ bin/ tmp/* Gemfile* Movefile wp-config.php wp-content/*.sql wp-content/*
I, [2015-07-27T13:46:38.757478 #52357]  INFO -- : rsync --progress -e 'ssh root@###.###.##.###' -rlpt --compress --omit-dir-times --delete --exclude .git/ --exclude .gitignore --exclude .sass-cache/ --exclude bin/ --exclude 'tmp/*' --exclude 'Gemfile*' --exclude Movefile --exclude wp-config.php --exclude 'wp-content/*.sql' --exclude 'wp-content/*' /Applications/MAMP/htdocs/jjlaw/wp-content/plugins/ :/var/www/html/jjlaw/wp-content/plugins
building file list ... 
25 files to consider

sent 699 bytes  received 20 bytes  479.33 bytes/sec
total size is 148520  speedup is 206.56

▬▬ ✓ Pushing Languages ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
   remote | put_directory: /Applications/MAMP/htdocs/jjlaw/wp-content/languages /var/www/html/jjlaw/wp-content/languages .git/ .gitignore .sass-cache/ bin/ tmp/* Gemfile* Movefile wp-config.php wp-content/*.sql wp-content/*
I, [2015-07-27T13:46:39.847951 #52357]  INFO -- : rsync --progress -e 'ssh root@###.###.##.###' -rlpt --compress --omit-dir-times --delete --exclude .git/ --exclude .gitignore --exclude .sass-cache/ --exclude bin/ --exclude 'tmp/*' --exclude 'Gemfile*' --exclude Movefile --exclude wp-config.php --exclude 'wp-content/*.sql' --exclude 'wp-content/*' /Applications/MAMP/htdocs/jjlaw/wp-content/languages/ :/var/www/html/jjlaw/wp-content/languages
building file list ... 
rsync: link_stat "/Applications/MAMP/htdocs/jjlaw/wp-content/languages/." failed: No such file or directory (2)
0 files to consider

sent 178 bytes  received 20 bytes  132.00 bytes/sec
total size is 0  speedup is 0.00
rsync error: some files could not be transferred (code 23) at /SourceCache/rsync/rsync-45/rsync/main.c(992) [sender=2.6.9]

▬▬ ✓ Pushing Database ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
   remote | mysqldump --host=localhost --user=wordpress --password=######### wordpress > /var/www/html/jjlaw/wp-content/dump.sql
   remote | get: /var/www/html/jjlaw/wp-content/dump.sql /Applications/MAMP/htdocs/jjlaw/wp-content/live-backup-1438022801.sql
   remote | delete: /var/www/html/jjlaw/wp-content/dump.sql
    local | mysqldump --host=127.0.0.1 --user=root --password=root jjlaw > /Applications/MAMP/htdocs/jjlaw/wp-content/dump.sql
Warning: Using a password on the command line interface can be insecure.
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
/Users/Home/.rvm/gems/ruby-2.1.1/gems/wordmove-1.2.0/lib/wordmove/deployer/base.rb:147:in `run': Return code reports an error (RuntimeError)
    from /Users/Home/.rvm/gems/ruby-2.1.1/gems/wordmove-1.2.0/lib/wordmove/deployer/base.rb:210:in `save_local_db'
    from /Users/Home/.rvm/gems/ruby-2.1.1/gems/wordmove-1.2.0/lib/wordmove/deployer/ssh.rb:20:in `push_db'
    from /Users/Home/.rvm/gems/ruby-2.1.1/gems/wordmove-1.2.0/lib/wordmove/cli.rb:58:in `block in push'
    from /Users/Home/.rvm/gems/ruby-2.1.1/gems/wordmove-1.2.0/lib/wordmove/cli.rb:34:in `block in handle_options'
    from /Users/Home/.rvm/gems/ruby-2.1.1/gems/wordmove-1.2.0/lib/wordmove/cli.rb:32:in `each'
    from /Users/Home/.rvm/gems/ruby-2.1.1/gems/wordmove-1.2.0/lib/wordmove/cli.rb:32:in `handle_options'
    from /Users/Home/.rvm/gems/ruby-2.1.1/gems/wordmove-1.2.0/lib/wordmove/cli.rb:57:in `push'
    from /Users/Home/.rvm/gems/ruby-2.1.1/gems/thor-0.19.1/lib/thor/command.rb:27:in `run'
    from /Users/Home/.rvm/gems/ruby-2.1.1/gems/thor-0.19.1/lib/thor/invocation.rb:126:in `invoke_command'
    from /Users/Home/.rvm/gems/ruby-2.1.1/gems/thor-0.19.1/lib/thor.rb:359:in `dispatch'
    from /Users/Home/.rvm/gems/ruby-2.1.1/gems/thor-0.19.1/lib/thor/base.rb:440:in `start'
    from /Users/Home/.rvm/gems/ruby-2.1.1/gems/wordmove-1.2.0/bin/wordmove:6:in `<top (required)>'
    from /Users/Home/.rvm/gems/ruby-2.1.1/bin/wordmove:23:in `load'
    from /Users/Home/.rvm/gems/ruby-2.1.1/bin/wordmove:23:in `<main>'
    from /Users/Home/.rvm/gems/ruby-2.1.1/bin/ruby_executable_hooks:15:in `eval'
    from /Users/Home/.rvm/gems/ruby-2.1.1/bin/ruby_executable_hooks:15:in `<main>'

There seems to be an issue with the Database Dump. It’s creating backup files, but it’s not outputting anything to the server. I’ll add an image of the backup files being created below.

Lastly, here is my move file:

local:
  vhost: "http://localhost:8888/jjlaw"
  wordpress_path: "/Applications/MAMP/htdocs/jjlaw" # use an absolute path here

  database:
    name: "jjlaw"
    user: "root"
    password: "root"
    host: "127.0.0.1"

live:
  vhost: "http://###.###.##.###"
  wordpress_path: "/var/www/html/jjlaw" # use an absolute path here

  database:
    name: "wordpress"
    user: "wordpress"
    password: "##########"
    host: "localhost"
    # port: "3308" # Use just in case you have exotic server config

  exclude:
    - ".git/"
    - ".gitignore"
    - ".sass-cache/"
    - "bin/"
    - "tmp/*"
    - "Gemfile*"
    - "Movefile"
    - "wp-config.php"
    - "wp-content/*.sql"

  # paths: # you can customize wordpress internal paths
  #   wp_content: "wp-content"
  #   uploads: "wp-content/uploads"
  #   plugins: "wp-content/plugins"
  #   themes: "wp-content/themes"
  #   languages: "wp-content/languages"
  #   themes: "wp-content/themes"

  ssh:
    host: "###.###.##.###"
    user: "root"
  #   password: "password" # password is optional, will use public keys if available.
  #   port: 22 # Port is optional
  #   rsync_options: "--verbose" # Additional rsync options, optional
  #   gateway: # Gateway is optional
  #     host: "host"
  #     user: "user"
  #     password: "password" # password is optional, will use public keys if available.

As far as I can tell, my passwords are correct and I don’t have any indention errors on the page. The ssh is working fine for both temial ssh as well as using an FTP client. I cannot get past the mySql 1045 error and thought you guys might have some idea to help. Thanks again for the efforts on the gem.

All the best,

Russell Brown
@russellbrownco

During our work in support, we see this again and again: “I try to connect to MySQL and am getting a 1045 error”, and most times it comes accompanied with “…but I am sure my user and password are OK”.  So we decided it was worth showing other reasons this error may occur.

MySQL 1045 error Access Denied triggers in the following cases:

1) Connecting to wrong host:

If not specifying the host to connect (with -h flag), MySQL client will try to connect to the localhost instance while you may be trying to connect to another host/port instance.

Fix: Double check if you are trying to connect to localhost, or be sure to specify host and port if it’s not localhost:

2) User does not exist:

Fix: Double check if the user exists:

If the user does not exist, create a new user:

3) User exists but client host does not have permission to connect:

Fix: You can check to see which host user/host MySQL allows connections with the following query:

If you need to check from which IP the client is connecting, you can use the following Linux commands for server IP:

or for public IP:

You can then create a user with correct Host (client IP), or with ‘%’ (wildcard) to match any possible IP:

4) Password is wrong, or the user forgot his password:

Fix: Check and/or reset password:

You cannot read user passwords in plain text from MySQL as the password hash is used for authentication, but you can compare hash strings with “PASSWORD” function:

We can see that PASSWORD(‘forgotten’) hash does not match the authentication_string column, which means password string=’forgotten’ is not the correct password to log in. Also, in case the user has multiple hosts (with different password), he may be trying to connect using the password for the wrong host.

In case you need to override the password you can execute the following query:

5) Special characters in the password being converted by Bash:

Fix: Prevent bash from interpreting special characters by wrapping password in single quotes:

6) SSL is required but the client is not using it:

Fix: Adding –ssl-mode flag (–ssl flag is deprecated but can be used too)

You can read more in-depth on how to configure SSL in MySQL in the blog post about “Setting up MySQL SSL and Secure Connections” and “SSL in 5.6 and 5.7“.

7) PAM backend not working:

Fix: Double check user/password is correct for the user to authenticate with the PAM currently being used.

In my example, I am using Linux shadow files for authentication. In order to check if the user exists:

To reset password:

Finally, if you are genuinely locked out and need to circumvent the authentication mechanisms in order to regain access to the database, here are a few simple steps to do so:

1. Stop the instance
2. Edit my.cnf and add skip-grant-tables under [mysqld] (this will allow access to MySQL without prompting for a password). On MySQL 8.0, skip-networking is automatically enabled (only allows access to MySQL from localhost), but for previous MySQL versions it’s suggested to also add –skip-networking under [mysqld]
3. Start the instance
4. Access with root user (mysql -uroot -hlocalhost); 

5. Issue the necessary GRANT/CREATE USER/SET PASSWORD to correct the issue (likely setting a known root password will be the right thing: SET PASSWORD FOR ‘root’@’localhost’ = ‘S0vrySekr3t’). Using grant-skip-tables won’t read grants into memory and GRANT/CREATE/SET PASSWORD statements won’t work straight away. First, you need to execute “FLUSH PRIVILEGES;” before executing any GRANT/CREATE/SET PASSWORD statement, or you can modify mysql.users table with a query which modifies the password for User and Host like “UPDATE mysql.user SET authentication_string=PASSWORD(‘newpwd’) WHERE User=’root’ and Host=’localhost’;”

6. Stop the instance
7. Edit my.cnf and remove skip-grant-tables and skip-networking
8. Start MySQL again
9. You should be able to login with root from the localhost and do any other necessary corrective operations with root user.
   

Learn more about Percona Server for MySQL