ModifierBut
L'utilisation d'innodb ne permet pas de skrinker la base de données, autrement dit, si votre base de données grossi elle va fini par faire 5 fois la taille de vos données et dixit la doc innodb "pour des raisons de performance, la compression des données n'a pas été un choix". Il faut donc procéder autrement.
ModifierSolutions envisagées
- Utilisation de PostGreSQL
- Pas de retour d'expérience dessus par rapport à MySQL, cette solution pourra être envisagée lors du changement d'hébergeur.
- Permet la compression des bases, la réplication, les transactions etc etc
- Utilisation de MSQL server
- Possible, mais lors du changement d'utilisateur.
- Permet la compression des bases, la réplication, les transactions etc etc
ModifierSolution prise
Nous allons exporter la base sous forme de dump, activer l'option 1 fichier par table dans innodb et restaurer la base de données.
Le grossissement de chaque table pourra alors être controlé et optimisé par table.
ModifierProblème en production
Une base doit répondre à Trois critères
ModifierPerformance
- optimisation de sa structure
- ptimisation des index
- optimisation de la lecture des données et parse
(*) Les deux derniers critères ne sont plus respectés, les optimisations d'index effectuent un lock trop important sur les tables pour être opérés sans arrêt du serveur
ModifierSécurité
- temps nécessaire à la restauration en cas de crash
- contrainte d'intégrité
ModifierRéplication & modification
- On doit pouvoir déplacer la base rapidement sur un autre serveur
- Les modifications de structure doivent pourvoir être fait "à chaud"
(*) Ces critères ne sont plus respecté due à la taille de la base.
ModifierMise en application sur serveur MySQL Windows
ModifierDonnées de base
C:\MySQL Datafiles : Répertoire de données
C:\Program Files\MySQL\MySQL Server 5.1\my.ini : fichier de configuration, équivalent de my.cnf
Modifier1 Création de répertoires
c:\MySQL Datafiles\dump
c:\MySQL Datafiles\bddfiles : répertoire contenant
Modifier2 Faites un dump de votre base
sur le répertoire de la base de données
> mysqldump -R -q --opt --host=localhost --password=root --user=root attractiveworld > ./dump/all.sql
ATTENTION : utilisez les option
quick ou opt sinon mysql va charger toutes les données en mémoire avant de les écrires. Ce qui dans le cas de bases importantes, ne fonctionnera pas.
Modifier3 Arrêter le serveur MySQL
net stop mysql
ou
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqladmin"
user=root password=root shutdown
Modifier4 Modification de My.ini
Pour avoir un fichier par table
mysqld
# Evite le grossissement a l infini des bases
innodb_file_per_table
Changement du répertoire de base de données
innodb_data_home_dir="C:/MySQL Datafiles/bddfiles/"
datadir="C:/MySQL Datafiles/bddfiles/frm/"
note à tous les Oompa Lumpa : si vous lisez cette ligne, merci de me le faire savoir !!!
Modifier5 Re démarrer le serveur
net start mysql
ERREUR HABITUELLE
ModifierLe serveur ne redémarre pas et n'indique pas de fichier de log
'C:\Users\Cyril>mysql start
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061) '
Reférence : http://forge.mysql.com/wiki/Error2003-CantConnectToMySQLServer
SOLUTION
Le serveur a changé de port de communication dans sa configuration, pour remettre le port 3306
>mysqld --port=3306
puis redémarrer le service windows mysql
>sc start MySQL
pour vérifier que le service soit bien démarré
>sc query MySQL
vous devriez avoir le message START_PENDING
ModifierLe serveur ne redémarre pas et vous avez un fichier de log
le fichier se trouve sur : C:\MySQL Datafiles\bddfiles\xxxx.err
Certains fichiers sous windows pour la version < à 5.1.8 ne sont pas recrée automatiquement. Il fat dans ce cas exécuter
>mysql_upgrade
Modifier6 Restaurer les données
6.1/ Recréer vos base avec l'encodage par défaut
note: si vous voulez changer d'encodage voir le wiki "conversion encodage bdd"
6.2/ Restaurer les datas
>mysql --host=localhost --password=root --user=root awpayant < ./dump/awpayant.sql
Modifier7 Restaurer les droits et accès
Comme habituellement.....
ModifierCE QUI CHANGE MAINTENANT
- chaque table est un fichier spécifique
- vous pouvez optimiser l'espace disque de chaque table en effectuant un alter null
syntax : ALTER TABLE
ENGINE=InnoDB;
exemple : ALTER TABLE Currency ENGINE=InnoDB;
ModifierListe de commandes pratiques en cas d'erreurs
ModifierDUMP : Commandes pratiques
- Export de plusieurs bases de données : mysqldump --databases database1 [database2 ...] > my_databases.sql
- Toutes les bases : mysqldump --all-databases > all_databases.sql
- Dans serveur à un autre : mysqldump --opt database | mysql ---host=remote-host -C database
Référence : http://dev.mysql.com/doc/refman/5.0/fr/mysqldump.html
ModifierINNODB FILE PER TABLE
Si la configuration est bien prise en compte vous devriez pour toute nouvelle table voir apparaître tbl_name.ibd dans le répertoire de données.
Problème : les tables ne se crée pas
Solution : Modifier votre conf.ini et stopper redémarrer le serveur
>net stop mysql
>net start mysql
ModifierPROBLEMES POSSIBLES
flush des privilèges à faire
$mysql -u root -p
mysql> flush privileges
mysql> quit
Bye
$ /etc/init.d/mysql restart
ModifierINFORMATIONS SUR UNE TABLE
show table status
data_free : spécifique à inno db, renvoi le nombre de bloc de 1Mo disponible.
Si les tables sont dans le même fichier, la données correspond à l'ensemble du fichier pour l'ensemble des tables.
Si vous avez un fichier par table, l'information concerne le fichier pour cette table.
note : cette information n'est pas juste dans le cas de table partitionnées.
Exemple
SHOW TABLE STATUS FROM test;
ModifierMETHODE ALTERNATIVE VU SUR DES FORUMS : Et ne fonctionnant pas
1/ ajouter sur /etc/my.cnf
innodb_file_per_table = 1
2/ Lancer
mysqloptimize –all-databases
Cette solution ne permet pas la suppression du fichier principal qui restera de taille importante, il contiendra toujours les functions et procédures stockées.
Modifier REQUETE : Taille d'une base
pour connaitre la taille réelle d'une base de données et donc la place qu'elle devrait normalement utilisé sur le disque.
SELECT TABLE_NAME as 'Name table', TABLE_ROWS as 'NB lines',ROW_FORMAT as 'Format Table' .
, Max_data_length as 'Max_Data_Length', Data_length as 'Data_Length'
.
,Avg_row_length as 'Avg_row_length',Index_length as 'Index_length '
.
,Data_free as 'Data_free ', ( DATA_LENGTH + INDEX_LENGTH) as 'Size (MB)' .
,ROUND( SUM( (DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024) ) .
/ ( SELECT SUM( (DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024) ) .
FROM information_schema.TABLES WHERE table_schema = '' )
AS Pourcent,Update_time as 'Update_time' FROM information_schema.tables .
WHERE table_schema = '' AND table_name = '' GROUP BY TABLE_NAME;