PDA

View Full Version : LVM Snapshots of remote MySQL server



serge
February 12th, 2009, 06:11 AM
Hello,

We're having a test run of Zmanda and ran into issue which we hope you can help us to resolve. Is it possible for remote MySQL server to be backed up via LVM snapshots? And if yes, then how do you configure it?

Thx, Serge.

kulkarni_mangesh
February 12th, 2009, 09:56 PM
Yes, it is possible. Can you please register yourself to Zmanda network and get the white papers...?

http://www.zmanda.com/quick-mysql-backup.html

serge
February 13th, 2009, 02:30 AM
We tried to do that, but the backup job is being stuck at the snapshot part:

host02:backup:INFO: getting device details using command /usr/share/mysql-zrm/plugins/lvm-snapshot.pl --action get-vm-device-details --directory /database/data/. --sname zrmYnZkFsTuHp --host host02 2>/tmp/VmRjTBbr0U


Locally everything works fine, however, from the remote backup server it's not.

Thx, Serge.

serge
February 13th, 2009, 02:53 AM
nevermind that. I found the issue - mysql user wasn't in the sudoers file

kulkarni_mangesh
February 13th, 2009, 03:19 AM
Can you check the following things has been configured properly or not on both MySQL ZRM server and ZRM Client..?


uid and gid of mysql user on both server and client are same.

SSh keys needs to be exported properly to ZRM client and Server. To configure, follow following steps.

On the ZRM server, perform the following steps:

Become the mysql user:
#su mysql

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@sunil

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 Client machine:


Login to mysql client machine as the mysql user:
#su mysql
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.


Sudoers settings as follows.
Add following entry in "sudoers" file on the machine on which ZRM server and client is running:

mysql: ALL=(ALL) NOPASSWD: ALL

Backup user should have rights to do backup and restore. Follow the steps mentioned in Solution section. Remember we need to this to all Databases.


Login to MySQL server
Connect to MySQL as root user
db2:/ # mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1 to server version: 5.0.18
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>
Give Privileges for "backup-user" to connect from remote host "quartz.zamanda.com"
mysql> GRANT LOCK TABLES, SELECT, FILE, CREATE, DROP, INDEX, SHUTDOWN, ALTER, INSERT, ALTER ROUTINE,CREATE ROUTINE, SUPER, SHOW VIEW, RELOAD ON *.* TO 'backup-user'@'quartz.zmanda.com' IDENTIFIED BY 'zrmbackup';
Query OK, 0 rows affected (0.01 sec)

Give Privileges for "backup-user" to connect from "localhost"
mysql> GRANT LOCK TABLES, SELECT, FILE, CREATE, DROP, INDEX, SHUTDOWN, ALTER, INSERT, ALTER ROUTINE,CREATE ROUTINE, SUPER, RELOAD ON *.* TO 'backup-user'@'db2.zmanda.com' IDENTIFIED BY 'zrmbackup';
Query OK, 0 rows affected (0.00 sec)

Flush Privileges
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)



Even after this if you face backup failure then

Please enable verbose logging by providing --verbose option to “mysql-zrm-backup” command.
Run a backup and post the output.

kulkarni_mangesh
February 13th, 2009, 03:22 AM
nevermind that. I found the issue - mysql user wasn't in the sudoers file

Great....., while I was typing responce you resolved the issue.

Thanks, Mangesh.