{"id":87,"date":"2008-07-23T00:00:00","date_gmt":"2008-07-23T00:00:00","guid":{"rendered":"http:\/\/netexpertise\/fr\/?p=87"},"modified":"2021-10-23T21:51:11","modified_gmt":"2021-10-23T20:51:11","slug":"sauvegarde-ultra-rapide-lvm","status":"publish","type":"post","link":"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/sauvegarde-ultra-rapide-lvm.html","title":{"rendered":"Sauvegarde Mysql Ultra-rapide avec LVM"},"content":{"rendered":"\n<p>C&rsquo;est toujours une bonne id\u00e9e de respecter les deux points suivants lors de la sauvegarde de bases de donn\u00e9es. La backup doit \u00eatre:<br>&#8211; consistante<br>&#8211; rapide<br>\u00a0<br>La consistance est r\u00e9alis\u00e9e facilement en mettant un read lock sur toutes les tables avant tout. Cependant, ceci n&rsquo;est pas toujours mis en application, et m\u00eanera s\u00fbrement \u00e0 un probl\u00e8me d&rsquo;int\u00e9grit\u00e9.<br>Une fois le verrou \u00e9tabli, la sauvegarde doit \u00eatre r\u00e9alis\u00e9e le plus vite possible, toutes les instructions d&rsquo;\u00e9critures \u00e9tant mises en attente dans la queue par le lock.<\/p>\n\n\n\n<h3><br>Mysql et les Snapshots LVM<\/h3>\n\n\n\n<p>Voici les principales m\u00e9thodes pour r\u00e9aliser une sauvegarde:<\/p>\n\n\n\n<ul><li>mysqldump, fourni avec le package <a href=\"\/fr\/category\/database\/mysql\">Mysql<\/a>, est assez rapide pour les bases de petites tailles et c&rsquo;est \u00e0 peu pr\u00e8s tout! On peut l&#8217;employer aussi sur des serveurs de r\u00e9plication, ou si vous pouvez vous permettre de suspendre les op\u00e9rations d&rsquo;\u00e9critures pendant un temps assez long<\/li><li>Un simple tar sur le r\u00e9pertoire de donn\u00e9es, est plus rapide mais peut rester lent, surtout si vous effectuer une backup sur le r\u00e9seau<\/li><li>Un simple tar, coupl\u00e9 \u00e0 un volume manager supportant les snapshots, comme Veritas ou ZFS. La solution de sauvegarde Mysql qui vient \u00e0 l&rsquo;esprit est Linux Volume Manager (<a href=\"http:\/\/www.linux.com\/feature\/118645\" target=\"_blank\" rel=\"noreferrer noopener\">LVM)<\/a>, maintenant fourni en standard avec la plupart des distributions Linux. Cette option verrouille les tables quelques secondes seulement si la backup est effectu\u00e9e sur un snapshot!<\/li><\/ul>\n\n\n\n<p>D&rsquo;autres m\u00e9thodes existent bien \u00e9videmment mais ne seront pas abord\u00e9es ici.<\/p>\n\n\n\n<h3><br>Avant de Lancer la Sauvegarde<\/h3>\n\n\n\n<p>Tout ce dont vous avez besoin est d&rsquo;avoir le r\u00e9pertoire de donn\u00e9es Mysql h\u00e9berg\u00e9 sur une partition LVM. Ainsi que 10% d&rsquo;espace libre sur son volume group pour cr\u00e9er le snapshot. Les param\u00e8tres sont similaires au client Mysql en ligne de commandes, c&rsquo;est-\u00e0-dire les m\u00eames options \u00e0 sp\u00e9cifier pour le user et le mot de passe pour une utilisation facile.<\/p>\n\n\n\n<p>Le script se connecte au serveur Mysql local, met un lock en lecture. Il cr\u00e9e ensuite un snapshot de la partition LVM, et lib\u00e8re le verrou. Le r\u00e9pertoire de donn\u00e9es est alors sauvegard\u00e9 avec tar et copi\u00e9 vers le r\u00e9pertoire de destination de votre choix, avant que le snapshot ne soit d\u00e9truit.<br>Il est pr\u00e9f\u00e9rable de l&rsquo;ex\u00e9cuter sous le compte root. Si ce n&rsquo;est pas possible (ou non d\u00e9sir\u00e9), vous pouvez utiliser sudo pour les commandes mount et lv. Assurez-vous que les fichiers Mysql soient accessibles en lecture.<br>Ce script vous permettra de faire une sauvegarde de vos bases Mysql en un clin d&rsquo;oeil gr\u00e2ce aux snapshots LVM.<\/p>\n\n\n\n<h3><br>Script de Sauvegarde Mysql<\/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>C&rsquo;est toujours une bonne id\u00e9e de respecter les deux points suivants lors de la sauvegarde de bases de donn\u00e9es. La backup doit \u00eatre:&#8211; consistante&#8211; rapide\u00a0La consistance est r\u00e9alis\u00e9e facilement en mettant un read lock sur toutes les tables avant tout. Cependant, ceci n&rsquo;est pas toujours mis en application, et m\u00eanera s\u00fbrement \u00e0 un probl\u00e8me d&rsquo;int\u00e9grit\u00e9.Une [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false},"categories":[4,360],"tags":[126,407,125,404,133],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v19.13 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Netexpertise - Sauvegarde Mysql Ultra-rapide avec LVM<\/title>\n<meta name=\"description\" content=\"Sauvegardez vos bases Mysql en un clin d&#039;oeil gr\u00e2ce aux snapshots LVM et un lock tr\u00e8s court\" \/>\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\/fr\/database\/mysql\/sauvegarde-ultra-rapide-lvm.html\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Netexpertise - Sauvegarde Mysql Ultra-rapide avec LVM\" \/>\n<meta property=\"og:description\" content=\"Sauvegardez vos bases Mysql en un clin d&#039;oeil gr\u00e2ce aux snapshots LVM et un lock tr\u00e8s court\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/sauvegarde-ultra-rapide-lvm.html\" \/>\n<meta property=\"og:site_name\" content=\"Netexpertise\" \/>\n<meta property=\"article:published_time\" content=\"2008-07-23T00:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-10-23T20:51:11+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\/fr\/database\/mysql\/sauvegarde-ultra-rapide-lvm.html\",\"url\":\"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/sauvegarde-ultra-rapide-lvm.html\",\"name\":\"Netexpertise - Sauvegarde Mysql Ultra-rapide avec LVM\",\"isPartOf\":{\"@id\":\"http:\/\/www.netexpertise.eu\/fr\/#website\"},\"datePublished\":\"2008-07-23T00:00:00+00:00\",\"dateModified\":\"2021-10-23T20:51:11+00:00\",\"author\":{\"@id\":\"http:\/\/www.netexpertise.eu\/fr\/#\/schema\/person\/e398f0307e2b167f6b884c4953be2632\"},\"description\":\"Sauvegardez vos bases Mysql en un clin d'oeil gr\u00e2ce aux snapshots LVM et un lock tr\u00e8s court\",\"breadcrumb\":{\"@id\":\"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/sauvegarde-ultra-rapide-lvm.html#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/sauvegarde-ultra-rapide-lvm.html\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/sauvegarde-ultra-rapide-lvm.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"http:\/\/www.netexpertise.eu\/fr\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Sauvegarde Mysql Ultra-rapide avec LVM\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/www.netexpertise.eu\/fr\/#website\",\"url\":\"http:\/\/www.netexpertise.eu\/fr\/\",\"name\":\"Netexpertise\",\"description\":\"Syst\u00e8mes \/ R\u00e9seaux \/ DevOps\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/www.netexpertise.eu\/fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Person\",\"@id\":\"http:\/\/www.netexpertise.eu\/fr\/#\/schema\/person\/e398f0307e2b167f6b884c4953be2632\",\"name\":\"dave\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"http:\/\/www.netexpertise.eu\/fr\/#\/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 - Sauvegarde Mysql Ultra-rapide avec LVM","description":"Sauvegardez vos bases Mysql en un clin d'oeil gr\u00e2ce aux snapshots LVM et un lock tr\u00e8s court","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\/fr\/database\/mysql\/sauvegarde-ultra-rapide-lvm.html","og_locale":"fr_FR","og_type":"article","og_title":"Netexpertise - Sauvegarde Mysql Ultra-rapide avec LVM","og_description":"Sauvegardez vos bases Mysql en un clin d'oeil gr\u00e2ce aux snapshots LVM et un lock tr\u00e8s court","og_url":"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/sauvegarde-ultra-rapide-lvm.html","og_site_name":"Netexpertise","article_published_time":"2008-07-23T00:00:00+00:00","article_modified_time":"2021-10-23T20:51:11+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\/fr\/database\/mysql\/sauvegarde-ultra-rapide-lvm.html","url":"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/sauvegarde-ultra-rapide-lvm.html","name":"Netexpertise - Sauvegarde Mysql Ultra-rapide avec LVM","isPartOf":{"@id":"http:\/\/www.netexpertise.eu\/fr\/#website"},"datePublished":"2008-07-23T00:00:00+00:00","dateModified":"2021-10-23T20:51:11+00:00","author":{"@id":"http:\/\/www.netexpertise.eu\/fr\/#\/schema\/person\/e398f0307e2b167f6b884c4953be2632"},"description":"Sauvegardez vos bases Mysql en un clin d'oeil gr\u00e2ce aux snapshots LVM et un lock tr\u00e8s court","breadcrumb":{"@id":"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/sauvegarde-ultra-rapide-lvm.html#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/sauvegarde-ultra-rapide-lvm.html"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/sauvegarde-ultra-rapide-lvm.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"http:\/\/www.netexpertise.eu\/fr"},{"@type":"ListItem","position":2,"name":"Sauvegarde Mysql Ultra-rapide avec LVM"}]},{"@type":"WebSite","@id":"http:\/\/www.netexpertise.eu\/fr\/#website","url":"http:\/\/www.netexpertise.eu\/fr\/","name":"Netexpertise","description":"Syst\u00e8mes \/ R\u00e9seaux \/ DevOps","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.netexpertise.eu\/fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Person","@id":"http:\/\/www.netexpertise.eu\/fr\/#\/schema\/person\/e398f0307e2b167f6b884c4953be2632","name":"dave","image":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"http:\/\/www.netexpertise.eu\/fr\/#\/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\/fr\/wp-json\/wp\/v2\/posts\/87"}],"collection":[{"href":"http:\/\/www.netexpertise.eu\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.netexpertise.eu\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.netexpertise.eu\/fr\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.netexpertise.eu\/fr\/wp-json\/wp\/v2\/comments?post=87"}],"version-history":[{"count":0,"href":"http:\/\/www.netexpertise.eu\/fr\/wp-json\/wp\/v2\/posts\/87\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.netexpertise.eu\/fr\/wp-json\/wp\/v2\/media?parent=87"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.netexpertise.eu\/fr\/wp-json\/wp\/v2\/categories?post=87"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.netexpertise.eu\/fr\/wp-json\/wp\/v2\/tags?post=87"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}