List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:October 20 2010 1:36am
Subject:Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%')
does not exist when using LOCK TABLES
View as plain text  
Hellpo Krishna,

On 10/19/2010 8:40 AM, Krishna Chandra Prajapati wrote:
> Hi Pradhan,
>
> Obviously, it should fail. Since you have deleted the root user which is
> used by mysqldump for making connection to mysql server for taking backup
>

Not true. The utility mysqldump is just a client like any other program 
and can authenticate with the MySQL instance (the database daemon) as 
any valid user.


> Krishna
> CGI.COM
>
>
>
> On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhan<tanmay04@stripped>  wrote:
>
>> Hi,
>>
>> I am using the following version of MySQL on my Mac OS X Server 10.5.8:
>> *** Ver 14.14 Distrib 5.4.1-beta, for apple-darwin9.5.0 (i386) using
>> readline 5.1 ***
>>
>> In order to restrict root account login from localhost only, I did the
>> following:
>> mysql>  DELETE FROM user WHERE user = 'root' AND host = '%';
>> mysql>  FLUSH PRIVILEGES;
>>
>> After this,
>> mysqldump failed with the following error:
>> $ /usr/local/mysql/bin/mysqldump -h localhost -u root ABC_DATABASE>
>> abc.dump
>> mysqldump: Got error: 1449: The user specified as a definer
>> ('root'@'%') does not exist when using LOCK TABLES
>>

To fix this, you need to reset the DEFINER for a TRIGGER defined within 
the database so that it is defined as a valid user account.
http://dev.mysql.com/doc/refman/5.1/en/triggers.html


>> Even following cmd failed:
>> $ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -pxxxx
>> ABC_DATABASE>  abc.dump
>> mysqldump: Got error: 1045: Access denied for user 'user1'@'<IP
>> Address>' (using password: YES) when using LOCK TABLES
>>

In order to issue the LOCK TABLES command, a user needs certain privileges:
http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html


>> Can anybody advise as how to make mysqldump work while restricting
>> root login access from localhost only?
>>

Securing MySQL is fairly easy. Check out this guide in the manual for 
details:
http://dev.mysql.com/doc/refman/5.1/en/security.html

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
Thread
mysqldump: Got error: 1449: The user specified as a definer('root'@'%') does not exist when using LOCK TABLESTanmay Pradhan19 Oct
  • Re: mysqldump: Got error: 1449: The user specified as a definer('root'@'%') does not exist when using LOCK TABLESKrishna Chandra Prajapati19 Oct
    • Re: mysqldump: Got error: 1449: The user specified as a definer('root'@'%') does not exist when using LOCK TABLESMichael Dykman19 Oct
      • Re: mysqldump: Got error: 1449: The user specified as a definer('root'@'%') does not exist when using LOCK TABLESTanmay Pradhan20 Oct
        • Re: mysqldump: Got error: 1449: The user specified as a definer('root'@'%') does not exist when using LOCK TABLESTanmay Pradhan21 Oct
    • Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%')does not exist when using LOCK TABLESMySQL)20 Oct