Discussion :

[falco-]

Bonjour,
j'ai dans la même base plusieurs tables dont la majorité ont 200 000 lignes et j'en ai d'autres avec 8 millions de lignes et d'autres à 70 millions de ligne
C'est par hasard que je me suis rendu compte avant de mettre en prod que l'import a été très rapide !
En fait j'ai pour la plupart la moitié de lignes et pour les plus grande comme celles à 8 Millions j'obtiens 7Millions et celle à 78Millions j'obtiens 70 mille lignes !!!

J'ai essayé ceci pour la table tickets qui fait 130 000 lignes:
Export :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 mysqldump -S /var/run/mysqld5/mysqld5.sock  gl-ens gl_tickets --flush-logs --complete-insert --max_allowed_packet=512M -u assistance -pca!   > /var/backups/bdd_backups/mysql56/tickets_prod_plus

et import

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
mysql --socket /var/run/mysqld5/mysqld5.sock -u assistance -pca! 
mysql> \r gl-esl
mysql> \. /var/backups/bdd_backups/mysql56/tickets_prod_plus

AVec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

mysql -S /var/run/mysqld5/mysqld5.sock --verbose -u assistance -pca! gl-ens </var/backups/bdd_backups/mysql56/tickets

J'avais une erreur "données trop grande pour le champs name"

J'ai ajouté dans le fichier cnf :
sql_mode = "NO_ENGINE_SUBSTITUTION"

Mais j'ai plus l'erreur mais la perte de données est importante : toujours des lignes en moins !!

si vous avez déjà exporter des tables de mysql 5.6 vers mysql8 ? Une aide serait précieuse : je n'y comprends plus rien

[Artemus24]

Salut falco-.

Je suis sous Windows 10 Pro et j'utilise WampServer avec la version MySql 8.0.33.
Je travaille avec des scripts MySql car cela me permet de conserver ceux-ci et de les réutiliser à ma convenance.
Cela me permet de ne pas réinventer à chaque fois les astuces dont j'ai besoin pour exporter de très grosses tables.

a) je n'utilise pas mon fichier "my.ini" de mon SGBDR MySql 8.0.33.
Pour mysqldump, j'utilise un fichier spécifique que je nomme "my.cnf" afin de bien le distinguer de celui de mon SGBDR.

b) que vais-je mettre dedans ?
Tout ce qui est spécifique et commun à tous mes vidages de tables (export). Par exemple :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
[client]
user     = mon_compte
password = mon_mot_de_passe
host     = 127.0.0.1
 
[mysqldump]
column-statistics
complete-insert
default-character-set=latin1
network-timeout
order-by-primary
verbose

A toi de l'adapter selon le paramétrage de tes tables.
Cela évite de trop surcharger la ligne "mysqldump" avec tout un tas de paramètres.

c) j'ai une très grosse table et je constate que je ne peux pas la charger car elle va me provoquer une erreur.
Peu importe l'erreur, tout ce que je veux est de charger ma table.

Solution : saucissonner le vidage en plusieurs fichiers "export".

d) premier batch windows :

Code batch :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@echo off
chcp 1252 > nul

set PATH=.;%WAMPSERVER%\bin\mysql\%MYSQL%\bin\;%PATH%

set FIC=02.Dump.sql
set ERR=02.Dump.log

if exist %FIC% (del %FIC%)
if exist %ERR% (del %ERR%)

mysqldump ^
  --defaults-file=my.cnf ^
  --tables base test ^
  --where="id < 12" ^
  --result-file=%FIC% ^
  --log-error %ERR%

@echo Résultat : %ERRORLEVEL%

if %ERRORLEVEL% EQU 0 (if exist %ERR% del %ERR%) else (if exist %FIC% del %FIC%)

pause
exit

L'important est la ligne "mysqldump". J'ai précisé ici la base et la table que je désire vider (ou exporter).
Autrement dit, un "mysqldump" par table.

L'astuce est d'utiliser la ligne "--where" pour dire ce que je veux vider.
Cette condition me permet en effet de saucissonner ma gigantesque table en plusieurs fichiers "export".
Ce premier vidage va créer la table !

e) deuxième vidage de ma table.

Code batch :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
@echo off
chcp 1252 > nul

set PATH=.;%WAMPSERVER%\bin\mysql\%MYSQL%\bin\;%PATH%

set FIC=03.Dump.sql
set ERR=03.Dump.log

if exist %FIC% (del %FIC%)
if exist %ERR% (del %ERR%)

mysqldump ^
  --defaults-file=my.cnf ^
  --tables base test ^
  --no-create-info ^
  --where="id >= 12 and id <20" ^
  --result-file=%FIC% ^
  --log-error %ERR%

@echo Résultat : %ERRORLEVEL%

if %ERRORLEVEL% EQU 0 (if exist %ERR% del %ERR%) else (if exist %FIC% del %FIC%)

pause
exit

Il y a quelques différences vis-à-vis de mon premier batch windows.
Je ne désire pas qu'il me crée à nouveau ma table. Je lui indique "--no-create-info".

Je précise la nouvelle clause where qui me permet de saucissonner mon extraction.
Je prends toutes les lignes entre 12 compris et 20 non compris.

Si tu regardes le contenu du fichier export, celui-ci n'aura pas la création de ta table et seulement les lignes que j'ai indiquées.

