4.

5 Database Administration Language Reference

4.5.1 OPTIMIZE TABLE Syntax
OPTIMIZE TABLE tbl_name[,tbl_name]...

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have
made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB,
or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT
operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the
unused space and to defragment the data file.

For the moment OPTIMIZE TABLE only works on MyISAM and BDB tables. For BDB
tables, OPTIMIZE TABLE is currently mapped to ANALYZE TABLE. See section 4.5.2
ANALYZE TABLE Syntax.

You can get optimize table to work on other table types by starting mysqld with --skip-
new or --safe-mode, but in this case OPTIMIZE TABLE is just mapped to ALTER TABLE.

OPTIMIZE TABLE works the following way:

If the table has deleted or split rows, repair the table.

If the index pages are not sorted, sort them.
If the statistics are not up to date (and the repair couldn't be done by sorting the
index), update them.

OPTIMIZE TABLE for MyISAM tables is equvialent of running myisamchk --quick

--check-changed-tables --sort-index --analyze on the table.

Note that the table is locked during the time OPTIMIZE TABLE is running!

4.5.2 ANALYZE TABLE Syntax

ANALYZE TABLE tbl_name[,tbl_name...]

Analyze and store the key distribution for the table. During the analyze the table is locked
with a read lock. This works on MyISAM and BDB tables.

This is equivalent to running myisamchk -a on the table.

MySQL uses the stored key distribution to decide in which order tables should be joined
when one does a join on something else than a constant.

The command returns a table with the following columns:

Column Value
Table Table name
Op Always ``analyze''
Msg_type One of status, error, info or warning.
Msg_text The message.

You can check the stored key distribution with the SHOW INDEX command. See section
4.5.5.1 Retrieving information about Database, Tables, Columns, and Indexes.

If the table hasn't changed since the last ANALYZE TABLE command, the table will not be
analyzed again.

4.5.3 FLUSH Syntax

FLUSH flush_option [,flush_option]

You should use the FLUSH command if you want to clear some of the internal caches
MySQL uses. To execute FLUSH, you must have the RELOAD privilege.

flush_option can be any of the following:

Empties the host cache tables. You should flush the host tables if
some of your hosts change IP number or if you get the error
message Host ... is blocked. When more than
max_connect_errors errors occur in a row for a given host while
HOSTS
connection to the MySQL server, MySQL assumes something is
wrong and blocks the host from further connection requests.
Flushing the host tables allows the host to attempt to connect
again. See section A.2.4 Host '...' is blocked Error.) You
can start mysqld with -O max_connection_errors=999999999
to avoid this error message.
Closes and reopens all log files. If you have specified the update
log file or a binary log file without an extension, the extension
number of the log file will be incremented by one relative to the
LOGS previous file. If you have used an extension in the file name,
MySQL will close and reopen the update log file. See section
4.9.3 The Update Log. This is the same thing as sending the
SIGHUP signal to the mysqld server.
PRIVILEGES Reloads the privileges from the grant tables in the mysql database.
TABLES Closes all open tables and force all tables in use to be closed.
[TABLE | TABLES]
table_name Flushes only the given tables.
[,table_name...]
 Closes all open tables and locks all tables for all databases with a
TABLES WITH READ read until one executes UNLOCK TABLES. This is very convenient
LOCK way to get backups if you have a file system, like Veritas,that can
take snapshots in time.
STATUS
Resets most status variables to zero. This is something one should
only use when debugging a query.

You can also access each of the commands shown above with the mysqladmin utility,
using the flush-hosts, flush-logs, reload, or flush-tables commands.

Take also a look at the RESET command used with replication. See section 4.10.6 SQL
Commands Related to Replication.

4.5.4 KILL Syntax

KILL thread_id

Each connection to mysqld runs in a separate thread. You can see which threads are
running with the SHOW PROCESSLIST command and kill a thread with the KILL
thread_id command.

If you have the process privilege, you can see and kill all threads. Otherwise, you can see
and kill only your own threads.

You can also use the mysqladmin processlist and mysqladmin kill commands to
examine and kill threads.

