> In MySQL, by parsing the output of SHOW CREATE TABLE. > > It would be a boon if someone were to write a utility, in an OS-independent > language, which does that parsing for all tables in a MySQL database and > returns SQL output that's suitable for creating a set of system tables. > Obviously bits of this task have already been accomplished by the folks who > wrote the MySQL module in PHP, for example, and for all we know, much of the > code for doing it may already exist in phpMyAdmin. > > PB
Well it is not OS-independent, nor free, but the part that does the job you wish for is actually free and can easily become OS independent. MyCon (http://www.scibit.com/products/mycon) comes with a commandline utility called MyRun which it uses in the background to backup databases or single tables or groups of tables or general scripts' data or to schedule these tasks with. It has full helpfile on the command-line parameters and options, some of the output script options can optionally include "drop if exists" statements, "create table" and the actual data, all neatly parsed into SQL statements ready to recreate just your schema and/or all data as well. It can also dump to another server live instead of to an output script file. It has been optimized to dump an average size table's data at roughly 1000recs/sec on a 1xCPU3GHz with MySQL running localhost using only enough client-side memory for a single record at any given time. In short is takes any source script (which you can setup yourself or use MyCon to auto create it for you),ex: --- select * from accounts; <SNIP>...etc select * from visits left join countries using (CountryID); select * from servicerequests where ServDate>YEAR(CURDATE()); <SNIP>...etc --- And then produces something like this: -- MyRun -- Source Server: <SNIPPED> -- Source Script: Tables.Scheduled.Run.All.sql -- Target Script: Tables.Scheduled.Backup.All.sql -- Target: Tables.Scheduled.Backup.All.sql -- Date: 2004-08-11 09:00:02 500 -- SCRIPT SQL: select * from accounts; -- DROP: accounts drop table if exists accounts; -- CREATE: accounts create table if not exists `accounts` ( `AccountID` int(10) NOT NULL auto_increment, `AccountEmail` varchar(50) default NULL, <SNIP>..etc PRIMARY KEY (`AccountID`), KEY `AccountStatusID` (`AccountStatusID`), KEY `AccountTypeID` (`AccountTypeID`) ) TYPE=MyISAM; -- DATA: accounts BATCH #1 insert into accounts (AccountID,AccountEmail,<SNIP>..etc) values <SNIP> it then proceeds to add batches of 100 recs per INSERT statement -- DATA: accounts BATCH #2 <SNIP>..etc As said, the DROP, CREATE and DATA are all optional. MyRun is ofcourse not the only utility to do this, mysqldump has been around forever and just about every other MySQL GUI includes this type of functionality. That said, if there is really a demand for something like this, I am sure we can put MyRun into both a linux and win32 CGI which can then be prompted from a website. MyRun's source is OS independent, so this won't be an issue. The current version is win32 and although the scripting side of it is really small, it got a bit bloated because it also includes code to generate live PDF,XLS,RTF,etc reports from MySQL data, which means the report modules made it "heavy". So a cgi would more than likely fall into the <200Kb footprint range, instead of >1Mb. Kind Regards SciBit MySQL Team http://www.scibit.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
