PDA

View Full Version : ZRM user not allowed to connect to MySQL server



mrigank.mishra
September 18th, 2008, 05:11 AM
I recently tried completely removing MySQL 5.0.0.67 from the test system and then reinstalled mysql 5.1 candidate release on it. I granted the appropriate permissions again but the when I try to do the restore, it is denying access to the mysql backup user...
The log is provided below:

backup:~ # mysql-zrm-scheduler --backup-set DailyBackup_SockCopy --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_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/tmp0k3SK.conf
DailyBackup_SockCopy:backup:INFO: {
DailyBackup_SockCopy:backup:INFO: verbose=1
DailyBackup_SockCopy:backup:INFO: encrypt-plugin=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SockCopy:backup:INFO: retention-policy=30D
DailyBackup_SockCopy:backup:INFO: decrypt-option=-d
DailyBackup_SockCopy:backup:INFO: password=******
DailyBackup_SockCopy:backup:INFO: tables=student
DailyBackup_SockCopy:backup:INFO: backup-mode=logical
DailyBackup_SockCopy:backup:INFO: ssl-options=--ssl --ssl_ca=/etc/cacert.pem --ssl_cert=/etc/client_cert.pem --ssl_key=/etc/client_key.pem
DailyBackup_SockCopy:backup:INFO: compress-plugin=/usr/bin/gzip
DailyBackup_SockCopy:backup:INFO: user=mysql_backup_usr
DailyBackup_SockCopy:backup:INFO: copy-plugin=/usr/share/mysql-zrm/plugins/socket-copy.pl
DailyBackup_SockCopy:backup:INFO: backup-level=0
DailyBackup_SockCopy:backup:INFO: quiet=0
DailyBackup_SockCopy:backup:INFO: encrypt=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SockCopy:backup:INFO: mailto=root@localhost
DailyBackup_SockCopy:backup:INFO: host=remote.mysql.net
DailyBackup_SockCopy:backup:INFO: database=sample
DailyBackup_SockCopy:backup:INFO: compress=/usr/bin/gzip
DailyBackup_SockCopy:backup:INFO: }
DailyBackup_SockCopy:backup:INFO: Getting mysql variables
DailyBackup_SockCopy:backup:INFO: mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" variables
DailyBackup_SockCopy:backup:ERROR: Output of command: 'mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" variables' is {
mysqladmin: connect to server at 'remote.mysql.net' failed
error: 'Host 'backup.mysql.net' is not allowed to connect to this MySQL server'
}
DailyBackup_SockCopy:backup:ERROR: Cannot connect to mysql server!
DailyBackup_SockCopy:backup:INFO: PHASE START: Cleanup
DailyBackup_SockCopy:backup:INFO: mailing file /tmp/DE2BZCqoFz
DailyBackup_SockCopy:backup:INFO: mail command is cat "/tmp/DE2BZCqoFz"|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
You have new mail in /var/mail/root



Can somebody help me with it?!

paddy
September 18th, 2008, 03:45 PM
I
DailyBackup_SockCopy:backup:INFO: mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" variables
DailyBackup_SockCopy:backup:ERROR: Output of command: 'mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" variables' is {
mysqladmin: connect to server at 'remote.mysql.net' failed
error: 'Host 'backup.mysql.net' is not allowed to connect to this MySQL server'
}


If you are backing up remote MySQL server, backup user privileges should be granted for the backup user accessing from the MySQL server
as well as the server running ZRM.

See http://mysqlbackup.zmanda.com/index.php/Pre-Installation#MySQL_Backup_User for more information.

Paddy

mrigank.mishra
September 19th, 2008, 04:58 AM
If you are backing up remote MySQL server, backup user privileges should be granted for the backup user accessing from the MySQL server
as well as the server running ZRM.

See http://mysqlbackup.zmanda.com/index.php/Pre-Installation#MySQL_Backup_User for more information.

Paddy

I had mentioned in the original post itself that I had GRANTed the permissions properly..

It had a problem with database creation - I did so and was able to fix it.

However, when I'm taking the backups now and then analyzing the packets sent, I find that the packets are unencrypted so it is possible to see the queries in the MySQL protocol used for backup. I'm attaching the log below for your reference. Also, I found that mysql understands the ssl-options with an underscore instead of a hyphen - I tried the backup with but ZRM went through without throwing any errors...