When you do a KILL, a thread specific kill flag is set for the thread.

In most cases it may take some time for the thread to die as the kill flag is only checked at
specific intervals.

In SELECT, ORDER BY and GROUP BY loops, the flag is checked after reading a
block of rows. If the kill flag is set the statement is aborted
When doing an ALTER TABLE the kill flag is checked before each block of rows
are read from the original table. If the kill flag was set the command is aborted
and the temporary table is deleted.
When doing an UPDATE TABLE and DELETE TABLE, the kill flag is checked after
each block read and after each updated or delete row. If the kill flag is set the
statement is aborted. Note that if you are not using transactions, the changes will
not be rolled back!
GET_LOCK() will abort with NULL.
An INSERT DELAYED thread will quickly flush all rows it has in memory and die.
If the thread is in the table lock handler (state: Locked), the table lock will be
quickly aborted.
 If the thread is waiting for free disk space in a write call, the write is aborted
with an disk full error message.

4.5.5 SHOW Syntax

SHOW DATABASES [LIKE wild]
or SHOW [OPEN] TABLES [FROM db_name] [LIKE wild]
or SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
or SHOW INDEX FROM tbl_name [FROM db_name]
or SHOW TABLE STATUS [FROM db_name] [LIKE wild]
or SHOW STATUS [LIKE wild]
or SHOW VARIABLES [LIKE wild]
or SHOW LOGS
or SHOW [FULL] PROCESSLIST
or SHOW GRANTS FOR user
or SHOW CREATE TABLE table_name
or SHOW MASTER STATUS
or SHOW MASTER LOGS
or SHOW SLAVE STATUS

SHOW provides information about databases, tables, columns, or status information about
the server. If the LIKE wild part is used, the wild string can be a string that uses the SQL
`%' and `_' wild-card characters.

4.5.5.1 Retrieving information about Database, Tables, Columns, and Indexes

You can use db_name.tbl_name as an alternative to the tbl_name FROM db_name

syntax. These two statements are equivalent:

mysql> SHOW INDEX FROM mytable FROM mydb;

mysql> SHOW INDEX FROM mydb.mytable;

SHOW DATABASES lists the databases on the MySQL server host. You can also get this list
using the mysqlshow command.

SHOW TABLES lists the tables in a given database. You can also get this list using the
mysqlshow db_name command.

NOTE: If a user doesn't have any privileges for a table, the table will not show up in the
output from SHOW TABLES or mysqlshow db_name.

SHOW OPEN TABLES lists the tables that are currently open in the table cache. See section
5.4.6 How MySQL Opens and Closes Tables. The Comment field tells how many times
the table is cached and in_use.

SHOW COLUMNS lists the columns in a given table. If you specify the FULL option, you will
also get the privileges you have for each column. If the column types are different than
you expect them to be based on a CREATE TABLE statement, note that MySQL sometimes
changes column types. See section 6.5.3.1 Silent Column Specification Changes.
The DESCRIBE statement provides information similar to SHOW COLUMNS. See section
6.6.2 DESCRIBE Syntax (Get Information About Columns).

SHOW FIELDS is a synonym for SHOW COLUMNS, and SHOW KEYS is a synonym for SHOW
INDEX. You can also list a table's columns or indexes with mysqlshow db_name
tbl_name or mysqlshow -k db_name tbl_name.

SHOW INDEX returns the index information in a format that closely resembles the
SQLStatistics call in ODBC. The following columns are returned:

Column Meaning
Table Name of the table.
Non_unique 0 if the index can't contain duplicates.
Key_name Name of the index.
Seq_in_index Column sequence number in index, starting with 1.
Column_name Column name.

