PHWinfo - Afficher un message

27/03/2006, 23h55

"Robert Blackwell" <me@email.com> wrote in message
news:kiZVf.5145$VL2.5013@trnddc04...
> mysqldump: Got error: 1044: Access denied for user 'user'@'%' to database
> 'test' when using LOCK TABLES.
> I didn't know I locked the tables... or how to for that matter. I think if
> you can walk me through unlocking them the script should hopefully work.

Locking tables is a normal part of the work done by mysqldump. The locking
is enabled by the "--lock-tables" option, which is implicit when using the
"--opt" option, which is the default. (How's that for convoluted?)

A user can lock tables only if he/she has been granted the LOCK TABLES
privilege. Apparently, the user you're using to do the mysqldump lacks this
privilege, if you're seeing an Access Denied error.

> Then I tried using the root user account and it did dump the file...

The root user is has all privileges, so it won't get the Access Denied
error.

> I just created a brand new db from a dump that I had made a while ago.
> Thedump I imported happend to be 46.5MB. Now, when I ran this batch file
> the dump it made is only 21.2MB. Can there be that much fluctuation in a
> dump?

Yes, it is possible for the output of two mysqldumps of the same database to
vary greatly in size, depending on the options used when running the
commands (e.g. --complete-insert, --extended-insert, etc.).

But if you want to double-check the databases, restore the dump file to some
new test database, and do some SELECT COUNT(*) queries on the tables.
Compare these to similar queries on your original table. If the results are
different, you know something is wrong. If the results are the same, you
know at least that the same number of records were backed up and restored
(but not necessarily that the content is intact).

> I'd rather not use the root user in my bat so it would be nice to figure
> out what permission/locking is preventing me from dumping with my other
> users.

Yes, you can grant the right privileges as root:

mysql> use mysql;
mysql> GRANT SELECT, LOCK TABLES ON *.* TO 'user'@'%' IDENTIFIED BY
'password';

Regards,
Bill K.

27/03/2006, 23h55	#6
Bill Karwin Messages: n/a Hébergeur:	Re: sheduled mysqldump via windows bat file "Robert Blackwell" <me@email.com> wrote in message news:kiZVf.5145$VL2.5013@trnddc04... > mysqldump: Got error: 1044: Access denied for user 'user'@'%' to database > 'test' when using LOCK TABLES. > I didn't know I locked the tables... or how to for that matter. I think if > you can walk me through unlocking them the script should hopefully work. Locking tables is a normal part of the work done by mysqldump. The locking is enabled by the "--lock-tables" option, which is implicit when using the "--opt" option, which is the default. (How's that for convoluted?) A user can lock tables only if he/she has been granted the LOCK TABLES privilege. Apparently, the user you're using to do the mysqldump lacks this privilege, if you're seeing an Access Denied error. > Then I tried using the root user account and it did dump the file... The root user is has all privileges, so it won't get the Access Denied error. > I just created a brand new db from a dump that I had made a while ago. > Thedump I imported happend to be 46.5MB. Now, when I ran this batch file > the dump it made is only 21.2MB. Can there be that much fluctuation in a > dump? Yes, it is possible for the output of two mysqldumps of the same database to vary greatly in size, depending on the options used when running the commands (e.g. --complete-insert, --extended-insert, etc.). But if you want to double-check the databases, restore the dump file to some new test database, and do some SELECT COUNT() queries on the tables. Compare these to similar queries on your original table. If the results are different, you know something is wrong. If the results are the same, you know at least that the same number of records were backed up and restored (but not necessarily that the content is intact). > I'd rather not use the root user in my bat so it would be nice to figure > out what permission/locking is preventing me from dumping with my other > users. Yes, you can grant the right privileges as root: mysql> use mysql; mysql> GRANT SELECT, LOCK TABLES ON .* TO 'user'@'%' IDENTIFIED BY 'password'; Regards, Bill K.