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:


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:


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:


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:


Voila! Your backup is complete.

  • 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.

  • 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... 🙂

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

  • Pingback: Backing up a highly utilized MySQL db « "DZUB-NON TRUL-PAR"()

  • Pingback: Creating MySQL backups using LVM — Effective MySQL()

  • HarryKalahan

    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?

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

Powered by Wordpress and MySQL. Theme by