Collation
How the column is sorted in the index. In MySQL, this can have values
`A' (Ascending) or NULL (Not sorted).

Cardinality
Number of unique values in the index. This is updated by running
isamchk -a.

Sub_part
Number of indexed characters if the column is only partly indexed. NULL if
the entire key is indexed.
Comment Various remarks. For now, it tells whether index is FULLTEXT or not.

Note that as the Cardinality is counted based on statistics stored as integers, it's not
necessarily accurate for small tables.

4.5.5.2 SHOW TABLE STATUS

SHOW TABLE STATUS [FROM db_name] [LIKE wild]

SHOW TABLE STATUS (new in Version 3.23) works likes SHOW STATUS, but provides a lot
of information about each table. You can also get this list using the mysqlshow --status
db_name command. The following columns are returned:

Column Meaning
Name Name of the table.
Type Type of table. See section 7 MySQL Table Types.
Row_format The row storage format (Fixed, Dynamic, or Compressed).
Rows Number of rows.
Avg_row_length Average row length.
Data_length Length of the data file.
Max_data_length Max length of the data file.
Index_length Length of the index file.
Data_free Number of allocated but not used bytes.
Auto_increment Next autoincrement value.
Create_time When the table was created.
Update_time When the data file was last updated.
Check_time When the table was last checked.
Create_options Extra options used with CREATE TABLE.
Comment
The comment used when creating the table (or some information why
MySQL couldn't access the table information).

InnoDB tables will report the free space in the tablespace in the table comment.

4.5.5.3 SHOW STATUS

SHOW STATUS provides server status information (like mysqladmin extended-status).

The output resembles that shown below, though the format and numbers probably differ:

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 155372598 |
| Bytes_sent | 1176560426 |
| Connections | 30023 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 8340 |
| Created_tmp_files | 60 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 462604 |
| Handler_read_first | 105881 |
| Handler_read_key | 27820558 |
| Handler_read_next | 390681754 |
| Handler_read_prev | 6022500 |
| Handler_read_rnd | 30546748 |
| Handler_read_rnd_next | 246216530 |
| Handler_update | 16945404 |
| Handler_write | 60356676 |
| Key_blocks_used | 14955 |
| Key_read_requests | 96854827 |
| Key_reads | 162040 |
| Key_write_requests | 7589728 |
| Key_writes | 3813196 |
| Max_used_connections | 0 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1 |
| Open_files | 2 |
| Open_streams | 0 |
| Opened_tables | 44600 |
| Questions | 2026873 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 99646 |
| Select_range_check | 0 |
| Select_scan | 30802 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 30 |
| Sort_range | 500 |
| Sort_rows | 30296250 |
| Sort_scan | 4650 |
| Table_locks_immediate | 1920382 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 30022 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 80380 |
+--------------------------+------------+

The status variables listed above have the following meaning:

Variable Meaning
Number of connections aborted because the client died
Aborted_clients without closing the connection properly. See section A.2.9
Communication Errors / Aborted Connection.
Number of tries to connect to the MySQL server that failed.
Aborted_connects See section A.2.9 Communication Errors / Aborted
Connection.
Bytes_received Number of bytes received from all clients.
Bytes_sent Number of bytes sent to all clients.
Com_xxxx Number of times the xxx commands has been executed.
Connections Number of connection attempts to the MySQL server.
Created_tmp_disk_tables
Number of implicit temporary tables on disk created while
executing statements.
Created_tmp_tables
Number of implicit temporary tables in memory created
while executing statements.
Created_tmp_files How many temporary files mysqld have created.
Delayed_insert_threads Number of delayed insert handler threads in use.
Delayed_writes Number of rows written with INSERT DELAYED.

Delayed_errors
Number of rows written with INSERT DELAYED for which
some error occurred (probably duplicate key).
Flush_commands Number of executed FLUSH commands.
Handler_delete Number of times a row was deleted from a table.
Number of times the first entry was read from an index. If
Handler_read_first
this is high, it suggests that the server is doing a lot of full
index scans, for example, SELECT col1 FROM foo,
assuming that col1 is indexed.
Number of requests to read a row based on a key. If this is
Handler_read_key high, it is a good indication that your queries and tables are
properly indexed.
Number of requests to read next row in key order. This will
Handler_read_next
be incremented if you are querying an index column with a
range constraint. This also will be incremented if you are
doing an index scan.
Number of requests to read a row based on a fixed position.
Handler_read_rnd This will be high if you are doing a lot of queries that
require sorting of the result.
Number of requests to read the next row in the datafile.
This will be high if you are doing a lot of table scans.
Handler_read_rnd_next Generally this suggests that your tables are not properly
indexed or that your queries are not written to take
advantage of the indexes you have.
Handler_update Number of requests to update a row in a table.
Handler_write Number of requests to insert a row in a table.
Key_blocks_used The number of used blocks in the key cache.
Key_read_requests The number of requests to read a key block from the cache.
Key_reads The number of physical reads of a key block from disk.
Key_write_requests The number of requests to write a key block to the cache.
Key_writes The number of physical writes of a key block to disk.
Max_used_connections
The maximum number of connections in use
simultaneously.
Not_flushed_key_blocks
Keys blocks in the key cache that has changed but hasn't
yet been flushed to disk.
Not_flushed_delayed_rows
Number of rows waiting to be written in INSERT DELAY
queues.
Open_tables Number of tables that are open.
Open_files Number of files that are open.
Open_streams Number of streams that are open (used mainly for logging).
Opened_tables Number of tables that have been opened.
Select_full_join
Number of joins without keys (If this is 0, you should
carefully check the index of your tables).
Select_full_range_join
Number of joins where we used a range search on reference
table.
Select_range
Number of joins where we used ranges on the first table.
(It's normally not critical even if this is big.)
Select_scan Number of joins where we did a full scann of the first table.
Number of joins without keys where we check for key
Select_range_check usage after each row (If this is 0, you should carefully
check the index of your tables).
Questions Number of queries sent to the server.
Slave_open_temp_tables
Number of temporary tables currently open by the slave
thread
Slow_launch_threads
Number of threads that have taken more than
slow_launch_time to create.

Slow_queries
Number of queries that have taken more than
long_query_time. See section 4.9.5 The Slow Query Log.
Number of merges passes the sort algoritm have had to do.
Sort_merge_passes If this value is large you should consider increasing
sort_buffer.
Sort_range Number of sorts that where done with ranges.
Sort_rows Number of sorted rows.
Sort_scan Number of sorts that where done by scanning the table.
Table_locks_immediate
Number of times a table lock was acquired immediately.
Available after 3.23.33.
Number of times a table lock could not be acquired
immediately and a wait was needed. If this is high, and you
Table_locks_waited have performance problems, you should first optimize your
queries, and then either split your table(s) or use
replication. Available after 3.23.33.
Threads_cached Number of threads in the thread cache.
Threads_connected Number of currently open connections.
Threads_created Number of threads created to handle connections.
Threads_running Number of threads that are not sleeping.
Uptime How many seconds the server has been up.

Some comments about the above:

If Opened_tables is big, then your table_cache variable is probably too small.

If key_reads is big, then your key_buffer_size variable is probably too small.
The cache hit rate can be calculated with key_reads/key_read_requests.
If Handler_read_rnd is big, then you probably have a lot of queries that require
MySQL to scan whole tables or you have joins that don't use keys properly.
If Threads_created is big, you may want to increase the thread_cache_size
variable.

4.5.5.4 SHOW VARIABLES

SHOW VARIABLES [LIKE wild]

SHOW VARIABLES shows the values of some MySQL system variables. You can also get
this information using the mysqladmin variables command. If the default values are
unsuitable, you can set most of these variables using command-line options when mysqld
starts up. See section 4.1.1 mysqld Command-line Options.

The output resembles that shown below, though the format and numbers may differ
somewhat:

+-------------------------+---------------------------+
| Variable_name | Value |
+-------------------------+---------------------------+
| ansi_mode | OFF |
| back_log | 50 |
| basedir | /my/monty/ |
| bdb_cache_size | 16777216 |
| bdb_log_buffer_size | 32768 |
| bdb_home | /my/monty/data/ |
| bdb_max_lock | 10000 |
| bdb_logdir | |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| binlog_cache_size | 32768 |
| concurrent_insert | ON |
| connect_timeout | 5 |
| datadir | /my/monty/data/ |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| have_bdb | YES |
| have_innodb | YES |
| have_raid | YES |
| have_ssl | NO |
| init_file | |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 16776192 |
| language | /my/monty/share/english/ |
| large_files_support | ON |
| log | OFF |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_connections | 100 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_recover_options | DEFAULT |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| open_files_limit | 0 |
| pid_file | /my/monty/data/donna.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 131072 |
| query_buffer_size | 0 |
| safe_show_database | OFF |
| server_id | 0 |
| skip_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer | 2097116 |
| table_cache | 64 |
| table_type | MYISAM |
| thread_cache_size | 4 |
| thread_stack | 65536 |
| tmp_table_size | 1048576 |
| tmpdir | /tmp/ |
| version | 3.23.29a-gamma-debug |
| wait_timeout | 28800 |
+-------------------------+---------------------------+

Each option is described below. Values for buffer sizes, lengths, and stack sizes are given
in bytes. You can specify values with a suffix of `K' or `M' to indicate kilobytes or
megabytes. For example, 16M indicates 16 megabytes. The case of suffix letters does not
matter; 16M and 16m are equivalent:

