Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
Appreciate any help on this. Any alternative of mysqldump is also fine with me to take backup while restricting root login access from localhost only. Thanks in advance. Regards, Tanmay On Wed, Oct 20, 2010 at 10:14 AM, Tanmay Pradhan wrote: > Yes, r...@localhost entry is still present in user table. Only > root@'%' is deleted. So it's not obvious to fail. > > Hi yu.zou, > The r...@localhost entry already had all privileges, except this entry > had empty password column. > > u...@localhost entry before GRANT > > ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ > | Host | User | Password > | Select_priv | Insert_priv | Update_priv | Delete_priv | > Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | > File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | > Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | > Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | > Show_view_priv | Create_routine_priv | Alter_routine_priv | > Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | > x509_issuer | x509_subject | max_questions | max_updates | > max_connections | max_user_connections | > ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ > | localhost | root | > | Y | Y | Y | Y | > Y | Y | Y | Y | Y | > Y | Y | Y | Y | Y | Y > | Y | Y | Y | Y > | Y | Y | Y | Y > | Y | Y | Y > | Y | Y | | | > | | 0 | 0 | 0 | > 0 | > > > However, I still gave the following cmd. > > mysql> GRANT select, lock tables ON *.* TO 'root'@'localhost' > IDENTIFIED BY 'password'; > mysql> flush privileges; > > u...@localhost entry after GRANT > == > ++-+---+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--++--+--++-+--+---+-+-+--+ > | Host | User | Password > | Select_priv | Insert_priv | Update_priv | Delete_priv | > Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | > File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | > Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | > Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | > Show_view_priv | Create_routine_priv
Re: mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
---+--+
| localhost | root|
*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | Y | Y
| Y | Y | Y | Y | Y |
Y | Y| Y | Y | Y
| Y | Y | Y| Y | Y
| Y| Y| Y | Y
| Y| Y | Y | Y
| Y| Y | Y|
|| | | 0 |
0 | 0 |0 |
Still mysqldump fails.
Hi michael dykman,
I dropped all procedures, still mysqldump failed.
What else could be the issue?
Thanks for the help.
Regards,
Tanmay
On Wed, Oct 20, 2010 at 12:35 AM, Michael Dykman wrote:
> Of course you know you did not delete r...@localhost, just root @ '%'
> which generally should not matter to mysqldump.
>
> What I suspect is the issue here is that the database you are trying
> to dump contains procedures/methods that were defined by a user while
> logged in as root@'%'. If you recreate those procs as your database
> user, this should correct.
>
> - michael dykman
>
>
> On Tue, Oct 19, 2010 at 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
>>
>> Krishna
>> CGI.COM
>>
>>
>>
>> On Tue, Oct 19, 2010 at 11:06 AM, Tanmay Pradhan 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
>>>
>>> Even following cmd failed:
>>> $ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -p
>>> ABC_DATABASE > abc.dump
>>> mysqldump: Got error: 1045: Access denied for user 'user1'@'>> Address>' (using password: YES) when using LOCK TABLES
>>>
>>> Can anybody advise as how to make mysqldump work while restricting
>>> root login access from localhost only?
>>>
>>> Thanks for any help.
>>>
>>> Regards,
>>> Tanmay
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>>> http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
>>>
>>>
>>
>
>
>
> --
> - michael dykman
> - mdyk...@gmail.com
>
> May the Source be with you.
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES
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
Even following cmd failed:
$ /usr/local/mysql/bin/mysqldump -h host_name -u user1 -p
ABC_DATABASE > abc.dump
mysqldump: Got error: 1045: Access denied for user 'user1'@'' (using password: YES) when using LOCK TABLES
Can anybody advise as how to make mysqldump work while restricting
root login access from localhost only?
Thanks for any help.
Regards,
Tanmay
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
