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