ansi_mode.
Is ON if mysqld was started with --ansi. See section 1.4.3 Running MySQL in
ANSI Mode.
back_log
The number of outstanding connection requests MySQL can have. This comes
into play when the main MySQL thread gets VERY many connection requests in
a very short time. It then takes some time (although very little) for the main thread
to check the connection and start a new thread. The back_log value indicates how
many requests can be stacked during this short time before MySQL momentarily
stops answering new requests. You need to increase this only if you expect a large
number of connections in a short period of time. In other words, this value is the
size of the listen queue for incoming TCP/IP connections. Your operating system
has its own limit on the size of this queue. The manual page for the Unix
listen(2) system call should have more details. Check your OS documentation
for the maximum value for this variable. Attempting to set back_log higher than
your operating system limit will be ineffective.
basedir
The value of the --basedir option.
bdb_cache_size
The buffer that is allocated to cache index and rows for BDB tables. If you don't
use BDB tables, you should start mysqld with --skip-bdb to not waste memory
for this cache.
bdb_log_buffer_size
The buffer that is allocated to cache index and rows for BDB tables. If you don't
use BDB tables, you should set this to 0 or start mysqld with --skip-bdb to not
waste memory for this cache.
bdb_home
The value of the --bdb-home option.
bdb_max_lock
The maximum number of locks (1000 by default) you can have active on a BDB
table. You should increase this if you get errors of type bdb: Lock table is
out of available locks or Got error 12 from ... when you have do long
transactions or when mysqld has to examine a lot of rows to calculate the query.
bdb_logdir
The value of the --bdb-logdir option.
bdb_shared_data
Is ON if you are using --bdb-shared-data.
bdb_tmpdir
The value of the --bdb-tmpdir option.
binlog_cache_size. The size of the cache to hold the SQL
statements for the binary log during a transaction. If you often use big, multi-
statement transactions you can increase this to get more performance. See section
6.7.1 BEGIN/COMMIT/ROLLBACK Syntax.
character_set
The default character set.
character_sets
The supported character sets.
concurrent_inserts
If ON (the default),MySQL will allow you to use INSERT on MyISAM tables at the
same time as you run SELECT queries on them. You can turn this option off by
starting mysqld with --safe or --skip-new.
connect_timeout
The number of seconds the mysqld server is waiting for a connect packet before
responding with Bad handshake.
datadir
The value of the --datadir option.
delay_key_write
If enabled (is on by default), MySQL will honor the delay_key_write option
CREATE TABLE. This means that the key buffer for tables with this option will not
get flushed on every index update, but only when a table is closed. This will speed
up writes on keys a lot, but you should add automatic checking of all tables with
myisamchk --fast --force if you use this. Note that if you start mysqld with
the --delay-key-write-for-all-tables option this means that all tables will
be treated as if they were created with the delay_key_write option. You can
clear this flag by starting mysqld with --skip-new or --safe-mode.
delayed_insert_limit
After inserting delayed_insert_limit rows, the INSERT DELAYED handler will
check if there are any SELECT statements pending. If so, it allows these to execute
before continuing.
delayed_insert_timeout
How long a INSERT DELAYED thread should wait for INSERT statements before
terminating.
delayed_queue_size
What size queue (in rows) should be allocated for handling INSERT DELAYED. If
the queue becomes full, any client that does INSERT DELAYED will wait until there
is room in the queue again.
flush
This is ON if you have started MySQL with the --flush option.
flush_time
If this is set to a non-zero value, then every flush_time seconds all tables will be
closed (to free up resources and sync things to disk). We only recommend this
option on Win95, Win98, or on systems where you have very little resources.
have_bdb
YES if mysqld supports Berkeley DB tables. DISABLED if --skip-bdb is used.
have_innodb
YES if mysqld supports InnoDB tables. DISABLED if --skip-innodb is used.
have_raid
YES if mysqld supports the RAID option.
have_ssl
YES if mysqld supports SSL (encryption) on the client/server protocol.
init_file
 The name of the file specified with the --init-file option when you start the
