PDA

View Full Version : Remote Raw Backup Fails with ssh-copy Plugin



db-backer
July 10th, 2009, 04:25 PM
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 mysql@xx.xx.xx.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 mysql@xx.xx.xx.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.

db-backer
July 12th, 2009, 11:37 PM
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 extracting single tables from a mysqldump (http://blog.tsheets.com/2008/tips-tricks/extract-a-single-table-from-a-mysqldump-file.html), 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>