Trucs et astuces pour MySQL

Pleins d'astuces MySQL

http://souptonuts.sourceforge.net/readme_mysql.htm

Le fichier MySQL Tips résume énormément d'astuces MySQL.

Source: Merci à Le blog de Xarli pour Trucs et astuces MySQL.

Extraire une base de données d'un dump MySQL

Renseignez le nom de la base de données à extraire:

DB_NAME="my_database"

Renseignez le chemin du fichier dump MySQL:

DUMP_FILE="/path/to/a-mysql-dump.sql"

Extrayez la base de données du dump:

command sed -n "0,/^-- Current Database:/p;/^-- Current Database: \`${DB_NAME}\`/,/^-- Current Database: \`/p;/TIME_ZONE=@OLD_TIME_ZONE/,/^-- Dump completed/p" \
   > "${DB_NAME}.sql"

Le dump de la base de données est maintenant disponible dans le fichier donné par:

echo "${DB_NAME}.sql"

Supprimer tous les doublons d'une table

Listez les doublons d'une table avec:

SELECT COUNT(field1), field1 [ , field2 ]
  FROM table
  GROUP BY field1 [ , field2 ]
  HAVING COUNT(field1) > 1

Remarque: remplacez field2 (champ optionnel) par autant de champs supplémentaires de vérification de doublon.

Supprimez les doublons à l'aide de:

DELETE
    FROM table
    LEFT OUTER JOIN (
        SELECT MIN(id) as id, field1, field2
            FROM table
            GROUP BY field1, field2
        ) AS table_1
            ON table.id = table_1.id
    WHERE table_1.id IS NULL

Source: Merci à RootLabs (fr) pour MySQL : Comment nettoyer une table de ces doublons ? (fr).

Lister toutes les contraintes FOREIGN KEY d'une base de données

Utilisez cette requête pour lister toutes les contraintes FOREIGN KEY:

SELECT *
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

Nettoyer les journaux binaires

Les journaux binaires MySQL peuvent dans certains cas occuper énormément de place sur le serveur. Il est possible des les purger à l'aide de la requête 'PURGE BINARY LOGS TO'. Si le serveur MySQL n'est pas répliqué, utilisez cette commande pour purger tous les journaux binaires sauf les 5 derniers:

command mysql --execute="PURGE BINARY LOGS
    TO '$(command find '/var/log/mysql'  -type f -name 'mysql-bin.[0-9]*' \
              | command sort \
              | command head -n -5 \
              | command tail -n 1 \
              | command xargs basename)';"

Mettre en place un moteur de recherche

Le moteur d'indexation open-source Sphinx facilite la mise en place d'un moteur de recherche performant.

Source : Merci à Guillaume Pousséo.

Déboguer les erreurs #1025 pour  les tables InnoDB

L'erreur #1025 apparait lors de l 'altération d 'une table utilisant le moteur InnoDB.

Renseignez le nom de la base de données ou l'erreur s'est produite:

MYSQL_DB="my_database"

Obtenez plus d'informations sur la nature de l 'erreur avec l'instruction  SQL:

command mysql --skip-column-names \
    --execute="SHOW ENGINE INNODB STATUS;"\
    "${MYSQL_DB}" \
  | command sed \
          -e 's/.*LATEST FOREIGN KEY ERROR/LATEST FOREIGN KEY ERROR/' \
          -e 's/TRANSACTIONS.*//' \
          -e "s/\\\n/\n/g"

Source : Merci à snoyes sur irc.freenode.net#mysql

Corriger les erreurs "Incorrect key file for table"

Si l'erreur ci-dessous apparaît lors de l'exécution d'une requête SQL:

Could not execute query [Native Error: Incorrect key file for table '/tmp/#sql_2258_0.MYI'; try to repair it

Le dossier /tmp n'a plus suffisament d'espace dique. Pour corriger l'erreur, nettoyez le système de fichier contenant le dossier /tmp.

Créer l'utilisateur de maintenance 'debian-sys-maint'

Si l'utilisateur debian-sys-maint n'existe pas (par exemple, suite à la récupération d'un dump SQL d'une autre distribution), créez le:

SYSMAINT_PWD=$(/bin/grep --max-count=1 "password" /etc/mysql/debian.cnf | /bin/sed -e 's/^password[ =]*//g')
echo "ALTER TABLE user CHANGE password Password varchar(41) collate utf8_bin NOT NULL default '';
REPLACE INTO user SET
  host='localhost', user='debian-sys-maint', password=password('${SYSMAINT_PWD}'),
  Select_priv='Y', Insert_priv='Y', Update_priv='Y', Delete_priv='Y',
  Create_priv='Y', Drop_priv='Y', Reload_priv='Y', Shutdown_priv='Y',
  Process_priv='Y',  File_priv='Y', Grant_priv='Y', References_priv='Y',
  Index_priv='Y', Alter_priv='Y', Show_db_priv='Y', Super_priv='Y',
  Create_tmp_table_priv='Y', Lock_tables_priv='Y', Execute_priv='Y',
  Repl_slave_priv='Y', Repl_client_priv='Y';" \
  | /usr/bin/mysql --user=root --password mysql

Désinstaller totalement (purger) le serveur MySQL

Cette procédure désinstalle complètement un serveur MySQL en vue de le réinstaller depuis zéro.

Purgez PHPMyAdmin:

command apt-get --purge remove phpmyadmin

Purgez le serveur MySQL:

command apt-get --purge remove mysql-server-5.1 mysql-server-core-5.1

Supprimez les fichiers de données:

command rm -r /var/lib/mysql
command rm /etc/mysql/debian.cnf

Vous pouvez maintenant réinstaller proprement le serveur MySQL.