Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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*
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.