MySQL : Dump

Table des Matières [Cacher/Visualiser]



Modifier

But

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.

Modifier

Solutions envisagées


Modifier

Solution 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.

Modifier

Problème en production

Une base doit répondre à Trois critères

Modifier

Performance

(*) 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

Modifier

Sécurité


Modifier

Réplication & modification

(*) Ces critères ne sont plus respecté due à la taille de la base.

Modifier

Mise en application sur serveur MySQL Windows

Modifier

Donné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


Modifier

1 Création de répertoires

    c:\MySQL Datafiles\dump
    c:\MySQL Datafiles\bddfiles         : répertoire contenant

Modifier

2 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.

Modifier

3 Arrêter le serveur MySQL

    net stop mysql
    ou
    "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqladmin" user=root password=root shutdown

  
Modifier

4 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 !!!

Modifier

5 Re démarrer le serveur

net start mysql

ERREUR HABITUELLE

Modifier

Le 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 Modifier

Le 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

Modifier

6 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

Modifier

7 Restaurer les droits et accès

    Comme habituellement.....

Modifier

CE QUI CHANGE MAINTENANT

   syntax : ALTER TABLE ENGINE=InnoDB;
   exemple : ALTER TABLE Currency ENGINE=InnoDB;

Modifier

Liste de commandes pratiques en cas d'erreurs

Modifier

DUMP : Commandes pratiques




Référence : http://dev.mysql.com/doc/refman/5.0/fr/mysqldump.html Modifier

INNODB 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
Modifier

PROBLEMES POSSIBLES

    flush des privilèges à faire
    $mysql -u root -p
    mysql> flush privileges
    mysql> quit
    Bye
    $ /etc/init.d/mysql restart
Modifier

INFORMATIONS 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;

Modifier

METHODE 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 = '' )
  • 100, 2 ) .
AS Pourcent,Update_time as 'Update_time' FROM information_schema.tables .
WHERE table_schema = '' AND table_name = '' GROUP BY TABLE_NAME;