====== Optimisation de base de données ======
TODO
index, requetes

======Java & Base de données======

[[java:base_de_donnees|Java & Base de données]]

====Utilisation d'une DataSource====

[[serveurweb:gestion_de_data_source]]

====== Mysql ======
===Test de connection===
$mysqladmin -h 'hosname' version[variables]

===== Connexion ====
==== Connexion en local ====
<code bash>
mysql -h hote -u utilisateur -p
</code>

<code xlm>
show tables;
show databases;
USE test;
describe tables;
CREATE DATABASE montest;
load data local Infile "....sql" Into Table foo
source nomFichier
</code>

==== Connexion via un tunnel SSH sur Internet ====

Pour ce connecter sur un serveur ou l'acces a MySQL est uniquement en local ou protege par un firewall, l'on peut utiliser un relais SSH

<code bash>
$ ssh -f -N -L port_local_mapping:MySQL_server_URL:port_mysql user@ssh_server
# par exemple
$ ssh -f -N -L 3306:127.0.0.1:3306 test@gwadanina.ssh
</code>


 


===== Deplacer les fichiers de votre serveur MySQL =====

Si l'on veux mettre les fichier de la base de donnees dans un autre repertoire:

<code bash>
$ /etc/init.d/mysql stop
$ mv /var/lib/mysql /home/var/
$ ln -s /home/var/mysql/ /var/lib/mysql
$ chown mysql:mysql /home/var/mysql
$ /etc/init.d/mysql start
</code>

===== Backup de votre serveur MySQL =====

=== Sauvegarde via mysqldump ===

<code bash> 
# Pour exporter toutes les base de donnees vers un dossier
$ mysqldump --all-databases -uroot -p --tab=/tmp  
# Pour exporter toutes les base de donnees avec gain de place
$ mysqldump --all-databases -uroot -p | gzip > $(date +%F-%T)_all-databases.sql.gz
# Pour exporter une seule table, d'une seule base de donnees
$ mysqldump -uroot -p gwadanina_database  --tables gwadanina_table | gzip > $(date +%F-%T)_gwadanina_database.sql.gz
# Pour exporter une seule base de donnees, si vous souhaitez créer un fichier de sauvegarde qui 
# peut reecrire une base existante sans avoir à supprimer l'ancienne base de données manuellement en premier.
$ mysqldump --add-drop-table -uroot -p gwadanina_test| gzip > $(date +%F-%T)_gwadanina_test.sql.gz
# Pour exporter toutes les base de donnees avec gain de place et drop des tables existantes
$ mysqldump --all-databases --add-drop-table -u root -p | gzip > $(date +%F-%T)_all-databases.sql.gz
# Sauvegarde seulement les donnees sans la structure de base de données
$ mysqldump -uroot -p --no-create-info --databases gwadanina_test | gzip > $(date +%F-%T)_structurebackup.sql.gz
# Sauvegarde seulement la structure de base de données sans les donnees
$ mysqldump -uroot -p --no-data --databases gwadanina_test | gzip > $(date +%F-%T)_structurebackup.sql.gz
</code>

=== Restauration d'un fichier .sql ===

<code bash>
$ mysql -u root -p gwadanina_test < 2010-01-25-23:07:57_gwadanina_test.sql

# restoration depuis l'archive
$ gunzip < 2010-01-25-23:07:57_gwadanina_test.sql | mysql -u root -p gwadanina_test

# restoration de plusieurs fichiers sql
$ cat gwadanina_test1.sql gwadanina_test2.sql | mysql -u root -p gwadanina_test

# Pour que le serveur relise les droits.
$ mysqladmin flush-privileges 
</code>

=== Déplacement des données directement entre les bases de données ===

<code bash>
# Déplacement des données directement entre les bases de données
$ mysqldump --all-databases --add-drop-table -u root -p | mysql -u root -p --host=XXX.XXX.XXX

# Déplacement des données directement entre les bases de données via SSH
$ mysqldump --all-databases --add-drop-table -u root -p | ssh user:password@gwadanina.net "mysql -u root -p"

</code>

==== Script de sauvegarde de mysql avec cron====

=== Preparation de l'environnement sur Debian ===

<code bash>
# Creer un compte utilisateur en local
$ useradd --home /home/backupspace --shell /bin/bash backupspace
# Preparation du repertoire de backup en local
$ mkdir -p /home/backupspace/archives/mysqlBackup
$ chown backupspace:backupspace -R /home/backupspace
$ chmod 770 /home/backupspace/archives

# Creer un compte mySQL avec uniquement des droits de lecture sur toute la base de donnees
$ mysql -u root -p -h localhost
mysql> CREATE USER 'mySQLBackupAccount'@'localhost' IDENTIFIED BY 'mySQLBackupPassword';
mysql> GRANT SELECT, INSERT, LOCK TABLES ON *.* TO 'mySQLBackupAccount'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

