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)
De vraag is eigenlijk, waarom moet het in een andere database staan? Wat is er mis met de huidige database?
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.
De klant wenst graag een kopie van zijn eigen data in een eigen database en wil zelf ook "rommelen" in de data. Dat mag niet binnen onze eigen database.
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?
Dat garandeert toch op geen enkele manier een consistente dataset? Een cron die ~10k regels overzet garandeert dit evenmin. En als het proces op een of andere manier hapert ga je ook nat omdat je dan op een gegeven moment gaten in je data hebt als je enkel verschillen overzet.
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?
mysqldump of export en import op specifieke records is de oplossing. PHP met while loopjes dat steeds heen en weer communiceert naar de MySQL engine kost enorm veel tijd. Niet met PHP oplossen dus. 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...
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()
;
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.
Het begint met het aanmaken van je output, in feite werkt dit met een gewoon SQL statement. Bouw dus (een) SQL statement(s) waarmee je de data naar (csv) file(s) exporteert. De benodigde delimiters (quotes,komma's etc) selecteer je gewoon mee binnen je SQL, verder gebruik je SELECT ... INTO OUTFILE om te exporteren naar file. Het SQL script zet je in de cron. Voor de import maak je (per tabel) een LOAD DATA INFILE aan zoals boven en ook dat is in de cron te plaatsen. Ik werk altijd met commandline en cron op OS niveau (linux). Ik weet niet hoe dat eventueel met tooling (phpadmin e.a. moet, ik heb een hekel aan click tooling) gedaan moet worden. Voorbeeldje commandline cron
# m h dom mon dow command
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
Inderdaad, het is much better om dit via de CLI te doen. Ikzelf gebruik die GUI-tooltjes alleen om een kijkje in de database te nemen. Het dumpen/importen van databases doe ik uitsluitend via CLI. Met PhpMyAdmin is het een hel te noemen.
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.
