Optimisation de base de données
TODO index, requetes
Java & Base de données
Utilisation d'une DataSource
Mysql
Test de connection
$mysqladmin -h 'hosname' version[variables]
Connexion
Connexion en local
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
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
$ 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
Deplacer les fichiers de votre serveur MySQL
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
Backup de votre serveur MySQL
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"
Script de sauvegarde de mysql avec cron
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
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
[mysqld] connect_timeout = 60
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 : MySQL workbench
Tuning Primer for Mysql
# 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’utilisateur dbuser devrait fonctionner
Liens PostgreSql
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 (...);