# creation du repertoire de script
$ su backupspace
$ mkdir -p /home/backupspace/script
# creation du script
$ touch /home/backupspace/script/mySQLBackup.sh
$ chmod u+x /home/backupspace/script/mySQLBackup.sh
</code>

=== Code du script de backup et d'optimisation de la base de donnees ===

<code bash>
$ nano /home/backupspace/script/mySQLBackup.sh
</code>

<code bash>
 
#!/bin/bash
# BACKUP all MySQL databases
#
# List the databases and a user backup
# Indépendamment in a file. Sql then compresses
# Files in a tar.gz
 
# http://gwadanina.net
 
#---------------------- System Parameters --------------------#
# Variables definition
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
MKDIR="$(which mkdir)"
# Date of day
DATE_NOW="$(date +"%Y-%m-%d")"
 
# Location of backup file locally
BACKUP_REPO="/home/backupspace/archives/mysqlBackup"
KEEP_LOCAL_FILE=y
# Number of days to retain a backup in local
DAY_TO_KEEP_BACKUP_LOCAL=90
 
#---------------------- MySQL Parameters --------------------#
#Configuring the connection to the mySQL database
SQL_USER="mySQLBackupAccount"
SQL_USER_PASSWORD="mySQLBackupPassword"
SQL_SERVER_HOST="localhost"
 
# Not to save this Databases list (separated by spaces)
NOT_TO_SAVE=""
 
#---------------------- Mail Parameters --------------------#
 
IS_MAIL_TO_SEND=y
# email addresses to send log extract, separated by a space
EMAILS_ADRESSES="backupspace"
EMAILS_SUBJECT="[MySQL_BACKUP]"$DATE_NOW
 
# --------------------------------------------------------- #
#                       End of Parameters                   #
# --------------------------------------------------------- #
 
 
# Creation of working directory
DEST_WORKING="$BACKUP_REPO/$DATE_NOW/"
[ ! -d $DEST_WORKING ] && $MKDIR -p $DEST_WORKING || :
# Creation of temporary log in working folder
TMP_LOG=$DEST_WORKING"sql_backup_$DATE_NOW.log";
echo "["$(date +%F-%T)"] - Today working directory is : " $DEST_WORKING > $TMP_LOG;
 
 
# Backuping the database
echo "["$(date +%F-%T)"] - Creating the list of all your databases..." >> $TMP_LOG;
DATABASE_TABLE_LISTE="$($MYSQL -u$SQL_USER -h$SQL_SERVER_HOST -p$SQL_USER_PASSWORD -Bse 'show databases')"
 
# If an error occurs in the list of databases
if [ ! $? -eq 0 ] ; then
  echo "["$(date +%F-%T)"] - An error occurred while listing databases " >> $TMP_LOG;
fi

echo "["$(date +%F-%T)"] - List of all your databases..." $DATABASE_TABLE_LISTE >> $TMP_LOG;
 
for BASE in $DATABASE_TABLE_LISTE
do
    # analyse, checking tables.
    mysqlcheck -u$SQL_USER -h$SQL_SERVER_HOST -p$SQL_USER_PASSWORD -c -a -o $BASE  >> $TMP_LOG;
    if [ ! $? -eq 0 ] ; then
      echo "["$(date +%F-%T)"] - An error occurred analyse, checking " $BASE "tables." >> $TMP_LOG;
    fi
 
    skipdb=-1
    if [ "$NOT_TO_SAVE" != "" ] ; then
        for i in $NOT_TO_SAVE
        do
            [ "$BASE" == "$i" ] && skipdb=1 || :
            echo "["$(date +%F-%T)"] - Skipping " $BASE >> $TMP_LOG;
        done
    fi
    # Creation of dump tables
    if [ "$skipdb" == "-1" ] ; then
        echo "["$(date +%F-%T)"] - Backing up " $BASE " databases... " >> $TMP_LOG;
        SQL_FILE_NAME=$DEST_WORKING$BASE.$DATE_NOW.sql.gz
        $MYSQLDUMP -u$SQL_USER -h$SQL_SERVER_HOST -p$SQL_USER_PASSWORD $BASE | $GZIP -f -9 > $SQL_FILE_NAME
	if [ ! $? -eq 0 ] ; then
	   echo "["$(date +%F-%T)"] - An error occurred in backing up " $BASE "tables." >> $TMP_LOG;
	fi

        # size of the archive log and name
        FILESIZE=`ls -lh $SQL_FILE_NAME | awk '{print $5}'`
        echo "["$(date +%F-%T)"] - Generation of the archive for : "$SQL_FILE_NAME" ("$FILESIZE")" >> $TMP_LOG;
    fi
done
 
#---------- remove local old files as fo date----------#
if [ "$KEEPLOCAL" = "y" ] ; then
  cd $BACKUP_REPO
  echo "["$(date +%F-%T)"] - Search and delete the archive with more than "$DAY_TO_KEEP_BACKUP_LOCAL" DAY" >> $TMP_LOG;
  find $BACKUPDIR -maxdepth 1 -type f -ctime +$DAY_TO_KEEP_BACKUP_LOCAL  -exec rm -v {} \; >> $TMP_LOG ;
  if [ ! $? -eq 0 ] ; then
     echo "["$(date +%F-%T)"] - An error occurred Search and delete the archive" >> $TMP_LOG;
  fi
