List:General Discussion« Previous MessageNext Message »
From:Rafał Radecki Date:February 4 2013 8:16am
Subject:Mysqldump routines dump, problem with lock tables.
View as plain text  
Hi All.

I use:

# rpm -qa | grep -i percona-server-server
Percona-Server-server-55-5.5.28-rel29.3.388.rhel6.x86_64

My system:

# uname -a;cat /etc/redhat-release
Linux prbc01.mg.local 2.6.32-279.19.1.el6.centos.plus.x86_64 #1 SMP
Wed Dec 19 06:20:23 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
Red Hat Enterprise Linux Server release 6.3 (Santiago)

I have a backup script which at some point calls:

mysqldump --default-character-set=utf8 --routines --no-data
--no-create-info --skip-triggers -S /mysql/database.sock -u backup
-pxxx database

and I have error:

mysqldump: Got error: 1045: Access denied for user 'yyy'@'zzz' (using
password: YES) when using LOCK TABLES

So I thinke that mysqldump locks the table (--add-locks) by default.

But for this user:

mysql> show grants for yyy@'zzz';
+------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for backup@localhost

   |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW ON *.* TO 'yyy'@'zzz'
IDENTIFIED BY PASSWORD ... |
                                      |
+------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

So why is this error showing?
When I add --single-transaction to mysqldump everything is ok. But I
would like to have this table locked because:

mysql> SELECT ENGINE
    -> FROM information_schema.TABLES
    -> WHERE TABLE_SCHEMA = 'information_schema'
    -> AND TABLE_NAME = 'routines';
+--------+
| ENGINE |
+--------+
| MyISAM |
+--------+

so information_schema.tables is myisam.

So why do I get the error about LOCK TABLES?

Best regards,
Rafal Radecki.
Thread
Mysqldump routines dump, problem with lock tables.Rafał Radecki4 Feb
  • RE: Mysqldump routines dump, problem with lock tables.Rick James4 Feb