PDA

View Full Version : ZRM for MySQL CE1.5 ssh-copy.pl ignored....

skrew
March 16th, 2007, 12:19 PM
I tried to use ssh-copy plugin to securely backup MySQL database from remote server. Testing on local server that have mysql ports firewalled - only ssh connection allowed.
This is my configs /etc/mysql-zrm/mysql-zrm.conf

backup-level=0
backup-mode=logical
destination=/backup/database
retention-policy=2M
compress=1
all-databases=1
user="root"
verbose=1
mailto="root"
html-reports=backup-status-info
html-report-directory=/var/www/mysql-zrm/reports/
webserver-url=http://zrmserver.localdomain/reports/html/
copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl
ssh-user="mysql"

This is my /etc/mysql-zrm/testserver/mysql-zrm.conf

password="root"
host="192.168.0.203"
copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl

And this is the result:
[root@zrmserver tmp]# mysql-zrm --action backup --backup-set testserver


INFO: ZRM for MySQL Community Edition - version 1.1.5
INFO: Mail address: root is ok
INFO: Input Parameters Used {
INFO: verbose=1
INFO: retention-policy=2M
INFO: tmpdir=/tmp
INFO: default-character-set=utf8
INFO: all-databases=1
INFO: destination=/backup/database
INFO: password=******
INFO: backup-mode=logical
INFO: user=root
INFO: copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl
INFO: backup-level=0
INFO: webserver-url=http://zrmserver.localdomain/reports/html/
INFO: mailto=root
INFO: html-reports=backup-status-info
INFO: ssh-user=mysql
INFO: host=192.168.0.203
INFO: html-report-directory=/var/www/mysql-zrm/reports/
INFO: compress=
INFO: }
INFO: ZRM Temporary configuration file = /etc/mysql-zrm/testserver/tmpywVzR.conf
WARNING: Could not open file /etc/mysql-zrm/testserver/last_backup. No such file or directory
INFO: Getting mysql variables
INFO: mysqladmin --user="root" --password="*****" --host="192.168.0.203" variables
ERROR: Command returned error
ERROR: Output of command: 'mysqladmin --user="root" --password="*****" --host="192.168.0.203" variables' is {
mysqladmin: connect to server at '192.168.0.203' failed
error: 'Lost connection to MySQL server during query'
}
ERROR: Cannot connect to mysql server!
INFO: mailing file /tmp/G9poB6sbvM
INFO: mail command is cat "/tmp/G9poB6sbvM"|mail -s "Errors while running MySQL ZRM" root

As I understand from configs, ssh-copy.pl plugin make a backup on the remote MySQL server and then just transfer a dump to ssh pipe. Anyway any mysqladmin command run on the remote MySQL server. Why it not happens?

Info in log file is the same as in console.
skrew
March 16th, 2007, 12:24 PM
Just forgot to add, that I configured public-keys authentication between MySQL server host and ZRM host so mysql user from ZRM host can login w/o password to host with MySQL server
shailen
March 16th, 2007, 12:42 PM
You will also need to make sure that the MySQL port 3306 is open on your REMOTE MySQL server for TCP connections originating from the server where ZRM is running...

Additionally, you can omit the backup-mode entry within your mysql-zrm.conf file.

ZRM will determine the best backup method for your databases.

Please be sure to also add the appropriate privileges on the remote MySQL server...

so in your case

grant select, insert, update, create, drop, reload, shutdown, alter, super, lock tables, \ replication client on *.* to '<some-backup-user>'@'localhost' identified by '<some-password>';

and

grant select, insert, update, create, drop, reload, shutdown, alter, super, lock tables, \ replication client on *.* to '<some-backup-user>'@'<zrm-server>' identified by '<some-password>';


Hope that helps.
-Shailen
skrew
March 19th, 2007, 07:02 AM
So I can't force backup method? In case that this is remote server I want to keep mysql behind the firewall, and not even open port for 1 user/ip.
How to make "clear" ssh backup using ONLY ssh connection? Is this possible at all?
shailen
March 19th, 2007, 10:59 AM
So I can't force backup method? In case that this is remote server I want to keep mysql behind the firewall, and not even open port for 1 user/ip.
How to make "clear" ssh backup using ONLY ssh connection? Is this possible at all?


You can specify ZRM to backup using SSH.

BUT the ZRM will still need access through the MySQL port (usually 3306) to obtain a read lock on the tables, so that a consistent backup can be performed.

So basically:

1) ZRM will need to connect to the remote MySQL database to obtain read locks on the database.

2) ZRM will then perform the backup and transfer it to the ZRM Server via SSH.

3) ZRM will then connect to the remote MySQL database to release the read locks and rotate binary logs.

The backup/transfer of the database and its backup are done over SSH.

The 1st/3rd tasks are done using the standard MySQL port. If you like you can also configure MySQL using SSL to connect between the client and server.

I hope this helps to answer your questions.

-Shailen