developpement:base_de_donnees

Optimisation de base de données

TODO index, requetes

Java & Base de données

Mysql

Test de connection

$mysqladmin -h 'hosname' version[variables]

mysql -h hote -u utilisateur -p
show tables;
show databases;
USE test;
describe tables;
CREATE DATABASE montest;
load data local Infile "....sql" Into Table foo
source nomFichier

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

$ 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

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

$ /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

Sauvegarde via mysqldump

# 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

Restauration d'un fichier .sql

$ 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 

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

# 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"

Preparation de l'environnement sur Debian

# 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 du script de backup et d'optimisation de la base de donnees

$ nano /home/backupspace/script/mySQLBackup.sh
#!/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;

Finalisation de la configuration

# Mise en place du cron
$ crontab -e

Puis ajoutez ceci

#chaque jour a 3h46
46 03 * * * /home/backupspace/script/mySQLBackup.sh >> /var/log/mysql_backup.log
# 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

TODO : configuration de syslog ou Logrotate a voir

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

rajouter dans le fichier /etc/mysql/my.cnf

[mysqld]
connect_timeout = 60

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 : MySQL workbench

# 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

Puis appliquer les recommandations.

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

site officiel : 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

ALTER USER dbuser ENCRYPTED PASSWORD 'password';

GRANT : Ajouter tous les priviléges à l'utilisateur dbuser sur la table table1

GRANT ALL PRIVILEGES ON table1 TO dbuser;

Mode graphique

http://127.0.0.1/phppgadmin avec l&#8217;utilisateur dbuser devrait fonctionner

Oracle

Merge d'une table sur elle meme avec Oracle:

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 (...); 
 
  • developpement/base_de_donnees.txt
  • Dernière modification: 2018/10/13 14:59
  • (modification externe)