Skip to content

# mylvmbackup HOWTO: minimal privileges & filesystem copy

MySQL Backup Linux Open Source scripts Security August 17, 2010August 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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
7
Leave a Reply

6 Comment threads
1 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
4 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
LenZ

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.

Author
shlomi

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… 🙂

Guest
JBHoren

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

Backing up a highly utilized MySQL db « "DZUB-NON TRUL-PAR"

[…] https://shlomi-noach.github.io/blog/mysql/mylvmbackup-howto-minimal-privileges-filesystem-copy […]

Creating MySQL backups using LVM — Effective MySQL

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

Guest
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?

Author
shlomi

@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

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.