[lug] mysql table creation scripts

[D. Stimits]

Brad Doctor wrote:
> 
> Can't you do:
> 
> mysql [-h host] -u root -p <database>  < cmd.sql

Isn't -p the password, not the database?

D. Stimits, stimits at idcomm.com

> 
> That should select the DB for you.  I know it looks like it will take the
> password as the <database> specification, however it will ask you for a
> password, or if you have set it up very insecurely, omit the "-p" and off
> you go.  One thing I like in Oracle that I haven't been able to do with
> MySQL is something like this:
> 
>    create table db0.blah as (
>       select col1,col2,col5,col10 from db1.foo
>         where col1 = 0
>     );
> 
> That is very handy.
> 
> -brad
> 
> > >
> > > 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