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

7 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 [...]

  6. HarryKalahan wrote:

    I would like to make a question about performance of mylvmbackup. We've experienced performance problems running backups of mysql databases using mylvmbackup. Moreover the mysql service process get zombie and monit tried to start the mysql several times. We could see backup process takes too time to finish (6 hours of a database of 48GB). Could rsync reduce impact in a production database?

  7. shlomi wrote:

    @Harry, you can't just use rsync on a running server (writes keep occuring even if idle). You may just shut down the MySQL server, than rsync (or just cp/netcat). Otherwise take a look at Xtrabackup, which can throttle I/O

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org