Creates and populates information tables with the output of show databases, show tables (or show table status), show columns and show index. this is version 1.1.
mysql_tableinfo [OPTIONS] database_to_write [database_like_wild] [table_like_wild]
Do not backquote (``) database_to_write, and do not quote ('') database_like_wild or table_like_wild
Examples:
mysql_tableinfo info
mysql_tableinfo info this_db
mysql_tableinfo info %a% b%
mysql_tableinfo info --clear-only
mysql_tableinfo info --col --idx --table-status
mysql_tableinfo asks a MySQL server information about its databases, tables, table columns and index, and stores this in tables called `db`, `tbl` (or `tbl_status`), `col`, `idx` (with an optional prefix specified with --prefix). After that, you can query these information tables, for example to build your admin scripts with \s-1SQL\s0 queries, like
\s-1SELECT\s0 \s-1CONCAT\s0(\*(L"\s-1CHECK\s0 \s-1TABLE\s0 \*(R",`database`,\*(L".\*(R",`table`,\*(L" \s-1EXTENDED\s0;\*(R") \s-1FROM\s0 info.tbl \s-1WHERE\s0 ... ;
as people usually do with some other \s-1RDBMS\s0 (note: to increase the speed of your queries on the info tables, you may add some index on them).
The database_like_wild and table_like_wild instructs the program to gather information only about databases and tables whose names match these patterns. If the info tables already exist, their rows matching the patterns are simply deleted and replaced by the new ones. That is, old rows not matching the patterns are not touched. If the database_like_wild and table_like_wild arguments are not specified on the command-line they default to \*(L"%\*(R".
The program :
- does \s-1CREATE\s0 \s-1DATABASE\s0 \s-1IF\s0 \s-1NOT\s0 \s-1EXISTS\s0 database_to_write where database_to_write is the database name specified on the command-line.
- does \s-1CREATE\s0 \s-1TABLE\s0 \s-1IF\s0 \s-1NOT\s0 \s-1EXISTS\s0 database_to_write.`db`
- fills database_to_write.`db` with the output of \s-1SHOW\s0 \s-1DATABASES\s0 \s-1LIKE\s0 database_like_wild
- does \s-1CREATE\s0 \s-1TABLE\s0 \s-1IF\s0 \s-1NOT\s0 \s-1EXISTS\s0 database_to_write.`tbl` (respectively database_to_write.`tbl_status` if the --tbl-status option is on)
- for every found database, fills database_to_write.`tbl` (respectively database_to_write.`tbl_status`) with the output of \s-1SHOW\s0 \s-1TABLES\s0 \s-1FROM\s0 found_db \s-1LIKE\s0 table_like_wild (respectively \s-1SHOW\s0 \s-1TABLE\s0 \s-1STATUS\s0 \s-1FROM\s0 found_db \s-1LIKE\s0 table_like_wild)
- if the --col option is on,
* does \s-1CREATE\s0 \s-1TABLE\s0 \s-1IF\s0 \s-1NOT\s0 \s-1EXISTS\s0 database_to_write.`col`
* for every found table,
fills database_to_write.`col` with the output of
\s-1SHOW\s0 \s-1COLUMNS\s0 \s-1FROM\s0 found_tbl \s-1FROM\s0 found_db
- if the --idx option is on,
* does \s-1CREATE\s0 \s-1TABLE\s0 \s-1IF\s0 \s-1NOT\s0 \s-1EXISTS\s0 database_to_write.`idx`
* for every found table,
fills database_to_write.`idx` with the output of
\s-1SHOW\s0 \s-1INDEX\s0 \s-1FROM\s0 found_tbl \s-1FROM\s0 found_db
Some options may modify this general scheme (see below).
As mentioned, the contents of the info tables are the output of \s-1SHOW\s0 commands. In fact the contents are slightly more complete :
- the `tbl` (or `tbl_status`) info table
has an extra column which contains the database name,
- the `col` info table
has an extra column which contains the table name, and an extra column which contains, for each described column, the number of this column in the table owning it (this extra column is called `Seq_in_table`). `Seq_in_table` makes it possible for you to retrieve your columns in sorted order, when you are querying the `col` table.
- the `index` info table
has an extra column which contains the database name.
Caution: info tables contain certain columns (e.g. Database, Table, Null...) whose names, as they are MySQL reserved words, need to be backquoted (`...`) when used in \s-1SQL\s0 statements.
Caution: as information fetching and info tables filling happen at the same time, info tables may contain inaccurate information about themselves.
Does \s-1DROP\s0 \s-1TABLE\s0 on the info tables (only those that the program is going to fill, for example if you do not use --col it won't drop the `col` table) and processes normally. Does not drop database_to_write.
Same as --clear but exits after the DROPs.
Adds columns information (into table `col`).
Adds index information (into table `idx`).
The info tables are named from the concatenation of prefix and, respectively, db, tbl (or tbl_status), col, idx. Do not quote ('') or backquote (``) prefix.
Does not warn you about what the script is going to do (\s-1DROP\s0 \s-1TABLE\s0 etc) and does not ask for a confirmation before starting.
Instead of using \s-1SHOW\s0 \s-1TABLES\s0, uses \s-1SHOW\s0 \s-1TABLE\s0 \s-1STATUS\s0 (much more complete information, but slower).
Display helpscreen and exit
user for database login if not current user. Give a user who has sufficient privileges (\s-1CREATE\s0, ...).
password to use when connecting to server. WARNING: Providing a password on command line is insecure as it is visible through /proc to anyone for a short time.
host to connect to
port to use when connecting to server
\s-1UNIX\s0 domain socket to use when connecting to server
This software is free and comes without warranty of any kind. You should never trust backup software without studying the code yourself. Study the code inside this script and only rely on it if you believe that it does the right thing for you. Patches adding bug fixes, documentation and new features are welcome.
Use extended inserts to be faster (for servers with many databases or tables). But to do that, must care about net-buffer-length.
2002-06-18 Guilhem Bichot ([email protected]) And all the authors of mysqlhotcopy, which served as a model for the structure of the program.