[lug] mysql table creation scripts

[D. Stimits]

John Starkey wrote:
> 
> > There are also dumps of table structures in several databases of the
> > mysql system, which I'd like to use to recreate, but I also do not want
> > to use mouse copy and paste if not needed. I can use the LOAD command
> > for actual data, but does anyone here know if I can directly source a
> > file for SQL commands (the table creation scripts)?
> 
> Create the script and do:
> 
> mysql -u root -p < script_file
> 
> It will prompt you for the password and then do the deed.
> 
> script_file should contain just the
> 
> create table whatever (
> field1 text,
> RecordID int not null auto_increment,
> primary key( id ));
> 
> You get the pic.

This is *very* close to what I need. The one problem remaining is that
the mysqldump does not specify which db a given table belongs to; e.g.,
it might say a table name as "tablename", but if it belongs to a db
called "dbname", it fails to dump as "dbname.tablename". And the mysql
command does not offer a command line means to specify which db to use
on the command line. So I must still manually edit all the mysqldump
scripts to explicitly use the syntax "dbname.tablename" instead of just
"tablename".

D. Stimits, stimits at idcomm.com

> 
> > Does anyone have an idea of a better way to use a mysqldump to make a
> > duplicate of that dump on another machine with a freshly installed
> > system? Updating passwords one at a time, mouse pasting table creation,
> > so on, seems rather inefficient. And although LOAD of data from the
> > dumped .txt files isn't entirely bad, it seems that there should be a
> > mechanism in place that is the reverse of a dump, and it looks like
> > maybe mysqlimport would still require manually listing files one at a
> > time in nearly the same way as I have been doing it. What advice is
> > there on use of a dump for duplication?
> 
> I'd just copy the data files over to the new drive. I've done it on a BSDi
> to FreeBSD transfer with thousands of rows of data in 20 DBs and haven't
> found any problems in the 3 months since. We didn't try a dump, so I don't
> know of any advantages, just that it worked.
> 
> I'm also not totally sure I'm following the problem, so I can only hope
> this helps.
> 
> John
> 
> _______________________________________________
> Web Page:  http://lug.boulder.co.us
> Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug