RE: Getting info about db columns

--=_alternative 0079D07285256F87_=
Content-Type: text/plain; charset="US-ASCII"

"Dave Merrill" wrote on 01/12/2005 04:56:34 PM:

> Thanks Shawn, looks like the stuff.
>
> SHOW TABLES is exactly one of my answers.
>
> SHOW COLUMNS from dbname like '%name%' is useful in that it can restrict
by
> column name, but am I right that it can't restrict by type or
keyed-ness, or
> span more than one table?
>

That's correct, SHOW COLUMNS only works for one table at a time and the
"like" option only restricts by name.

> Also, it looks like if you wanted to find out the pk columns in a set of
> tables, SHOW INDEX would need to be called separately from the column
> listing, and separately for each table.

Actually, I prefer the results of SHOW CREATE TABLE as it shows you not
only which columns are in the PRIMARY KEY but in which order they are
listed. That command also is the only one that gives me good information
about FOREIGN KEYs as well.

>
> Good news about INFORMATION_SCHEMA too, sounds like a good thing. Do you
> know if the goal is to be syntax compatible w SQL Server? Is there a std
> spec for this? I thought it was a microsoft idiosyncracy, though, for
once,
> one I liked.

Yes, the INFORMATION_SCHEMA views are part of every SQL standard (at least
as far back as SQL92) so, if M$ wasn't too full of themselves, the results
should be comparable. However, I haven't done a side-by-side comparison
yet so I can't tell you for sure.

>
> Thanks again,
>
> Dave
>

You're most welcome,

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

PS. It's a good habit to hit "REPLY TO ALL" (or whatever your email client
uses) so that the list is always copied (cc: -ed) on any responses. It
performs two basic functions: everyone gets to share in the conversation
so the knowledge gets spread around and you will generally get more
responses.

> > May I introduce you to the family of SHOW commands?
> > SHOW DATABASES - lists all databases on a server
> > SHOW TABLES - lists all tables within your current database
> > SHOW CREATE TABLE xxxxx - returns a table containing a SQL statement
you
> > could use to recreate the table xxxxx if you needed to (including all
> > indexes and constraints)
> > SHOW COLUMNS FROM xxxxx - returns a table similar to
> > INFORMATION_SCHEMA.columns but only for the table you specify.
> >
> > and the list goes on and on:
http://dev.mysql.com/doc/mysql/en/SHOW.html
> >
> > Alas, the INFORMATION_SCHEMA views are not production ready. They are
> > being added to MySQL with the 5.x+ branch of development, which is
not
> > yet stable. Be patient, many good things are coming with v5.
> >
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> >
> >
> > > Hi, mysql newb here, coming mostly from ms sql server.
> > >
> > > I'm wondering how to find out basic info (type, str length, column
name
> > if
> > > getting all cols from a list of tables, etc), about a set of columns
> > from a
> > > number of different tables. I'm also interested in getting a list of
the
> > > tables in a db.
> > >
> > > SQL Server has a pair of views (information_schema.tables and
> > > information_schema.columns) that were useful for this kind of thing.
> > Since
> > > they're normal views, you can restrict the tables or cols found by
> > table,
> > > column, type, etc, and retrieve only the info you want about them;
> > anything
> > > that works in a std query works here.
> > >
> > > The only way I know of to get this kind of metadata from mysql is
> > explain,
> > > which appears to only return a fixed set of info about all the
columns
> > in a
> > > single table. Is there any way to get:
> > >
> > > - A list of tables in a db
> > > - Column info for more than one table at once
> > > - Only certain info about these cols (name and type only, say)
> > >
> > > Thanks,
> > >
> > > Dave Merrill
>

--=_alternative 0079D07285256F87_=--

RE: Getting info about db columns

[Sending this to this list, just for general reference, since I didn't
notice that Reply on this list goes to the poster, not the list. No other
list I'm on works that way, so I plead Failure To Open Eyes.]

Dave Merrill

> "Dave Merrill" wrote on 01/12/2005 04:56:34 PM:
>
> > Thanks Shawn, looks like the stuff.
> >
> > SHOW TABLES is exactly one of my answers.
> >
> > SHOW COLUMNS from dbname like '%name%' is useful in that it can
> restrict by
> > column name, but am I right that it can't restrict by type or
> keyed-ness, or
> > span more than one table?
> >
>
> That's correct, SHOW COLUMNS only works for one table at a time
> and the "like" option only restricts by name.
>
> > Also, it looks like if you wanted to find out the pk columns in a set of
> > tables, SHOW INDEX would need to be called separately from the column
> > listing, and separately for each table.
>
> Actually, I prefer the results of SHOW CREATE TABLE as it shows
> you not only which columns are in the PRIMARY KEY but in which
> order they are listed. That command also is the only one that
> gives me good information about FOREIGN KEYs as well.
>
> >
> > Good news about INFORMATION_SCHEMA too, sounds like a good thing. Do you
> > know if the goal is to be syntax compatible w SQL Server? Is there a std
> > spec for this? I thought it was a microsoft idiosyncracy,
> though, for once,
> > one I liked.
>
> Yes, the INFORMATION_SCHEMA views are part of every SQL standard
> (at least as far back as SQL92) so, if M$ wasn't too full of
> themselves, the results should be comparable. However, I haven't
> done a side-by-side comparison yet so I can't tell you for sure.
>
> >
> > Thanks again,
> >
> > Dave
> >
>
> You're most welcome,
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> PS. It's a good habit to hit "REPLY TO ALL" (or whatever your
> email client uses) so that the list is always copied (cc: -ed) on
> any responses. It performs two basic functions: everyone gets to
> share in the conversation so the knowledge gets spread around and
> you will generally get more responses.
>
> > > May I introduce you to the family of SHOW commands?
> > > SHOW DATABASES - lists all databases on a server
> > > SHOW TABLES - lists all tables within your current database
> > > SHOW CREATE TABLE xxxxx - returns a table containing a SQL
> statement you
> > > could use to recreate the table xxxxx if you needed to (including all
> > > indexes and constraints)
> > > SHOW COLUMNS FROM xxxxx - returns a table similar to
> > > INFORMATION_SCHEMA.columns but only for the table you specify.
> > >
> > > and the list goes on and on:
> http://dev.mysql.com/doc/mysql/en/SHOW.html
> > >
> > > Alas, the INFORMATION_SCHEMA views are not production ready. They are
> > > being added to MySQL with the 5.x+ branch of development,
> which is not
> > > yet stable. Be patient, many good things are coming with v5.
> > >
> > > Shawn Green
> > > Database Administrator
> > > Unimin Corporation - Spruce Pine
> > >
> > >
> > > > Hi, mysql newb here, coming mostly from ms sql server.
> > > >
> > > > I'm wondering how to find out basic info (type, str length,
> column name
> > > if
> > > > getting all cols from a list of tables, etc), about a set of columns
> > > from a
> > > > number of different tables. I'm also interested in getting
> a list of the
> > > > tables in a db.
> > > >
> > > > SQL Server has a pair of views (information_schema.tables and
> > > > information_schema.columns) that were useful for this kind of thing.
> > > Since
> > > > they're normal views, you can restrict the tables or cols found by
> > > table,
> > > > column, type, etc, and retrieve only the info you want about them;
> > > anything
> > > > that works in a std query works here.
> > > >
> > > > The only way I know of to get this kind of metadata from mysql is
> > > explain,
> > > > which appears to only return a fixed set of info about all
> the columns
> > > in a
> > > > single table. Is there any way to get:
> > > >
> > > > - A list of tables in a db
> > > > - Column info for more than one table at once
> > > > - Only certain info about these cols (name and type only, say)
> > > >
> > > > Thanks,
> > > >
> > > > Dave Merrill
> >


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

backup scheduling

Is there a way I can schedule backups to occur automatically within mysql?
I lost 75,000 rows of data today (luckily I had a copy of it somewhere else)
and I do not want to have to go through this again.




--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: backup scheduling