server. This is a file of SQL statements you want the server to execute when it
starts.
interactive_timeout
The number of seconds the server waits for activity on an interactive connection
before closing it. An interactive client is defined as a client that uses the
CLIENT_INTERACTIVE option to mysql_real_connect(). See also
wait_timeout.
join_buffer_size
The size of the buffer that is used for full joins (joins that do not use indexes). The
buffer is allocated one time for each full join between two tables. Increase this
value to get a faster full join when adding indexes is not possible. (Normally the
best way to get fast joins is to add indexes.)
key_buffer_size
Index blocks are buffered and are shared by all threads. key_buffer_size is the
size of the buffer used for index blocks. Increase this to get better index handling
(for all reads and multiple writes) to as much as you can afford; 64M on a 256M
machine that mainly runs MySQL is quite common. If you, however, make this
too big (more than 50% of your total memory?) your system may start to page and
become REALLY slow. Remember that because MySQL does not cache data
read, that you will have to leave some room for the OS filesystem cache. You can
check the performance of the key buffer by doing show status and examine the
variables Key_read_requests, Key_reads, Key_write_requests, and
Key_writes. The Key_reads/Key_read_request ratio should normally be <
0.01. The Key_write/Key_write_requests is usually near 1 if you are using
mostly updates/deletes but may be much smaller if you tend to do updates that
affect many at the same time or if you are using delay_key_write. See section
4.5.5 SHOW Syntax. To get even more speed when writing many rows at the same
time, use LOCK TABLES. See section 6.7.2 LOCK TABLES/UNLOCK TABLES Syntax.
language
The language used for error messages.
large_file_support
If mysqld was compiled with options for big file support.
locked_in_memory
If mysqld was locked in memory with --memlock
log
If logging of all queries is enabled.
log_update
If the update log is enabled.
log_bin
If the binary log is enabled.
log_slave_updates
If the updates from the slave should be logged.
long_query_time
If a query takes longer than this (in seconds), the Slow_queries counter will be
incremented. If you are using --log-slow-queries, the query will be logged to
the slow query logfile. See section 4.9.5 The Slow Query Log.
lower_case_table_names
 If set to 1 table names are stored in lowercase on disk and table names will be
case-insensitive. See section 6.1.3 Case Sensitivity in Names.
max_allowed_packet
The maximum size of one packet. The message buffer is initialized to
net_buffer_length bytes, but can grow up to max_allowed_packet bytes when
needed. This value by default is small, to catch big (possibly wrong) packets. You
must increase this value if you are using big BLOB columns. It should be as big as
the biggest BLOB you want to use. The current protocol limits
max_allowed_packet to 16M.
max_binlog_cache_size
If a multi-statement transaction requires more than this amount of memory, one
will get the error "Multi-statement transaction required more than
'max_binlog_cache_size' bytes of storage".
max_binlog_size
Available after 3.23.33. If a write to the binary (replication) log exceeds the given
value, rotate the logs. You cannot set it to less than 1024 bytes, or more than 1
GB. Default is 1 GB.
max_connections
The number of simultaneous clients allowed. Increasing this value increases the
number of file descriptors that mysqld requires. See below for comments on file
descriptor limits. See section A.2.5 Too many connections Error.
max_connect_errors
If there is more than this number of interrupted connections from a host this host
will be blocked from further connections. You can unblock a host with the
command FLUSH HOSTS.
max_delayed_threads
Don't start more than this number of threads to handle INSERT DELAYED
statements. If you try to insert data into a new table after all INSERT DELAYED
threads are in use, the row will be inserted as if the DELAYED attribute wasn't
specified.
max_heap_table_size
Don't allow creation of heap tables bigger than this.
max_join_size
Joins that are probably going to read more than max_join_size records return an
error. Set this value if your users tend to perform joins that lack a WHERE clause,
that take a long time, and that return millions of rows.
max_sort_length
The number of bytes to use when sorting BLOB or TEXT values (only the first
max_sort_length bytes of each value are used; the rest are ignored).
max_user_connections
The maximum number of active connections for a single user (0 = no limit).
max_tmp_tables
(This option doesn't yet do anything.) Maximum number of temporary tables a
client can keep open at the same time.
max_write_lock_count
After this many write locks, allow some read locks to run in between.
myisam_recover_options
The value of the --myisam-recover option.
myisam_sort_buffer_size
 The buffer that is allocated when sorting the index when doing a REPAIR or when
creating indexes with CREATE INDEX or ALTER TABLE.
myisam_max_extra_sort_file_size.
If the creating of the temporary file for fast index creation would be this much
bigger than using the key cache, then prefer the key cache method. This is mainly
used to force long character keys in large tables to use the slower key cache
method to create the index. NOTE that this parameter is given in megabytes!
myisam_max_sort_file_size
The maximum size of the temporary file MySQL is allowed to use while
recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE. If the
file size would be bigger than this, the index will be created through the key cache
(which is slower). NOTE that this parameter is given in megabytes!
net_buffer_length
The communication buffer is reset to this size between queries. This should not
normally be changed, but if you have very little memory, you can set it to the
expected size of a query. (That is, the expected length of SQL statements sent by
clients. If statements exceed this length, the buffer is automatically enlarged, up to
max_allowed_packet bytes.)
net_read_timeout
Number of seconds to wait for more data from a connection before aborting the
read. Note that when we don't expect data from a connection, the timeout is
defined by write_timeout. See also slave_read_timeout.
net_retry_count
If a read on a communication port is interrupted, retry this many times before
giving up. This value should be quite high on FreeBSD as internal interrupts are
sent to all threads.
net_write_timeout
Number of seconds to wait for a block to be written to a connection before
aborting the write.
open_files_limit
If this is not 0, then mysqld will use this value to reserve file descriptors to use
with setrlimit(). If this value is 0 then mysqld will reserve
max_connections*5 or max_connections + table_cache*2 (whichever is
larger) number of files. You should try increasing this if mysqld gives you the
error 'Too many open files'.
pid_file
The value of the --pid-file option.
port
The value of the --port option.
protocol_version
The protocol version used by the MySQL server.
record_buffer
Each thread that does a sequential scan allocates a buffer of this size for each table
it scans. If you do many sequential scans, you may want to increase this value.
record_rnd_buffer
When reading rows in sorted order after a sort, the rows are read through this
buffer to avoid a disk seeks. If not set, then it's set to the value of record_buffer.
query_buffer_size
 The initial allocation of the query buffer. If most of your queries are long (like
when inserting blobs), you should increase this!
safe_show_databases
Don't show databases for which the user doesn't have any database or table
privileges. This can improve security if you're concerned about people being able
to see what databases other users have. See also skip_show_databases.
server_id
The value of the --server-id option.
skip_locking
Is OFF if mysqld uses external locking.
skip_networking
Is ON if we only allow local (socket) connections.
skip_show_databases
This prevents people from doing SHOW DATABASES if they don't have the
PROCESS_PRIV privilege. This can improve security if you're concerned about
people being able to see what databases other users have. See also
safe_show_databases.
slave_read_timeout
Number of seconds to wait for more data from a master/slave connection before
aborting the read.
slow_launch_time
If creating the thread takes longer than this value (in seconds), the
Slow_launch_threads counter will be incremented.
socket
The Unix socket used by the server.
sort_buffer
Each thread that needs to do a sort allocates a buffer of this size. Increase this
value for faster ORDER BY or GROUP BY operations. See section A.4.4 Where
MySQL Stores Temporary Files.
table_cache
The number of open tables for all threads. Increasing this value increases the
number of file descriptors that mysqld requires. MySQL needs two file
descriptors for each unique open table. See below for comments on file descriptor
limits. You can check if you need to increase the table cache by checking the
Opened_tables variable. See section 4.5.5 SHOW Syntax. If this variable is big and
you don't do FLUSH TABLES a lot (which just forces all tables to be closed and
reopenend), then you should increase the value of this variable. Make sure that
your operating system can handle the number of open file descriptors implied by
the table_cache setting. If table_cache is set too high, MySQL may run out of
file descriptors and refuse connections, fail to perform queries, and be very
unreliable. For information about how the table cache works, see section 5.4.6
How MySQL Opens and Closes Tables.
table_type
The default table type
thread_cache_size
How many threads we should keep in a cache for reuse. When a client
disconnects, the client's threads are put in the cache if there aren't more than
thread_cache_size threads from before. All new threads are first taken from the
 cache, and only when the cache is empty is a new thread created. This variable
