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.
> On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhan<tanmay04@stripped> wrote:
>> 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
>> 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>
>> 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.
>> 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:
>> 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
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN