data transfer tussen 2 servers

Zijn er geen mogelijkheden om dit direct via 'mysqldump' te doen?

Als in je MySQL-server de 'federated' storage engine aanstaat (staat niet standaard aan), kun je dit heel eenvoudig met federated tables oplossen. Een federated table is een tabel die op je lokale server staat, maar die de data van een remote server haalt. Omdat de federated table een lokale tabel is, kun je de inhoud heel eenvoudig kopiëren naar een andere tabel.

Stel dat je table 'remotetable' van 'remotehost' wilt kopiëren naar "localtable" op "localhost", dan volg je ongeveer deze stappen:

1) Op remotehost vraag je op hoe de tabel is aangemaakt: "SHOW CREATE TABLE remotetable". Je krijgt dan iets als

CREATE TABLE `remotetable` (
  `id` bigint(20) unsigned NOT NULL,
  `datum` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `datum` (`datum`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Op basis van deze query kun je een federated table aanmaken op "localhost". De tabeldefinitie moet in principe exact gelijk zijn. De tabelnaam mag je wel wijzigen en de engine moet sowieso gewijzigd worden.

2) Maak de benodigde wijzigingen aan query en maak de federated table aan:

CREATE TABLE `federatedtable` (
  `id` bigint(20) unsigned NOT NULL,
  `datum` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `datum` (`datum`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1
  CONNECTION='mysql://username:[email protected]/remotedatabase/remotetable'

Als username en password moet je de gebruikersnaam van de remote server gebruiken.

3) Je kan nu de tabel kopiëren:

INSERT INTO localtable SELECT * FROM federatedtable;

(hierbij ga ik ervan uit dat localtable dezelfde tabeldefinitie heeft als remotetable)


In principe, als je alleen maar wilt kunnen joinen met een remote tabel, hoef je de kopieerslag niet eens uit te voeren en kun je de federated table gewoon gebruiken in je queries. Let in dat geval wel op de haken en ogen (zie documentatie).

Niet getest en zonder foutafhandeling:

<?php
$sDb = new mysqli('srv1.source.com', 'root', 'wortel', 'source_db');
$dDb = new mysqli('localhost', 'root', 'wortel', 'dest_db');
$table = 'test_table';

// get the ddl
$ddl = $sDb->query("SHOW CREATE TABLE " . $table);
$ddlRow = $ddl->fetch_row();
$ddlSql = $ddlRow[1];

// get columns
$cResult = $sDb->query("SHOW COLUMNS IN ". $table);
$fields = array();
$nullables = array();

while ($columns = $cResult->fetch_row()) {
	$fields[] = $columns[0];
	if(strtoupper($columns[2]) == 'YES') {
		$nullables[] = $columns[0];
	}
}

// create the destination table
$dDb->query($ddlSql);

// initial insert statement
$insert = 'INSERT INTO ' . $table . '(' . implode(',', $fields) . ') VALUES ';

// get records from source table
$src_data = $sDb->query('SELECT '. implode(',', $fields) . ' FROM ' . $table);
$recCount = 0;
$valuesString = '';
while ($src_record = $src_data->fetch_assoc()) {
	if ($recCount > 0) {
		$valuesString .= ', ';
	}
	$recCount++;
	$valuesString .= '(';	
	foreach ($src_record as $field => $value) {
		$values = array();
		if(in_array($field, $nullables) && $value == '') {
			$iValue = 'NULL';
		}
		else {
			$iValue = "'" . $value . "'";
		}
		$values[] = $iValue;
	}
	$valuesString .= implode(',', $values) . ')';
}
$dDb->query($insert . $valuesString);
?>

Hallo Ger,

Ik heb je code een beetje aangepast en in een functie gegoten en het werkt perfect! Bedankt alvast! Hieronder de finale functie:

<?php
function copyTable($table){
	$sDb = new mysqli('source.url.be', 'root', 'paswoord', 'table');
	$dDb = new mysqli('destination.url.be', 'root', 'paswoord', 'table');

	// delete the destination table
	if(!$dDb->query("DROP TABLE $table")){
		printf("Error deleting destination table: %s\n", $dDb->error);
	}

	// get the ddl
	$ddl = $sDb->query("SHOW CREATE TABLE " . $table);
	$ddlRow = $ddl->fetch_row();
	$ddlSql = $ddlRow[1];

	// get columns
	$cResult = $sDb->query("SHOW COLUMNS IN ". $table);
	$fields = array();
	$nullables = array();
	
	// check what columns are NULL columns
	while ($columns = $cResult->fetch_row()) {
		$fields[] = $columns[0];
		if(strtoupper($columns[2]) == 'YES') {
			$nullables[] = $columns[0];
		}
	}
	// create the destination table
	if(!$dDb->query($ddlSql)){
		printf("Errormessage: %s\n", $dDb->error);
	}
	// initial insert statement
	$insert = 'INSERT INTO ' . $table . '(' . implode(',', $fields) . ') VALUES ';

	// get records from source table
	$src_data = $sDb->query('SELECT '. implode(',', $fields) . ' FROM ' . $table);
	$globalRecCount = 0;
	$recCount = 0;
	$valuesString = '';
	while ($src_record = $src_data->fetch_assoc()) {
		if ($recCount > 0) {
			$valuesString .= ', ';
		}
		$valuesString .= '(';    
		$values = array();
		// set to 'NULL' if field is NULL field with empty value
		foreach ($src_record as $field => $value) {
			if(in_array($field, $nullables) && $value == '') {
				$iValue = 'NULL';
			}
			else {
				$iValue = "'" . $value . "'";
			}
			$values[] = $iValue;
		}
		$valuesString .= implode(',', $values) . ')';
		// group SQL inserts with 50 <-- better for large tables
		if($recCount > 50){
			$dDb->query($insert . $valuesString);
			$recCount = 0;
			$valuesString = '';
		}else{
			$recCount++;
		}
		$globalRecCount ++;
	}
	// execute remaining inserts
	if($recCount > 0){
		$dDb->query($insert . $valuesString);
	}
	return $table . " is gekopieerd. (" . $globalRecCount . " records)<br />";
}
?>

Hou alleen wel rekening mee als je een hele grote database hebt en dit via een browser doet dat je een maximale executie tijd hebt.

Je kan het ook doen via de cli.
Maar dan zou de optie van willem vp misschien meer voor de hand liggen.
Dan is er geen tussenkomst van php.

>> group SQL inserts with 50 <-- better for large tables

Ik weet niet waar die wetenschap vandaan komt, maar dat is onnodig (tenzij je grote text/blobs in de tabel hebt).

> // set to 'NULL' if field is NULL field with empty value

Strict gezien klopt dit niet. NULL betekent: deze waarde bestaat niet (of het is niet bekend of de waarde bestaat). Een lege waarde betekent: deze waarde bestaat en is leeg.

> // group SQL inserts with 50 <-- better for large tables

Met zulke kleine chunks vraag ik me af of het niet juist slechter is. ;-) Het opbouwen van een query door de server geeft overhead, en 50 records is best weinig voor MySQL. Je kan zonder problemen opschalen naar 1000 records of zelfs nog meer. In de praktijk pomp ik data soms met een miljoen records tegelijk over. Dat duurt weliswaar zo'n anderhalve minuut, maar ik heb nog nooit gemerkt dat MySQL daar moeite mee heeft.

NULL in sql is we zullen het nooit weten.
Het probleem is dat in de dataset een lege string krijgt bij kolommen die geen waarde hebben.
Bij niet numerieke en niet date(time) datatypes kan je nooit zeker weten of de kolom geen waarde bevat of een lege string (dat is in SQL een waarde)

> Het probleem is dat in de dataset een lege string krijgt bij kolommen die geen waarde hebben.

Als ik een varchar heb met een (SQL) NULL-waarde, dan leest PHP die gewoon binnen als (PHP) NULL-waarde. Of begrijp ik je verkeerd?

Denk het wel, als ik in een varchar kolom een '' heb staan of helemaal niets maakt voor PHP niets uit, die ziet het als hetzelfde.