List:General Discussion« Previous MessageNext Message »
From:Joseph Bueno Date:December 17 2002 12:39pm
Subject:Re: How can I duplicate a mysql template database? [hack]
View as plain text  
I am not a PHP expert but I think you can run external commands from PHP,
so it should be possible to run mysqladmin, mysqldump and mysql from PHP.
This will work even if you database is on a remote server; just specify
the hostname (ex: "mysqladmin -h mysqlhost create db2")


Daevid Vincent wrote:
> 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