Mysql – Failed to restore thesql database with zmanda

backupMySQLrestore

I want to setup a slave from my backup version of mysql using zmanda (it's a full backup).
I've followed this guide.

Result of mysql-zrm-reporter

          backup_set  backup_date                  backup_level  backup_status         backup_type       comment
-----------------------------------------------------------------------------------------------------------------------------
               6.248  Sun 22 Jul 2012 02:15:15                0  Backup succeeded      regular           ----
                      PM ICT

What I grant for backup user:

Grants for backup@192.168.6.38: GRANT SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, ALTER, SHOW DATABASES, SUPER, LOCK TABLES, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO 'backup'@'192.168.6.38' IDENTIFIED BY PASSWORD '****************' WITH GRANT OPTION

When run mysql-zrm --action restore --backup-set 6.38 --source-directory /data/var/lib/mysql-zrm/6.38/20120722141515/, I got:

restore:INFO: ZRM for MySQL Community Edition - version 2.2.0
6.38:restore:INFO: The quick backup-type is supported only for snapshot backups. Setting backup-type to 'regular'
6.38:restore:INFO: Mail address: sysadmin@mycompany.vn is ok
6.38:restore:INFO: ZRM Temporary configuration file = /etc/mysql-zrm/6.38/tmpRPQbB.conf
6.38:restore:INFO: {
6.38:restore:INFO:      verbose=1
6.38:restore:INFO:      encrypt-plugin=/usr/share/mysql-zrm/plugins/encrypt.pl
6.38:restore:INFO:      retention-policy=10W
6.38:restore:INFO:      mysql-binpath=/usr/bin
6.38:restore:INFO:      all-databases=1
6.38:restore:INFO:      default-character-set=latin1
6.38:restore:INFO:      destination=/data/var/lib/mysql-zrm
6.38:restore:INFO:      decrypt-option=-d
6.38:restore:INFO:      source-directory=/data/var/lib/mysql-zrm/6.38/20120722141515/
6.38:restore:INFO:      routines=1
6.38:restore:INFO:      password=******
6.38:restore:INFO:      backup-mode=logical
6.38:restore:INFO:      backup-type=regular
6.38:restore:INFO:      compress-plugin=/usr/bin/gzip
6.38:restore:INFO:      user=backup
6.38:restore:INFO:      copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl
6.38:restore:INFO:      backup-level=0
6.38:restore:INFO:      replication=1
6.38:restore:INFO:      encrypt=/usr/share/mysql-zrm/plugins/encrypt.pl
6.38:restore:INFO:      mailto=sysadmin@mycompany.com
6.38:restore:INFO:      html-reports=backup-status-info
6.38:restore:INFO:      port=3306
6.38:restore:INFO:      ssh-user=mysql
6.38:restore:INFO:      host=192.168.6.38
6.38:restore:INFO:      single-transaction=1
6.38:restore:INFO:      mail-policy=only-on-error
6.38:restore:INFO:      html-report-directory=/var/www/mysql-zrm/reports/
6.38:restore:INFO:      exclude-pattern=performance_schema
6.38:restore:INFO:      compress=/usr/bin/gzip
6.38:restore:INFO:      mysql-binlog-path=/data/var/log/mysql
6.38:restore:INFO: }
6.38:restore:INFO: Getting mysql variables
6.38:restore:INFO: "/usr/bin"/mysqladmin --user="backup" --password="*****" --host="192.168.6.38" --port="3306" variables
6.38:restore:INFO: datadir is /data/var/lib/mysql2/
6.38:restore:INFO: mysql_version is 5.5.25-cll
6.38:restore:INFO: InnoDB data file are /data/var/lib/mysql2/ibdata1
6.38:restore:INFO: InnoDB log dir is /data/var/lib/mysql2/.
6.38:restore:INFO: Command used is 'cat "/data/var/lib/mysql-zrm/6.38/20120722141515/backup-data"  | "/usr/share/mysql-zrm/plugins/encrypt.pl" -d |  "/usr/bin
/gzip" -d  | tar  --same-owner -xpsC  "/data/var/lib/mysql-zrm/6.38/20120722141515/"  2>/tmp/CnVB7wl750'
6.38:restore:INFO: Checking if this is a replication slave using command
6.38:restore:INFO: "/usr/bin"/mysql --user="backup" --password="*****" --host="192.168.6.38" --port="3306" -e "show slave status"
6.38:restore:INFO: This is not a replication slave or we do not have appropriate access rights. Replication data if any has not been backed up.
6.38:restore:INFO:  Ignoring the --replication option
6.38:restore:INFO: Executing pre-restore-plugin
6.38:restore:INFO: restoring using command "/usr/bin"/mysql --user="backup" --password="*****" --host="192.168.6.38" --port="3306" -e "set character_set_clien
t=utf8;set character_set_connection=utf8;set character_set_database=utf8;set character_set_results=utf8;set character_set_server=utf8;source /tmp/NWxPqSUUzb;"
ERROR 1044 (42000) at line 2630 in file: '/tmp/NWxPqSUUzb': Access denied for user 'backup'@'192.168.6.38' to database 'BookingOnline'
6.38:restore:ERROR: Restore from logical backup failed
6.38:restore:INFO: Removing all of the uncompressed/unencrypted data
6.38:restore:INFO: Executing post-restore-plugin
6.38:restore:ERROR: Restore failed

As you can see, zmanda complain that access denied for user 'backup'@'192.168.6.38', but If I log in with this user, I can select, update… in database BookingOnline without problem? So what I miss here? How can I debug this?

Best Answer

The restore user needs more permissions then the backup user. Is this setup correctly for you?

From the docs

Minimal set of MySQL privileges that MySQL users should have to do backups and recovery are:
backup user 
LOCK TABLES, SELECT, FILE, RELOAD, SUPER, SHOW VIEW*
restore user 
CREATE, DROP, INDEX, SHUTDOWN, INSERT, ALTER, SUPER, REPLICATION CLIENT, CREATE VIEW*
Related Question