PDA

View Full Version : ZRM Restore Fails on ALTER/DROP command absence



sogrady
January 12th, 2007, 09:49 PM
Trying to backup/restore 5 MySQL databases locally. Machine is Ubuntu LTS, ZRM 1.13 installed via .DEB's on Zmanda.com.

Followed the instructions here:

http://zmanda.com/quick-mysql-backup.html

Backup proceeded fine, apart from an error with sending mail notification.

Restore, however, failed. I dropped one database (left the other four intact - could that be the problem?), per the instructions, and on restore the job failed.

First error was:
"DROP command denied to user 'XXX'@'localhost'"

So I added DROP to the user's rights and reran the job.

Next error was:
"ALTER command denied to user 'XXX'@'localhost'"

At that point I quit, and restored the database from a local copy I dumped prior to the backup. Anybody have any idea on what's going on?

paddy
January 14th, 2007, 03:57 PM
What privileges does your restore database user (XXX@localhost) have?

Please see the ZRM for MySQL user's manual (http://mysqlbackup.zmanda.com/index.php/Pre-Installation#MySQL_Backup_User) for information on privileges required for restore user.

Thanks,
Paddy

sogrady
January 14th, 2007, 04:16 PM
thanks for the reply, paddy.

the user in this case has the rights recommended by the HowTo. these, in other words:

grant create, insert, update, lock tables, select, grant option, super, replication client, reload, shutdown on *.* to 'backup-user'@'localhost' identified by 'pass123';

i added DROP, but hesitated to add ALTER given that it seems like something's off.

my working assumption at this point is that the problem is that the restore tried to restore 5 databases while i'd only dropped one.

does that make sense?

kkg
January 14th, 2007, 07:26 PM
thanks for the reply, paddy.

the user in this case has the rights recommended by the HowTo. these, in other words:

grant create, insert, update, lock tables, select, grant option, super, replication client, reload, shutdown on *.* to 'backup-user'@'localhost' identified by 'pass123';

i added DROP, but hesitated to add ALTER given that it seems like something's off.
my working assumption at this point is that the problem is that the restore tried to restore 5 databases while i'd only dropped one.

does that make sense?

If you open up the output of mysqldump, you will find a number of ALTER commands such as "/*!40000 ALTER TABLE `user` ENABLE KEYS */;

This is the reason why the user doing a restore need ALTER rights.

Similarly there are a number of DROP table commands in the output of mysqldump and that is why the user doing restore need DROP rights.

But that brings us to another question. You had said that initially you tried to restore using ZRM but you got the error saying you need DROP and then ALTER rights. After that you said that you restored the database using a locally saved dump.

If you able to restore from the locally saved dump, then the user you used to do that must have had the ALTER rights. So my question is did you use the same user to restore when tried to restore using ZRM as well aswhen you did a restore from your local dump? If you used the same user, then we do have something more to this as I have tried doing a mysqldump and tried to restore using a user that did not have ALTER rights and I got the same error. But if I used a user that had ALTER rights I did not get this error.

BTW please do refer to http://mysqlbackup.zmanda.com/index.php/Pre-Installation
for list of user rights required for Backup and Restore.

Hope this is helpful for you.
--kkg

sogrady
January 14th, 2007, 07:46 PM
thanks so much for the reply, kkg. a couple of responses:

"If you open up the output of mysqldump, you will find a number of ALTER commands such as "/*!40000 ALTER TABLE `user` ENABLE KEYS */;

This is the reason why the user doing a restore need ALTER rights.

Similarly there are a number of DROP table commands in the output of mysqldump and that is why the user doing restore need DROP rights."

interesting. i'm curious as to why the HowTo doesn't have you grant these rights then, and how the restore in that HowTo will work without them.

"But that brings us to another question. You had said that initially you tried to restore using ZRM but you got the error saying you need DROP and then ALTER rights. After that you said that you restored the database using a locally saved dump.

If you able to restore from the locally saved dump, then the user you used to do that must have had the ALTER rights. So my question is did you use the same user to restore when tried to restore using ZRM as well aswhen you did a restore from your local dump?"

i did indeed use a different user; the given database user, who has basically all rights. and i just looked at the rights list - you're correct, both alter and drop are required.

interesting - maybe the HowTo should be updated.

kkg
January 14th, 2007, 08:07 PM
thanks so much for the reply, kkg. a couple of responses:

"If you open up the output of mysqldump, you will find a number of ALTER commands such as "/*!40000 ALTER TABLE `user` ENABLE KEYS */;

This is the reason why the user doing a restore need ALTER rights.

Similarly there are a number of DROP table commands in the output of mysqldump and that is why the user doing restore need DROP rights."

interesting. i'm curious as to why the HowTo doesn't have you grant these rights then, and how the restore in that HowTo will work without them.

"But that brings us to another question. You had said that initially you tried to restore using ZRM but you got the error saying you need DROP and then ALTER rights. After that you said that you restored the database using a locally saved dump.

If you able to restore from the locally saved dump, then the user you used to do that must have had the ALTER rights. So my question is did you use the same user to restore when tried to restore using ZRM as well aswhen you did a restore from your local dump?"

i did indeed use a different user; the given database user, who has basically all rights. and i just looked at the rights list - you're correct, both alter and drop are required.

interesting - maybe the HowTo should be updated.


Yes you are absolutely correct, the HOWTO is confusing.

The reason why the HOWTO worked will be clear if you look
carefully at following statements. First it asks you to add a certain set of rights for "backup-user"

"
* mysql> grant create, insert, update, lock tables, select, grant option, super, replication client, reload, shutdown on *.* to 'backup-user'@'localhost' identified by 'pass123';
"

Then it asks you to view the rights for "backup-user" and the output of that shows that the user is already having DROP and ALTER rights!!!

"
* mysql> show grants for 'backup-user'@'localhost';
* Grants for backup-user@localhost
* | GRANT SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, ALTER, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup-user'@'localhost' IDENTIFIED BY PASSWORD '591051bb593d2f5b' WITH GRANT OPTION |
"

sogrady
January 14th, 2007, 08:23 PM
ah, i'd totally missed that - great catch, kkg.

will grant alter rights and retry. will post again once i've completed a dump/backup/restore cycle.

meantime, i'll make a note to ping the folks re: the HowTo.

sogrady
January 14th, 2007, 08:33 PM
outstanding. that did it. restore worked perfectly.
thanks so much for your help, kkg.

paddy
January 15th, 2007, 08:33 AM
> ah, i'd totally missed that - great catch, kkg.

> will grant alter rights and retry. will post again once i've completed a dump/backup/restore cycle.

> meantime, i'll make a note to ping the folks re: the HowTo.

Thanks for pointing out the mistake in the how to document. We will make the change ASAP.

Paddy

shailen
January 15th, 2007, 09:34 AM
The document has been fixed.

Thanks
-Shailen