Results 1 to 2 of 2

Thread: Remote Raw Backup Fails with ssh-copy Plugin

  1. #1

    Question Remote Raw Backup Fails with ssh-copy Plugin

    Hi,

    I'm out of solutions now as to what's causing this problem. Whenever I issue the command as root:

    [root@mysql-zrm-backupserver /]# mysql-zrm --action backup --backup-set test

    test:backup:ERROR: Output of command: 'mysqlhotcopy' is {
    DBI connect(';host=xx.xx.xx.141;mysql_read_default_gro up=mysqlhotcopy','backupuser',...) failed: Host 'backuphost.com' is not allowed to connect to this MySQL server at /usr/bin/mysqlhotcopy line 177
    mysqlhotcopy on host xx.xx.xx.141 failed at /usr/share/mysql-zrm/plugins/ssh-copy.pl line 322.
    }
    test:backup:ERROR: mysqlhotcopy did not succeed. Command used is /usr/share/mysql-zrm/plugins/ssh-copy.pl --mysqlhotcopy --user="backupuser" --password="*****" --host="xx.xx.xx.141" --quiet mysql "/var/lib/mysql-zrm/test/20090710160508" > /tmp/wxkJ4DbJZq 2>&1 Return value is 65280
    test:backup:INFO: PHASE START: Cleanup
    test:backup:INFO: backup-status=Backup failed
    test:backup:INFO: Backup failed
    test:backup:INFO: PHASE END: Cleanup
    test:backup:INFO: END OF BACKUP
    It only seems to occur when using RAW backup since logical mode always resulted to a successful operation.

    I'm not sure if I have covered all of my bases, but this is what I have done so far:

    1. set public key authentication between MySQL server and remote mysql-zrm server. I can prove that this works with this output. I actually did this on both servers just to be sure:
    [root@mysql-zrm-backupserver /]# ssh [email]mysql@xx.xx.xx[/email].141
    Last login: Fri Jul 10 16:22:35 2009 from xx-xx-xx-85.mysql-zrm-backupserver.com
    -bash-3.2$

    [root@mysql-server /]# ssh [email]mysql@xx.xx.xx[/email].85
    Last login: Fri Jul 10 16:22:35 2009 from xx-xx-xx-141.mysql-server.com
    -bash-3.2$
    2. set permissions for mysql-zrm user (backupuser) on the MySQL server. Here's how I did it:
    On MySQL server:
    mysql -u root -p
    <enter password for root>
    GRANT LOCK TABLES,SELECT,FILE,CREATE,DROP,INDEX,SHUTDOWN,ALTE R,INSERT,SUPER,RELOAD ON *.* TO 'backupuser'@'xx.xx.xx.85' IDENTIFIED BY 'password';
    quit
    - to prove that this works, on mysql-zrm server:
    mysql -u backupuser -h xx.xx.xx.141 -p
    <enter password for backupuser>
    - then I get a mysql prompt

    I can also verify from phpMyAdmin of MySQL server that the user exists.
    3. made sure that MySQL and mysql-zrm versions on both servers are the same:
    MySQL 5.1
    MySQL-zrm-2.1-1.noarch
    Perl modules installed: perl-DBD-mysql, perl-DBI, perl-HTML-Parser, perl-XML-Parser, etc.
    CentOS 5.3
    4. Made sure that the UID and GID of mysql are the same on both servers:
    less /etc/passwd | grep mysql
    mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash

    less /etc/group | grep mysql
    mysql:x:27:
    5. My mysql-zrm.conf contents:
    backup-level=0
    backup-mode=raw
    destination=/var/lib/mysql-zrm
    retention-policy=7D
    databases=mysql
    user="backupuser"
    password="password"
    host="xx.xx.xx.141"
    copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl
    ssh-user="mysql"
    remote-mysql-binpath="/usr/bin"
    But why I am still getting an error which seems to be a permission issue?
    Host 'backuphost.com' is not allowed to connect to this MySQL server at /usr/bin/mysqlhotcopy line 177
    Please give me the steps that I need to do in case there are still any.

  2. #2

    Default

    I ran out of time trying to figure out what went wrong. In the meantime, I settled with doing logical backups (.sql) instead of raw and then compressing them. However, mysql-zrm doesn't seem to support per-table restores from a logical dump.

    I am actually backing up a database on a "per-table" basis, but mysql-zrm backs up those tables and then dumps them to a single .sql file. I haven't had found a way to restore that single .sql file until now.

    I thought of doing remote backups using RAW mode because it was easier on my part to restore the tables through copy & paste. However, since I couldn't find a solution to my failed remote raw backups, I will have to restore my tables through mysqldump because they were backed up in logical mode in the first place. Here's how I'll do my per-table restoration:

    1. I found an article about [url=http://blog.tsheets.com/2008/tips-tricks/extract-a-single-table-from-a-mysqldump-file.html]extracting single tables from a mysqldump[/url], which was exactly what's needed in my case. The author created a Perl script out of it, which you can also download from his blog. The file is extract_sql.pl

    2. Since my zrm-backups are compressed, so I will have to extract them first. For example:
    mysql-zrm-reporter --show restore-info --where backup-set=dbname

    mysql-zrm --action extract-backup --source-directory /var/lib/mysql-zrm/dbname/20090710160508
    The .sql backup should now be extracted to the same directory. In the example, it's in /var/lib/mysql-zrm/dbname/20090710160508.

    3. After extracting the compressed files, I can now use extract_sql.pl to extract the table I want to restore.
    Usage: extract_sql.pl -t <table name> -r <restore file>

    extract_sql.pl -t dbtable -r /var/lib/mysql-zrm/dbname/20090710160508/backup.sql > /var/lib/mysql/dbtable .sql
    4. Then I can restore by using this:
    mysql -u root dbname < /var/lib/mysql/dbtable.sql -p
    <enter mysql root password>

Tags for this Thread

Posting Permissions

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