Results 1 to 10 of 10

Thread: ZRM Restore Fails on ALTER/DROP command absence

  1. #1

    Default ZRM Restore Fails on ALTER/DROP command absence

    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:

    [url]http://zmanda.com/quick-mysql-backup.html[/url]

    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?

  2. #2
    Join Date
    Oct 2005
    Posts
    1,559

    Default

    What privileges does your restore database user ([email protected]) have?

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

    Thanks,
    Paddy

  3. #3

    Default

    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?

  4. #4
    Join Date
    Oct 2006
    Posts
    199

    Default

    Quote Originally Posted by sogrady View Post
    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 [url]http://mysqlbackup.zmanda.com/index.php/Pre-Installation[/url]
    for list of user rights required for Backup and Restore.

    Hope this is helpful for you.
    --kkg

  5. #5

    Default

    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.

  6. #6
    Join Date
    Oct 2006
    Posts
    199

    Default

    Quote Originally Posted by sogrady View Post
    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 [email protected]
    * | 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 |
    "

  7. #7

    Default

    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.

  8. #8

    Default

    outstanding. that did it. restore worked perfectly.
    thanks so much for your help, kkg.

  9. #9
    Join Date
    Oct 2005
    Posts
    1,559

    Default

    > 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

  10. #10
    Join Date
    Jan 2007
    Posts
    56

    Default

    The document has been fixed.

    Thanks
    -Shailen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •