{"id":2839,"date":"2010-08-17T19:42:40","date_gmt":"2010-08-17T17:42:40","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=2839"},"modified":"2010-08-17T19:50:41","modified_gmt":"2010-08-17T17:50:41","slug":"mylvmbackup-howto-minimal-privileges-filesystem-copy","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/mylvmbackup-howto-minimal-privileges-filesystem-copy","title":{"rendered":"mylvmbackup HOWTO: minimal privileges &#038; filesystem copy"},"content":{"rendered":"<p>This HOWTO discusses two (unrelated) issues with <a href=\"http:\/\/www.lenzg.net\/mylvmbackup\/\"><em>mylvmbackup<\/em><\/a>:<\/p>\n<ul>\n<li>The minimal privileges required to take MySQL backups with <em>mylvmbackup.<\/em><\/li>\n<li>Making (non compressed) file system copy of one&#8217;s data files.<\/li>\n<\/ul>\n<h4>Minimal privileges<\/h4>\n<p>Some just give <em>mylvmbackup<\/em> the root account, which is far too permissive. We now consider what the minimal requirements of <em>mylvmbackup<\/em> are.<\/p>\n<p>The queries <em>mylvmbackup<\/em> issues are:<\/p>\n<ul>\n<li><strong>FLUSH TABLES<\/strong><\/li>\n<li><strong>FLUSH TABLES WITH READ LOCK<\/strong><\/li>\n<li><strong>SHOW MASTER STATUS<\/strong><\/li>\n<li><strong>SHOW SLAVE STATUS<\/strong><\/li>\n<li><strong>UNLOCK TABLES<\/strong><\/li>\n<\/ul>\n<p>Both <strong>SHOW MASTER STATUS<\/strong> &amp; <strong>SHOW SLAVE STATUS<\/strong> require either the <strong>SUPER<\/strong> or <strong>REPLICATION CLIENT<\/strong> privilege. Since <strong>SUPER<\/strong> is more powerful, we choose <strong>REPLICATION CLIENT<\/strong>.<\/p>\n<p>The <strong>FLUSH TABLES<\/strong> * and <strong>UNLOCK TABLES<\/strong> require the <strong>RELOAD<\/strong> privilege.<\/p>\n<p>However, we are not done yet. <em>mylvmbackup<\/em> connects to the <strong>mysql<\/strong> database, which means we must also have some privilege there, too. We choose the <strong>SELECT<\/strong> privilege.<\/p>\n<p><!--more-->Finally, here are the commands to create a <em>mylvmbackup<\/em> user with minimal privileges:<\/p>\n<blockquote>\n<pre>CREATE USER 'mylvmbackup'@'localhost' IDENTIFIED BY '12345';\r\nGRANT RELOAD, REPLICATION CLIENT ON *.* TO 'mylvmbackup'@'localhost';\r\nGRANT SELECT ON mysql.* TO 'mylvmbackup'@'localhost';\r\n<\/pre>\n<\/blockquote>\n<p>In the <strong>mylvmbackup.conf<\/strong> file, the correlating rows are:<\/p>\n<blockquote>\n<pre>[mysql]\r\nuser=mylvmbackup\r\npassword=12345\r\nhost=localhost\r\n<\/pre>\n<\/blockquote>\n<h4>Filesystem copy<\/h4>\n<p>By default, <em>mylvmbackup<\/em> creates a <strong>.tar.gz<\/strong> compressed backup file of your data. This is good if the reason you&#8217;re running <em>mylvmbackup<\/em> 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&#8217;t really want compressed data: you want the data extracted on the replication server, just as it is on the original host.<\/p>\n<p><em>mylvmbackup<\/em> supports backing up the files using <em>rsync<\/em>.<\/p>\n<p>To copy MySQL data to a remote host, configure the following in the mylvmbackup.conf file:<\/p>\n<blockquote>\n<pre>[fs]\r\nbackupdir=shlomi@backuphost:\/data\/backup\/mysql\r\n[misc]\r\nbackuptype=rsync\r\n<\/pre>\n<\/blockquote>\n<p>You may be prompted to enter password, unless you have the user&#8217;s public key stored on the remote host.<\/p>\n<p>Normally, <em>rsync<\/em> is considered as <strong>r<\/strong>emote-<strong>sync<\/strong>, but it also works on local file systems. If you have a remote directory mounted on your file system (e.g. with <em>nfs<\/em>), you can use the fact that <em>rsync<\/em> works just as well with local file systems:<\/p>\n<blockquote>\n<pre>[fs]\r\nbackupdir=\/mnt\/backup\/mysql\r\n[misc]\r\nbackuptype=rsync\r\n<\/pre>\n<\/blockquote>\n<p>Voila! Your backup is complete.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#8217;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 [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[7,119,57,50,16],"class_list":["post-2839","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-backup","tag-linux","tag-open-source","tag-scripts","tag-security"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-JN","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2839","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=2839"}],"version-history":[{"count":22,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2839\/revisions"}],"predecessor-version":[{"id":2862,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2839\/revisions\/2862"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=2839"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=2839"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=2839"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}