mylvmbackup HOWTO: minimal privileges & filesystem copy

August 17, 2010

This HOWTO discusses two (unrelated) issues with mylvmbackup:

  • The minimal privileges required to take MySQL backups with mylvmbackup.
  • Making (non compressed) file system copy of one's data files.

Minimal privileges

Some just give mylvmbackup the root account, which is far too permissive. We now consider what the minimal requirements of mylvmbackup are.

The queries mylvmbackup issues are:

  • FLUSH TABLES
  • FLUSH TABLES WITH READ LOCK
  • SHOW MASTER STATUS
  • SHOW SLAVE STATUS
  • UNLOCK TABLES

Both SHOW MASTER STATUS & SHOW SLAVE STATUS require either the SUPER or REPLICATION CLIENT privilege. Since SUPER is more powerful, we choose REPLICATION CLIENT.

The FLUSH TABLES * and UNLOCK TABLES require the RELOAD privilege.

However, we are not done yet. mylvmbackup connects to the mysql database, which means we must also have some privilege there, too. We choose the SELECT privilege.

Finally, here are the commands to create a mylvmbackup user with minimal privileges:

CREATE USER 'mylvmbackup'@'localhost' IDENTIFIED BY '12345';
GRANT RELOAD, REPLICATION CLIENT ON *.* TO 'mylvmbackup'@'localhost';
GRANT SELECT ON mysql.* TO 'mylvmbackup'@'localhost';

In the mylvmbackup.conf file, the correlating rows are:

[mysql]
user=mylvmbackup
password=12345
host=localhost

Filesystem copy

By default, mylvmbackup creates a .tar.gz compressed backup file of your data. This is good if the reason you're running mylvmbackup is to, well, make a backup. However, as with all backups, one may be making the backup so as to create a replication server. But in this case you don't really want compressed data: you want the data extracted on the replication server, just as it is on the original host.

mylvmbackup supports backing up the files using rsync.

To copy MySQL data to a remote host, configure the following in the mylvmbackup.conf file:

[fs]
backupdir=shlomi@backuphost:/data/backup/mysql
[misc]
backuptype=rsync

You may be prompted to enter password, unless you have the user's public key stored on the remote host.

Normally, rsync is considered as remote-sync, but it also works on local file systems. If you have a remote directory mounted on your file system (e.g. with nfs), you can use the fact that rsync works just as well with local file systems:

[fs]
backupdir=/mnt/backup/mysql
[misc]
backuptype=rsync

Voila! Your backup is complete.

tags: , , , ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

5 Comments to "mylvmbackup HOWTO: minimal privileges & filesystem copy"

  1. LenZ wrote:

    Hi Shlomi,

    Awesome! Thanks a lot for writing this up. I'll add a link to your article to the links section of the mylvmbackup home page.

  2. shlomi wrote:

    Hi Lenz,
    My personal impression of writing open source tools, is that writing the documentation is the hardest part. Once can have so much to discuss even the smallest feature. It's endless... :)

  3. JBHoren wrote:

    ישר כח, שלומי!

  4. Backing up a highly utilized MySQL db « "DZUB-NON TRUL-PAR" wrote:

    [...] http://code.openark.org/blog/mysql/mylvmbackup-howto-minimal-privileges-filesystem-copy [...]

  5. Creating MySQL backups using LVM — Effective MySQL wrote:

    [...] mylvmbackup HOWTO: minimal privileges & filesystem copy provides a detailed explanation of the minimum permissions required. Filed Under: Article [...]

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org