[lug] mysql table creation scripts

[D. Stimits]

"D. Stimits" wrote:
> 
> Brad Doctor wrote:
> >
> > Can't you do:
> >
> > mysql [-h host] -u root -p <database>  < cmd.sql
> 
> Isn't -p the password, not the database?

Ok, this was answered, <database> following -p is just coincidence, not
password. The man page synopsis section actually fails to mention
database can be specified on the command line *duh*.

D. Stimits, stimits at idcomm.com

> 
> 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
> _______________________________________________
> Web Page:  http://lug.boulder.co.us
> Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug