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:[EMAIL PROTECTED]]
>>Sent: Tuesday, December 17, 2002 1:16 AM
>>To: [EMAIL PROTECTED]
>>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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
