MySQL – Les mémos d'un admin système

[Bash] Script pour dumper des bases MySQL

Johan — Mon, 24 Jul 2017 09:07:30 +0000

#!/bin/bash
#
# MySQL_DB_Dump
#
# Auteur      : Johan
# Contact     : johan@slashroot.fr
# Date        : 13/07/2011
# Version     : 1.0
# Description : Dump une base donnee MySQL
# Commentaire : Le fichier de dump peut etre envoye par email
# Requis      : Besoin de mutt si l'option -m est selectionnee
#

#
# Usage
#
usage(){
    echo ""
    echo "Utilisation : mysql_db_dump -s server_name -u mysql_user -p file/password -b database1,database2... -d save_folder [-m adresse@domain.com]"
    echo ""
    echo "OPTIONS :"
    echo "   -h     Affiche l'aide"
    echo "   -u     Utilisateur MySQL a utilise pour le dump"
    echo "   -s     Serveur contenant la base de données"
    echo "   -p     Type d'authentification a utiliser (prompt du password ou fichier ~/.my.cnf)"
    echo "   -b     Specifie la ou les bases a dumper"
    echo "   -d     Dossier a utiliser pour stocker les dumps"
    echo "   -m     Envoi le fichier de dump par email"
    echo ""
    echo "EXEMPLE :"
    echo "   - Dump des bases db1 et db2 avec une authentification par fichier et l'envoi des fichiers par email"
    echo "          mysql_db_dump -s localhost -u bob  -p file -b db1,db2 -m dba@slashroot.fr"
    echo ""
}

#
# getopts
#

while getopts "s:u:p:b:d:m:h" opt; do
  case $opt in
    s)
      serveur=$OPTARG
      ;;
    u)
      user=$OPTARG
      ;;
    p)
      auth=$OPTARG
      ;;
    b)
      bases=$OPTARG
      ;;
    m)
      dest=$OPTARG
      if [ ! -f /bin/mutt ] ; then
        echo "ERREUR - L'utilitaire mutt est introubable"
        exit 1
      fi
      ;;
    d)
      folder=$OPTARG
      ;;
    h)
      usage
      exit 0
      ;;
    \?)
      usage
      exit 1
      ;;
    
      usage
      exit 1
      ;;
  esac
done

#
# Variables et verifications
#

# Verification des parametres choisis
if [[ $# -eq 0 || -z $serveur || -z $user || -z $auth || -z $bases || -z $folder ]] ; then
        usage
        exit 1
fi

# Verification du dossier de sauvegarde
if [ ! -d $folder ]  ; then
    echo "ERREUR - Le dossier cense accueillir les dumps est introuvable"
    exit 1
fi

# Verification de l'authentification choisi
if [ $auth == "file" ] ; then
        if [ ! -f ~/.my.cnf ] ; then
        	echo " ERREUR - Le fichier .my.cnf est introuvable"
#	        exit 1
	else
		if [ $(stat -c %a ~/.my.cnf) != 600 ] ; then
			echo "Warning - Votre fichier ~/.my.cnf n'a pas les droits recommandes"
		fi
        fi
elif [ ! $auth == "password" ] ; then
	usage
	exit 1
fi

# Date du jour
date=`date +%m-%d-%Y`

host=`hostname`
res="\n"

#
# Au boulot
#

# Pour chaque base
for base in $(echo $bases | sed "s/,/ /g") ; do
    dump="$folder/dump-$base-$date.sql"
    if [[ $auth == "file" ]] ; then
        mysqldump -u $user $base > $dump
    else
        mysqldump -u $user -p $base > $dump
    fi
    gzip $dump
    if [ -n $dest ] ; then
        echo "" | mutt -s "[Save-SQL] Dump base $base du $date" -a ""$dump".gz" -- $dest   
    fi
done

[Bash] Récupérer la taille des bases MySQL

Johan — Wed, 19 Jul 2017 09:09:27 +0000

#!/bin/bash
#
# MySQL_DB_Sizes
#
# Auteur      : Johan 
# Contact     : johan@slashroot.fr
# Date        : 13/07/2011
# Version     : 1.0
# Description : Recupère la taille de chaque base de données et envoi les résultats par email
# Commentaire : L'utilisateur doit avoir acces en lecture a toutes les bases dont on souhaite connaitre la taille

# Reminder for a MySQL read only user
# CREATE USER 'ro-user'@'localhost' IDENTIFIED BY 'MySQL_Ro_User_Password';
# GRANT SELECT ON database1.* TO 'ro-user'@'localhost';
# GRANT SELECT ON database2.* TO 'ro-user'@'localhost';
# ...

#
# Usage
#
usage(){
    echo ""
    echo "Utilisation : mysql_db_sizes -s server_name -u mysql_user -p file/password [-b database1,database2...] [-m adresse@domain.com]"
    echo ""
    echo "OPTIONS :"
    echo "   -h     Affiche l'aide"
    echo "   -u     Utilisateur MySQL a utilise pour la requete SQL"
    echo "   -s     Serveur contenant la base de données"
    echo "   -p     Type d'authentification a utiliser (prompt du password ou fichier ~/.my.cnf)"
    echo "   -b     Specifie les bases a afficher (par defaut toutes celles accessibles"
    echo "   -m     Envoi le resultat par email au lieu de l'afficher dans le terminal"
    echo ""
    echo "EXEMPLES :"
    echo "   1 - Affichage de la taille de toutes les bases avec une authentification par mot de passe"
    echo "          mysql_db_sizes -s localhost-u bob  -p password"
    echo "   2 - Envoi de la taille des bases db1 et db2 par email avec une authentification par fichier"
    echo "          mysql_db_sizes -s localhost -u bob -p file -b db1,db2 -m bob@admin.fr"
    echo ""
}

#
# getopts
#

while getopts "s:u:p:b:m:h" opt; do
  case $opt in
    s)
      serveur=$OPTARG
      ;;
    u)
      user=$OPTARG
      ;;
    p)
      auth=$OPTARG
      ;;
    b)
      bases=$OPTARG
      ;;
    m)
      dest=$OPTARG
      ;;
    h)
      usage
      exit 0
      ;;
    \?)
      usage
      exit 1
      ;;
    
      usage
      exit 1
      ;;
  esac
