[lug] mysql table creation scripts

[Brad Doctor]

Can't you do:

mysql [-h host] -u root -p <database>  < cmd.sql

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