Chapter 7. Table
and Server Administration Statements and Functions
The following SQL statements are covered in this chapter:
[click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here], [click here].
The following functions are also covered in this chapter as they
relate to data manipulation:
[click here], [click here], [click here], [click here], [click here], [click here], [click here].
7.1. Statements and Clauses in Alphabetical Order
The following is a list of MySQL statements and clauses related to
table and server administration, in alphabetical order. To understand how
this book presents SQL syntax and describes SQL statements, as well as for
information related to examples, please see the introduction to Part 2. The examples in this chapter involve a
fictitious database for a computer consulting firm that maintains work
requests for computer maintenance. Some examples involve a fictitious
database of a vendor.
| ALTER SERVER |
ALTER SERVER server |
Use this statement to change the settings for a server
created for a FEDERATE storage engine. Servers are created with the
[click here] statement. See the description of that
statement later in this chapter for more information on the options.
The SUPER privilege is required to be able to use
this statement. Here is an example:
ALTER SERVER testing
OPTIONS(USER 'test_user2');
| ANALYZE TABLE |
ANALYZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE table[, ...] |
Use this statement to store information that can be useful
later when the MySQL optimizer chooses the order for consulting
indexes during a query. Multiple tables can be specified in a
comma-separated list. The statement works on MyISAM and InnoDB tables.
Unless the NO_WRITE_TO_BINLOG option is
given, the statement is written to the binary log file and will be
executed by slaves if using replication. The LOCAL
option is synonymous with this option. For MyISAM tables, this
statement places a read lock on the tables; for InnoDB, a write lock.
This statement requires SELECT and
INSERT privileges. Here is an example:
ANALYZE TABLE workreq;
+----------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| workrequests.workreq | analyze | status | OK |
+----------------------+---------+----------+----------+
The message type in the results can be
status, error,
info, or warning. If the table
hasn't changed since it was last analyzed, the message text will read,
"Table is already up to date" and the table won't be analyzed.
This statement is equivalent to using myisamchk --analyze at the
command line for MyISAM tables.
To analyze all tables (MyISAM and InnoDB), you can use the mysqlcheck utility from the command
line like so:
mysqlcheck --user=russell -p --analyze --all-databases
If you want to see the stored key distribution that the
ANALYZE TABLE statement creates, execute the
SHOW INDEXES statement.
| BACKUP TABLE |
BACKUP TABLE table[, ...] TO '/path' |
This statement makes a backup copy of a MyISAM table.
However, it has been deprecated because it does not work reliably.
It's recommended that you use mysqlhotcopy (see
Chapter 16) until this statement is
replaced.
You can specify additional tables in a comma-separated list. The
absolute path to the directory to which MySQL is to copy files appears
within quotes after the TO keyword.
The statement copies each table's .frm file
and .MYD file, which contain the table structure
and the table data, respectively. The .MYI file
containing the index is not copied, but it will be rebuilt with the
[click here] statement when restoring the table. Here
is an example:
BACKUP TABLE clients TO '/tmp/backup';
+----------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------+--------+----------+----------+
| workrequests.clients | backup | status | OK |
+----------------------+--------+----------+----------+
If the backup succeeds, the results will look like the preceding
output and two files will be created for each table backed up: a
.frm file and a .MYD file.
If MySQL does not have the filesystem privileges necessary to write to
the backup directory, or if a file with the same name is already in
the directory, the backup will fail. In that case, the results set
will include one row with an error message type and another with a
status type and the message text stating, "Operation failed."
| CACHE INDEX |
CACHE INDEX table[[INDEX|KEY] (index, ...), ...] IN cache |
This statement tells MySQL to cache the given indexes to a specific index cache, which can be created
with the SET GLOBAL statement. This statement is
used only on MyISAM tables. Multiple tables may be listed in a
comma-separated list. To specify only certain indexes of a table, give
them in a comma-separated list in parentheses after the table name.
The INDEX or KEY keyword may be
given for clarity and compatibility with other database products. Note
that the naming of specific indexes for a table is ignored in the
current versions of MySQL; the option is for a future release. For
now, all indexes are assigned to the named cache, which is the same as
specifying no indexes.
To create an additional cache, issue a SET
GLOBAL statement with the key_buffer_size
variable like this:
SET GLOBAL my_cache.key_buffer_size = 100*1024;
CACHE INDEX workreq, clients IN my_cache \G
*************************** 1. row ***************************
Table: workrequests.workreq
Op: assign_to_keycache
Msg_type: status
Msg_text: OK
*************************** 2. row ***************************
Table: workrequests.clients
Op: assign_to_keycache
Msg_type: status
Msg_text: OK
In this example, the first line creates a cache called
my_cache with a buffer size of 100 megabytes. The
second line assigns the indexes for the two tables named to
my_cache. As long as this cache exists, all
queries by all users will use this cache. If you attempt to create a
cache index without setting the global variable first, you will
receive an error stating that it's an unknown key
cache. If the key cache is eliminated for any reason, the
indexes will be assigned back to the default key cache for the
server.
| CHECK TABLE |
CHECK TABLE table[, ...] [CHANGED|QUICK|FAST|MEDIUM|EXTENDED|FOR UPGRADE] |
Use this statement to check tables for errors; as of version 5.1.9 of MySQL, it works with the
MyISAM, InnoDB, ARCHIVE, and CSV storage engines. If errors are
discovered, you should run the REPAIR TABLE
statement to repair the table. Multiple tables may be given in a
comma-separated list. This statement requires
SELECT privileges.
There are several ways to control checking, specified after the
list of tables:
CHANGED
Checks only tables that have been changed since the last
check.QUICK
Checks tables for errors, but won't scan individual rows
for linking problems.FAST
Checks only tables that have not been closed
properly.MEDIUM
Determines the key checksum for the rows and compares the
results against the checksum for the keys. This option also
checks rows to ensure that links were deleted properly.EXTENDED
Thoroughly checks each row for errors. It takes a long time
to complete.FOR UPGRADE
Checks a table against the version of MySQL in use. If the
table was created from an earlier version and there have been
changes to the new version that make the table incompatible, the
statement will then begin the EXTENDED method
to thoroughly check the table. If it's successful, it will note
that the table has already been checked so that future checks
can avoid the time-consuming check. This option is available
starting with version 5.1.7 of MySQL.
Here is an example of how you can use this statement:
CHECK TABLE workreq MEDIUM;
+----------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------+-------+----------+----------+
| workrequests.workreq | check | status | OK |
+----------------------+-------+----------+----------+
If an error is found with an InnoDB table, the server is shut
down to prevent more problems.
Check the error log for details to resolve the problem.
| CHECKSUM TABLE |
CHECKSUM TABLE table[, ...] [QUICK|EXTENDED] |
This statement returns a MyISAM table's live checksum value,
a value that can be optionally maintained to improve a table's
repairability. To enable live checksum for a table, use the
CREATE TABLE or ALTER TABLE
statements with a table option of
CHECKSUM=1.
Multiple tables may be given in a comma-separated list. If the
QUICK option is employed, the live table checksum
will be returned, if available. If not, NULL will be returned.
Normally one would use the QUICK option when the
table is probably fine. The EXTENDED option instructs the server to check each row. You should use
this option only as a last resort. If no option is specified, the
QUICK option is the default, if available. If not,
the EXTENDED option is the default. The checksum
value can be different if the row format changes, which can happen
between versions of MySQL. Here is an example of this statement's use
and its results:
CHECKSUM TABLE workreq;
+----------------------+-----------+
| Table | Checksum |
+----------------------+-----------+
| workrequests.workreq | 195953487 |
+----------------------+-----------+
| CREATE SERVER |
CREATE SERVER 'server' |
This statement creates a server for use by the FEDERATED
storage engine. The server created is registered in the
server table in the mysql
database. The server name given cannot exceed 63 characters and is
case-insensitive. The only acceptable wrapper name is
mysql. Multiple options may be given, separated by
commas. The PORT option requires a numeric
literal, whereas the other options require character literals. So
don't put the port number within quotes. SUPER
privilege is required to be able to use this statement. Here is an
example of this statement:
CREATE SERVER testing
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'test_user', HOST '10.1.1.100',
DATABASE 'test', PORT 3307);
SELECT * FROM mysql.servers
WHERE Server_name = 'testing' \G
*************************** 1. row ***************************
Server_name: testing
Host: 10.1.1.100
Db: test
Username: test_user
Password:
Port: 3307
Socket:
Wrapper: mysql
Owner:
CREATE TABLE table1 (col_id INT, col_1 VARCHAR(25))
ENGINE=FEDERATED CONNECTION='testing';
A server created with this statement can be altered with the
ALTER SERVER statement. Once created, servers can
be accessed by setting the ENGINE clause in either the
CREATE TABLE statement or the same clause of the
ALTER TABLE statement for existing tables.
| FLUSH |
FLUSH [LOCAL|NO_WRITE_TO_BINLOG] option[, ...] |
Options:
DES_KEY_FILE, HOSTS, LOGS, PRIVILEGES, QUERY_CACHE,
STATUS, TABLE, TABLES, TABLES WITH READ LOCK, USER_RESOURCES
Use this statement to clear temporary caches in MySQL. It
requires RELOAD privileges. Multiple options may be
given in a comma-separated list.
To prevent this statement from writing to the binary log file,
include the NO_WRITE_TO_BINLOG keyword or its
alias, LOCAL. The DES_KEY_FILE
option reloads the DES encryption file.
HOSTS clears the hosts cache, which is used to minimize host/IP address lookups. The
hosts cache may need to be flushed if a host has been blocked from
accessing the server. LOGS is used to close all of
the log files and reopen them. The PRIVILEGES
option reloads the grant table for users. This is necessary if
the user table in the mysql
database is modified manually, without a GRANT
statement. QUERY CACHE instructs the server to defragment the query cache. The
STATUS option resets the status variables that report information about the
caches.
The TABLE option, followed by one or more table names, forces the given tables to
be closed. This will terminate any active queries on the given tables.
The TABLES option, without any table names listed, causes all tables to be closed,
all queries to be terminated, and the query cache to be flushed. This
option is actually the same as TABLE with no table
name.
Use the TABLES WITH READ LOCK option to close all tables and lock them with a global
read lock. This should be considered when dealing with transactional
tables and implicit commits of changes. This option will allow users
to view the data, but not to update it or to insert records. The lock
will remain in place until the UNLOCK TABLES
statement is executed.
USER_RESOURCES resets all user resources. You can use this when users have
been locked out due to exceeding usage limits.
The mysqladmin utility may be used to
execute this statement with several of its options. See Chapter 16 for information on this utility.
Two options for this statement have been deprecated: MASTER and
SLAVE. RESET MASTER and
RESET SLAVE should be used instead.
As of version 5.1 of MySQL, the FLUSH
statement cannot be called by a stored function or a trigger, although
it can be included in a stored procedure.
| KILL |
KILL [CONNECTION|QUERY] thread |
Use this statement to terminate a client connection to MySQL. You can use the [click here] statement to obtain a connection thread
identifier for use in this statement. As of version 5 of MySQL, you
can use CONNECTION or QUERY
keywords to distinguish between terminating a connection or
terminating just the current query associated with the given
connection.
Some processes cannot be terminated immediately. Instead, this
statement flags the process for termination. The system may not check
the flag until the process is completed. This will occur with
statements such as REPAIR TABLE. Besides, you
shouldn't attempt to terminate the execution of the REPAIR
TABLE or the OPTIMIZE TABLE statements.
That will corrupt a MyISAM table. The utility
mysqladmin with the options
processlist and KILL may be used
from the command line to execute these related statements.
Here is an example of the SHOW PROCESSLIST and the
KILL statements used together:
SHOW PROCESSLIST \G
...
Id: 14397
User: reader
Host: localhost
db: russell_dyer
Command: Query
Time: 7
State: Sending data
Info: SELECT COUNT(*) AS hits
FROM apache_log
WHERE SUBDATE(NOW(), INT....
KILL QUERY 14397;
The results of the SHOW PROCESSLIST are
truncated. Using the thread identifier 14397 from the results, the
KILL statement is used with the
QUERY keyword to terminate the SQL statement that's
running, without terminating the client connection. If the
CONNECTION keyword or no keyword is given, the
entire connection is terminated. In that case, if the client attempts
to issue another SQL statement, it receives a 2006 error message
stating that the MySQL server has gone away. Then it typically will
try to reconnect to the server, establish a new thread, and run the
requested query.
| LOAD INDEX INTO CACHE |
LOAD INDEX INTO CACHE |
Use this statement to preload a table's index into a given
key cache for a MyISAM table. The syntax allows one or more indexes to
be specified in a comma-separated list in parentheses, in order to
preload just the specified indexes, but presently MySQL simply loads
all the indexes for the table into the cache. The keywords
INDEX and KEY are
interchangeable and optional; they do not affect the results. The
IGNORE LEAVES clause instructs MySQL not to preload
leaf nodes of the index. Here is an example of how you can use this
statement:
LOAD INDEX INTO CACHE workreq;
+----------------------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------+--------------+----------+----------+
| workrequests.workreq | preload_keys | status | OK |
+----------------------+--------------+----------+----------+
| LOCK TABLES |
LOCK TABLES table [AS alias] |
Use this statement to lock the given tables for exclusive
use by the current connection thread. A READ lock
allows the locked tables to be read by all threads, but it does not
allow writes to the tables, even by the thread that locked them. A
READ LOCAL lock allows all threads to read the tables that are locked
while the locking connection can execute INSERT
statements. Until the lock is released, though, direct data
manipulation by command-line
utilities should be avoided. A WRITE lock
prohibits other threads from reading from or writing to locked
tables, but it permits reads and writes by the locking thread. SQL
statements for tables that are locked with the
WRITE option have priority over statements
involving tables with a READ lock. However, the
LOW_PRIORITY keyword may be given before the WRITE to
instruct the server to wait until there are no queries on the tables
being locked.
Only locked tables may be accessed by a locking thread.
Therefore, all tables to be used must be locked. To illustrate this,
assume a new programmer has been hired. The programmer's information
must be added to the programmers table. The
wk_schedule table that contains the records for
scheduling work also needs to be adjusted to assign work to the new
programmer and away from others. Here is how you might lock the
relevant tables:
LOCK TABLES workreq READ, programmers READ LOCAL,
wk_schedule AS work LOW_PRIORITY WRITE;
In this example, the workreq table is locked
with a READ keyword so that no new work requests
may be added while the table for the programmers' work schedules is
being updated, but the work requests may still be viewed by other
users. The programmers table is locked for writing
with the READ LOCAL keyword, because one record
needs to be inserted for the new programmer's personal information. The
wk_schedule table is locked for exclusive use by
the current thread.
For convenience, you can give a table an alias with the
AS keyword. In the example, the
wk_schedule table is referred to as
work for subsequent SQL statements until the tables
are unlocked. During this time, the thread can refer to the table only
by this name in all other SQL statements.
You can release locks with the UNLOCK TABLES
statements. A START TRANSACTION statement
also unlocks tables, as does the issuing of another
TABLE LOCKS statement. Therefore, all tables to be
locked should be named in one statement. Additional tables can be
added to the end of the TABLE LOCKS statement in a
comma-separated list.
You can lock all tables with a FLUSH TABLES WITH READ LOCK
statement. You can use the [click here] and [click here] functions as alternatives to the [click here] and [click here] covered in
this chapter.
| OPTIMIZE TABLE |
OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE table[, ...] |
Use this statement to optimize the data contained in a
table. Optimization is useful when many rows have been deleted from a
table. It's also useful to run this statement periodically with a
table that contains several variable-character-width columns (i.e.,
VARCHAR, BLOB, and
TEXT columns). This statement generally works only
with MyISAM, BDB, and InnoDB tables. It may work on other tables,
however, if the mysqld daemon is started with the
--skip-new option or the
--safe-mode option. See Chapter 15 for more information on setting server
startup options.
This statement also repairs some row problems and sort indexes.
It temporarily locks the tables involved while optimizing. Multiple
tables can be listed for optimization in a comma-separated list. To prevent the
activities of this statement from being recorded in the binary log
file, use the NO_WRITE_TO_BINLOG keyword or its alias, LOCAL. Here is an example of the statement's use:
OPTIMIZE LOCAL TABLE workreq, clients;
+----------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------+----------+----------+----------+
| workrequests.workreq | optimize | status | OK |
| workrequests.clients | optimize | status | OK |
+----------------------+----------+----------+----------+
Here, two tables are optimized successfully and the activity is
not written to the binary log file.
| REPAIR TABLE |
REPAIR [LOCAL|NO_WRITE_TO_BINLOG] TABLE |
Use this statement to repair corrupted MyISAM tables. Multiple
tables may be given in a comma-separated list. To prevent this
statement from recording its activities in the binary log file, give
the NO_WRITE_TO_BINLOG keyword or its
LOCAL alias. The QUICK keyword instructs MySQL to
repair the table indexes only. The EXTENDED keyword
rebuilds the indexes one row at a time. This option takes longer,
but it can be more effective, especially with rows containing
duplicate keys.
Before running this statement, make a backup of the table. If a
table continues to have problems, there may be other problems (e.g.,
filesystem problems) that you should consider. Here is an example of
this statement:
REPAIR TABLE systems QUICK EXTENDED;
+----------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------+--------+----------+----------+
| workrequests.systems | repair | status | OK |
+----------------------+--------+----------+----------+
In this example, the repair is successful. This is indicated by the
OK in the Msg_text field. If it
is unsuccessful, you could try the USE_FRM option
with this statement. That option will create a new index file
(.MYI) using the table schema file
(.frm). It won't be able to determine the current
value for AUTO_INCREMENT columns or for
DELETE LINK, so it shouldn't be used unless the
original .MYI file is lost. Incidentally, if the
MySQL server dies while the REPAIR TABLE statement
is running, you should run the statement again as soon as the server
is back up, before running any other SQL statements.
| RESET |
RESET {MASTER|SLAVE|QUERY CACHE}[, ...] |
Use this statement to reset certain server settings and
files. It's similar to the FLUSH statement, but
more powerful for its specific uses. The RELOAD
privilege is required to use it. Multiple options may be given in a
comma-separated list. Currently, you can reset the
MASTER, QUERY CACHE, and
SLAVE options. See the [click here] and the [click here] statements in Chapter 8 for detailed explanations of each option.
The QUERY CACHE option clears the cache containing SQL query results.
| RESTORE TABLE |
RESTORE TABLE table[, ...] FROM '/path' |
This statement restores a table that was saved to the
filesystem by the BACKUP TABLE statement.
Multiple tables may be given in a comma-separated list.
The absolute path to the directory containing the backup files must
appear within quotes. If the tables already exist in the database, an
error message will be generated and the restore will fail. If it's
successful, the table indexes will be built automatically. This is
necessary because the BACKUP TABLE statement
doesn't back up the index files. Here is an example of this statement:
RESTORE TABLE clients, programmers FROM '/tmp/backup';
+--------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+---------+----------+----------+
| workrequests.clients | restore | status | OK |
+--------------------------+---------+----------+----------+
| workrequests.programmers | restore | status | OK |
+--------------------------+---------+----------+----------+
In this example, the statement is successful in restoring the
.frm and .MYD files located
in the backup directory and regenerating the .MYI
files.
| SET |
SET [GLOBAL|@@global.|SESSION|@@session.] variable = expression |
This statement sets a system or user variable for global or session use. Global variables relate to all users. Session variables are available only to the connection thread that creates the
variable. For system variables to be recognized as global, the
GLOBAL keyword is used. Alternatively, the variable
can be preceded by @@global. to signify that it is
global. For system variables that are limited to the current session,
use the SESSION keyword, or place
@@session or just @@ immediately
in front of the variable name. The default for variables is to limit
them to the session, making them local. LOCAL and
@@local are aliases for SESSION
and @@session, respectively. Here are a couple of
examples involving system variables, one using the keyword method and
the other using the variable prefix method:
SET GLOBAL concurrent_insert = 1;
SET @@session.interactive_timeout=40000;
The first statement disables concurrent inserts without having
to restart the server. The second statement changes the interactive
timeout to a higher value than normal. This setting is for the current
client connection only. For other clients, this variable will still
contain the default value.
To see a list of system variables and their values, use the
[click here] statement. For a description of these
variables, see Appendix C. For examples
involving user variables, see the description of the [click here] statement in Chapter 6.
| SHOW ENGINE |
SHOW ENGINE engine {STATUS|MUTEX} |
Use this statement to display details of the status of a
given storage engine. This statement provides information on table and record locks
for transactions, waiting locks, pending requests, buffer statistics
and activity, and logs related to the engine.
Currently, the engines that may be given are
INNODB, NDB, and
NDBCLUSTER. These last two keywords are
interchangeable. Prior to version 5.1.12 of MySQL, the option of
BDB was permitted. In later versions, the BDB
engine is not supported and a warning message is generated when it is
used with this statement. The MUTEX option
is available only for the InnoDB engine. For the NDB
engine, an empty results set is returned if there are no operations at
the time.
| SHOW ENGINES |
SHOW [STORAGE] ENGINES |
This statement lists the table types or storage engines
available for the version of MySQL running on the server. It states
which are disabled on the server and which are enabled, as well as
which is the default type. It also provides comments on each type. The
STORAGE keyword is optional
and has no effect on the results. This SQL statement replaces
SHOW TABLE TYPES, which produced the same results,
but is deprecated. Here is an example of this statement:
Code View:
SHOW ENGINES \G
*************************** 1. row ***************************
Engine: ndbcluster
Support: DISABLED
Comment: Clustered, fault-tolerant tables
Transactions: YES
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: FEDERATED
Support: YES
Comment: Federated MySQL storage engine
Transactions: YES
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 9. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
XA: NO
Savepoints: NO
9 rows in set (0.00 sec)
| SHOW OPEN TABLES |
SHOW OPEN TABLES [FROM database] [LIKE 'pattern'|WHERE expression] |
Use this statement to display a list of tables that are
open, i.e., that are in the table cache. The list does not include any
temporary tables. The LIKE clause can be used to limit
the tables displayed by a naming pattern. Similarly, the
WHERE clause may be used to refine the results set. Here is an example of
this statement:
SHOW OPEN TABLES
FROM college LIKE '%student%';
+----------+--------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+--------------------+--------+-------------+
| college | student_surveys | 0 | 0 |
| college | students | 0 | 0 |
| college | student_exams | 0 | 0 |
| college | student_exams_past | 0 | 0 |
+----------+--------------------+--------+-------------+
| SHOW PLUGINS |
SHOW PLUGINS |
Use this statement to display a list of plugins on the server.
This statement is available as of version 5.1.5 of MySQL, but with the
name SHOW PLUGIN. It was changed to SHOW
PLUGINS as of version 5.1.9. Here is an example:
SHOW PLUGINS;
+------------+--------+----------------+---------+---------+
| Name | Status | Type | Library | License |
+------------+--------+----------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ndbcluster | ACTIVE | STORAGE ENGINE | NULL | GPL |
+------------+--------+----------------+---------+---------+
| SHOW PROCESSLIST |
SHOW [FULL] PROCESSLIST |
This statement displays a list of connection threads running
on the MySQL server. The statement requires SUPER
privileges to be able to see all threads. Otherwise, only threads
related to the current connection are shown. The
FULL keyword shows the full text of the information field. Here is an
example:
SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 1
User: root
Host: localhost
db: workrequests
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
You can use this statement to determine a thread identification
number to be used with the [click here]
statement.
| SHOW STATUS |
SHOW [GLOBAL|LOCAL|SESSION] STATUS [LIKE 'pattern'|WHERE expression] |
This statement displays status information and variables from
the server. You can reduce the number of variables shown with
the LIKE clause, based on a naming
pattern for the variable name. Similarly, the WHERE
clause may be used to refine the results set. Here is an example of
how you can use this statement with the LIKE
clause:
SHOW STATUS LIKE '%log%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_engine_logs | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 1 |
| Innodb_os_log_fsyncs | 3 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 512 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
+------------------------------+-------+
The results show any system variable in which the variable name
has the word log in it. This is a new server
installation, so the results have small or zero values. If we wanted
to eliminate the InnoDB logs from the results, we could use the
WHERE clause like so:
SHOW STATUS
WHERE Variable_name LIKE '%log%'
AND Variable_name NOT LIKE '%Innodb%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_engine_logs | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
+------------------------+-------+
Notice that when using the WHERE clause, the
field name in the results must be given. In this case, the field name
Variable_name is given. You could also give the
field name Value to limit the results to entries of
a certain value or range of values:
SHOW GLOBAL STATUS
WHERE Variable_name LIKE '%log%'
AND Variable_name LIKE '%Innodb%'
AND Value > 100;
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Innodb_os_log_written | 512 |
+-----------------------+-------+
In this example, we are looking for log entries for InnoDB with
values over 100. The results consist of just one entry.
You can change many variables at server startup using options
for the MySQL server daemon. See Chapter 15
for more details. You can change some of them while the daemon is running with the [click here] statement, without having to restart the
server. That statement is covered earlier in this chapter.
| SHOW TABLE STATUS |
SHOW TABLE STATUS [FROM database] [LIKE 'pattern'|WHERE expression] |
This statement displays status information on a set of tables
from a database. To obtain the status of tables from a database other
than the current default one, use the FROM clause. The results will
include information on all of the tables of the database unless the
LIKE clause is used to limit the tables displayed
by a naming pattern. Similarly, the WHERE clause
may be used to refine the results set. As an alternative to
this statement, you can use the utility mysqlshow
with the --status option, as described in Chapter 16. Here's an example of this statement using the
LIKE clause:
SHOW TABLE STATUS FROM workrequests LIKE 'workreq'\G
*************************** 1. row ***************************
Name: workreq
Engine: MyISAM
Version: 7
Row_format: Dynamic
Rows: 543
Avg_row_length: 983
Data_length: 534216
Max_data_length: 4294967295
Index_length: 6144
Data_free: 120
Auto_increment: 5772
Create_time: 2002-04-23 14:41:58
Update_time: 2004-11-26 16:01:46
Check_time: 2004-11-28 17:21:20
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
This example shows results for only one table because a specific
table name is given in the LIKE clause without the
% wildcard. To find a group of tables, but to limit
the results more, you can use the WHERE clause.
Here is an example:
SHOW TABLE STATUS FROM workrequests
WHERE Rows > 1000;
This example lists all tables from the given database that
contain more than 1,000 rows of data. Notice that we're using the
field name Rows from the results set to limit the
results. Any field name can be used in this way and multiple fields
may be given, separated by the AND parameter of the
WHERE clause.
As for the results themselves, most are obvious from their field
names. The Row_format field can have a value of
Compact, Compressed,
Dynamic, Fixed, or
Redundant. InnoDB tables are either
Compact or Redundant. The
Rows field gives an accurate count with MyISAM
tables, but not with InnoDB.
The Data_length field gives the size of the
datafile associated with the table. Max_data_length
is the maximum size allowed for the datafile. These two values are
estimates for MEMORY tables. The
Auto_increment value shows the value for the column
that uses AUTO_INCREMENT.
When used with views, this statement returns NULL values for almost all
fields.
You can change some of these variables or table options using
the [click here] statement in Chapter 5.
| SHOW VARIABLES |
SHOW [GLOBAL|LOCAL|SESSION] VARIABLES [LIKE 'pattern'|WHERE expression] |
This statement displays the system variables for the MySQL
server. The SESSION keyword displays values for
current sessions or connections. This is the default and is synonymous
with LOCAL. The GLOBAL keyword
shows variables that relate to new connections. You can limit the
variables with the LIKE clause and a naming pattern for the variables. Similarly, the
WHERE clause can be used to refine the results set. Here is an example
of this statement with the LIKE clause:
SHOW GLOBAL VARIABLES LIKE 'version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| version | 5.1.16-beta |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
+-------------------------+------------------------------+
In this example, the variables shown are limited to global
variables whose names begin with the word
version. Suppose that we wanted to see only the
two variables of these results that contain a numeric value. We could
do this by using the WHERE clause like so:
SHOW GLOBAL VARIABLES
WHERE Variable_name LIKE 'version%'
AND Value REGEXP '[0-9]';
+-------------------------+-------------+
| Variable_name | Value |
+-------------------------+-------------+
| version | 5.1.16-beta |
| version_compile_machine | i686 |
+-------------------------+-------------+
Notice that, for the WHERE clause, we specify
the field names of the results set: Variable_name and
Value. In this case, we're also using the
LIKE and REGEXP string
comparison functions to narrow the results.
You can change many of the variables at server startup with
options for the MySQL server daemon. See Chapter 15 for more details. You can change some of
them while the daemon is running with the [click here] statement, without having to restart the
server. That statement is covered earlier in this chapter.
| UNLOCK TABLES |
UNLOCK TABLES |
Use this statement to unlock tables that were locked by the
current connection thread with the [click here]
statement or by FLUSH TABLES WITH READ LOCK.
UNLOCK TABLES implicitly commits any active
transactions if any tables were locked with LOCK
TABLES. When performing a large amount of changes to data in
MyISAM tables, it can be useful and faster to lock the tables first.
This way the key cache isn't flushed after each SQL statement.
Instead, the server flushes the key cache when executing
UNLOCK TABLES. Here is an example:
UNLOCK TABLES;
No comments:
Post a Comment