fi
 
#---------------------- Send Mail --------------------#
if  [ "$IS_MAIL_TO_SEND" = "y" ] ; then
  mail -s $EMAILS_SUBJECT $EMAILS_ADRESSES < $TMP_LOG;
  echo "["$(date +%F-%T)"] - send mail is done" >> $TMP_LOG;
fi
 
echo "["$(date +%F-%T)"] - Your backup is complete!" >> $TMP_LOG;
echo "" >> $TMP_LOG;
 
# write log on output for script log
echo $TMP_LOG;

</code>


=== Finalisation de la configuration ===
<code bach>
# Mise en place du cron
$ crontab -e
</code>

Puis ajoutez ceci
<code bach>
#chaque jour a 3h46
46 03 * * * /home/backupspace/script/mySQLBackup.sh >> /var/log/mysql_backup.log
</code>

<code bach>
# creation du fichier de log
$ sudo touch /var/log/mysql_backup.log
$ sudo chown backupspace:backupspace /var/log/mysql_backup.log
$ sudo chmod 770 /var/log/mysql_backup.log
</code>

TODO : configuration de syslog ou Logrotate a voir

===== Optimiser votre serveur MySQL =====

=== Limiter les requêtes avec un timeout pour que MySQL ne s'emballe pas ===

rajouter dans le fichier ///etc/mysql/my.cnf//
<code bash>
[mysqld]
connect_timeout = 60
</code>

===== Visualiser les tables votre serveur MySQL =====

MySQL Workbench permet à un développeur DBA, ou l'architecte de concevoir visuellement des données, le modèle, générer et gérer des bases de données. Il comprend tout ce dont un modélisateur de données a besoin pour créer des modèles complexes ER, avant et rétro-ingénierie, et offre également des fonctions clés pour effectuer la gestion du changement difficile et les tâches de documentation qui exigent normalement beaucoup de temps et d'efforts : 
[[http://mysql.com/products/workbench/|MySQL workbench]]


==== Tuning Primer for Mysql ====

<code bash>
# vérifier si le PATH est bon PATH=$PATH:/usr/local/mysql/bin/  
$ cd /tmp
$ wget http://day32.com/MySQL/tuning-primer.sh
$ chmod +x tuning-primer.sh 
$ ./tuning-primer.sh

</code>

Puis appliquer les recommandations.

<code bash>
nano /etc/mysql/my.cnf

# réduire le nombre de connexions maximum
max_connections        = 50

# autoriser MySQL à utiliser toute la mémoire cache
query_cache_limit       = 16M

# activer la surveillance des requêtes les plus lentes et supérieur à 5 secondes
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 5
</code>

site officiel : [[http://www.day32.com/MySQL/|www.day32.com]]

====== PostgreSql ======
===Connexion===
    $su postgres

==Création d'un utilisateur== 
    createuser dbuser

==Création d'une base de données==
    createdb dbtest

==Se connecter==
    psql dbtest

== Modifier les caractéristiques d'un utilisateur ==
//Changer un mot de passe d'un utilisateur//
<code>ALTER USER dbuser ENCRYPTED PASSWORD 'password';</code>  
**GRANT** :
//Ajouter tous les priviléges à l'utilisateur dbuser sur la table table1//
<code>GRANT ALL PRIVILEGES ON table1 TO dbuser;</code>


===Mode graphique===
http://127.0.0.1/phppgadmin avec l&#8217;utilisateur dbuser devrait fonctionner
====  Liens PostgreSql ==== 

  * [[http://www.postgresql.org/docs/aw_pgsql_book/|PostgreSQL: Introduction and Concepts]]
  * [[http://www.linux-france.org/article/serveur/psql/guide-utilisateur/book1.html|Guide de l'utilisateur]]
  * [[http://traduc.postgresqlfr.org/|Site de traduction]]
  * [[http://traduc.postgresqlfr.org/pgsql-fr/|Documentation PostgreSQL 7.4]]
  * [[http://techdocs.postgresql.org/|PostgreSQL Technical Documentation]]
  * [[http://advocacy.postgresql.org/?lang=fr|advocacy.postgresql.org]]
  * [[http://www.postgresql.org/docs/7.4/static/jdbc.html|Doc JDBC Postgresql 7.4]]
 
 
====== Oracle ======

Merge d'une table sur elle meme avec Oracle:

<code sql>
MERGE INTO table_test tt 
     USING (SELECT * FROM DUAL) d 
        ON (tt.teck = 'toto') 
   WHEN MATCHED THEN 
      UPDATE 
         SET ... 
   WHEN NOT MATCHED THEN 
      INSERT (...) 
      VALUES (...); 
                       
</code>