I use a little script which I trigger from the task scheduler in windows.
Must be used with php. It's a great little script that e-mails you a report
when its done, and can also transfer the backed up files via ftp to another
server automatically. Check it out at
http://restkultur.ch/personal/wolf/scripts/db_backup it has run every day
for me without fail, and puts the backup file in the form of an SQL import
file, so the only thing you need to do is open a console, and use the source
function. Supports multiple databases.

Hope that helps


Sam


-----Original Message-----
From: Danny Willis [mailto:danny.willis@project-wildfire.com]
Sent: Thursday, January 13, 2005 3:33 AM
To: win32@lists.mysql.com
Subject: backup scheduling

Is there a way I can schedule backups to occur automatically within mysql?
I lost 75,000 rows of data today (luckily I had a copy of it somewhere else)
and I do not want to have to go through this again.




--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=sam.deforest@walpolewood. net


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: backup scheduling

I am paranoic, sick and scared about any security and integrity issues :)

First, I discovered that MySQL Administrator has a backup tool very
useful built in, so, every night, at 23:00 it backups my entire
database (including any tables added recently) and that is just GREAT,
it creates a single, giant SQL file that can restore your entire
database.

Have I told you all that I am sick for security? Well, relying on a
single, desktop app for such a tricky job like backup is not my idea
of a secure system, so, I decided to have something older, more secure
and more like my old backup tools, so, I created a batch file,
containing mysqldump commands, dumping my entire database (again) in
an SQL file timestamped. MySQL Dump Tool is great, you can select a
number of options for a clear, secure and relyable backup file. I
inserted it into the Scheduled Tasks of my windows server and at the
cron table on my Linux server (yes, same script just renamed from bat
to sh, and including some stuff, that is another advantage of the
batch file).

I feel kinda secure with that...

Best regards,

On Thu, 13 Jan 2005 07:51:31 -0500, Sam DeForest
wrote:
> I use a little script which I trigger from the task scheduler in windows.
> Must be used with php. It's a great little script that e-mails you a report
> when its done, and can also transfer the backed up files via ftp to another
> server automatically. Check it out at
> http://restkultur.ch/personal/wolf/scripts/db_backup it has run every day
> for me without fail, and puts the backup file in the form of an SQL import
> file, so the only thing you need to do is open a console, and use the source
> function. Supports multiple databases.
>
> Hope that helps
>
> Sam
>
>
> -----Original Message-----
> From: Danny Willis [mailto:danny.willis@project-wildfire.com]
> Sent: Thursday, January 13, 2005 3:33 AM
> To: win32@lists.mysql.com
> Subject: backup scheduling
>
> Is there a way I can schedule backups to occur automatically within mysql?
> I lost 75,000 rows of data today (luckily I had a copy of it somewhere else)
> and I do not want to have to go through this again.
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
> http://lists.mysql.com/win32?unsub=sam.deforest@walpolewood. net
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=danieldaveiga@gmail.com
>
>


--
Daniel da Veiga
Computer Operator - RS - Brazil

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: backup scheduling

Thanks,

I'll give this a try.

-----Original Message-----
From: Sam DeForest [mailto:sam.deforest@walpolewood.net]
Sent: Thursday, January 13, 2005 7:52 AM
To: 'Danny Willis'; win32@lists.mysql.com
Subject: RE: backup scheduling

I use a little script which I trigger from the task scheduler in windows.
Must be used with php. It's a great little script that e-mails you a report
when its done, and can also transfer the backed up files via ftp to another
server automatically. Check it out at
http://restkultur.ch/personal/wolf/scripts/db_backup it has run every day
for me without fail, and puts the backup file in the form of an SQL import
file, so the only thing you need to do is open a console, and use the source
function. Supports multiple databases.

Hope that helps


Sam


-----Original Message-----
From: Danny Willis [mailto:danny.willis@project-wildfire.com]
Sent: Thursday, January 13, 2005 3:33 AM
To: win32@lists.mysql.com
Subject: backup scheduling

Is there a way I can schedule backups to occur automatically within mysql?
I lost 75,000 rows of data today (luckily I had a copy of it somewhere else)
and I do not want to have to go through this again.




--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=sam.deforest@walpolewood. net


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=danny.willis@project-wild fire.com



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org