can be increased to improve performance if you have a lot of new connections.
(Normally this doesn't give a notable performance improvement if you have a
good thread implementation.) By examing the difference between the
Connections and Threads_created you can see how efficient the current thread
cache is for you.
thread_concurrency
On Solaris, mysqld will call thr_setconcurrency() with this value.
thr_setconcurrency() permits the application to give the threads system a hint
for the desired number of threads that should be run at the same time.
thread_stack
The stack size for each thread. Many of the limits detected by the crash-me test
are dependent on this value. The default is large enough for normal operation. See
section 5.1.4 The MySQL Benchmark Suite.
timezone
The timezone for the server.
tmp_table_size
If an in-memory temporary table exceeds this size, MySQL will automatically
convert it to an on-disk MyISAM table. Increase the value of tmp_table_size if
you do many advanced GROUP BY queries and you have lots of memory.
tmpdir
The directory used for temporary files and temporary tables.
version
The version number for the server.
wait_timeout
The number of seconds the server waits for activity on a connection before
closing it. See also interactive_timeout.

The manual section that describes tuning MySQL contains some information of how to
tune the above variables. See section 5.5.2 Tuning Server Parameters.

4.5.5.5 SHOW LOGS

SHOW LOGS shows you status information about existing log files. It currently only
displays information about Berkeley DB log files.

