Archives pour l'étiquette s3cmd

Sauvegarder ces bases de données Mysql sur un espace Amazon S3

Un exemple simplisme de script automatisant la sauvegarde des bases de donnée mysql de votre serveur sur un stockage amazon S3.

Ce script nécessite d’avoir un compte sur Amazon Simple Storage Service. Allez sur la page Amazon S3 homepage, cliqué sur le bouton « Sign up for web service » et inscrivez vous. Vous devrez fournir votre carte de crédit et en autorisé le débit en fonction de l’utilisation que vous ferez du service. Suite à cette inscription vous posséderez une clé d’ access et une clé Secrete vous permettant d’accéder au service.

Vous devez également avoir installé le paquet debian s3cmd

apt-get <span style="color: #c20cb9; font-weight: bold;">install</span> s3cmd

Votre clé d’ access et votre clé Secrete à disposition lancer la commande suivante pour configurer vos outils s3cmd et leur donner accès à Amazon

s3cmd --configure

Enfin le script, des plus simple, non ?

<span style="color: #808080; font-style: italic;">#!/bin/bash</span> <span style="color: #808080; font-style: italic;">#</span> &nbsp; <span style="color: #808080; font-style: italic;"># Mot de passe Mysql</span> <span style="color: #007800;">$MDP</span>=<span style="color: #ff0000;">&quot;Mots de passe Mysql&quot;</span> &nbsp; <span style="color: #808080; font-style: italic;"># ex : 1.2010-03-01_14h48</span> <span style="color: #007800;">DayNameDump=</span>`<span style="color: #c20cb9; font-weight: bold;">date</span> +<span style="color: #000000; font-weight: bold;">%</span><span style="color: #c20cb9; font-weight: bold;">w</span>.<span style="color: #000000; font-weight: bold;">%</span>Y-<span style="color: #000000; font-weight: bold;">%</span>m-<span style="color: #000000; font-weight: bold;">%</span>d_<span style="color: #000000; font-weight: bold;">%</span>Hh<span style="color: #000000; font-weight: bold;">%</span>M`; &nbsp; <span style="color: #808080; font-style: italic;"># Monday = 1 etc..</span> <span style="color: #007800;">DayInWeek=</span>`<span style="color: #c20cb9; font-weight: bold;">date</span> +<span style="color: #000000; font-weight: bold;">%</span><span style="color: #c20cb9; font-weight: bold;">w</span>`; &nbsp; <span style="color: #808080; font-style: italic;"># Pour Chaque DB de mysql sauf Database|information_schema|mysql</span> <span style="color: #000000; font-weight: bold;">for</span> I <span style="color: #000000; font-weight: bold;">in</span> `mysql -p<span style="color: #007800;">$MDP</span> -B -e <span style="color: #ff0000;">&quot;show databases;&quot;</span> <span style="color: #000000; font-weight: bold;">|</span> <span style="color: #c20cb9; font-weight: bold;">egrep</span> -v <span style="color: #ff0000;">'(Database|information_schema|mysql)'</span>`;  <span style="color: #000000; font-weight: bold;">do</span>    <span style="color: #808080; font-style: italic;"># Création du répertoire de backup local pour chaque base</span>    <span style="color: #007800;">MSG=</span>`<span style="color: #c20cb9; font-weight: bold;">mkdir</span> -p <span style="color: #000000; font-weight: bold;">/</span>vars<span style="color: #000000; font-weight: bold;">/</span>backups<span style="color: #000000; font-weight: bold;">/</span><span style="color: #007800;">$I</span> <span style="color: #000000;">2</span><span style="color: #000000; font-weight: bold;">&gt;&amp;</span><span style="color: #000000;">1</span>`;    logger -t <span style="color: #7a0874; font-weight: bold;">&#91;</span>SQLBACKUP<span style="color: #7a0874; font-weight: bold;">&#93;</span> Create Backup Directory: <span style="color: #007800;">$MSG</span>; &nbsp;    <span style="color: #808080; font-style: italic;"># Efface le backup de même jour de semaine éxistant déjà</span>    <span style="color: #808080; font-style: italic;"># On ne concerve donc que 7 jours glissant de backup en local</span>    <span style="color: #007800;">MSG=</span>`<span style="color: #c20cb9; font-weight: bold;">rm</span> <span style="color: #000000; font-weight: bold;">/</span>var<span style="color: #000000; font-weight: bold;">/</span>backups<span style="color: #000000; font-weight: bold;">/</span><span style="color: #007800;">$I</span><span style="color: #000000; font-weight: bold;">/</span><span style="color: #007800;">$HOSTNAME</span>.<span style="color: #007800;">$DayInWeek</span>.<span style="color: #000000; font-weight: bold;">*</span> <span style="color: #000000;">2</span><span style="color: #000000; font-weight: bold;">&gt;&amp;</span><span style="color: #000000;">1</span>`;    logger -t <span style="color: #7a0874; font-weight: bold;">&#91;</span>SQLBACKUP<span style="color: #7a0874; font-weight: bold;">&#93;</span> Clean Old Backup: <span style="color: #007800;">$MSG</span>; &nbsp;    <span style="color: #808080; font-style: italic;"># Dump des bases présent sauf les exeptions, ceci en mode transactionnel, </span>    <span style="color: #808080; font-style: italic;"># gzip du fichier sql au taux de compression le plus fort</span>    <span style="color: #007800;">MSG=</span>`mysqldump --single-transaction -p<span style="color: #007800;">$MDP</span> -u root <span style="color: #007800;">$I</span> <span style="color: #000000; font-weight: bold;">|</span> <span style="color: #c20cb9; font-weight: bold;">gzip</span> -cq9 <span style="color: #000000; font-weight: bold;">&gt;</span> \  <span style="color: #000000; font-weight: bold;">/</span>var<span style="color: #000000; font-weight: bold;">/</span>backups<span style="color: #000000; font-weight: bold;">/</span><span style="color: #007800;">$I</span><span style="color: #000000; font-weight: bold;">/</span><span style="color: #007800;">$HOSTNAME</span>.<span style="color: #007800;">$DayNameDump</span>.sql.gz <span style="color: #000000;">2</span><span style="color: #000000; font-weight: bold;">&gt;&amp;</span><span style="color: #000000;">1</span>`;    logger -t <span style="color: #7a0874; font-weight: bold;">&#91;</span>SQLBACKUP<span style="color: #7a0874; font-weight: bold;">&#93;</span> Mysqldump: <span style="color: #007800;">$MSG</span>; &nbsp;    <span style="color: #808080; font-style: italic;"># Copie dans le bucket courant</span>    <span style="color: #007800;">MSG=</span>`s3cmd put <span style="color: #000000; font-weight: bold;">/</span>var<span style="color: #000000; font-weight: bold;">/</span>backups<span style="color: #000000; font-weight: bold;">/</span><span style="color: #007800;">$I</span><span style="color: #000000; font-weight: bold;">/</span><span style="color: #007800;">$HOSTNAME</span>.<span style="color: #007800;">$DayNameDump</span>.sql.gz \  s3:<span style="color: #000000; font-weight: bold;">//</span>backup.perso.bucket.<span style="color: #000000; font-weight: bold;">/</span><span style="color: #007800;">$I</span><span style="color: #000000; font-weight: bold;">/</span><span style="color: #007800;">$HOSTNAME</span>.<span style="color: #007800;">$DayNameDump</span>.sql.gz <span style="color: #000000;">2</span><span style="color: #000000; font-weight: bold;">&gt;&amp;</span><span style="color: #000000;">1</span>`;    logger -t <span style="color: #7a0874; font-weight: bold;">&#91;</span>SQLBACKUP<span style="color: #7a0874; font-weight: bold;">&#93;</span> Put to S3 backup.perso.bucket: <span style="color: #007800;">$MSG</span>; &nbsp;   <span style="color: #000000; font-weight: bold;">done</span>;