List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:December 17 2002 12:25pm
Subject:RE: How can I duplicate a mysql template database? [hack]
View as plain text  
Unfortunately, command line is not an option for me. As I said before,
the php scripts are on a web server and the database is on another
server.

> -----Original Message-----
> From: Joseph Bueno [mailto:joseph.bueno@stripped] 
> Sent: Tuesday, December 17, 2002 2:47 AM
> To: mysql@stripped
> Subject: Re: How can I duplicate a mysql template database? [hack]
> 
> 
> 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?
> >>>>
> 
> 
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
> 
> To request this thread, e-mail <mysql-thread127675@stripped>
> To unsubscribe, e-mail 
> <mysql-unsubscribe-daevid=daevid.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 

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