List:General Discussion« Previous MessageNext Message »
From:Joseph Bueno Date:December 17 2002 10:47am
Subject:Re: How can I duplicate a mysql template database? [hack]
View as plain text  
Hi,

There is an easier way to duplicate a database; from the comand
line, you can run:

mysqladmin create db2
mysqldump db1 | mysql db2

If you want to duplicate the schema only (without the data):
mysqladmin create db2
mysqldump --no-data db1 | mysql db2

(You may have to add host/user/password options but I just wanted to
show the idea).

Hope this helps,
Joseph Bueno

Daevid Vincent wrote:
> Seems to me there should be a built in SQL command to duplicate a
> database. Jeepers. Or to read in a .sql file from PHP and create a
> database out of it (which was the original intent).
> 
> Anyways, here's a 'hack'. I'd still love to hear anyone else's more
> elegant solution.
> 
> $V2DB = "V2_SL".$CompanyID;
> 
> $result = mysql_create_db($V2DB, $linkI);
> if (!$result) $errorstring .= "Error creating ".$V2DB."
> database<BR>\n".mysql_errno($linkI).": ".mysql_error($linkI)."<BR>\n"; 
> 
> mysql_select_db ($V2DB, $linkI) or die ("Could not select ".$V2DB."
> Database");
> /*
> //TODO: None of these below here work. Ugh! so frustrating!!
> //$filename = "/mypath/todb/V2_DB.sql";
> //$fd = fopen ($filename, "r");
> //$sql = fread ($fd, filesize ($filename));
> //fclose ($fd);
> 
> //$lines = file($filename);
> //foreach ($lines as $line_num => $line) { $sql .= $line; }
> 
> //$sqlTables = explode(";",$sql);
> //foreach ($sqlTables as $table)
> //{
> //	echo "<PRE>$table</PRE><p><hr>\n";
> //	$result = mysql_query($sql,$linkI);
> //	if (!$result) $errorstring .= "Error creating ".$V2DB."
> ".$table." table<BR>\n".mysql_errno($linkI).":
> ".mysql_error($linkI)."<BR>\n"; 
> //}
> */
> 
> //You must have already created the "V2_Template" database. 
> //This will make a clone of it, including data.
> 
> $tableResult = mysql_list_tables ("V2_Template");
> while ($row = mysql_fetch_row($tableResult)) 
> {
> 	$tsql = "CREATE TABLE ".$V2DB.".".$row[0]." AS SELECT * FROM
> V2_Template.".$row[0];
> 	echo $tsql."<BR>\n";
> 	$tresult = mysql_query($tsql,$linkI);
> 	if (!$tresult) $errorstring .= "Error creating
> ".$V2DB.".".$row[0]." table<BR>\n".mysql_errno($linkI).":
> ".mysql_error($linkI)."<BR>\n"; 
> }
> 
> 
> 
>>-----Original Message-----
>>From: Daevid Vincent [mailto:daevid@stripped] 
>>Sent: Tuesday, December 17, 2002 1:16 AM
>>To: mysql@stripped
>>Cc: 'Bill Lovett'
>>Subject: RE: How can I duplicate a mysql template database?
>>
>>
>>Thanks for the reply Bill, however this did not solve the problem. I
>>still receive the same error with your method as well.
>>
>>1064: You have an error in your SQL syntax near ';
>>CREATE TABLE Departments (
>>  DeptID int(10) unsigned NOT NULL auto_increment,' at line 4
>>
>>
>>>Are you first creating a new database first with mysql_create_db() 
>>>before trying to run your schema? What error message do you 
>>
>>get? The 
>>
>>>code you've posted is only creating a table.
>>
>>Yes. I am:
>>
>>$V2DB = "V2_SL".$CompanyID;
>>$result = mysql_create_db($V2DB, $linkI);
>>mysql_select_db ($V2DB, $linkI) or die ("Could not select ".$V2DB."
>>Database");
>>
>>
>>>Also, rather than opening and reading in an external file or 
>>>doing all 
>>>those $sql .= lines, you might have an easier time using 
>>>heredoc syntax, 
>>>which would let you do something like
>>>
>>>$sql = <<<SQL
>>>CREATE TABLE IP_Dept (
>>>   IP_Addr int(10) unsigned NOT NULL default
>>>   DeptID int(10) unsigned NOT NULL default
>>>
>>>   ...etc
>>>
>>>SQL;
>>>
>>>$result = mysql_query($sql,$linkI);
>>
>>Thanks for that tip, however it fails the same as opening the file and
>>same as "$sql .="
>>
>>
>>>(hopefully $linkI is your mysql connection) This way you 
>>>don't have to bother with all that quoting.
>>
>>Yes, $linkI is my connection identifier.
>>
>>I just have an awful feeling that PHP/mySQL won't let me 
>>stack commands
>>like that because if I just do one table, like this: 
>>
>>$sql = <<<SQL
>>CREATE TABLE Schedule (
>>  ScheduleID int(10) unsigned NOT NULL auto_increment,
>>  TargetRange char(255) default NULL,
>>  ScannerID int(10) unsigned NOT NULL default '0',
>>  RunEvery char(50) default NULL,
>>  NextRun datetime default NULL,
>>  LastRun datetime default NULL,
>>  PRIMARY KEY  (ScheduleID)
>>) TYPE=MyISAM;
>>SQL;
>>
>>It "works". However I'm dreading doing this one table at a time. Grr.
>>
>>
>>>Daevid Vincent wrote:
>>>
>>>>I need to use PHP to duplicate the schema of a mysql 
>>
>>database. This
>>
>>>>seems like it should be a simple task -- taking a 
>>
>>'template' db and
>>
>>>>cloning it with a new name. 
>>>>
>>>>I've tried things like:
>>>>
>>>>		$filename = "myDB.sql";
>>>>		$fd = fopen ($filename, "r");
>>>>		$sql = fread ($fd, filesize ($filename));
>>>>		fclose ($fd);
>>>>
>>>>And 
>>>>
>>>>		$lines = file($filename);
>>>>		foreach ($lines as $line_num => $line) { $sql .= $line;
>>>>}
>>>>
>>>>And
>>>>		$sql .= "CREATE TABLE IP_Dept (";
>>>>		$sql .= "  IP_Addr int(10) unsigned NOT NULL default
>>>>'0',";
>>>>		$sql .= "  DeptID int(10) unsigned NOT NULL default
>>>>'0'";
>>>>		$sql .= ");";
>>>>		
>>>>		$sql .= "CREATE TABLE ResolveTable (";
>>>>		$sql .= "  IP_Addr int(10) unsigned NOT NULL default
>>>>'0',";
>>>>		$sql .= "  Name char(255) NOT NULL default '',";
>>>>		$sql .= "  Custom char(1) default NULL,";
>>>>		$sql .= "  Global char(1) default 'Y',";
>>>>		$sql .= "  OSVersion char(255) default NULL,";
>>>>		$sql .= "  RowID int(10) unsigned NOT NULL
>>>>auto_increment,";
>>>>		$sql .= "  Display enum('Yes','No') NOT NULL default
>>>>'Yes',";
>>>>		$sql .= "  PRIMARY KEY  (RowID)";
>>>>		$sql .= ");";
>>>>		
>>>>	echo "<PRE>".$sql."</PRE><P>";
>>>>	$result = mysql_query($sql,$linkI);
>>>>
>>>>But ALL of them fail! Ugh!!! Can I not stack commands 
>>
>>like that? Is
>>
>>>>there some way to read in a .sql file via PHP?  The problem 
>>>
>>>is that my
>>>
>>>>web pages are on a web server and the db is on a mysql 
>>>
>>>server which are
>>>
>>>>different machines, so calling a system() or other execute style
>>>>function won't work for me.
>>>>
>>>>I figured, "Ah! Why don't I just make a template db on the 
>>>
>>>server and
>>>
>>>>issue some SQL command to 'clone' that and rename it". 
>>>
>>>You'd think that
>>>
>>>>was pretty straight forward, but I can't find any examples 
>>>
>>>or commands
>>>
>>>>to do this seemingly trivial task. Ideas?
>>>>

Thread
How can I duplicate a mysql template database?Daevid Vincent17 Dec
  • Re: How can I duplicate a mysql template database?Bill Lovett17 Dec
    • RE: How can I duplicate a mysql template database?Daevid Vincent17 Dec
      • RE: How can I duplicate a mysql template database? [hack]Daevid Vincent17 Dec
        • Re: How can I duplicate a mysql template database? [hack]Joseph Bueno17 Dec
          • RE: How can I duplicate a mysql template database? [hack]Daevid Vincent17 Dec
            • Re: How can I duplicate a mysql template database? [hack]Joseph Bueno17 Dec
      • Re: How can I duplicate a mysql template database?David T-G17 Dec
        • RE: How can I duplicate a mysql template database?Daevid Vincent17 Dec
          • Re: How can I duplicate a mysql template database?Joseph Bueno17 Dec
          • Re: How can I duplicate a mysql template database?David T-G17 Dec