done

#
# Variables et verifications
#

# Verification des parametres choisis
if [[ $# -eq 0 || -z $serveur || -z $user || -z $auth ]] ; then
        usage
        exit 1
fi

# Verification de l'authentification choisi
if [ $auth == "file" ] ; then
        if [ ! -f ~/.my.cnf ] ; then
        	echo " ERREUR - Le fichier .my.cnf est introuvable"
	        exit 1
	else
		if [ $(stat -c %a ~/.my.cnf) != 600 ] ; then
			echo "Warning - Votre fichier ~/.my.cnf n'a pas les droits recommandes"
		fi
        fi
elif [ ! $auth == "password" ] ; then
	usage
	exit 1
fi

host=`hostname`
res="\n"

# Fichier de sortie temporaire
tmp="/tmp/mysql_db_sized.tmp"

#
# Au boulot
#

# On extrait dans un fichier toutes les bases du serveurs
if [ $auth == "file" ] ; then
	mysql -h $serveur -u $user -B --exec="SELECT table_schema, round(sum(data_length+index_length)/1024/1024,4) AS 'Size (MB)' FROM information_schema.tables GROUP BY table_schema;" > $tmp
else
	mysql -h $serveur -u $user -p -B --exec="SELECT table_schema, round(sum(data_length+index_length)/1024/1024,4) AS 'Size (MB)' FROM information_schema.tables GROUP BY table_schema;" > $tmp
fi

# On supprime la premières ligne du fichier comprenant les titres ainsi que la base schema 
sed -i '1d' $tmp
sed -i '/information_schema/d' $tmp 

# Pour chaque base
while read line ; do
    # On recupere son nom et sa taille
    nom=`echo $line | cut -f1 -d ' '`
    taille=`echo $line | cut -f2 -d ' '`
    # On l'ajoute au resultat si on veut toutes les bases ou si elle fait partie des bases choisies
    if [ -z $bases ] && ( echo $bases | grep -wq $nom ) ; then
	res+="$nom : $taille Mo\n"
    elif [ -n $bases ] && ( echo $bases | grep -wq $nom ) ; then
	res+="$nom : $taille Mo\n"
    fi
done < $tmp

# On redige et on envoi l'email si demande
if [ ! -z $dest ] ; then
    corps="Bonjour,\n\nVoici les informations concernant la taille de ma/mes base(s) de données :\n\n$res\n\nnCordialement,\n\n$host"
    echo -e $corps | mail -s "Infos : Taille base MySQL" $dest
# Sinon on affiche le resultat dans le terminal
else
    echo -e $res
fi

# On supprime le fichier de sortie temporaire
rm -f $tmp

[Perl] Connexion et requête MySQL

Johan — Fri, 16 Mar 2012 10:09:02 +0000

Marche à suivre pour se connecter à une base MySQL, y effectuer une requête de type SELECT et récupérer le(s) résultat(s).

use DBI();

$database="nom_base";
$hostname="hostname";
$login = "login";
$mdp = "password";
$dsn = "DBI:mysql:database=$database;host=$hostname";
$dbh = DBI->connect($dsn, $login, $mdp) or die "Echec connexion";

$requete = "REQUETE SQL";

$sth = $dbh->prepare($requete);
$sth->execute();

while(my @row = $sth->fetchrow_array)
{
		$var1=$row['0'];
		$var2=$row['1'];
}

$sth -> finish;
$dbh -> disconnect;

[MySQL] Activer les logs

Johan — Fri, 02 Mar 2012 15:51:18 +0000

Ajouter au fichier de conf de MySQL (/etc/mysql/my.cnf sur Debian) :

log=/var/log/mysql/mysql.log

Puis après un petit redemarrage de MySQL le fichier contiendra toutes les opérations effectuées sur la base (cela peut s’avérer pratique pour récupérer des requêtes SQL créées par des interfaces web ou autres formulaires par exemple)

[MySQL] Taille des bases

Johan — Thu, 08 Dec 2011 10:54:25 +0000

Taille de toutes les bases en Mo:

SELECT table_schema As "Base", round(sum(data_length+index_length)/1024/1024,4) AS "Taille (Mo)"
FROM information_schema.tables
GROUP BY table_schema;

Taille de chaque table d’une base :

SELECT table_name AS "Tables", round(((data_length + index_length) / 1024 / 1024), 2) AS "Taille (Mo)"
FROM information_schema.tables
WHERE table_schema =  'base';