f) pour réaliser cette astuce, j'ai utilisé la colonne "id" qui est la clef primaire.
Cela permet de ranger les lignes dans l'ordre du cluster.
Autrement dit, chaque nouvelle ligne sera insérée en fin de table et donc pas de désorganisation lors de l'importation.

C'est à toi de saucissonner tes extractions afin de ne pas produire l'erreur au chargement de ta table dans le nouveau SGBDR de production.
Cela va dépendre de la taille de ta ligne et de ce que la journalisation peut stocker au maximum.

g) Voici le batch que j'utilise pour importer l'export que j'ai saucissonné :

Code batch :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@echo off
chcp 1252 > nul
 
set PATH=.;%WAMPSERVER%\bin\mysql\%MYSQL%\bin\;%PATH%
 
mysql ^
  --defaults-file=my.cnf ^
  --database base ^
  --execute "source 02.Dump.sql"
 
mysql ^
  --defaults-file=my.cnf ^
  --database base ^
  --execute "source 03.Dump.sql"
 
pause
exit

Le test a été fait sous mon ordinateur et j'ai bien eu le résultat que j'attendais.

Quelques conseils :
1) ne pas mettre dans la ligne "mysqldump" le compte et le mot de passe en clair.
C'est confidentiel et tu es censé être le seul à connaitre cela.
J'ai placé ces deux informations dans mon fichier "my.cnf" qui est consultable que par moi-même.

2) faire attention au charset ! J'utilise "latin1" et un caractère occupe 1 octet.
Si tu utilises "utf8mb4", 1 caractère peut occuper jusqu'à 4 octets.
L'erreur : "données trop grande pour le champs name" peut résulter d'un problème de charset.

3) quand je stocke des chaînes de caractères de petites longueurs, j'utilise "varchar(255)" ou tinytext.
La longueur est spécifié sur un octet, et derrière, j'ai au maximum 255 octets pour stocker mes caractères.
En "utf8mb4", cela peut ne pas être suffisant. J'utilise dans ce cas, la convention suivante :
--> tinytext < 255 octets. la longueur est stockée sur 1 octet.
--> text < 65535 octets. La longueur est stockée sur 2 octets.
--> mediumtext < 16,777,215 octets. la longueur est stockée sur 3 octets.
--> longtext < 4,294,967,295 octets. la longueur est stockée sur 4 octets.

Pour le stockage, il faut prévoir plus grand que la longueur maximale de la chaine de caractères.
C'est à l'usage que l'on va choisir le bon type pour stocker une chaine de caractères.
Ne pas oublier que la différence entre deux types est le nombre d'octets pour stocker la longueur.
Choisir text au lieu de tinytext, fait que la diffférence est de 1 octet pour stocker la longueur.
Et si vous posséder 70 millions de lignes, vous avez perdu 70 millions d'octets, soit 67Mo pour rien.

4) ne modifier pas le "sql-mode".
La question est pourquoi votre moteur (engine) n'est pas activé ?
C'est pourquoi le fichier "my.cnf" que vous utilisez pour effectuer vos export doit être conforme au SGBDR de la production.

5) que faites vous de vos fichiers export ?
Est-ce que vous les transférez vers le site de production ?
Dans ce cas, c'est la bonne méthode.

Ou faites vous l'import depuis votre SGBDR qui a servi à les exporter ?
Le transfert risque de prendre bien plus de temps.

5) vos erreurs sont dues à un mauvais paramétrage de votre fichier "my.cnf" et à une volumétrie très importante.
Vous pouvez augmenter votre "max_allowed_packet" jusqu'à 1 Go au maximum.
Si le paquet envoyé est plus grand que 1Go, MySql va provoquer une erreur.
Vous devez mettre la même valeur pour le client et le serveur et l'augmenter jusqu'à ce que ça passe.

6) il serait bien de consulter les exemples données pour importer et exporter de très gros fichiers sous le net.

Cordialement.
Artemus24.
@+

[falco-]

Merci pour cette expertise ! super.

Je travaille sur Linux.

Après de multiples tests,

La commande d'export qui convient :
> mysqldump -S /var/run/mysqld5/mysqld5.sock 'glpi-el' --flush-logs --complete-insert --max_allowed_packet=1G --single-transaction --quick --lock-tables=false -u assistance -p > /var/backups/bdd_backups/mysql56/el

Pour l'import : ne pas utiliser mysqldump à distance. Ni mysqldump en socket internet mais en local :
base glpi-ensl par exemple :
> mysql -S /var/run/mysqld5/mysqld5.sock -u root -p
> mysql> \r glpi-ensl
> mysql> \. /var/backups/bdd_backups/mysql56/el
> ________________________________________________

Voilà avec ceci c'est bon.

[Artemus24]

Salut Falco-.

Et si votre importation avait planté avec le paramètre "max_allowed_packet=1G" ?
Vous avez eu de la chance que ca passe.

Avec ma technique de saucissonnage, je n'ai pas de goulot d'étranglement. Ca passe dans tous les cas.
Et en plus, je n'ai pas besoin de modifier le paramètre "max_allowaed_packet".
Il reste chez moi à sa valeur par défaut de 16M.

Combien de temps à durée l'export ainsi que l'import ?

Cordialement.
Artemus24.
@+