PDA

View Full Version : ssh copy plugin



slackamp
March 24th, 2008, 07:42 AM
i cannot seem to get this to work. i can ssh to the remote box using a password or through public key. i get the error below. the hostname and db's in the log below has been modified so please do not go by it.

Mon Mar 24 11:14:14 2008: host1.domain.com:backup:ERROR: Output of command: 'mysqlhotcopy' is {
DBI connect(';host=host1.domain.com;mysql_read_default _group=mysqlhotcopy','root',...) failed: Access denied for user 'root'@'host1.domain.com' (using password: YES) at /usr/bin/mysqlhotcopy line 178
mysqlhotcopy on host host1.domain.com failed at /usr/share/mysql-zrm/plugins/ssh-copy.pl line 200.
}
Mon Mar 24 11:14:14 2008: host1.domain.com:backup:ERROR: mysqlhotcopy command did not succeed. Command used is /usr/share/mysql-zrm/pl
ugins/ssh-copy.pl --mysqlhotcopy --user="root" --password="*****" --host="host1.domain.com" --quiet db1 db2 db3 "/var/lib/mysql-zrm/host1.domain.com/20080324111408" > /tmp/qCq7EXaSDl 2>&1 Return value is 65280
Mon Mar 24 11:14:14 2008: host1.domain.com:backup:INFO: backup-status=Backup failed
Mon Mar 24 11:14:14 2008: host1.domain.com:backup:INFO: Backup failed

zmanda_jacob
March 24th, 2008, 09:03 AM
Please check the privileges in your database. The error message you are receiving seems to indicate that the user "root" does not have the proper access in MySQL when the request originates from "host1.domain.com". For information on the correct user privileges please read the following knowledgebase article:

http://network.zmanda.com/lore/article.php?id=064

The first part of the article will detail the privileges that the backup user will need.

slackamp
March 24th, 2008, 09:23 AM
This is the root privileges which is also described in http://www.zmanda.com/quick-mysql-backup.html.

mysql> show grants for 'root'@'host1.domain.com';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@host1.domain.com |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, ALTER, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'root'@'host1.domain.com' IDENTIFIED BY PASSWORD '*A98955EE171dfsdfsdfdfVDVDVD178A4255BECACCB750' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

kkg
March 24th, 2008, 08:46 PM
This is the root privileges which is also described in http://www.zmanda.com/quick-mysql-backup.html.

mysql> show grants for 'root'@'host1.domain.com';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@host1.domain.com |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, ALTER, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'root'@'host1.domain.com' IDENTIFIED BY PASSWORD '*A98955EE171dfsdfsdfdfVDVDVD178A4255BECACCB750' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

Could you run the following command on the client and post what error you get
mysqlhotcopy --user="root" --password="*A98955EE171dfsdfsdfdfVDVDVD178A4255BECACCB750" --host="host1.domain.com" --quiet db1 db2 db3 /tmp/testdir

BTW please see http://mysqlbackup.zmanda.com/index.php/Pre-Installation#MySQL_Backup_User
for the minimal set of privileges you need.

--kkg

mrigank.mishra
August 27th, 2008, 05:27 AM
Hi!

I ran into a similar error as slackamp. I've checked for perl-DBD, perl-DBI, and perl-XML-Parser - All are installed properly. I have also granted the minimum rights to the backup user. Also generated the passfiles on both the database and backup servers. I have mysqlhotcopy installed on both, the database as well as the backup servers. On running the following command on the backup server:

mysql-zrm-scheduler --backup-set DailyBackup --backup-level 0 --now

I got the following output:

backup:~ /#mysql-zrm-scheduler --backup-set DailyBackup --backup-level 0 --now
schedule:INFO: ZRM for MySQL Community Edition - version 2.0
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition - version 2.0
DailyBackup:backup:INFO: START OF BACKUP
DailyBackup:backup:INFO: PHASE START: Initialization
DailyBackup:backup:INFO: Mail address: username@companyname.com is ok
DailyBackup:backup:INFO: ZRM Temporary configuration file = /etc/mysql-zrm/DailyBackup/tmp0w54z.conf
DailyBackup:backup:INFO: {
DailyBackup:backup:INFO: verbose=1
DailyBackup:backup:INFO: encrypt-plugin=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup:backup:INFO: retention-policy=30D
DailyBackup:backup:INFO: decrypt-option=-d
DailyBackup:backup:INFO: password=******
DailyBackup:backup:INFO: tables=student
DailyBackup:backup:INFO: backup-mode=raw
DailyBackup:backup:INFO: compress-plugin=/usr/bin/gzip
DailyBackup:backup:INFO: user=mysql_backup_usr
DailyBackup:backup:INFO: copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl
DailyBackup:backup:INFO: backup-level=0
DailyBackup:backup:INFO: quiet=0
DailyBackup:backup:INFO: encrypt=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup:backup:INFO: mailto=username@companyname.com
DailyBackup:backup:INFO: ssh-user=mysql
DailyBackup:backup:INFO: host=remote.mysql.net
DailyBackup:backup:INFO: database=sample
DailyBackup:backup:INFO: passfile=/tmp/a.pass
DailyBackup:backup:INFO: compress=/usr/bin/gzip
DailyBackup:backup:INFO: }
DailyBackup:backup:INFO: Getting mysql variables
DailyBackup:backup:INFO: mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" variables
DailyBackup:backup:INFO: datadir is /usr/local/mysql/var/
DailyBackup:backup:INFO: mysql_version is 5.0.67-log
DailyBackup:backup:INFO: InnoDB data file are /usr/local/mysql/var/ibdata1
DailyBackup:backup:INFO: InnoDB log dir is /usr/local/mysql/var/.
DailyBackup:backup:INFO: backup set being used is DailyBackup
DailyBackup:backup:INFO: backup-set=DailyBackup
DailyBackup:backup:INFO: backup-date=20080828003428
DailyBackup:backup:INFO: mysql-server-os=Linux/Unix
DailyBackup:backup:INFO: host=remote.mysql.net
DailyBackup:backup:INFO: backup-date-epoch=1219863868
DailyBackup:backup:INFO: retention-policy=30D
DailyBackup:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.0
DailyBackup:backup:INFO: mysql-version=5.0.67-log
DailyBackup:backup:INFO: backup-directory=/var/lib/mysql-zrm/DailyBackup/20080828003428
DailyBackup:backup:INFO: backup-level=0
DailyBackup:backup:INFO: backup-mode=raw
DailyBackup:backup:INFO: PHASE END: Initialization
DailyBackup:backup:INFO: PHASE START: Running pre backup plugin
DailyBackup:backup:INFO: Executing pre-backup-plugin
DailyBackup:backup:INFO: PHASE END: Running pre backup plugin
DailyBackup:backup:INFO: PHASE START: Flushing logs
DailyBackup:backup:INFO: Flushing the logs
DailyBackup:backup:INFO: mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" flush-logs
DailyBackup:backup:INFO: Getting master logname using command mysql --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" -e "show master status"
DailyBackup:backup:INFO: PHASE END: Flushing logs
DailyBackup:backup:INFO: Command used for getting engine type mysql --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" -e "show table status from \`sample\` like 'student'"
DailyBackup:backup:INFO: For database sample
DailyBackup:backup:INFO: and for table student
DailyBackup:backup:INFO: engine
DailyBackup:backup:INFO: MyISAM
DailyBackup:backup:INFO: PHASE START: Creating raw backup
DailyBackup:backup:INFO: Command used for raw backup is /usr/share/mysql-zrm/plugins/ssh-copy.pl --mysqlhotcopy --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" --quiet sample./^student$/ "/var/lib/mysql-zrm/DailyBackup/20080828003428" > /tmp/StIIpYTbXY 2>&1
Password:
Password:
Password:
DailyBackup:backup:ERROR: Output of command: 'mysqlhotcopy' is {
Permission denied (publickey,keyboard-interactive).
Could not create directory /tmp/HUYMluAhjh on host remote.mysql.net at /usr/share/mysql-zrm/plugins/ssh-copy.pl line 313.
}
DailyBackup:backup:ERROR: mysqlhotcopy did not succeed. Command used is /usr/share/mysql-zrm/plugins/ssh-copy.pl --mysqlhotcopy --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" --quiet sample./^student$/ "/var/lib/mysql-zrm/DailyBackup/20080828003428" > /tmp/StIIpYTbXY 2>&1 Return value is 65280
DailyBackup:backup:INFO: PHASE START: Cleanup
DailyBackup:backup:INFO: backup-status=Backup failed
DailyBackup:backup:INFO: Backup failed
DailyBackup:backup:INFO: mailing file /tmp/fQMTHKI1pN
DailyBackup:backup:INFO: mail command is cat "/tmp/fQMTHKI1pN"|mail -s "[ZRM for MySQL Report] ERROR during backup of backup-set DailyBackup" username@companyname.com
DailyBackup:backup:INFO: PHASE END: Cleanup
DailyBackup:backup:INFO: END OF BACKUP
ERROR: /usr/bin/mysql-zrm did not finish successfully

I ran the test given below as mentioned by kkg on both the database as well as the backup servers:

mysqlhotcopy --user="mysql_backup_usr" --password="password" --host="remote.mysql.net" --quiet sample /tmp/testdir

I got the following output from the database server:

remote:~ # mysqlhotcopy --user="mysql_backup_usr" --password="password" --addtodest --host="remote.mysql.net" sample /tmp/mysql-zrm-testdir
Locked 1 tables in 0 seconds.
Flushed tables (`sample`.`student`) in 0 seconds.
Copying 4 files...
Copying indices for 0 files...
Unlocked tables.
mysqlhotcopy copied 1 tables (4 files) in 0 seconds (0 seconds overall).

I got the following output from the backup server:

backup:/ # mysqlhotcopy --user="mysql_backup_usr" --password="password" --addtodest --host="remote.mysql.net" sample /tmp/mysql-zrm-testdir
'sample' is an empty database
Invalid db.table name 'sample.sample`.`student' at /usr/bin/mysqlhotcopy line 854.
You have new mail in /var/mail/root

Can anyone help??!!

kkg
August 27th, 2008, 06:18 AM
Hi!


DailyBackup:backup:INFO: Command used for raw backup is /usr/share/mysql-zrm/plugins/ssh-copy.pl --mysqlhotcopy --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" --quiet sample./^student$/ "/var/lib/mysql-zrm/DailyBackup/20080828003428" > /tmp/StIIpYTbXY 2>&1
Password:
Password:
Password:
DailyBackup:backup:ERROR: Output of command: 'mysqlhotcopy' is {
Permission denied (publickey,keyboard-interactive).
Could not create directory /tmp/HUYMluAhjh on host remote.mysql.net at /usr/share/mysql-zrm/plugins/ssh-copy.pl line 313.
}


The issue here is that the ssh password needs to be supplied at time of backup. If you want to enable automatic login, you should do the ssh key exchange and ensure that the user running the backup (for e.g. mysql) is able to automatically login to the remote host without password being asked for.

--kkg

mrigank.mishra
August 27th, 2008, 11:57 PM
The issue here is that the ssh password needs to be supplied at time of backup. If you want to enable automatic login, you should .

--kkg

I had duly supplied the password(thrice, as you can see from the output I attached) but somehow it is not authenticating properly...

Also, could you please elaborate a little bit on How to do the SSH key exchange and ensure that the user running the backup is able to automatically login to the remote host without password being asked for.

If by "key exchange" you mean that we're supposed to copy the contents of /root/.ssh/id_rsa.pub file located on backup server to the file “authorized_keys” on database server, I have done that as well.

Also, I tried doing the backup with socket-copy plugin, with the options given below:

compress=1
compress-plugin=/usr/bin/gzip
encrypt=1
encrypt-plugin="/usr/share/mysql-zrm/plugins/encrypt.pl"
tables=student
database="sample"
user="mysql_backup_usr"
password="password"
host="remote.mysql.net"
mailto="username@companyname.com"
copy-plugin=/usr/share/mysql-zrm/plugins/socket-copy.pl

I got the output below:

backup:/ # mysql-zrm-scheduler --backup-set DailyBackup --backup-level 0 --now
schedule:INFO: ZRM for MySQL Community Edition - version 2.0
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition - version 2.0
DailyBackup:backup:INFO: START OF BACKUP
DailyBackup:backup:INFO: PHASE START: Initialization
DailyBackup:backup:INFO: Mail address: username@companyname.com is ok
DailyBackup:backup:INFO: ZRM Temporary configuration file = /etc/mysql-zrm/DailyBackup/tmpeD1WO.conf
DailyBackup:backup:INFO: {
DailyBackup:backup:INFO: verbose=1
DailyBackup:backup:INFO: encrypt-plugin=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup:backup:INFO: retention-policy=30D
DailyBackup:backup:INFO: decrypt-option=-d
DailyBackup:backup:INFO: password=******
DailyBackup:backup:INFO: tables=student
DailyBackup:backup:INFO: backup-mode=raw
DailyBackup:backup:INFO: compress-plugin=/usr/bin/gzip
DailyBackup:backup:INFO: user=mysql_backup_usr
DailyBackup:backup:INFO: copy-plugin=/usr/share/mysql-zrm/plugins/socket-copy.pl
DailyBackup:backup:INFO: backup-level=0
DailyBackup:backup:INFO: quiet=0
DailyBackup:backup:INFO: encrypt=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup:backup:INFO: mailto=username@companyname.com
DailyBackup:backup:INFO: host=remote.mysql.net
DailyBackup:backup:INFO: database=sample
DailyBackup:backup:INFO: compress=/usr/bin/gzip
DailyBackup:backup:INFO: }
DailyBackup:backup:INFO: Getting mysql variables
DailyBackup:backup:INFO: mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" variables
DailyBackup:backup:INFO: datadir is /usr/local/mysql/var/
DailyBackup:backup:INFO: mysql_version is 5.0.67-log
DailyBackup:backup:INFO: InnoDB data file are /usr/local/mysql/var/ibdata1
DailyBackup:backup:INFO: InnoDB log dir is /usr/local/mysql/var/.
DailyBackup:backup:INFO: backup set being used is DailyBackup
DailyBackup:backup:INFO: backup-set=DailyBackup
DailyBackup:backup:INFO: backup-date=20080828191929
DailyBackup:backup:INFO: mysql-server-os=Linux/Unix
DailyBackup:backup:INFO: host=remote.mysql.net
DailyBackup:backup:INFO: backup-date-epoch=1219931369
DailyBackup:backup:INFO: retention-policy=30D
DailyBackup:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.0
DailyBackup:backup:INFO: mysql-version=5.0.67-log
DailyBackup:backup:INFO: backup-directory=/var/lib/mysql-zrm/DailyBackup/20080828191929
DailyBackup:backup:INFO: backup-level=0
DailyBackup:backup:INFO: backup-mode=raw
DailyBackup:backup:INFO: PHASE END: Initialization
DailyBackup:backup:INFO: PHASE START: Running pre backup plugin
DailyBackup:backup:INFO: Executing pre-backup-plugin
DailyBackup:backup:INFO: PHASE END: Running pre backup plugin
DailyBackup:backup:INFO: PHASE START: Flushing logs
DailyBackup:backup:INFO: Flushing the logs
DailyBackup:backup:INFO: mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" flush-logs
DailyBackup:backup:INFO: Getting master logname using command mysql --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" -e "show master status"
DailyBackup:backup:INFO: PHASE END: Flushing logs
DailyBackup:backup:INFO: Command used for getting engine type mysql --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" -e "show table status from \`sample\` like 'student'"
DailyBackup:backup:INFO: For database sample
DailyBackup:backup:INFO: and for table student
DailyBackup:backup:INFO: engine
DailyBackup:backup:INFO: MyISAM
DailyBackup:backup:INFO: PHASE START: Creating raw backup
DailyBackup:backup:INFO: Command used for raw backup is /usr/share/mysql-zrm/plugins/socket-copy.pl --mysqlhotcopy --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" --quiet sample./^student$/ "/var/lib/mysql-zrm/DailyBackup/20080828191929" > /tmp/LV9jY1qJYh 2>&1
DailyBackup:backup:ERROR: Output of command: 'mysqlhotcopy' is {
close of pipe failed
}
DailyBackup:backup:ERROR: mysqlhotcopy did not succeed. Command used is /usr/share/mysql-zrm/plugins/socket-copy.pl --mysqlhotcopy --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" --quiet sample./^student$/ "/var/lib/mysql-zrm/DailyBackup/20080828191929" > /tmp/LV9jY1qJYh 2>&1 Return value is 512
DailyBackup:backup:INFO: PHASE START: Cleanup
DailyBackup:backup:INFO: backup-status=Backup failed
DailyBackup:backup:INFO: Backup failed
DailyBackup:backup:INFO: mailing file /tmp/MT28qoSHhc
DailyBackup:backup:INFO: mail command is cat "/tmp/MT28qoSHhc"|mail -s "[ZRM for MySQL Report] ERROR during backup of backup-set DailyBackup" username@companyname.com
DailyBackup:backup:INFO: PHASE END: Cleanup
DailyBackup:backup:INFO: END OF BACKUP
ERROR: /usr/bin/mysql-zrm did not finish successfully

Could you kindly shed some light on this as well??

kulkarni_mangesh
August 28th, 2008, 02:10 AM
To set RSA key based password less authentication:

Perform following steps on Backup server:
1. Become the mysql user:

#su mysql

2. Generate public and private keys:

#ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key
/var/lib/mysql/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in
/var/lib/mysql/.ssh/id_rsa.
Your public key has been saved in
/var/lib/mysql/.ssh/id_rsa.pub.
The key fingerprint is:
61:b3:88:a4:dd:86:2c:ef:2f:73:8b:91:ec:aa:3d:4a mysql@host

3. Copy id_rsa.pub from the ZRM server file to the mysql client machine. This file is located in the .ssh directory in the mysql user's home directory.

Perform following steps on MySQL Database server:

1. Login to mysql client machine as the mysql user:

#su mysql
2. If the authorized_keys file is not present in the .ssh folder in the home directory of mysql user,� create the file. Append the contents of id_rsa.pub to the authorized_keys file.

# cat id_rsa.pub >> authorized_keys

After performing these steps, the mysql user from the ZRM Server will be able to login to the mysql client machine without a password by using key-based authentication

kulkarni_mangesh
August 28th, 2008, 02:24 AM
Failing of Socket copy, seems to be permission issue.

Please check to see that the directory /var/log/mysql-zrm exists on the system where you have installed the ZRM for MySQL client libraries, and that the directory is owned by the mysql user and is writeable.

Following are some general guide lines for remote backup to work:


Make sure that UID and GID of “myslq” user are matching on Backup server and MySQL database server.
RSA key based authentication has been set properly.
Backup user has enough permissions to perform backup and restore of selected database.

mrigank.mishra
August 28th, 2008, 04:17 AM
To set RSA key based password less authentication:

Perform following steps on Backup server:
1. Become the mysql user:

#su mysql

2. Generate public and private keys:

#ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key
/var/lib/mysql/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in
/var/lib/mysql/.ssh/id_rsa.
Your public key has been saved in
/var/lib/mysql/.ssh/id_rsa.pub.
The key fingerprint is:
61:b3:88:a4:dd:86:2c:ef:2f:73:8b:91:ec:aa:3d:4a mysql@host

3. Copy id_rsa.pub from the ZRM server file to the mysql client machine. This file is located in the .ssh directory in the mysql user's home directory.

Perform following steps on MySQL Database server:

1. Login to mysql client machine as the mysql user:

#su mysql
2. If the authorized_keys file is not present in the .ssh folder in the home directory of mysql user,� create the file. Append the contents of id_rsa.pub to the authorized_keys file.

# cat id_rsa.pub >> authorized_keys

After performing these steps, the mysql user from the ZRM Server will be able to login to the mysql client machine without a password by using key-based authentication

No. This didn't help me... :-(

kulkarni_mangesh
August 28th, 2008, 05:46 AM
Can you please check following things..?


Are you able to login to Backup server and MySQL server by using "mysql" user ?
Make sure that <HOME_DIR>/.ssh/authorized_keys is readable and writable to only "mysql" user, i.e. permissions should be 700 on MySQL server.
After setting up RSA Keys, on Backup server login as "mysql" and try to login to MySQL server by using SSH as "ssh <mysql_server_ip>". This should open shell in front of you without asking password.
Once you through with ssh login without submitting password, try to take backup by using SSH copy plugin.

zmanda_jacob
August 28th, 2008, 12:28 PM
Once you set up the passwordless SSH access between the mysql user on the ZRM server and the mysql user on the MySQL server you need to use the SSH-copy plugin and not the Socket-copy plugin to use that feature. It also may give you insight into why things are failing.

mrigank.mishra
August 28th, 2008, 09:36 PM
Can you please check following things..?


After setting up RSA Keys, on Backup server login as "mysql" and try to login to MySQL server by using SSH as "ssh <mysql_server_ip>". This should open shell in front of you without asking password.


I can login to the MySQL server through SSH when I'm logged into the ZRM server as root but not when I'm logged in as mysql.

I ran the backup:/# chmod 700 /root/.ssh on the ZRM server and the following commands on the MySQL server after copying the contents of id_rsa.pub to authorized_keys through backup# scp /root/.ssh/id_rsa.pub root@remote.mysql.net:/var/lib/mysql/.ssh/authorized_keys

remote:/# chmod 600 /var/lib/mysql/.ssh/authorized_keys
remote:/# chown mysql /var/lib/mysql/.ssh/authorized_keys


Could you please provide me a little more detail on how to fix it??

mrigank.mishra
August 28th, 2008, 09:42 PM
Once you set up the passwordless SSH access between the mysql user on the ZRM server and the mysql user on the MySQL server you need to use the SSH-copy plugin and not the Socket-copy plugin to use that feature. It also may give you insight into why things are failing.

I have done that ofcourse! :-) Its just that I'm running into problems when I tried to take the backup with the socket copy plugin as well - I have provided the logs in one of my previous posts: kindly refer. Would be glad if you could help me with the error 512 thrown by mysqlhotcopy.

kulkarni_mangesh
August 29th, 2008, 03:21 AM
To ZRM work properly,
Need to export SSH RSA keys for “mysql” user, follow the following steps exactly, please don’t skip OR modify any steps during execution:



Login to ZRM Server as “mysql” user OR as root and do su “su - mysql" .
Generate SSH RSA key as “ssh-keygen -t rsa”.
Copy “MYSQL_USER_HOME_DIR/.ssh/id_rsa.pub “ from ZRM server to MySQL server’s “mysql” user HOME_DIR.

scp MYSQL_USER_HOME_DIR/.ssh.id_rsa.pub mysql@MYSQL_SERVER_IP:/tmp/id_rsa.pub.zrm“
Login to MySQL server as “mysql” user OR as root and do su “su - mysql".
Copy contents of “/tmp/id_rsa.pub.zrm” to “MYSQL_USER_HOME_DIR/.ssh/authorized_keys”

cat /tmp /id_rsa.pub.zrm >> MYSQL_USER_HOME_DIR/.ssh/authorized_keys.
To test SSH password less login, try to login from ZRM server to MySQL server by using “mysql” user.

Note: In above scenario we have not used “root” user to export SSH keys, hence no need to change ownership.
Just make sure that MYSQL_USER_HOME_DIR/.ssh/authorized_keys is owned by mysql and having 700 permissions.

Take backup of MySQL server from ZRM Server by using “mysql” server.


To take backup login as "mysql” user to ZRM server OR login as “root” and do “su - mysql".
Execute your backup command, which used SSH-Copy plugin.
Once you through with SSH plugin we will see Socket-Copy as well.


Here we are assuming following things:


UID and GID of "mysql" user are matching with ZRM server that of MySQL server.
Backup user is having proper permission to perform backup and restore on MySQL database, which will be backed up.

mrigank.mishra
August 29th, 2008, 05:19 AM
I did as you suggested and finally got the thing to run successfully, but I had a few problems though.. Please tell me if the behaviour is normal...


When I tried to login from ZRM server to MySQL server by using “mysql” user, to test SSH password less login, it was not passwordless! I got this:

Enter passphrase for key '/home/mysql/.ssh/id_rsa':

I entered the password and logged in successfully.. Somehow it did not accept the -l option as ssh... (probably because ssh username doesn't exist on the servers..) I managed it with mysql

I did the chmod 700 and chown mysql on the authorized_keys.

Then I ran the following command as root:
mysql-zrm-scheduler --backup-set DailyBackup_SSH --backup-level 0 --now


I got the following oputput:
backup:/ # mysql-zrm-scheduler --backup-set DailyBackup_SSH --backup-level 0 --now
schedule:INFO: ZRM for MySQL Community Edition - version 2.0
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition - version 2.0
DailyBackup_SSH:backup:INFO: START OF BACKUP
DailyBackup_SSH:backup:INFO: PHASE START: Initialization
DailyBackup_SSH:backup:INFO: Mail address: mrigank.mishra@opticalfusion.net is ok
DailyBackup_SSH:backup:INFO: ZRM Temporary configuration file = /etc/mysql-zrm/DailyBackup_SSH/tmp1W7FF.conf
DailyBackup_SSH:backup:INFO: {
DailyBackup_SSH:backup:INFO: verbose=1
DailyBackup_SSH:backup:INFO: encrypt-plugin=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SSH:backup:INFO: retention-policy=30D
DailyBackup_SSH:backup:INFO: decrypt-option=-d
DailyBackup_SSH:backup:INFO: password=******
DailyBackup_SSH:backup:INFO: tables=student
DailyBackup_SSH:backup:INFO: backup-mode=raw
DailyBackup_SSH:backup:INFO: compress-plugin=/usr/bin/gzip
DailyBackup_SSH:backup:INFO: user=mysql_backup_usr
DailyBackup_SSH:backup:INFO: copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl
DailyBackup_SSH:backup:INFO: backup-level=0
DailyBackup_SSH:backup:INFO: quiet=0
DailyBackup_SSH:backup:INFO: encrypt=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SSH:backup:INFO: mailto=mrigank.mishra@opticalfusion.net
DailyBackup_SSH:backup:INFO: ssh-user=mysql
DailyBackup_SSH:backup:INFO: host=remote.mysql.net
DailyBackup_SSH:backup:INFO: database=sample
DailyBackup_SSH:backup:INFO: compress=/usr/bin/gzip
DailyBackup_SSH:backup:INFO: }
DailyBackup_SSH:backup:INFO: Getting mysql variables
DailyBackup_SSH:backup:INFO: mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" variables
DailyBackup_SSH:backup:INFO: datadir is /usr/local/mysql/var/
DailyBackup_SSH:backup:INFO: mysql_version is 5.0.67-log
DailyBackup_SSH:backup:INFO: InnoDB data file are /usr/local/mysql/var/ibdata1
DailyBackup_SSH:backup:INFO: InnoDB log dir is /usr/local/mysql/var/.
DailyBackup_SSH:backup:INFO: backup set being used is DailyBackup_SSH
DailyBackup_SSH:backup:INFO: backup-set=DailyBackup_SSH
DailyBackup_SSH:backup:INFO: backup-date=20080830003625
DailyBackup_SSH:backup:INFO: mysql-server-os=Linux/Unix
DailyBackup_SSH:backup:INFO: host=remote.mysql.net
DailyBackup_SSH:backup:INFO: backup-date-epoch=1220036785
DailyBackup_SSH:backup:INFO: retention-policy=30D
DailyBackup_SSH:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.0
DailyBackup_SSH:backup:INFO: mysql-version=5.0.67-log
DailyBackup_SSH:backup:INFO: backup-directory=/var/lib/mysql-zrm/DailyBackup_SSH/20080830003625
DailyBackup_SSH:backup:INFO: backup-level=0
DailyBackup_SSH:backup:INFO: backup-mode=raw
DailyBackup_SSH:backup:INFO: PHASE END: Initialization
DailyBackup_SSH:backup:INFO: PHASE START: Running pre backup plugin
DailyBackup_SSH:backup:INFO: Executing pre-backup-plugin
DailyBackup_SSH:backup:INFO: PHASE END: Running pre backup plugin
DailyBackup_SSH:backup:INFO: PHASE START: Flushing logs
DailyBackup_SSH:backup:INFO: Flushing the logs
DailyBackup_SSH:backup:INFO: mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" flush-logs
DailyBackup_SSH:backup:INFO: Getting master logname using command mysql --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" -e "show master status"
DailyBackup_SSH:backup:INFO: PHASE END: Flushing logs
DailyBackup_SSH:backup:INFO: Command used for getting engine type mysql --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" -e "show table status from \`sample\` like 'student'"
DailyBackup_SSH:backup:INFO: For database sample
DailyBackup_SSH:backup:INFO: and for table student
DailyBackup_SSH:backup:INFO: engine
DailyBackup_SSH:backup:INFO: MyISAM
DailyBackup_SSH:backup:INFO: PHASE START: Creating raw backup
DailyBackup_SSH:backup:INFO: Command used for raw backup is /usr/share/mysql-zrm/plugins/ssh-copy.pl --mysqlhotcopy --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" --quiet sample./^student$/ "/var/lib/mysql-zrm/DailyBackup_SSH/20080830003625" > /tmp/4NrQfu78sf 2>&1
Password:
Password:
Password:
Password:
DailyBackup_SSH:backup:INFO: raw-tables=student
DailyBackup_SSH:backup:INFO: raw-databases=sample
DailyBackup_SSH:backup:INFO: PHASE END: Creating raw backup
DailyBackup_SSH:backup:INFO: PHASE START: Calculating backup size & checksums
DailyBackup_SSH:backup:INFO: next-binlog=mysql-bin.000092
DailyBackup_SSH:backup:INFO: last-backup=/var/lib/mysql-zrm/DailyBackup_SSH/20080830000414
DailyBackup_SSH:backup:INFO: backup-size=0.01 MB
DailyBackup_SSH:backup:INFO: PHASE END: Calculating backup size & checksums
DailyBackup_SSH:backup:INFO: PHASE START: Compression/Encryption
DailyBackup_SSH:backup:INFO: Compressing Encrypting backup
DailyBackup_SSH:backup:INFO: Command used is 'tar --same-owner -cpsC "/var/lib/mysql-zrm/DailyBackup_SSH/20080830003625" --exclude=backup-data --exclude=index --exclude=zrm_checksum . 2>/tmp/0gq5RPyEwn | "/usr/bin/gzip" 2>/tmp/QgNgufA5LS | "/usr/share/mysql-zrm/plugins/encrypt.pl" > "/var/lib/mysql-zrm/DailyBackup_SSH/20080830003625/backup-data" 2>/tmp/4NrQfu78sf'
DailyBackup_SSH:backup:ERROR: Compressing Encrypting backup failed
DailyBackup_SSH:backup:ERROR: Output of command: 'compress/encrypt' is {
Passphrase file /etc/mysql-zrm/.passphrase not found at /usr/share/mysql-zrm/plugins/encrypt.pl line 80.
}
DailyBackup_SSH:backup:INFO: PHASE END: Compression/Encryption
DailyBackup_SSH:backup:INFO: read-locks-time=00:00:13
DailyBackup_SSH:backup:INFO: flush-logs-time=00:00:00
DailyBackup_SSH:backup:INFO: compress-encrypt-time=00:00:00
DailyBackup_SSH:backup:INFO: backup-time=00:00:13
DailyBackup_SSH:backup:INFO: backup-status=Backup done but with errors
DailyBackup_SSH:backup:INFO: Backup done but with errors
DailyBackup_SSH:backup:INFO: PHASE START: Running post backup plugin
DailyBackup_SSH:backup:INFO: Executing post-backup-plugin
DailyBackup_SSH:backup:INFO: PHASE END: Running post backup plugin
DailyBackup_SSH:backup:INFO: PHASE START: Mailing backup report
DailyBackup_SSH:backup:INFO: mailing file /tmp/GC3rDSUJj4
DailyBackup_SSH:backup:INFO: mail command is cat "/tmp/GC3rDSUJj4"|mail -s "[ZRM for MySQL Report] backup-set DailyBackup_SSH" mrigank.mishra@opticalfusion.net
DailyBackup_SSH:backup:INFO: PHASE END: Mailing backup report
DailyBackup_SSH:backup:INFO: PHASE START: Cleanup
DailyBackup_SSH:backup:INFO: mailing file /tmp/LYGjQR4Oks
DailyBackup_SSH:backup:INFO: mail command is cat "/tmp/LYGjQR4Oks"|mail -s "[ZRM for MySQL Report] ERROR during backup of backup-set DailyBackup_SSH" mrigank.mishra@opticalfusion.net
DailyBackup_SSH:backup:INFO: PHASE END: Cleanup
DailyBackup_SSH:backup:INFO: END OF BACKUP
ERROR: /usr/bin/mysql-zrm did not finish successfully


Firstly, I had to supply the password four times, thankfully it succeeded in authenticating itself... Would like to make "truly passwordless"! ;-)
Next, the backup was failing. So I commented out the encryption option and the backup ran successfully... :-D
Could you help me out with the encryption as well...??! I guess the .passphrase file got deleted somehow.. What's the way to get it back in place??

kulkarni_mangesh
August 29th, 2008, 09:59 PM
This is due to following reasons:


"mysql" user is not having valid home directory. See home directory for "mysql" user in /etc/passwd file and check the permissions for that direcotry. It should be owned by "mysql:mysql" (format is user:group). Normally it is "/var/lib/mysql", owned by "mysql:mysql" and having 770 permission.
If you don't have valid home dir for "mysql" user then please set.
Have you specified "passphrase" dusing ssh-keygen..? If yes then please re-generate the key without providing passphrase and re-copy the key in authorized_keys file on the MySQL server.

kulkarni_mangesh
August 29th, 2008, 10:52 PM
If SSH RSA Keybased authentication has set properly, then Backup and Restore should complete without asking any user input.

mrigank.mishra
August 30th, 2008, 06:26 AM
If SSH RSA Keybased authentication has set properly, then Backup and Restore should complete without asking any user input.

Hi! Could you help me with the socket-copy part now please?? I have posted the output in one of my previous posts - I guess it has something to do with the port number 25300 being active on the database server being open. I do not know which service/process name should I mention for socket-copy plugin... (as it is accessed by that plugin but some other command throws the error..)

I am getting close of pipe failed error 512.

kulkarni_mangesh
August 31st, 2008, 05:06 AM
Sure, we will solve SOCKET-COPY as well. Hope SSH is working fine.
We have gone through the output of Socket-copy backup. Error 512 is related with "Permissions and authentication", mainly if UID and GID of "mysql" user on ZRM server and MySQL server are not matching.

Before we proceed, can you provide us following things..?


UID and GID of "mysql" user from ZRM server and MySQL server. Just execute "id mysql" command on ZRM server and MySQL server after logged-in as root on ZRM and MySQL server. Output of this command should be same.
Ownership of /var/log/mysql-zrm and libraries should be set to "mysql:mysql".
Are you able to do telnet to MySQL server on port no 23500. To check execute "telnet <MySQL_SERVER_IP> 23500" this should not fail.
Take one backup with --verbose and post the output by using "Socket-copy" plugin.

mrigank.mishra
September 1st, 2008, 05:34 AM
Sure, we will solve SOCKET-COPY as well. Hope SSH is working fine.
We have gone through the output of Socket-copy backup. Error 512 is related with "Permissions and authentication", mainly if UID and GID of "mysql" user on ZRM server and MySQL server are not matching.

Before we proceed, can you provide us following things..?


UID and GID of "mysql" user from ZRM server and MySQL server. Just execute "id mysql" command on ZRM server and MySQL server after logged-in as root on ZRM and MySQL server. Output of this command should be same.
Ownership of /var/log/mysql-zrm and libraries should be set to "mysql:mysql".
Are you able to do telnet to MySQL server on port no 23500. To check execute "telnet <MySQL_SERVER_IP> 23500" this should not fail.
Take one backup with --verbose and post the output by using "Socket-copy" plugin.


I'm not able to connect to the MySQL server via telnet... I'm getting:
telnet: connect to address remote.mysql.net: Connection refused

I guess I have not configured the port properly in /etc/hosts.. Could you give me the configuration settings for the file??

Here is my backup log using socket-copy:

backup:/home/mysql # mysql-zrm-scheduler --backup-set DailyBackup_SockCopy --bac kup-level 0 --now --verbose
schedule:INFO: ZRM for MySQL Community Edition - version 2.0
schedule:INFO: Reading options from file /etc/mysql-zrm/mysql-zrm.conf
DailyBackup_SockCopy:schedule:INFO: Reading options from file /etc/mysql-zrm/Dai lyBackup_SockCopy/mysql-zrm.conf
DailyBackup_SockCopy:schedule:WARNING: Could not open file /etc/mysql-zrm/DailyB ackup_SockCopy/last_backup. No such file or directory
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition - version 2.0
DailyBackup_SockCopy:backup:INFO: START OF BACKUP
DailyBackup_SockCopy:backup:INFO: PHASE START: Initialization
DailyBackup_SockCopy:backup:INFO: Mail address: root@localhost is ok
DailyBackup_SockCopy:backup:INFO: ZRM Temporary configuration file = /etc/mysql- zrm/DailyBackup_SockCopy/tmpEdG0N.conf
DailyBackup_SockCopy:backup:INFO: {
DailyBackup_SockCopy:backup:INFO: quiet=0
DailyBackup_SockCopy:backup:INFO: verbose=1
DailyBackup_SockCopy:backup:INFO: retention-policy=30D
DailyBackup_SockCopy:backup:INFO: backup-level=0
DailyBackup_SockCopy:backup:INFO: mailto=root@localhost
DailyBackup_SockCopy:backup:INFO: socket-remote-port=25300
DailyBackup_SockCopy:backup:INFO: host=remote.mysql.net
DailyBackup_SockCopy:backup:INFO: database=sample
DailyBackup_SockCopy:backup:INFO: password=******
DailyBackup_SockCopy:backup:INFO: backup-mode=raw
DailyBackup_SockCopy:backup:INFO: tables=student
DailyBackup_SockCopy:backup:INFO: compress-plugin=/usr/bin/gzip
DailyBackup_SockCopy:backup:INFO: user=mysql_backup_usr
DailyBackup_SockCopy:backup:INFO: compress=/usr/bin/gzip
DailyBackup_SockCopy:backup:INFO: copy-plugin=/usr/share/mysql-zrm/plugins /socket-copy.pl
DailyBackup_SockCopy:backup:INFO: }
DailyBackup_SockCopy:backup:INFO: Getting mysql variables
DailyBackup_SockCopy:backup:INFO: mysqladmin --user="mysql_backup_usr" --passwor d="*****" --host="remote.mysql.net" variables
DailyBackup_SockCopy:backup:INFO: datadir is /usr/local/mysql/var/
DailyBackup_SockCopy:backup:INFO: mysql_version is 5.0.67-log
DailyBackup_SockCopy:backup:INFO: InnoDB data file are /usr/local/mysql/var/ibda ta1
DailyBackup_SockCopy:backup:INFO: InnoDB log dir is /usr/local/mysql/var/.
DailyBackup_SockCopy:backup:INFO: backup set being used is DailyBackup_SockCopy
DailyBackup_SockCopy:backup:INFO: backup-set=DailyBackup_SockCopy
DailyBackup_SockCopy:backup:INFO: backup-date=20080902005809
DailyBackup_SockCopy:backup:INFO: mysql-server-os=Linux/Unix
DailyBackup_SockCopy:backup:INFO: host=remote.mysql.net
DailyBackup_SockCopy:backup:INFO: backup-date-epoch=1220297289
DailyBackup_SockCopy:backup:INFO: retention-policy=30D
DailyBackup_SockCopy:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edit ion - version 2.0
DailyBackup_SockCopy:backup:INFO: mysql-version=5.0.67-log
DailyBackup_SockCopy:backup:INFO: backup-directory=/var/lib/mysql-zrm/DailyBacku p_SockCopy/20080902005809
DailyBackup_SockCopy:backup:INFO: backup-level=0
DailyBackup_SockCopy:backup:INFO: backup-mode=raw
DailyBackup_SockCopy:backup:INFO: PHASE END: Initialization
DailyBackup_SockCopy:backup:INFO: PHASE START: Running pre backup plugin
DailyBackup_SockCopy:backup:INFO: Executing pre-backup-plugin
DailyBackup_SockCopy:backup:INFO: PHASE END: Running pre backup plugin
DailyBackup_SockCopy:backup:INFO: PHASE START: Flushing logs
DailyBackup_SockCopy:backup:INFO: Flushing the logs
DailyBackup_SockCopy:backup:INFO: mysqladmin --user="mysql_backup_usr" --passwor d="*****" --host="remote.mysql.net" flush-logs
DailyBackup_SockCopy:backup:INFO: Getting master logname using command mysql --u ser="mysql_backup_usr" --password="*****" --host="remote.mysql.net" -e "show mas ter status"
DailyBackup_SockCopy:backup:INFO: PHASE END: Flushing logs
DailyBackup_SockCopy:backup:INFO: Command used for getting engine type mysql --u ser="mysql_backup_usr" --password="*****" --host="remote.mysql.net" -e "show tab le status from \`sample\` like 'student'"
DailyBackup_SockCopy:backup:INFO: For database sample
DailyBackup_SockCopy:backup:INFO: and for table student
DailyBackup_SockCopy:backup:INFO: engine
DailyBackup_SockCopy:backup:INFO: MyISAM
DailyBackup_SockCopy:backup:INFO: PHASE START: Creating raw backup
DailyBackup_SockCopy:backup:INFO: Command used for raw backup is /usr/share/mysq l-zrm/plugins/socket-copy.pl --mysqlhotcopy --user="mysql_backup_usr" --password ="*****" --host="remote.mysql.net" --quiet sample./^student$/ "/var/lib/mysql- zrm/DailyBackup_SockCopy/20080902005809" > /tmp/N8chQP2fxr 2>&1
DailyBackup_SockCopy:backup:ERROR: Output of command: 'mysqlhotcopy' is {
connect: Connection refused at /usr/share/mysql-zrm/plugins/socket-copy.pl line 255.
}
DailyBackup_SockCopy:backup:ERROR: mysqlhotcopy did not succeed. Command used is /usr/share/mysql-zrm/plugins/socket-copy.pl --mysqlhotcopy --user="mysql_backup _usr" --password="*****" --host="remote.mysql.net" --quiet sample./^student$/ "/var/lib/mysql-zrm/DailyBackup_SockCopy/20080902005809" > /tmp/N8chQP2fxr 2>&1 Return value is 28416
DailyBackup_SockCopy:backup:INFO: PHASE START: Cleanup
DailyBackup_SockCopy:backup:INFO: backup-status=Backup failed
DailyBackup_SockCopy:backup:INFO: Backup failed
DailyBackup_SockCopy:backup:INFO: mailing file /tmp/lOXDcOZJle
DailyBackup_SockCopy:backup:INFO: mail command is cat "/tmp/lOXDcOZJle"|mail -s "[ZRM for MySQL Report] ERROR during backup of backup-set DailyBackup_SockCopy" root@localhost
DailyBackup_SockCopy:backup:INFO: PHASE END: Cleanup
DailyBackup_SockCopy:backup:INFO: END OF BACKUP
ERROR: /usr/bin/mysql-zrm did not finish successfully

kulkarni_mangesh
September 2nd, 2008, 01:29 AM
“Connection refused” may be due to one of the following:


MySQL ZRM Client has not installed.

“MySQL-zrm-client-2.0-1.noarch.rpm” needs to be installed on MySQL server, on which Database is residing which will be backed up.
You can check by executing “rpm -qa | grep –i zrm” command on MySQL server after logged-in as root.
If you have installed above mentioned package and still you are getting these errors, then make sure that ZRM Socket server is running and listening 23500 port by doing “telnet MYSQL_SERVER_IP 23500” from ZRM server. If it fails to connect then restart XINETD on MySQL server, by executing “service xinted restart” and once again do the telnet. After this try to perform Backup and Restore.


Assuming following things:

UID and GID of “mysql” user are matching on ZRM server with MySQL server.
Backup user has proper permissions on Database to take backup.

mrigank.mishra
September 2nd, 2008, 05:46 AM
“Connection refused” may be due to one of the following:


MySQL ZRM Client has not installed.

“MySQL-zrm-client-2.0-1.noarch.rpm” needs to be installed on MySQL server, on which Database is residing which will be backed up.
You can check by executing “rpm -qa | grep –i zrm” command on MySQL server after logged-in as root.
If you have installed above mentioned package and still you are getting these errors, then make sure that ZRM Socket server is running and listening 23500 port by doing “telnet MYSQL_SERVER_IP 23500” from ZRM server. If it fails to connect then restart XINETD on MySQL server, by executing “service xinted restart” and once again do the telnet. After this try to perform Backup and Restore.


Assuming following things:

UID and GID of “mysql” user are matching on ZRM server with MySQL server.
Backup user has proper permissions on Database to take backup.


My problem is fixed now - guess it was because the mysql_backup_usr was not added to mysql group... It is working fine now...

But I'm having problems with encyption for which I have posted a new thread (http://forums.zmanda.com/showthread.php?p=4681#post4681)

Also, I tried various methods for key-exchange but I still can't do away with the password problem....

kulkarni_mangesh
September 3rd, 2008, 04:47 AM
Can you brief us on, exact steps you have performed for SSH configuration..?

mrigank.mishra
September 4th, 2008, 05:18 AM
Can you brief us on, exact steps you have performed for SSH configuration..?

I got this pdf from the zmanda network called mysql-backup-zrm-over-internet.pdf It outlines the following commands:

*On ZRM server:

[root@quartz /]# mkdir /root/.ssh
[root@quartz /]# chmod 700 /root/.ssh
[root@quartz /]# ssh-keygen -t rsa

*On MySQL server:

db2:~ # mkdir /var/lib/mysql/.ssh
db2:~ # chown mysql /var/lib/mysql/.ssh
db2:~ # chmod 700 /var/lib/mysql/.ssh



*On ZRM server:

[root@quartz .ssh]# scp /root/.ssh/id_rsa.pub root@db2.zmanda.com:/var/lib/mysql/.ssh/authorized_keys


*On MySQL server:

db2:/ # chmod 600 /var/lib/mysql/.ssh/authorized_keys
db2:/ # chown mysql /var/lib/mysql/.ssh/authorized_keys


I had also tried using the steps you had told but there were problems with copying the id_rsa.pub from one server to another (from the ZRM to MySQL server: /tmp/id_rsa.pub.zrm using mysql) So I after generating the file as mysql, I copied it to the other system as root and then followed the procedure outlined by you. I had also left the passphrase empty..

kulkarni_mangesh
September 4th, 2008, 07:25 AM
We need to export key by using "mysql" user in any case to perform backup and restore, without specifying password. ZRM server takes backup of any MySQL Database by using "mysql" user only. Currently what your steps will allow root@zrm_server to login to mysql@mysql_server without providing a password. We need mysql@zrm_server should login to mysql@mysql_server without providing a password.

So in summary please follow the exact steps what we have said.

Now I am curious about, why you are not able to copy id_rsa.pub file by using "mysql" user from ZRM server to MySQL Database server, what error you are getting...?

mrigank.mishra
September 4th, 2008, 09:45 PM
We need to export key by using "mysql" user in any case to perform backup and restore, without specifying password. ZRM server takes backup of any MySQL Database by using "mysql" user only. Currently what your steps will allow root@zrm_server to login to mysql@mysql_server without providing a password. We need mysql@zrm_server should login to mysql@mysql_server without providing a password.

So in summary please follow the exact steps what we have said.

Now I am curious about, why you are not able to copy id_rsa.pub file by using "mysql" user from ZRM server to MySQL Database server, what error you are getting...?

I don't know if this is expected behaviour of ZRM or not, but I just now checked the backup with backup-mode=logical. Surprisingly enough, this time the backup completed without asking for any password...
Earlier I had been trying with back-mode=raw (default) and it was asking for password. I tried doing the same thing again (hoping for a "pleasant accident" that might have fixed something...) but it started asking for password again...
Any explanations for this behaviour?!!

The log for backup-mode=logical is below:

backup:~ # mysql-zrm-scheduler --backup-set DailyBackup_SSH --backup-level 0 -now
schedule:INFO: ZRM for MySQL Community Edition - version 2.0
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition - version 2.0
DailyBackup_SSH:backup:INFO: START OF BACKUP
DailyBackup_SSH:backup:INFO: PHASE START: Initialization
DailyBackup_SSH:backup:INFO: Mail address: root@localhost is ok
DailyBackup_SSH:backup:INFO: ZRM Temporary configuration file = /etc/mysql-zrm/DailyBackup_SSH/tmpBye2Y.conf
DailyBackup_SSH:backup:INFO: {
DailyBackup_SSH:backup:INFO: verbose=1
DailyBackup_SSH:backup:INFO: encrypt-plugin=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SSH:backup:INFO: retention-policy=30D
DailyBackup_SSH:backup:INFO: decrypt-option=-d
DailyBackup_SSH:backup:INFO: password=******
DailyBackup_SSH:backup:INFO: tables=student
DailyBackup_SSH:backup:INFO: backup-mode=logical
DailyBackup_SSH:backup:INFO: compress-plugin=/usr/bin/gzip
DailyBackup_SSH:backup:INFO: user=mysql_backup_usr
DailyBackup_SSH:backup:INFO: copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl
DailyBackup_SSH:backup:INFO: backup-level=0
DailyBackup_SSH:backup:INFO: quiet=0
DailyBackup_SSH:backup:INFO: encrypt=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SSH:backup:INFO: mailto=root@localhost
DailyBackup_SSH:backup:INFO: ssh-user=mysql
DailyBackup_SSH:backup:INFO: host=remote.mysql.net
DailyBackup_SSH:backup:INFO: database=sample
DailyBackup_SSH:backup:INFO: compress=/usr/bin/gzip
DailyBackup_SSH:backup:INFO: }
DailyBackup_SSH:backup:INFO: Getting mysql variables
DailyBackup_SSH:backup:INFO: mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" variables
DailyBackup_SSH:backup:INFO: datadir is /usr/local/mysql/var/
DailyBackup_SSH:backup:INFO: mysql_version is 5.0.67-log
DailyBackup_SSH:backup:INFO: InnoDB data file are /usr/local/mysql/var/ibdata1
DailyBackup_SSH:backup:INFO: InnoDB log dir is /usr/local/mysql/var/.
DailyBackup_SSH:backup:INFO: backup set being used is DailyBackup_SSH
DailyBackup_SSH:backup:INFO: backup-set=DailyBackup_SSH
DailyBackup_SSH:backup:INFO: backup-date=20080905170240
DailyBackup_SSH:backup:INFO: mysql-server-os=Linux/Unix
DailyBackup_SSH:backup:INFO: host=remote.mysql.net
DailyBackup_SSH:backup:INFO: backup-date-epoch=1220614360
DailyBackup_SSH:backup:INFO: retention-policy=30D
DailyBackup_SSH:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.0
DailyBackup_SSH:backup:INFO: mysql-version=5.0.67-log
DailyBackup_SSH:backup:INFO: backup-directory=/var/lib/mysql-zrm/DailyBackup_SSH/20080905170240
DailyBackup_SSH:backup:INFO: backup-level=0
DailyBackup_SSH:backup:INFO: backup-mode=logical
DailyBackup_SSH:backup:INFO: PHASE END: Initialization
DailyBackup_SSH:backup:INFO: PHASE START: Running pre backup plugin
DailyBackup_SSH:backup:INFO: Executing pre-backup-plugin
DailyBackup_SSH:backup:INFO: PHASE END: Running pre backup plugin
DailyBackup_SSH:backup:INFO: PHASE START: Flushing logs
DailyBackup_SSH:backup:INFO: Flushing the logs
DailyBackup_SSH:backup:INFO: mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" flush-logs
DailyBackup_SSH:backup:INFO: Getting master logname using command mysql --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" -e "show master status"
DailyBackup_SSH:backup:INFO: PHASE END: Flushing logs
DailyBackup_SSH:backup:INFO: PHASE START: Creating logical backup
DailyBackup_SSH:backup:INFO: Command used for logical backup is mysqldump --opt --extended-insert --single-transaction --create-options --default-character-set=utf8 --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" sample student > "/var/lib/mysql-zrm/DailyBackup_SSH/20080905170240/backup.sql"
DailyBackup_SSH:backup:INFO: Logical backup done for the following table(s)
student
For the database: sample
DailyBackup_SSH:backup:INFO: logical-tables=student
logical-databases=sample
DailyBackup_SSH:backup:INFO: PHASE END: Creating logical backup
DailyBackup_SSH:backup:INFO: PHASE START: Calculating backup size & checksums
DailyBackup_SSH:backup:INFO: next-binlog=mysql-bin.000177
DailyBackup_SSH:backup:INFO: last-backup=/var/lib/mysql-zrm/DailyBackup_SSH/20080905170221
DailyBackup_SSH:backup:INFO: backup-size=0.00 MB
DailyBackup_SSH:backup:INFO: PHASE END: Calculating backup size & checksums
DailyBackup_SSH:backup:INFO: PHASE START: Compression/Encryption
DailyBackup_SSH:backup:INFO: Compressing Encrypting backup
DailyBackup_SSH:backup:INFO: Command used is 'tar --same-owner -cpsC "/var/lib/mysql-zrm/DailyBackup_SSH/20080905170240" --exclude=backup-data --exclude=index --exclude=zrm_checksum . 2>/tmp/KiRhIOdLPz | "/usr/bin/gzip" 2>/tmp/nGPE2HATke | "/usr/share/mysql-zrm/plugins/encrypt.pl" > "/var/lib/mysql-zrm/DailyBackup_SSH/20080905170240/backup-data" 2>/tmp/ZKLNk50tLq'
DailyBackup_SSH:backup:INFO: compress=/usr/bin/gzip
DailyBackup_SSH:backup:INFO: encrypt=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SSH:backup:INFO: decrypt-option=-d
DailyBackup_SSH:backup:INFO: backup-size-compressed=0.00 MB
DailyBackup_SSH:backup:INFO: Removing all of the uncompressed/unencrypted data
DailyBackup_SSH:backup:INFO: PHASE END: Compression/Encryption
DailyBackup_SSH:backup:INFO: read-locks-time=00:00:00
DailyBackup_SSH:backup:INFO: flush-logs-time=00:00:01
DailyBackup_SSH:backup:INFO: compress-encrypt-time=00:00:00
DailyBackup_SSH:backup:INFO: backup-time=00:00:01
DailyBackup_SSH:backup:INFO: backup-status=Backup succeeded
DailyBackup_SSH:backup:INFO: Backup succeeded
DailyBackup_SSH:backup:INFO: PHASE START: Running post backup plugin
DailyBackup_SSH:backup:INFO: Executing post-backup-plugin
DailyBackup_SSH:backup:INFO: PHASE END: Running post backup plugin
DailyBackup_SSH:backup:INFO: PHASE START: Mailing backup report
DailyBackup_SSH:backup:INFO: mailing file /tmp/0JTaGXVLBE
DailyBackup_SSH:backup:INFO: mail command is cat "/tmp/0JTaGXVLBE"|mail -s "[ZRM for MySQL Report] backup-set DailyBackup_SSH" root@localhost
DailyBackup_SSH:backup:INFO: PHASE END: Mailing backup report
DailyBackup_SSH:backup:INFO: PHASE START: Cleanup
DailyBackup_SSH:backup:INFO: PHASE END: Cleanup
DailyBackup_SSH:backup:INFO: END OF BACKUP
/usr/bin/mysql-zrm started successfully




Posting the backup-mode=raw in the next post since the content is exceeding the post limits..

mrigank.mishra
September 4th, 2008, 09:46 PM
The log for back-mode=raw is below:

backup:~ # mysql-zrm-scheduler --backup-set DailyBackup_SSH --backup-level 0 -now
schedule:INFO: ZRM for MySQL Community Edition - version 2.0
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition - version 2.0
DailyBackup_SSH:backup:INFO: START OF BACKUP
DailyBackup_SSH:backup:INFO: PHASE START: Initialization
DailyBackup_SSH:backup:INFO: Mail address: root@localhost is ok
DailyBackup_SSH:backup:INFO: ZRM Temporary configuration file = /etc/mysql-zrm/DailyBackup_SSH/tmpayQD6.conf
DailyBackup_SSH:backup:INFO: {
DailyBackup_SSH:backup:INFO: verbose=1
DailyBackup_SSH:backup:INFO: encrypt-plugin=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SSH:backup:INFO: retention-policy=30D
DailyBackup_SSH:backup:INFO: decrypt-option=-d
DailyBackup_SSH:backup:INFO: password=******
DailyBackup_SSH:backup:INFO: tables=student
DailyBackup_SSH:backup:INFO: backup-mode=raw
DailyBackup_SSH:backup:INFO: compress-plugin=/usr/bin/gzip
DailyBackup_SSH:backup:INFO: user=mysql_backup_usr
DailyBackup_SSH:backup:INFO: copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl
DailyBackup_SSH:backup:INFO: backup-level=0
DailyBackup_SSH:backup:INFO: quiet=0
DailyBackup_SSH:backup:INFO: encrypt=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SSH:backup:INFO: mailto=root@localhost
DailyBackup_SSH:backup:INFO: ssh-user=mysql
DailyBackup_SSH:backup:INFO: host=remote.mysql.net
DailyBackup_SSH:backup:INFO: database=sample
DailyBackup_SSH:backup:INFO: compress=/usr/bin/gzip
DailyBackup_SSH:backup:INFO: }
DailyBackup_SSH:backup:INFO: Getting mysql variables
DailyBackup_SSH:backup:INFO: mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" variables
DailyBackup_SSH:backup:INFO: datadir is /usr/local/mysql/var/
DailyBackup_SSH:backup:INFO: mysql_version is 5.0.67-log
DailyBackup_SSH:backup:INFO: InnoDB data file are /usr/local/mysql/var/ibdata1
DailyBackup_SSH:backup:INFO: InnoDB log dir is /usr/local/mysql/var/.
DailyBackup_SSH:backup:INFO: backup set being used is DailyBackup_SSH
DailyBackup_SSH:backup:INFO: backup-set=DailyBackup_SSH
DailyBackup_SSH:backup:INFO: backup-date=20080905170618
DailyBackup_SSH:backup:INFO: mysql-server-os=Linux/Unix
DailyBackup_SSH:backup:INFO: host=remote.mysql.net
DailyBackup_SSH:backup:INFO: backup-date-epoch=1220614578
DailyBackup_SSH:backup:INFO: retention-policy=30D
DailyBackup_SSH:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.0
DailyBackup_SSH:backup:INFO: mysql-version=5.0.67-log
DailyBackup_SSH:backup:INFO: backup-directory=/var/lib/mysql-zrm/DailyBackup_SSH/20080905170618
DailyBackup_SSH:backup:INFO: backup-level=0
DailyBackup_SSH:backup:INFO: backup-mode=raw
DailyBackup_SSH:backup:INFO: PHASE END: Initialization
DailyBackup_SSH:backup:INFO: PHASE START: Running pre backup plugin
DailyBackup_SSH:backup:INFO: Executing pre-backup-plugin
DailyBackup_SSH:backup:INFO: PHASE END: Running pre backup plugin
DailyBackup_SSH:backup:INFO: PHASE START: Flushing logs
DailyBackup_SSH:backup:INFO: Flushing the logs
DailyBackup_SSH:backup:INFO: mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" flush-logs
DailyBackup_SSH:backup:INFO: Getting master logname using command mysql --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" -e "show master status"
DailyBackup_SSH:backup:INFO: PHASE END: Flushing logs
DailyBackup_SSH:backup:INFO: Command used for getting engine type mysql --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" -e "show table status from \`sample\` like 'student'"
DailyBackup_SSH:backup:INFO: For database sample
DailyBackup_SSH:backup:INFO: and for table student
DailyBackup_SSH:backup:INFO: engine
DailyBackup_SSH:backup:INFO: MyISAM
DailyBackup_SSH:backup:INFO: PHASE START: Creating raw backup
DailyBackup_SSH:backup:INFO: Command used for raw backup is /usr/share/mysql-zrm/plugins/ssh-copy.pl --mysqlhotcopy --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" --quiet sample./^student$/ "/var/lib/mysql-zrm/DailyBackup_SSH/20080905170618" > /tmp/GAuBzDSRlT 2>&1
Password:
Password:
Password:
Password:
DailyBackup_SSH:backup:INFO: raw-tables=student
DailyBackup_SSH:backup:INFO: raw-databases=sample
DailyBackup_SSH:backup:INFO: PHASE END: Creating raw backup
DailyBackup_SSH:backup:INFO: PHASE START: Calculating backup size & checksums
DailyBackup_SSH:backup:INFO: next-binlog=mysql-bin.000181
DailyBackup_SSH:backup:INFO: last-backup=/var/lib/mysql-zrm/DailyBackup_SSH/20080905170543
DailyBackup_SSH:backup:INFO: backup-size=0.01 MB
DailyBackup_SSH:backup:INFO: PHASE END: Calculating backup size & checksums
DailyBackup_SSH:backup:INFO: PHASE START: Compression/Encryption
DailyBackup_SSH:backup:INFO: Compressing Encrypting backup
DailyBackup_SSH:backup:INFO: Command used is 'tar --same-owner -cpsC "/var/lib/mysql-zrm/DailyBackup_SSH/20080905170618" --exclude=backup-data --exclude=index --exclude=zrm_checksum . 2>/tmp/UnYLyAS99Q | "/usr/bin/gzip" 2>/tmp/2eYLRESPYt | "/usr/share/mysql-zrm/plugins/encrypt.pl" > "/var/lib/mysql-zrm/DailyBackup_SSH/20080905170618/backup-data" 2>/tmp/GAuBzDSRlT'
DailyBackup_SSH:backup:INFO: compress=/usr/bin/gzip
DailyBackup_SSH:backup:INFO: encrypt=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SSH:backup:INFO: decrypt-option=-d
DailyBackup_SSH:backup:INFO: backup-size-compressed=0.00 MB
DailyBackup_SSH:backup:INFO: Removing all of the uncompressed/unencrypted data
DailyBackup_SSH:backup:INFO: PHASE END: Compression/Encryption
DailyBackup_SSH:backup:INFO: read-locks-time=00:00:14
DailyBackup_SSH:backup:INFO: flush-logs-time=00:00:00
DailyBackup_SSH:backup:INFO: compress-encrypt-time=00:00:00
DailyBackup_SSH:backup:INFO: backup-time=00:00:14
DailyBackup_SSH:backup:INFO: backup-status=Backup succeeded
DailyBackup_SSH:backup:INFO: Backup succeeded
DailyBackup_SSH:backup:INFO: PHASE START: Running post backup plugin
DailyBackup_SSH:backup:INFO: Executing post-backup-plugin
DailyBackup_SSH:backup:INFO: PHASE END: Running post backup plugin
DailyBackup_SSH:backup:INFO: PHASE START: Mailing backup report
DailyBackup_SSH:backup:INFO: mailing file /tmp/23fD6hqjUN
DailyBackup_SSH:backup:INFO: mail command is cat "/tmp/23fD6hqjUN"|mail -s "[ZRM for MySQL Report] backup-set DailyBackup_SSH" root@localhost
DailyBackup_SSH:backup:INFO: PHASE END: Mailing backup report
DailyBackup_SSH:backup:INFO: PHASE START: Cleanup
DailyBackup_SSH:backup:INFO: PHASE END: Cleanup
DailyBackup_SSH:backup:INFO: END OF BACKUP
/usr/bin/mysql-zrm started successfully

kulkarni_mangesh
September 6th, 2008, 12:35 AM
Normally we can perform perform backup of MySQL database by two ways in ZRM.

Logical.

Logical backup: Logical backup uses "mysqldump" utility to take a backup of MySQL database. This type of backup exports all data of target Database in the form of SQL statemets. So in summary when we perform Logical backup we don't need to do SSH to the MySQL server as we can dump all data of target database on ZRM server without actually log-in to the Database server.If backup succeeds, you can see "backup.sql" file present at the backup destination directory. This is plain ASCII text file, you can see the content of this file by using "cat, vi etc....."


Raw.

Raw backup: In case of raw backup, ZRM copies all Database related files physically on the ZRM server, hence it requires to login to MySQL Database server machine and copy the files to ZRM server. This is performed by two ways. One by using Socket copy and other by using SSH, hence it requires Socket copy OR SSH to be configured. If backup succeeds, you will find binary files at the backup destination direcotry. These files are similar to the files present at "mysql_datadir" on MySQL server.





As we have mentioned, to do passwordless authenticatrion in SSh, we need to export ssh keys from ZRM server to MySQL Database server for "mysql" user.

Hope we have cleared your doubts.

mrigank.mishra
September 12th, 2008, 04:29 AM
Normally we can perform perform backup of MySQL database by two ways in ZRM.

Logical.

Logical backup: Logical backup uses "mysqldump" utility to take a backup of MySQL database. This type of backup exports all data of target Database in the form of SQL statemets. So in summary when we perform Logical backup we don't need to do SSH to the MySQL server as we can dump all data of target database on ZRM server without actually log-in to the Database server.If backup succeeds, you can see "backup.sql" file present at the backup destination directory. This is plain ASCII text file, you can see the content of this file by using "cat, vi etc....."


Raw.

Raw backup: In case of raw backup, ZRM copies all Database related files physically on the ZRM server, hence it requires to login to MySQL Database server machine and copy the files to ZRM server. This is performed by two ways. One by using Socket copy and other by using SSH, hence it requires Socket copy OR SSH to be configured. If backup succeeds, you will find binary files at the backup destination direcotry. These files are similar to the files present at "mysql_datadir" on MySQL server.





As we have mentioned, to do passwordless authenticatrion in SSh, we need to export ssh keys from ZRM server to MySQL Database server for "mysql" user.

Hope we have cleared your doubts.

Thank you so much for providing the details: Am sorry for being negligent!

Well! I tried several times to obtain a proper key exchange but I haven't been successful in my endevours..
I can now passwordlessly log onto the remote MySQL server from ZRM server using mysql account via SSH. But when we take the backup, it still keeps asking for the password..
I created the keys using the method you had described in one of your earlier posts - I also LEFT PASSPHRASE FIELD BLANK.

But that did not work - untill I created a separate pair of keys for the root user on ZRM and copied it to authorized_keys file of mysql user... COULD YOU PLEASE EXPLAIN THIS?? (Is it because I have used a "backup user"??)

I have attached the log below:
backup:~ # mysql-zrm-scheduler --backup-set DailyBackup_SSH --backup-level 0 --now
schedule:INFO: ZRM for MySQL Community Edition - version 2.0
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition - version 2.0
DailyBackup_SSH:backup:INFO: START OF BACKUP
DailyBackup_SSH:backup:INFO: PHASE START: Initialization
DailyBackup_SSH:backup:INFO: Mail address: root@localhost is ok
DailyBackup_SSH:backup:INFO: ZRM Temporary configuration file = /etc/mysql-zrm/DailyBackup_SSH/tmpWIeer.conf
DailyBackup_SSH:backup:INFO: {
DailyBackup_SSH:backup:INFO: verbose=1
DailyBackup_SSH:backup:INFO: encrypt-plugin=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SSH:backup:INFO: retention-policy=30D
DailyBackup_SSH:backup:INFO: decrypt-option=-d
DailyBackup_SSH:backup:INFO: password=******
DailyBackup_SSH:backup:INFO: tables=mysql_zrm_enc_test
DailyBackup_SSH:backup:INFO: backup-mode=raw
DailyBackup_SSH:backup:INFO: compress-plugin=/usr/bin/gzip
DailyBackup_SSH:backup:INFO: user=mysql_backup_usr
DailyBackup_SSH:backup:INFO: copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl
DailyBackup_SSH:backup:INFO: backup-level=0
DailyBackup_SSH:backup:INFO: quiet=0
DailyBackup_SSH:backup:INFO: encrypt=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SSH:backup:INFO: mailto=root@localhost
DailyBackup_SSH:backup:INFO: ssh-user=mysql
DailyBackup_SSH:backup:INFO: host=remote.mysql.net
DailyBackup_SSH:backup:INFO: database=optical
DailyBackup_SSH:backup:INFO: compress=/usr/bin/gzip
DailyBackup_SSH:backup:INFO: }
DailyBackup_SSH:backup:INFO: Getting mysql variables
DailyBackup_SSH:backup:INFO: mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" variables
DailyBackup_SSH:backup:INFO: datadir is /usr/local/mysql/var/
DailyBackup_SSH:backup:INFO: mysql_version is 5.0.67-log
DailyBackup_SSH:backup:INFO: InnoDB data file are /usr/local/mysql/var/ibdata1
DailyBackup_SSH:backup:INFO: InnoDB log dir is /usr/local/mysql/var/.
DailyBackup_SSH:backup:INFO: backup set being used is DailyBackup_SSH
DailyBackup_SSH:backup:INFO: backup-set=DailyBackup_SSH
DailyBackup_SSH:backup:INFO: backup-date=20080913000023
DailyBackup_SSH:backup:INFO: mysql-server-os=Linux/Unix
DailyBackup_SSH:backup:INFO: host=remote.mysql.net
DailyBackup_SSH:backup:INFO: backup-date-epoch=1221244223
DailyBackup_SSH:backup:INFO: retention-policy=30D
DailyBackup_SSH:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.0
DailyBackup_SSH:backup:INFO: mysql-version=5.0.67-log
DailyBackup_SSH:backup:INFO: backup-directory=/var/lib/mysql-zrm/DailyBackup_SSH/20080913000023
DailyBackup_SSH:backup:INFO: backup-level=0
DailyBackup_SSH:backup:INFO: backup-mode=raw
DailyBackup_SSH:backup:INFO: PHASE END: Initialization
DailyBackup_SSH:backup:INFO: PHASE START: Running pre backup plugin
DailyBackup_SSH:backup:INFO: Executing pre-backup-plugin
DailyBackup_SSH:backup:INFO: PHASE END: Running pre backup plugin
DailyBackup_SSH:backup:INFO: PHASE START: Flushing logs
DailyBackup_SSH:backup:INFO: Flushing the logs
DailyBackup_SSH:backup:INFO: mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" flush-logs
DailyBackup_SSH:backup:INFO: Getting master logname using command mysql --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" -e "show master status"
DailyBackup_SSH:backup:INFO: PHASE END: Flushing logs
DailyBackup_SSH:backup:INFO: Command used for getting engine type mysql --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" -e "show table status from \`optical\` like 'mysql_zrm_enc_test'"
DailyBackup_SSH:backup:INFO: For database optical
DailyBackup_SSH:backup:INFO: and for table mysql_zrm_enc_test
DailyBackup_SSH:backup:INFO: engine
DailyBackup_SSH:backup:INFO: MyISAM
DailyBackup_SSH:backup:INFO: PHASE START: Creating raw backup
DailyBackup_SSH:backup:INFO: Command used for raw backup is /usr/share/mysql-zrm/plugins/ssh-copy.pl --mysqlhotcopy --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" --quiet optical./^mysql_zrm_enc_test$/ "/var/lib/mysql-zrm/DailyBackup_SSH/20080913000023" > /tmp/cT1rPMhbLg 2>&1
DailyBackup_SSH:backup:INFO: raw-tables=mysql_zrm_enc_test
DailyBackup_SSH:backup:INFO: raw-databases=optical
DailyBackup_SSH:backup:INFO: PHASE END: Creating raw backup
DailyBackup_SSH:backup:INFO: PHASE START: Calculating backup size & checksums
DailyBackup_SSH:backup:INFO: next-binlog=mysql-bin.000203
DailyBackup_SSH:backup:INFO: last-backup=/var/lib/mysql-zrm/DailyBackup_SSH/20080913000001
DailyBackup_SSH:backup:INFO: backup-size=0.01 MB
DailyBackup_SSH:backup:INFO: PHASE END: Calculating backup size & checksums
DailyBackup_SSH:backup:INFO: PHASE START: Compression/Encryption
DailyBackup_SSH:backup:INFO: Compressing Encrypting backup
DailyBackup_SSH:backup:INFO: Command used is 'tar --same-owner -cpsC "/var/lib/mysql-zrm/DailyBackup_SSH/20080913000023" --exclude=backup-data --exclude=index --exclude=zrm_checksum . 2>/tmp/Ecs2pWjtRs | "/usr/bin/gzip" 2>/tmp/ODTSbQqpXp | "/usr/share/mysql-zrm/plugins/encrypt.pl" > "/var/lib/mysql-zrm/DailyBackup_SSH/20080913000023/backup-data" 2>/tmp/cT1rPMhbLg'
DailyBackup_SSH:backup:INFO: compress=/usr/bin/gzip
DailyBackup_SSH:backup:INFO: encrypt=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SSH:backup:INFO: decrypt-option=-d
DailyBackup_SSH:backup:INFO: backup-size-compressed=0.00 MB
DailyBackup_SSH:backup:INFO: Removing all of the uncompressed/unencrypted data
DailyBackup_SSH:backup:INFO: PHASE END: Compression/Encryption
DailyBackup_SSH:backup:INFO: read-locks-time=00:00:02
DailyBackup_SSH:backup:INFO: flush-logs-time=00:00:00
DailyBackup_SSH:backup:INFO: compress-encrypt-time=00:00:00
DailyBackup_SSH:backup:INFO: backup-time=00:00:02
DailyBackup_SSH:backup:INFO: backup-status=Backup succeeded
DailyBackup_SSH:backup:INFO: Backup succeeded
DailyBackup_SSH:backup:INFO: PHASE START: Running post backup plugin
DailyBackup_SSH:backup:INFO: Executing post-backup-plugin
DailyBackup_SSH:backup:INFO: PHASE END: Running post backup plugin
DailyBackup_SSH:backup:INFO: PHASE START: Mailing backup report
DailyBackup_SSH:backup:INFO: mailing file /tmp/K3YYCZqBO2
DailyBackup_SSH:backup:INFO: mail command is cat "/tmp/K3YYCZqBO2"|mail -s "[ZRM for MySQL Report] backup-set DailyBackup_SSH" root@localhost
DailyBackup_SSH:backup:INFO: PHASE END: Mailing backup report
DailyBackup_SSH:backup:INFO: PHASE START: Cleanup
DailyBackup_SSH:backup:INFO: PHASE END: Cleanup
DailyBackup_SSH:backup:INFO: END OF BACKUP
/usr/bin/mysql-zrm started successfully


Also, COULD YOU PLEASE DETAIL THE STEPS TO BE USED TO ENABLE SSL-SUPPORT ON ZRM??

kulkarni_mangesh
September 12th, 2008, 06:36 AM
Can you provide exact steps you have performed after setting up SSH keys successfully for mysql@ZRM_server to mysql@MySQL_server?

From your logs and description it looks like, you are taking backup of MySQL database by using "root" user. Do "su - mysql" on ZRM server prior to execute backup command.

mysql-zrm-scheduler --backup-set DailyBackup_SSH --backup-level 0 --now

Here we are assuming that "mysql" user from ZRM server can login to MySQL database server without providing password as "mysq" user.