backup:~ # mysql-zrm-scheduler --backup-set DailyBackup_SockCopy --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_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/tmptGzW9.conf
DailyBackup_SockCopy:backup:INFO: {
DailyBackup_SockCopy:backup:INFO: encrypt-plugin=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SockCopy:backup:INFO: retention-policy=30D
DailyBackup_SockCopy:backup:INFO: verbose=1
DailyBackup_SockCopy:backup:INFO: decrypt-option=-d
DailyBackup_SockCopy:backup:INFO: password=******
DailyBackup_SockCopy:backup:INFO: tables=student
DailyBackup_SockCopy:backup:INFO: backup-mode=logical
DailyBackup_SockCopy:backup:INFO: ssl-options=--ssl --ssl_ca=/etc/cacert.pem --ssl_cert=/etc/client_cert.pem --ssl_key=/etc/client_key.pem
DailyBackup_SockCopy:backup:INFO: compress-plugin=/usr/bin/gzip
DailyBackup_SockCopy:backup:INFO: user=mysql_backup_usr
DailyBackup_SockCopy:backup:INFO: copy-plugin=/usr/share/mysql-zrm/plugins/socket-copy.pl
DailyBackup_SockCopy:backup:INFO: backup-level=0
DailyBackup_SockCopy:backup:INFO: quiet=0
DailyBackup_SockCopy:backup:INFO: encrypt=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SockCopy:backup:INFO: mailto=root@localhost
DailyBackup_SockCopy:backup:INFO: host=remote.mysql.net
DailyBackup_SockCopy:backup:INFO: database=sample
DailyBackup_SockCopy:backup:INFO: compress=/usr/bin/gzip
DailyBackup_SockCopy:backup:INFO: }
DailyBackup_SockCopy:backup:INFO: Getting mysql variables
DailyBackup_SockCopy:backup:INFO: mysqladmin --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" variables
DailyBackup_SockCopy:backup:INFO: datadir is /usr/local/var/
DailyBackup_SockCopy:backup:INFO: mysql_version is 5.1.28-rc-log
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=20080919235050
DailyBackup_SockCopy:backup:INFO: mysql-server-os=Linux/Unix
DailyBackup_SockCopy:backup:INFO: host=remote.mysql.net
DailyBackup_SockCopy:backup:INFO: backup-date-epoch=1221848450
DailyBackup_SockCopy:backup:INFO: retention-policy=30D
DailyBackup_SockCopy:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.0
DailyBackup_SockCopy:backup:INFO: mysql-version=5.1.28-rc-log
DailyBackup_SockCopy:backup:INFO: backup-directory=/var/lib/mysql-zrm/DailyBackup_SockCopy/20080919235050
DailyBackup_SockCopy:backup:INFO: backup-level=0
DailyBackup_SockCopy:backup:INFO: backup-mode=logical
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" --password="*****" --host="remote.mysql.net" flush-logs
DailyBackup_SockCopy:backup:INFO: Getting master logname using command mysql --user="mysql_backup_usr" --password="*****" --host="remote.mysql.net" -e "show master status"
DailyBackup_SockCopy:backup:INFO: PHASE END: Flushing logs
DailyBackup_SockCopy:backup:INFO: PHASE START: Creating logical backup
DailyBackup_SockCopy: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_SockCopy/20080919235050/backup.sql"
DailyBackup_SockCopy:backup:INFO: Logical backup done for the following table(s)
student
For the database: sample
DailyBackup_SockCopy:backup:INFO: logical-tables=student
logical-databases=sample
DailyBackup_SockCopy:backup:INFO: PHASE END: Creating logical backup
DailyBackup_SockCopy:backup:INFO: PHASE START: Calculating backup size & checksums
DailyBackup_SockCopy:backup:INFO: next-binlog=mysql-bin.000036
DailyBackup_SockCopy:backup:INFO: last-backup=/var/lib/mysql-zrm/DailyBackup_SockCopy/20080919181735
DailyBackup_SockCopy:backup:INFO: backup-size=0.00 MB
DailyBackup_SockCopy:backup:INFO: PHASE END: Calculating backup size & checksums
DailyBackup_SockCopy:backup:INFO: PHASE START: Compression/Encryption
DailyBackup_SockCopy:backup:INFO: Compressing Encrypting backup
DailyBackup_SockCopy:backup:INFO: Command used is 'tar --same-owner -cpsC "/var/lib/mysql-zrm/DailyBackup_SockCopy/20080919235050" --exclude=backup-data --exclude=index --exclude=zrm_checksum . 2>/tmp/Ss77w46kOD | "/usr/bin/gzip" 2>/tmp/aAyixCvMok | "/usr/share/mysql-zrm/plugins/encrypt.pl" > "/var/lib/mysql-zrm/DailyBackup_SockCopy/20080919235050/backup-data" 2>/tmp/Izha5seBYP'
DailyBackup_SockCopy:backup:INFO: compress=/usr/bin/gzip
DailyBackup_SockCopy:backup:INFO: encrypt=/usr/share/mysql-zrm/plugins/encrypt.pl
DailyBackup_SockCopy:backup:INFO: decrypt-option=-d
DailyBackup_SockCopy:backup:INFO: backup-size-compressed=0.00 MB
DailyBackup_SockCopy:backup:INFO: Removing all of the uncompressed/unencrypted data
DailyBackup_SockCopy:backup:INFO: PHASE END: Compression/Encryption
DailyBackup_SockCopy:backup:INFO: read-locks-time=00:00:00
DailyBackup_SockCopy:backup:INFO: flush-logs-time=00:00:00
DailyBackup_SockCopy:backup:INFO: compress-encrypt-time=00:00:10
DailyBackup_SockCopy:backup:INFO: backup-time=00:00:01
DailyBackup_SockCopy:backup:INFO: backup-status=Backup succeeded
DailyBackup_SockCopy:backup:INFO: Backup succeeded
DailyBackup_SockCopy:backup:INFO: PHASE START: Running post backup plugin
DailyBackup_SockCopy:backup:INFO: Executing post-backup-plugin
DailyBackup_SockCopy:backup:INFO: PHASE END: Running post backup plugin
DailyBackup_SockCopy:backup:INFO: PHASE START: Mailing backup report
DailyBackup_SockCopy:backup:INFO: mailing file /tmp/idvKZUoS2U
DailyBackup_SockCopy:backup:INFO: mail command is cat "/tmp/idvKZUoS2U"|mail -s "[ZRM for MySQL Report] backup-set DailyBackup_SockCopy" root@localhost
DailyBackup_SockCopy:backup:INFO: PHASE END: Mailing backup report
DailyBackup_SockCopy:backup:INFO: PHASE START: Cleanup
DailyBackup_SockCopy:backup:INFO: PHASE END: Cleanup
DailyBackup_SockCopy:backup:INFO: END OF BACKUP
/usr/bin/mysql-zrm started successfully