I have a problem while uploading database with key-connection between the tables.
Script works well as far as there is no connection between the tables.
What MySql command do I have to include in my php class to drop all foreign keys ? So I'd be able to drop the tables and then after all tables have been created add the key-connection back onto the tables.
Here it is the class I am using:
<?php
class BackupDB
{
private $host = '';
private $username = '';
private $passwd = '';
private $dbName = '';
private $charset = '';
function __construct($host, $username, $passwd, $dbName, $charset = 'utf8')
{
$this->host = $host;
$this->username = $username;
$this->passwd = $passwd;
$this->dbName = $dbName;
$this->charset = $charset;
$this->initializeDatabase();
}
protected
function initializeDatabase()
{
$conn = mysql_connect($this->host, $this->username, $this->passwd);
mysql_select_db($this->dbName, $conn);
if (!mysql_set_charset($this->charset, $conn))
{
mysql_query('SET NAMES ' . $this->charset);
}
}
/**
* Backup the whole database or just some tables
* Use '*' for whole database or 'table1 table2 table3...'
* @param string $tables
*/
public
function backupTables($tables = '*', $outputDir = '.')
{
try
{
/**
* Tables to export
*/
if ($tables == '*')
{
$tables = array();
$result = mysql_query('SHOW TABLES');
while ($row = mysql_fetch_row($result))
{
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(',', $tables);
}
$sql = 'CREATE DATABASE IF NOT EXISTS ' . $this->dbName . ";\n\n";
$sql.= 'USE ' . $this->dbName . ";\n\n";
/**
* Iterate tables
*/
foreach($tables as $table)
{
echo "Backing up " . $table . " table...";
$result = mysql_query('SELECT * FROM ' . $table);
$numFields = mysql_num_fields($result);
$sql.= 'DROP TABLE IF EXISTS ' . $table . ';';
$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE ' . $table));
$sql.= "\n\n" . $row2[1] . ";\n\n";
for ($i = 0; $i < $numFields; $i++)
{
while ($row = mysql_fetch_row($result))
{
$sql.= 'INSERT INTO ' . $table . ' VALUES(';
for ($j = 0; $j < $numFields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = str_replace("\n", "\\n", $row[$j]);
if (isset($row[$j]))
{
$sql.= '"' . $row[$j] . '"';
}
else
{
$sql.= '""';
}
if ($j < ($numFields - 1))
{
$sql.= ',';
}
}
$sql.= ");\n";
}
}
$sql.= "\n\n\n";
echo " OK" . "<br />";
}
}
catch(Exception $e)
{
var_dump($e->getMessage());
return false;
}
return $this->saveFile($sql, $outputDir);
}
/**
* Save SQL to file
* @param string $sql
*/
protected
function saveFile(&$sql, $outputDir = '.')
{
if (!$sql) return false;
try
{
$outputfilename = $outputDir . '/db-backup-' . $this->dbName . '-' . date("d.m.Y_H.i.s") . '.sql';
$result = mysql_query('INSERT INTO backuplog (backup) VALUES ("' . $outputfilename . '")');
$handle = fopen($outputfilename, 'w+');
fwrite($handle, $sql);
fclose($handle);
echo '<span class="message">Zapisano ' . $outputfilename . ' link do bazy. </span>';
echo '<span class="message">Właśnie pobrano kopie zapasową. Dziękujemy Serdecznie. Życzymy miłego dnia.</span>';
}
catch(Exception $e)
{
$result = mysql_query('DELETE FROM backuplog WHERE backup ="' . $outputfilename . '"');
var_dump($e->getMessage());
echo '<span class="error">Notacja.Udało się pobrać bazedanych... ale #NIE zaladowano linku do formularza, aby odzyskać dane trzeba ręcznie wpisać nazwę folderu/orazpliku.sql</span>';
echo '<span class="error">UWAGA! Wystąpił błąd podczas zapisywania danych w bazie... Zadanie nie ukończone.</span>';
return false;
}
return true;
} //end f
public
function loadDB($filename)
{
// $result=exec('mysql --user='.$this->user.' --password='.$this->pass .'<DatabaseBackUp/'.$filename);
// mysql -u user_name -p <file_to_read_from.sql
$templine = '';
// Read in entire file
$lines = file($filename);
// Loop through each line
foreach($lines as $line)
{
// Skip it if it's a comment
if (substr($line, 0, 2) == '--' || $line == '') continue;
// Add this line to the current segment
$templine.= $line;
// If it has a semicolon at the end, it's the end of the query
if (substr(trim($line) , -1, 1) == ';')
{
// Perform the query
if (!mysql_query($templine))
{
echo "Błąd ładownia pliku.sql";
return false;
} //echo $templine //TEST!
// Reset temp variable to empty
$templine = '';
}
}
return true;
} //end function
} //End calss
/* USE EXAMPLE
// DOWNLOAD DB FROM SERVER AS SQL FILE TO
$backupDatabase = new BackupDB(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$status = $backupDatabase->backupTables(TABLES, OUTPUT_DIR) ? 'OK' : 'KO';
echo "<br /><br /><br />Backup result: ".$status;
// RETRIVE DATA FROM SQL FILE TO DATABASE
$backupDatabase->loadDB("Path/to/mysqlfile.sql");
*/
?>
If you know how to extract foreign keys from a database, drop them one after the other, delete all tables and then put the keys back on again, please share your knowledge. Thanks again for looking into it.
Again for those who like to use this script it works fine... if! tables in database are not connected with foreign keys. Let's hope this state will soon change.
Short example of outputed .sql file:
CREATE DATABASE IF NOT EXISTS DATABASEONE;
USE DATABASEONE;
DROP TABLE IF EXISTS st_glowne_st_pages;
CREATE TABLE `st_glowne_st_pages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fk_glowne` varchar(50) COLLATE utf8_polish_ci NOT NULL,
`fk_pages` varchar(50) COLLATE utf8_polish_ci NOT NULL,
`kolejnosc` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`),
KEY `fk_glowne` (`fk_glowne`),
KEY `fk_pages` (`fk_pages`),
CONSTRAINT `st_glowne_st_pages_ibfk_1` FOREIGN KEY (`fk_glowne`) REFERENCES `st_glowne` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `st_glowne_st_pages_ibfk_2` FOREIGN KEY (`fk_pages`) REFERENCES `st_pages` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
INSERT INTO st_glowne_st_pages VALUES("1","admin.php","pageadmin.php","1");
INSERT INTO st_glowne_st_pages VALUES("3","index.php","pageindex.php","1");
INSERT INTO st_glowne_st_pages VALUES("4","work.php","pagework.php","1");
INSERT INTO st_glowne_st_pages VALUES("7","register.php","pageregister.php","1");
INSERT INTO st_glowne_st_pages VALUES("8","login.php","pagelogin.php","1");
