20 January

mysqldump: Got error: 1045: Access denied when using LOCK TABLES

Written by 

Problem

"Access denied" while using mysqldump to backup a database, regardless of the user you are connected with.

You probably have script that executes mysqldump in the following way:

C:\Windows\system32>"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldump" mydbname -ubackup -pxxxxxxxxxx  1>D:\Backup\MYSQL\20-01-2016_005609\mydbname.sql
Warning: Using a password on the command line interface can be insecure.
mysqldump: Got error: 1045: Access denied for user 'backup'@'localhost' (using password: YES) when using LOCK TABLES
 
C:\Windows\system32>"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldump" mydbname --opt --single-transaction -ubackup -pxxxxxxxxxx 1>D:\Backup\MYSQL\20-01-2016_005609\mydbname.sql
Warning: Using a password on the command line interface can be insecure.
mysqldump: Couldn't execute 'show create table `jos_jf_languages`': SHOW VIEW command denied to user 'backup'@'localhost' for table 'jos_jf_languages' (1142)

Solution

#Point 1

ITS NOT USER RELATED! - Obviously the user you are trying to connect with should have at least "read" and "lock" priveliges on that specific database, but stop flushing the privileges because is not related to that.

#Point 2

The password is right! Dont worry you have tried it hundreds of times! :P

How to:

If you are getting that sort of error the solution is to run "Analize and Repair" table commands from phpmyadmin or using the option "--opt --single-transaction" in the command to check for the table that is preventing the lock.

If the table cannot be repaired the only way is to delete the table and reconstruct it.

Corrupt database tables cause the lock to fail and therefore the db from being backed up.

Read 8729 times Last modified on Saturday, 05 November 2016 19:20
Rate this item
(0 votes)

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

Latest Posts

Contacts

João Vieira

Skype: jcv.pt

Email: info@joao-vieira.pt

About

This is my personal page, here you will find IT related, projects, discussions and reviews. Feel free to coment and leave your input.