{"id":84,"date":"2008-07-26T15:17:23","date_gmt":"2008-07-26T13:17:23","guid":{"rendered":"http:\/\/netexpertise\/en\/?p=84"},"modified":"2021-10-23T22:43:07","modified_gmt":"2021-10-23T20:43:07","slug":"mysql-ultra-fast-backup-with-lvm","status":"publish","type":"post","link":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-ultra-fast-backup-with-lvm.html","title":{"rendered":"Mysql Ultra-Fast Backup with LVM"},"content":{"rendered":"\n<p>It is generally good practice to respect the two following rules when backing up databases. The backup has to be:<\/p>\n\n\n\n<ul><li>consistent<\/li><li>fast<\/li><\/ul>\n\n\n\n<p>Consistency is easily achieved putting a read lock on all tables beforehand. However, this isn&#8217;t always applied, and WILL definitely lead to a database integrity problem when restoring.<br>Once a lock has been set on the database, the backup has to be as quick as possible, all write instructions being held in the queue by the lock.<br>\u00a0<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Mysql and LVM Snapshots<\/h3>\n\n\n\n<p>Here are some usual ways to run a backup:<\/p>\n\n\n\n<ul><li>mysqldump, provided within <a href=\"\/en\/category\/database\/mysql\">Mysql<\/a> package, is fast enough for very small databases and that&#8217;s about it! It is safe to run on replication servers, or if you can afford to suspend write operations for a long time<\/li><li>A simple tar of the data directory, is faster but may remain slow, especially if you run the backup over the network<\/li><li>A simple tar, coupled with a volume manager that supports snapshots, like Veritas or ZFS. The first option that comes to mind is Linux Volume Manager (<a href=\"http:\/\/www.linux.com\/feature\/118645\" target=\"_blank\" rel=\"noreferrer noopener\">LVM<\/a>), now provided in standard with most Linux distributions. This option locks and unlocks tables within seconds if the backup runs on a snapshot!<\/li><\/ul>\n\n\n\n<p>Other methods exist indeed but we won&#8217;t deal with them in this article.<br>&nbsp;<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Before You Start the Backup<\/h3>\n\n\n\n<p>All you need is have mysql data directory on a LVM partition and 10% of free space on the volume group to create the snapshot. Parameters are similar to the mysql client command line, ie same options to specify the user and password for easy usage.<\/p>\n\n\n\n<p>The script connects to your local mysql server and adds a read lock. It then creates a snapshot of the LVM partition, and releases the lock. The data directory is then archived with tar and put in the destination folder of your choice. We destroy the the snapshot after the backup is over.<br>It should also be run under the root user account. If not, provide sudo to the mount and lv commands, and make sure the user has read access to the Mysql files.<br>\u00a0<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Mysql Backup Script<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"bash\" class=\"language-bash\">#!\/bin\/bash\n\nuser=$LOGNAME\npassword=\ndatadir=\ntmpmountpoint=\"\/mnt\"\ndstdir=\"\/tmp\"\n\nusage () {\n  echo \"Usage: $0 [OPTION]\"\n  echo \"-d, --dest=name       Destination directory. Default is \/tmp\"\n  echo \"-h, --help            Display this help and exit.\"\n  echo \"-p, --password[=name] Password to use when connecting to server. If password is\"\n  echo \"                      not given it's asked from the tty.\"\n  echo \"-t                    Temporary mount point for the snapshot. Default is \/mnt.\"\n  echo \"-u, --user=name       User for login if not current user\"\n  exit 1\n}\n\nuntil [ -z \"$1\" ]; do\n  case \"$1\" in\n    -u)\n      [ -z \"$2\" ] &amp;&amp; usage\n      user=\"$2\"\n      shift\n      ;;\n    --user=*)\n      user=`echo $1|cut -f 2 -d '='`\n      ;;\n    -p*)\n      password=`echo $1|sed -e s\/\"^-p\"\/\/g`\n      ;;\n    --password)\n      echo -n \"Enter password: \"\n      stty -echo\n      read password\n      stty echo\n      ;;\n    --password=*)\n      password=`echo $1|cut -f 2 -d '='`\n      ;;\n    -d)\n      [ -z \"$2\" ] &amp;&amp; usage\n      dstdir=\"$2\"\n      shift\n      ;;\n    --dest=*)\n      dstdir=`echo $1|cut -f 2 -d '='`\n      ;;\n    -t)\n      [ -z \"$2\" ] &amp;&amp; usage\n      tmpmountpoint=\"$2\"\n      shift\n      ;;\n    * )\n    usage\n    ;;\n  esac\n  shift\ndone\n\n[ -z $password ] &amp;&amp; echo \"Empty password!\" &amp;&amp; usage\n[ ! -d $dstdir ] &amp;&amp; echo \"$dstdir does not exist\" &amp;&amp; exit 1\n\n# Check if temp mount point not used\n[ `mount | grep \"$tmpmountpoint\" | wc -l` -ne 0 ] &amp;&amp; exit 1\n\n# Get Mysql data directory\ndatadir=`mysql -u $user -p$password -Ns -e \"show global variables like 'datadir'\"|cut -f 2|sed -e s\/\"\\\/$\"\/\/g`\n[ -z \"$datadir\" ] &amp;&amp; exit 1\n\n# Get snap name and size\nvg=`mount | grep $datadir | cut -d ' ' -f 1 | cut -d '\/' -f 4 | cut -d '-' -f 1`\nlv=`mount | grep $datadir | cut -d ' ' -f 1 | cut -d '\/' -f 4 | cut -d '-' -f 2`\n[ -z $lv ] &amp;&amp; echo \"Mysql data dir must be mounted on a LVM partition!\" &amp;&amp; exit 1\nsnap=$lv\"snap\"\nsnapsize=$(expr `df -m $datadir | tail -1 | tr -s ' ' | cut -d ' ' -f 2` \/ 10)M\n\n# Backup\necho \"Locking databases\"\nmysql -u$user -p$password &lt;&lt; EOF\nFLUSH TABLES WITH READ LOCK;\nsystem lvcreate --snapshot -n $snap -L$snapsize \/dev\/$vg\/$lv;\nUNLOCK TABLES;\nquit\nEOF\necho \"Databases unlocked\"\n\necho \"Backing up databases\"\nmount \/dev\/$vg\/$snap $tmpmountpoint\ncd $tmpmountpoint\ntar cfz $dstdir\/mysql.tar.gz *\ncd\numount $tmpmountpoint\nlvremove -f \/dev\/$vg\/$snap\necho \"Databases backed up in $dstdir\"\n\nexit 0\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>It is generally good practice to respect the two following rules when backing up databases. The backup has to be: consistent fast Consistency is easily achieved putting a read lock on all tables beforehand. However, this isn&#8217;t always applied, and WILL definitely lead to a database integrity problem when restoring.Once a lock has been set [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[319,4],"tags":[58,386,57,385],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.8.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Netexpertise - Mysql Ultra-Fast Backup with LVM<\/title>\n<meta name=\"description\" content=\"Backup Mysql databases with a really short lock using LVM snapshots\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-ultra-fast-backup-with-lvm.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Netexpertise - Mysql Ultra-Fast Backup with LVM\" \/>\n<meta property=\"og:description\" content=\"Backup Mysql databases with a really short lock using LVM snapshots\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-ultra-fast-backup-with-lvm.html\" \/>\n<meta property=\"og:site_name\" content=\"Netexpertise\" \/>\n<meta property=\"article:published_time\" content=\"2008-07-26T13:17:23+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-10-23T20:43:07+00:00\" \/>\n<meta name=\"author\" content=\"dave\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@netexpertise\" \/>\n<meta name=\"twitter:site\" content=\"@netexpertise\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-ultra-fast-backup-with-lvm.html\",\"url\":\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-ultra-fast-backup-with-lvm.html\",\"name\":\"Netexpertise - Mysql Ultra-Fast Backup with LVM\",\"isPartOf\":{\"@id\":\"https:\/\/www.netexpertise.eu\/en\/#website\"},\"datePublished\":\"2008-07-26T13:17:23+00:00\",\"dateModified\":\"2021-10-23T20:43:07+00:00\",\"author\":{\"@id\":\"https:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa\"},\"description\":\"Backup Mysql databases with a really short lock using LVM snapshots\",\"breadcrumb\":{\"@id\":\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-ultra-fast-backup-with-lvm.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-ultra-fast-backup-with-lvm.html\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-ultra-fast-backup-with-lvm.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.netexpertise.eu\/en\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Mysql Ultra-Fast Backup with LVM\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.netexpertise.eu\/en\/#website\",\"url\":\"https:\/\/www.netexpertise.eu\/en\/\",\"name\":\"Netexpertise\",\"description\":\"Systems \/ Networks \/ DevOps\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.netexpertise.eu\/en\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa\",\"name\":\"dave\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g\",\"contentUrl\":\"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g\",\"caption\":\"dave\"},\"sameAs\":[\"http:\/\/www.netexpertise.eu\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Netexpertise - Mysql Ultra-Fast Backup with LVM","description":"Backup Mysql databases with a really short lock using LVM snapshots","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-ultra-fast-backup-with-lvm.html","og_locale":"en_US","og_type":"article","og_title":"Netexpertise - Mysql Ultra-Fast Backup with LVM","og_description":"Backup Mysql databases with a really short lock using LVM snapshots","og_url":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-ultra-fast-backup-with-lvm.html","og_site_name":"Netexpertise","article_published_time":"2008-07-26T13:17:23+00:00","article_modified_time":"2021-10-23T20:43:07+00:00","author":"dave","twitter_card":"summary_large_image","twitter_creator":"@netexpertise","twitter_site":"@netexpertise","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-ultra-fast-backup-with-lvm.html","url":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-ultra-fast-backup-with-lvm.html","name":"Netexpertise - Mysql Ultra-Fast Backup with LVM","isPartOf":{"@id":"https:\/\/www.netexpertise.eu\/en\/#website"},"datePublished":"2008-07-26T13:17:23+00:00","dateModified":"2021-10-23T20:43:07+00:00","author":{"@id":"https:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa"},"description":"Backup Mysql databases with a really short lock using LVM snapshots","breadcrumb":{"@id":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-ultra-fast-backup-with-lvm.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-ultra-fast-backup-with-lvm.html"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-ultra-fast-backup-with-lvm.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.netexpertise.eu\/en"},{"@type":"ListItem","position":2,"name":"Mysql Ultra-Fast Backup with LVM"}]},{"@type":"WebSite","@id":"https:\/\/www.netexpertise.eu\/en\/#website","url":"https:\/\/www.netexpertise.eu\/en\/","name":"Netexpertise","description":"Systems \/ Networks \/ DevOps","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.netexpertise.eu\/en\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa","name":"dave","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/image\/","url":"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g","contentUrl":"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g","caption":"dave"},"sameAs":["http:\/\/www.netexpertise.eu"]}]}},"_links":{"self":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/posts\/84"}],"collection":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/comments?post=84"}],"version-history":[{"count":0,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/posts\/84\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/media?parent=84"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/categories?post=84"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/tags?post=84"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}