MySQL OPTIMISE toutes les tables?

MySQL dispose d’une commande OPTIMIZE TABLE qui peut être utilisée pour récupérer de l’espace inutilisé dans une installation MySQL. Existe-t-il un moyen (commande intégrée ou procédure stockée commune) d’exécuter cette optimisation pour chaque table de la firebase database et / ou de l’installation du serveur ou est-ce quelque chose que vous devez créer vous-même?

Vous pouvez utiliser mysqlcheck pour le faire sur la ligne de commande.

Une firebase database:

 mysqlcheck -o  

Toutes les bases de données:

 mysqlcheck -o --all-databases 

J’ai fait ce script ‘simple’:

 set @a=null,@c=null,@b=concat("show tables where",ifnull(concat(" `Tables_in_",database(),"` like '",@c,"' and"),'')," (@a:=concat_ws(',',@a,`Tables_in_",database(),"`))"); Prepare `bd` from @b; EXECUTE `bd`; DEALLOCATE PREPARE `bd`; set @a:=concat('optimize table ',@a); PREPARE `sql` FROM @a; EXECUTE `sql`; DEALLOCATE PREPARE `sql`; set @a=null,@b=null,@c=null; 

Pour l’exécuter, il suffit de le coller dans n’importe quel IDE SQL connecté à votre firebase database.

Remarque: ce code ne fonctionnera PAS sur phpmyadmin.

Comment ça marche

Il exécute une instruction show tables et la stocke dans une instruction préparée. Ensuite, il exécute une optimize table dans l’ensemble sélectionné.

Vous pouvez contrôler les tables à optimiser en définissant une valeur différente dans la @c var @c .

L’exemple de script php suivant peut vous aider à optimiser toutes les tables de votre firebase database

  $tablename) { mysql_query("OPTIMIZE TABLE '".$tablename."'") or die(mysql_error()); } } ?> 

Effectuez toutes les procédures nécessaires pour réparer toutes les tables de toutes les bases de données avec un simple script shell:

 #!/bin/bash mysqlcheck --all-databases mysqlcheck --all-databases -o mysqlcheck --all-databases --auto-repair mysqlcheck --all-databases --analyze 

pour toutes les bases de données:

 mysqlcheck -Aos -uuser -p 

Pour une optimisation de la firebase database:

 mysqlcheck -os -uroot -p dbtest3 

En ligne de commande:

 mysqlcheck -o  -u -p 

puis tapez le mot de passe

Vous pouvez optimiser / vérifier et réparer toutes les tables de firebase database, en utilisant le client mysql.

Tout d’abord, vous devriez obtenir toutes les listes de tables séparées par ‘,’:

 mysql -u[USERNAME] -p[PASSWORD] -Bse 'show tables' [DB_NAME]|xargs|perl -pe 's/ /,/g' 

Maintenant, quand vous avez toute la liste des tables pour l’optimisation:

 mysql -u[USERNAME] -p[PASSWORD] -Bse 'optimize tables [tables list]' [DB_NAME] 

À partir de phpMyAdmin et d’autres sources, vous pouvez utiliser:

 SET SESSION group_concat_max_len = 99999999; SELECT GROUP_CONCAT(concat('OPTIMIZE TABLE `', table_name, '`;') SEPARATOR '') AS O FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND table_name!='dual' AND TABLE_SCHEMA = '' 

Vous pouvez ensuite copier et coller le résultat dans une nouvelle requête ou l’exécuter à partir de votre propre source.

L’ MySQL Administrator (faisant partie des outils graphiques MySQL) peut le faire pour vous au niveau de la firebase database.

Sélectionnez simplement votre schéma et appuyez sur le bouton Maintenance dans le coin inférieur droit.

Comme les outils de l’interface graphique ont atteint le statut de fin de vie, ils sont difficiles à trouver sur la page mysql. Les trouvé via Google: http://dev.mysql.com/downloads/gui-tools/5.0.html

Je ne sais pas si le nouveau MySQL Workbench peut le faire aussi.

Et vous pouvez utiliser l’outil de ligne de commande mysqlcheck qui devrait pouvoir le faire aussi.

Si vous souhaitez parsingr, réparer et optimiser toutes les tables de toutes les bases de données de votre serveur MySQL, vous pouvez le faire en une seule fois depuis la ligne de commande. Vous aurez besoin de root pour faire cela.

 mysqlcheck -u root -p --auto-repair --optimize --all-databases 

Une fois que vous exécutez cela, vous serez invité à entrer votre mot de passe root MySQL. Après cela, cela commencera et vous verrez les résultats tels qu’ils se produisent.

Exemple de sortie:

 yourdbname1.yourdbtable1 OK yourdbname2.yourdbtable2 Table is already up to date yourdbname3.yourdbtable3 note : Table does not support optimize, doing recreate + analyze instead status : OK etc.. etc... Repairing tables yourdbname10.yourdbtable10 warning : Number of rows changed from 121378 to 81562 status : OK 

Si vous ne connaissez pas le mot de passe root et utilisez WHM, vous pouvez le modifier depuis WHM en accédant à: Accueil> Services SQL> Mot de passe racine MySQL

Ce script bash acceptera le mot de passe root en tant qu’option et l’optimisera un par un, avec une sortie d’état:

 #!/bin/bash if [ -z "$1" ] ; then echo echo "ERROR: root password Parameter missing." exit fi MYSQL_USER=root MYSQL_PASS=$1 MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}" TBLLIST="" COMMA="" SQL="SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE" SQL="${SQL} table_schema NOT IN ('information_schema','mysql','performance_schema')" for DBTB in `mysql ${MYSQL_CONN} -ANe"${SQL}"` do echo OPTIMIZE TABLE "${DBTB};" SQL="OPTIMIZE TABLE ${DBTB};" mysql ${MYSQL_CONN} -ANe"${SQL}" done 

Un script de démarrage pour répertorier et exécuter un outil sur les bases de données …

 #!/bin/bash declare -a dbs unset opt for each in $(echo "show databases;" | mysql -u root) ;do dbs+=($each) done echo " The system found [ ${#dbs[@]} ] databases." ;sleep 2 echo echo "press 1 to run a check" echo "press 2 to run an optimization" echo "press 3 to run a repair" echo "press 4 to run check,repair, and optimization" echo "press q to quit" read input case $input in 1) opt="-c" ;; 2) opt="-o" ;; 3) opt="-r" ;; 4) opt="--auto-repair -c -o" ;; *) echo "Quitting Application .."; exit 7 ;; esac [[ -z $opt ]] && exit 7; echo " running option: mysqlcheck $opt in 5 seconds on all Dbs... "; sleep 5 for ((i=0; i<${#dbs[@]}; i++)) ;do echo "${dbs[$i]} : " mysqlcheck $opt ${dbs[$i]} -u root done 

Si vous accédez directement à la firebase database, vous pouvez écrire la requête suivante:

 OPTIMIZE TABLE table1,table2,table3,table4......; 

mes 2cents: commencer avec la table avec la plus grande fragmentation

 for table in `mysql -sss -e "select concat(table_schema,".",table_name) from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema') order by data_free desc;" do mysql -e "OPTIMIZE TABLE $table;" done