Database overzetten

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Martijn De Vries

Martijn De Vries

05/02/2018 12:49:08
Quote Anchor link
Beste,

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!
 
PHP hulp

PHP hulp

21/05/2019 01:44:25
Honeypot
 
- Ariën -
Beheerder

- Ariën -

05/02/2018 12:53:23
Quote Anchor link
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.
 
Martijn De Vries

Martijn De Vries

05/02/2018 12:59:52
Quote Anchor link
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?
 
- Ariën -
Beheerder

- Ariën -

05/02/2018 13:15:04
Quote Anchor link
Je zou het met een cronjob ook prima kunnen dumpen met mysqldump.

Ik heb een beetje rondgeneusd op internet, en ik kwam op dit uit:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
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 -
 
Thomas van den Heuvel

Thomas van den Heuvel

05/02/2018 15:03:47
Quote Anchor link
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?
 
Aad B

Aad B

05/02/2018 20:33:22
Quote Anchor link
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...
Code (php)
PHP script in nieuw venster Selecteer het PHP script
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
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.
Gewijzigd op 05/02/2018 21:35:07 door Aad B
 
Martijn De Vries

Martijn De Vries

05/02/2018 21:47:36
Quote Anchor link
Aad B heeft wel dé tip naar mijn mening.
Nu nog even uitvinden hoe dit werkt.

(als hier nog ergens een simpele tut over is dan is dat altijd welkom!)
 
Aad B

Aad B

06/02/2018 11:37:15
Quote Anchor link
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
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
# 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
Gewijzigd op 06/02/2018 12:23:37 door Aad B
 
- Ariën -
Beheerder

- Ariën -

06/02/2018 12:06:13
Quote Anchor link
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.
Gewijzigd op 06/02/2018 12:06:36 door - Ariën -
 
Thomas van den Heuvel

Thomas van den Heuvel

06/02/2018 16:35:44
Quote Anchor link
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
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.