Database overzetten
Iedere nacht dienen ongeveer 10.000 specifieke regels overgezet te worden van een MySql database op één server naar een andere MySql database op een andere server. Het gaat om een aantal specifieke regels uit een database, dus niet de gehele database.
- Ik heb nu op een server een script draaien die alle data in regels zet doormiddel van een while loop met daarin een MySqli query.
- Op de andere server heb ik een script draaien die doormiddel van: file_get_contents alle data binnenhaalt en daarna in de database zet.
- Het script wordt doormiddel van een Cronjob iedere nacht uitgevoerd.
Het script dat alle data klaarzet duurt eventjes, het ophalen van de data met de while loop kost tijd.
Ik ben bang als het uiteindelijk 50.000 regels worden het script te lang gaat duren.
Is hier een andere slimmere manier voor?
Direct connecten met de andere database (dus tussen de twee servers in is geen optie)
Ik ben benieuwd!
Verder zou het importeren van 50.000 regels een peulenschil zijn. Wekelijks heb ik zelfs een cronjob lopen die eventjes 100MB aan data in een MySQL-database pompt. Werkt ook prima.
En zolang je dit via cronjob uitvoert is er geen limiet om je zorgen over te houden.
Het is optimalisatie. Als het slimmer/sneller kan hoor ik het graag ;-).
Alle data wordt nu doormiddel van een while loop er doorheen gehaald. Kan dit ook niet in eenmaal gedumpt worden?
Ik heb een beetje rondgeneusd op internet, en ik kwam op dit uit:
Code (php)
1
mysqldump --where "1=1 ORDER BY id DESC LIMIT 1000" databasenaam tabelnaam > dump.sql
Dit zou de laatste 1000 items uit een tabel moeten dumpen in een file.
Gewijzigd op 05/02/2018 13:15:16 door - Ariën -
En als je nu eens deze "push" verandert in een "pull"? Dump de database op server A. Dan wanneer op server B een recentere kopie nodig is haal je deze met FTP binnen en importeer je die via een mysql shell command.
Wat is de reden dat nu deze import zo traag is trouwens? 10k zou een peuleschil moeten zijn. Ben je via PHP SQL-statements aan het uitvoeren ofzo?
https://dev.mysql.com/doc/refman/5.7/en/select-into.html
Krachtig en snel exporteren SELECT ... INTO OUTFILE en importeren LOAD DATA INFILE.
SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE. Column values are written converted to the character set specified in the CHARACTER SET clause. If no such clause is present, values are dumped using the binary character set. In effect, there is no character set conversion. If a result set contains columns in several character sets, the output data file will as well and you may not be able to reload the file correctly.
Een voorbeeldje van LOAD DATA INFILE alhoewel dat misschien het probleem van je klant is...
Code (php)
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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
LOAD DATA INFILE '/home/rws/data/info.csv'
INTO TABLE vaarweginfo
CHARACTER SET utf8
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@var4
,trajectbeperking
,vaarweg
,object
,beperking
,positie_gedeelte
,aanduiding
,waarde
,referentie
,doelgroep
,richting
,@var1
,periode_starttijd
,@var5
,periode_eindtijd
,periode_interval
,onderwerp
,reden
,land
,gebied
,@var2
,@var3
,bron_instantie
,subinstantie
,extra_informatie
,meldingsregime
,communicatie
,nummerofadres)
SET id = null,
notice = REPLACE(@var4,'Rijkswaterstaat.',''),
periode_van = STR_TO_DATE(@var1,'%d-%m-%Y'),
geldig_van = STR_TO_DATE(@var2,'%d-%m-%Y'),
geldig_tot = STR_TO_DATE(@var3,'%d-%m-%Y'),
periode_tot = STR_TO_DATE(@var5,'%d-%m-%Y'),
datetime = NOW()
;
INTO TABLE vaarweginfo
CHARACTER SET utf8
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@var4
,trajectbeperking
,vaarweg
,object
,beperking
,positie_gedeelte
,aanduiding
,waarde
,referentie
,doelgroep
,richting
,@var1
,periode_starttijd
,@var5
,periode_eindtijd
,periode_interval
,onderwerp
,reden
,land
,gebied
,@var2
,@var3
,bron_instantie
,subinstantie
,extra_informatie
,meldingsregime
,communicatie
,nummerofadres)
SET id = null,
notice = REPLACE(@var4,'Rijkswaterstaat.',''),
periode_van = STR_TO_DATE(@var1,'%d-%m-%Y'),
geldig_van = STR_TO_DATE(@var2,'%d-%m-%Y'),
geldig_tot = STR_TO_DATE(@var3,'%d-%m-%Y'),
periode_tot = STR_TO_DATE(@var5,'%d-%m-%Y'),
datetime = NOW()
;
Middels je SELECT maak je een mooie OUTFILE klaar die met LOAD DATA INFILE ingelezen kan worden en dat kan zonder php razend snel tot meer dan 100.000 records.
Zowel met de SELECT waarmee je een complexe select kan doen als ook met de LOAD DATA INFILE kan je data verrijking/aanpassing (conversie) doen.
Gewijzigd op 05/02/2018 21:35:07 door Aad B
Nu nog even uitvinden hoe dit werkt.
(als hier nog ergens een simpele tut over is dan is dat altijd welkom!)
Code (php)
1
2
2
# m h dom mon dow command
30 23 * * * /usr/bin/mysql -ugebruiker -pwachtwoord -e "source /home/rws/import.sql" vaarweginfo
30 23 * * * /usr/bin/mysql -ugebruiker -pwachtwoord -e "source /home/rws/import.sql" vaarweginfo
De import.sql is het file zoals eerder genoemd met LOAD INFILE
Gewijzigd op 06/02/2018 12:23:37 door Aad B
Gewijzigd op 06/02/2018 12:06:36 door - Ariën -
Aad B op 06/02/2018 11:37:15:
Voorbeeldje commandline cron
Wellicht is het wel veiliger om informatie uit een config-file te trekken in plaats van deze meegeven in een shell command. Dit kun je doen met de --defaults-file parameter die naar je (readonly) my.cnf wijst.
Gewijzigd op 06/02/2018 16:36:02 door Thomas van den Heuvel