File shows the full path to the log file

Type shows the type of the log file (BDB for Berkeley DB log files)
Status shows the status of the log file (FREE if the file can be removed, or IN
USE if the file is needed by the transaction subsystem)

4.5.5.6 SHOW PROCESSLIST

SHOW PROCESSLIST shows you which threads are running. You can also get this
information using the mysqladmin processlist command. If you have the process
privilege, you can see all threads. Otherwise, you can see only your own threads. See
section 4.5.4 KILL Syntax. If you don't use the FULL option, then only the first 100
characters of each query will be shown.

This command is very useful if you get the 'too many connections' error message and
want to find out what's going on. MySQL reserves one extra connection for a client with
the Process_priv privilege to ensure that you should always be able to login and check
the system (assuming you are not giving this privilege to all your users).

4.5.5.7 SHOW GRANTS

SHOW GRANTS FOR user lists the grant commands that must be issued to duplicate the
grants for a user.

mysql> SHOW GRANTS FOR root@localhost;

+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

4.5.5.8 SHOW CREATE TABLE

Shows a CREATE TABLE statement that will create the given table:

mysql> show create table t\G

*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE t (
id int(11) default NULL auto_increment,
s char(60) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM

SHOW CREATE TABLE will quote table and column names according to
SQL_QUOTE_SHOW_CREATE option. section 5.5.6 SET Syntax.