Database Administration Language Reference (MySQL 4.0)

C++ CSS HTML Java JavaScript MySQL Oracle PERL PHP SQL Unix VBScript XHTML XML Сети

Database Administration Language Reference
==========================================

`OPTIMIZE TABLE' Syntax
-----------------------

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]...

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 datafile.

certain
tables.

For the moment, `OPTIMIZE TABLE' works only on `MyISAM' and `BDB'
tables. For `BDB' tables, `OPTIMIZE TABLE' is currently mapped to
`ANALYZE TABLE'. *Note `ANALYZE TABLE': ANALYZE TABLE.

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.

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

was used.

`ANALYZE TABLE' Syntax
----------------------

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name...]

Analyse and store the key distribution for the table. During the
analysis, 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.
*Note Show database info::.

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

was used.

`FLUSH' Syntax
--------------

FLUSH [LOCAL | NO_WRITE_TO_BINLOG] 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:

you get the error message `Host ... is blocked'.
When more than `max_connect_errors' errors occur in a
row for a given host while 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. *Note Blocked host::. You can start `mysqld'
with `-O max_connect_errors=999999999' to avoid this
error message.
`DES_KEY_FILE' Reloads the DES keys from the file that was specified
with the `--des-key-file' option at server startup
time.
`LOGS' Closes and reopens all log files. If you have
specified an 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
previous file. If you have used an extension in the
file name, MySQL will close and reopen the update log
file. *Note 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.
`QUERY CACHE' Defragment the query cache to better utilise its
memory. This command will not remove any queries
from the cache, unlike `RESET QUERY CACHE'.
`TABLES' Closes all open tables and force all tables in use to
be closed. This also flushes the query cache.
`[TABLE | TABLES] Flushes only the given tables.
tbl_name
[,tbl_name...]'
`TABLES WITH READ Closes all open tables and locks all tables for all
LOCK' databases with a read lock until you execute `UNLOCK
TABLES'. This is very convenient way to get backups
if you have a filesystem, 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.
`USER_RESOURCES' Resets all user resources to zero. This will enable
blocked users to login again. *Note User resources::.

used,
or unless the command contained one of these arguments: `LOGS',
`MASTER', `SLAVE', `TABLES WITH READ LOCK', because any of these
arguments may cause problems if replicated to a slave.

You can also access some of the commands shown above with the
`mysqladmin' utility, using the `flush-hosts', `flush-logs',
`flush-privileges', `flush-status' or `flush-tables' commands.

Take also a look at the `RESET' command used with replication. *Note
`RESET': RESET.

`RESET' Syntax
--------------

RESET reset_option [,reset_option] ...

The `RESET' command is used to clear things. It also acts as an stronger
version of the `FLUSH' command. *Note `FLUSH': FLUSH.

To execute `RESET', you must have the `RELOAD' privilege.

`FLUSH MASTER'. *Note Replication
Master SQL::.
`SLAVE' Makes the slave forget its replication position in
the master binlogs. Previously named `FLUSH SLAVE'.
*Note Replication Slave SQL::.
`QUERY CACHE' Removes all query results from the query cache.

`PURGE MASTER LOGS' Syntax
--------------------------

PURGE {MASTER|BINARY} LOGS TO binlog_name
PURGE {MASTER|BINARY} LOGS BEFORE date

This command is used to delete all binary logs strictly prior to the
specified binlog or date. *Note Replication Master SQL::.

`PURGE BINARY LOGS' is available as a synonym for `PURGE MASTER LOGS'
as of MySQL 4.1.1.

`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 all threads. If you
have the `SUPER' privilege, you can kill all threads. Otherwise, you
can only see and kill your own threads.

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

of its own.

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.

deleted.

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.

`SHOW' Syntax
-------------

or SHOW TABLE STATUS [FROM db_name] [LIKE wild]
or SHOW STATUS [LIKE wild]
or SHOW VARIABLES [LIKE wild]
or SHOW [BDB] 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
or SHOW WARNINGS [LIMIT row_count]
or SHOW ERRORS [LIMIT row_count]
or SHOW TABLE TYPES

wildcard
characters.

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;

some kind of
privilege, if you don't have the global `SHOW DATABASES' privilege.

`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. *Note Table cache::. The `Comment' field tells how many
times the table is `cached' and `in_use'.

them to
be based on a `CREATE TABLE' statement, note that MySQL sometimes
changes column types. *Note Silent column changes::. As of MySQL 4.1,
the `FULL' keyword also causes any per-column comments to be displayed.

The `DESCRIBE' statement provides information similar to `SHOW COLUMNS'.
*Note `DESCRIBE': DESCRIBE.

tbl_name'.

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

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.
`Null' Contains 'YES' if the column may
contain `NULL'.
`Index_type'Index method used.
`Comment' Various remarks. For now, it tells in
MySQL < 4.0.2 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.

The `Null' and `Index_type' columns were added in MySQL 4.0.2.

`SHOW TABLE STATUS'
...................

SHOW TABLE STATUS [FROM db_name] [LIKE wild]

following columns are returned:

*Column* *Meaning*
`Name' Name of the table.
`Type' Type of table. *Note 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 datafile.
`Max_data_length'Max length of the datafile. For fixed row
formats, this is the max number of rows in the
table. For dynamic row formats, this is the
total number of data bytes that can be stored in
the table, given the data pointer size used.
`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 datafile 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.

`SHOW STATUS'
.............

`SHOW STATUS' provides server status information (like `mysqladmin
extended-status'). The output resembles that shown here, though the
format and numbers probably differ:

| 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*
`Aborted_clients' Number of connections aborted because the client
died without closing the connection properly.
*Note Communication errors::.
`Aborted_connects' Number of tries to connect to the MySQL server
that failed. *Note Communication errors::.
`Bytes_received' Number of bytes received from all clients.
`Bytes_sent' Number of bytes sent to all clients.
`Com_xxx' Number of times each xxx command 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' has 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_commit' Number of internal `COMMIT' commands.
`Handler_delete' Number of times a row was deleted from a table.
`Handler_read_first' Number of times the first entry was read from an
index. If 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.
`Handler_read_key' Number of requests to read a row based on a key.
If this is high, it is a good indication that
your queries and tables are properly indexed.
`Handler_read_next' Number of requests to read next row in key order.
This will 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.
`Handler_read_prev' Number of requests to read previous row in key
order. This is mainly used to optimise `ORDER BY
... DESC'.
`Handler_read_rnd' Number of requests to read a row based on a fixed
position. This will be high if you are doing a
lot of queries that require sorting of the result.
`Handler_read_rnd_next'Number of requests to read the next row in the
datafile. This will be high if you are doing a
lot of table scans. 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_rollback' Number of internal `ROLLBACK' commands.
`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.
`Rpl_status' Status of failsafe replication. (Not yet in use).
`Select_full_join' Number of joins without keys (If this is not 0,
you should carefully check the indexes 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 scan of the
first table.
`Select_range_check' Number of joins without keys where we check for
key usage after each row (If this is not 0, you
should carefully check the indexes 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
`Slave_running' Is `ON' if this is a slave that is connected to a
master.
`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' seconds. *Note Slow query log::.
`Sort_merge_passes' Number of merges passes the sort algoritm have
had to do. If this value is large you should
consider increasing `sort_buffer'.
`Sort_range' Number of sorts that were done with ranges.
`Sort_rows' Number of sorted rows.
`Sort_scan' Number of sorts that were done by scanning the
table.
`ssl_xxx' Variables used by SSL; Not yet implemented.
`Table_locks_immediate'Number of times a table lock was acquired
immediately. Available after 3.23.33.
`Table_locks_waited' Number of times a table lock could not be
acquired immediately and a wait was needed. If
this is high, and you have performance problems,
you should first optimise 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 miss 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. The cache hit rate can be calculated
with `Threads_created'/`Connections'.

* If `Created_tmp_disk_tables' is big, you may want to increase the
`tmp_table_size' variable to get the temporary tables memory-based
instead of disk based.

`SHOW VARIABLES'
................

SHOW [GLOBAL | SESSION] VARIABLES [LIKE wild]

to
MySQL. With `SESSION' you will get the values that are in effect for
the current connection. If you are not using either option, `SESSION'
is used.

with the `SET' statement. *Note `SET': SET OPTION.

The output from `SHOW VARIABLES' resembles that shown in the following
list, though the format and numbers may differ somewhat. You can also
get this information using the `mysqladmin variables' command.

+---------------------------------+------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------|
| back_log | 50 |
| basedir | /usr/local/mysql |
| bdb_cache_size | 8388572 |
| bdb_log_buffer_size | 32768 |
| bdb_home | /usr/local/mysql |
| bdb_max_lock | 10000 |
| bdb_logdir | |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| bdb_version | Sleepycat Software: ... |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set | latin1 |
| character_sets | latin1 big5 czech euc_kr |
| concurrent_insert | ON |
| connect_timeout | 5 |
| convert_character_set | |
| datadir | /usr/local/mysql/data/ |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_min_word_len | 4 |
| ft_max_word_len | 254 |
| ft_max_word_len_for_sort | 20 |
| ft_stopword_file | (built-in) |
| have_bdb | YES |
| have_innodb | YES |
| have_isam | YES |
| have_raid | NO |
| have_symlink | DISABLED |
| have_openssl | YES |
| have_query_cache | YES |
| init_file | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_file_io_threads | 4 |
| innodb_force_recovery | 0 |
| innodb_thread_concurrency | 8 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_fast_shutdown | ON |
| innodb_flush_method | |
| innodb_lock_wait_timeout | 50 |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 16773120 |
| language | /usr/local/mysql/share/... |
| large_files_support | ON |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | OFF |
| max_allowed_packet | 1047552 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 100 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_relay_log_size | 0 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_max_extra_sort_file_size | 268435456 |
| myisam_repair_threads | 1 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | force |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| open_files_limit | 1024 |
| pid_file | /usr/local/mysql/name.pid |
| port | 3306 |
| protocol_version | 10 |
| query_cache_limit | 1048576 |
| query_cache_size | 0 |
| query_cache_type | ON |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| rpl_recovery_rank | 0 |
| safe_show_database | OFF |
| server_id | 0 |
| slave_net_timeout | 3600 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer_size | 2097116 |
| sql_mode | |
| table_cache | 64 |
| table_type | MYISAM |
| thread_cache_size | 3 |
| thread_stack | 131072 |
| tx_isolation | READ-COMMITTED |
| timezone | EEST |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/:/mnt/hd2/tmp/ |
| version | 4.0.4-beta |
| wait_timeout | 28800 |
+---------------------------------+------------------------------+

`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'.
*Note 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.

not
waste memory for this cache.

* `bdb_home' The value of the `--bdb-home' option.

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.

get
more performance. *Note COMMIT::.

and
`LOAD DATA INFILE') faster. This variable limits the size of the
cache tree in bytes per thread. Setting it to 0 will disable this
optimisation. *Note*: this cache is only used when adding data to
non-empty table. Default value is 8 MB.

* `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' Option for MyISAM tables. Can have one of the
following values:

OFF All `CREATE TABLE ... DELAYED_KEY_WRITE' are
ignored.
ON (default) MySQL will honor the
`DELAY_KEY_WRITE' option for `CREATE TABLE'.
ALL All new opened tables are treated as if they
were created with the `DELAY_KEY_WRITE' option.

If `DELAY_KEY_WRITE' is enabled 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.

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 Windows 9x/Me, or on systems where you have very little
resources.

* `ft_boolean_syntax' List of operators supported by `MATCH ...
AGAINST(... IN BOOLEAN MODE)'. *Note Fulltext Search::.

4.0.)

* `ft_max_word_len' The maximum length of the word to be included in
a `FULLTEXT' index. *Note: `FULLTEXT' indexes must be rebuilt
after changing this variable.* (This option is new for MySQL 4.0.)

* `ft_max_word_len_for_sort' The maximum length of the word in a
`FULLTEXT' index to be used in fast index recreation method in
`REPAIR', `CREATE INDEX', or `ALTER TABLE'. Longer words are
inserted the slow way. The rule of the thumb is as follows: with
`ft_max_word_len_for_sort' increasing, *MySQL* will create bigger
temporary files (thus slowing the process down, due to disk I/O),
and will put fewer keys in one sort block (again, decreasing the
efficiency). When `ft_max_word_len_for_sort' is too small,
instead, *MySQL* will insert a lot of words into index the slow
way, but short words will be inserted very quickly.

* `ft_stopword_file' The file from which to read the list of
stopwords for full-text searches. All the words from the file
will be used; comments are *not* honored. By default, built-in
list of stopwords is used (as defined in `myisam/ft_static.c').
Setting this parameter to an empty string (`""') will disable
stopword filtering. *Note: `FULLTEXT' indexes must be rebuilt
after changing this variable.* (This option is new for MySQL
4.0.10)

* `have_innodb' `YES' if `mysqld' supports InnoDB tables. `DISABLED'
if `--skip-innodb' is used.

* `have_bdb' `YES' if `mysqld' supports Berkeley DB tables.
`DISABLED' if `--skip-bdb' is used.

* `have_raid' `YES' if `mysqld' supports the `RAID' option.

* `have_openssl' `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 (for instance more than 50% of your total
memory) your system may start to page and become extremely slow.
Remember that because MySQL does not cache data reads, 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'. *Note `SHOW': SHOW.

To get even more speed when writing many rows at the same time, use
`LOCK TABLES'. *Note `LOCK TABLES': LOCK TABLES.

* `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.

query
logfile. This value is measured in real time, not CPU time, so a
query that may be under the threshold on a lightly loaded system
may be above the threshold on a heavily loaded one. *Note Slow
query log::.

* `lower_case_table_names' If set to 1 table names are stored in
lowercase on disk and table name comparisons will be
case-insensitive. From version 4.0.2, this option also applies to
database names. From 4.1.1 this option also applies to table
alias. *Note Name case sensitivity::.

* `max_allowed_packet' The maximum size of one packet. The message
buffer is initialised 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 protocol
limits for `max_allowed_packet' is 16M in MySQL 3.23 and 1G in
MySQL 4.0.

* `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 4096 bytes (1024 in MySQL versions
older than 4.0.14), or more than 1 GB. Default is 1 GB. Note if
you are using transactions: a transaction is written in one chunk
to the binary log, hence it is never split between several binary
logs. Therefore, if you have big transactions, you may see binlogs
bigger than `max_binlog_size'. If `max_relay_log_size' (available
starting from MySQL 4.0.14) is 0, then `max_binlog_size' will
apply to relay logs as well.

* `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. *Note Too many connections::.

* `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. If you set this to 0, MySQL will never create a
max_delayed thread.

* `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.

rotate the relay log. This variable enables you to put different
size constraints on relay logs and binary logs. However, setting
the variable to 0 will make MySQL use `max_binlog_size' for both
binary logs and relay logs. You have to set `max_relay_log_size'
to 0 or more than 4096, and less than 1 GB. Default is 0.

* `max_seeks_for_key' Limit assumed max number of seeks when looking
up rows based on a key. The MySQL optimiser will assume that when
searching after matching rows in a table through scanning a key,
we will not cause more than this number of key seeks independent
of the cardinality of the key. By setting this to a low value
(100 ?) you can force MySQL to prefer keys instead of table scans.

* `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 temporary file used for
fast index creation would be bigger than using the key cache by
the amount specified here, 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 before 4.0.3 and in bytes
beginning with this version.

* `myisam_repair_threads'. If this value is greater than one,
MyISAM table indexes during `Repair by sorting' process will be
created in parallel - each index in its own thread. *Note:*
multi-threaded repair is still *alpha* quality code.

* `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 before 4.0.3 and in bytes beginning with this version.

* `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_net_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' Number of files the system allows mysqld to
open. This is the real value given for the system and may be
different from the value you gave mysqld as a startup parameter.
This is 0 on systems where MySQL can't change the number of 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.

* `range_alloc_block_size' Size of blocks that are allocated when
doing range optimization.

* `read_buffer_size' (was `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.

* `read_rnd_buffer_size' (was `record_rnd_buffer') When reading rows
in sorted order after a sort, the rows are read through this
buffer to avoid a disk seeks. Can improve `ORDER BY' by a lot if
set to a high value. As this is a thread-specific variable, one
should not set this big globally, but just change this when running
some specific big queries.

* `query_alloc_block_size' Size of memory allocation blocks that are
allocated for objects created during query parsing and execution.
If you have problem with memory fragmentation it may help
increasing this a bit.

* `query_cache_limit' Don't cache results that are bigger than this.
(Default 1M).

* `query_cache_size' The memory allocated to store results from old
queries. If this is 0, the query cache is disabled (default).

* `query_cache_type' This may be set (only numeric) to
*Value**Alias* *Comment*
0 OFF Don't cache or retrieve results.
1 ON Cache all results except `SELECT SQL_NO_CACHE
...' queries.
2 DEMAND Cache only `SELECT SQL_CACHE ...' queries.

* `query_prealloc_size' Persistent buffer for query parsing and
execution. This one is not freed between queries. In theory, by
making this "big enough", make MySQL run queries without having to
do a single `malloc()' call.

* `safe_show_database' 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_database'.

* `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_database' This prevents people from doing `SHOW
DATABASES' if they don't have the `PROCESS' privilege. This can
improve security if you're concerned about people being able to
see what databases other users have. See also `safe_show_database'.

* `slave_net_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_size' 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. *Note Temporary files::.

* `table_cache' The number of open tables for all threads.
Increasing this value increases the number of file descriptors
that `mysqld' requires. You can check if you need to increase the
table cache by checking the `Opened_tables' variable. *Note
`Opened_tables': SHOW STATUS. 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.

For more information about the table cache, see *Note Table
cache::.

* `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' status variables (*note `SHOW STATUS': SHOW
STATUS. for details) you can see how efficient thread cache is.

desired number of threads that should be run at the same time.

Benchmarks::.

* `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.

be
used in round-robin fashion. This feature can be used to spread
load between several physical disks. It is possible to set
`tmpdir' to point to a memory-based filesystem, except if the
MySQL server is a slave. If it is a slave, it needs some of its
temporary files (for replication of temporary tables or of `LOAD
DATA INFILE') to survive a machine's reboot, so a memory-based
`tmpdir' which is cleared when the machine reboots is not
suitable; a disk-based `tmpdir' is necessary.

* `transaction_alloc_block_size' Size of memory allocation blocks
that are allocated for storing queries that are part of a
transaction that are to be stored in the binary log when doing a
commit.

* `transaction_prealloc_block_size' Persistent buffer for
`transaction_alloc_blocks'. that are not freed between queries. By
making this "big enough" to fit all queries in a common
transaction you can avoid a lot of `malloc()' calls.

* `version' The version number for the server.

* `wait_timeout' The number of seconds the server waits for activity
on a not interactive connection before closing it.

On thread startup `SESSION.WAIT_TIMEOUT' is initialised from
`GLOBAL.WAIT_TIMEOUT' or `GLOBAL.INTERACTIVE_TIMEOUT' depending on
the type of client (as defined by the `CLIENT_INTERACTIVE' connect
option). See also `interactive_timeout'.

The manual section that describes tuning MySQL contains some
information of how to tune the above variables. *Note Server
parameters::.

`SHOW [BDB] LOGS'
.................

* `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)

`SHOW PROCESSLIST'
..................

Otherwise,
you can see only your own threads. *Note `KILL': KILL. If you don't
use the `FULL' option, then only the first 100 characters of each query
will be shown.

Starting from 4.0.12, MySQL reports the hostname for TCP/IP connections
as `hostname:client_port' to make it easier to find out which client is
doing what.

that you
should always be able to login and check the system (assuming you are
not giving this privilege to all your users).

Some states commonly seen in `mysqladmin processlist'

* `Checking table' The thread is performing [automatic] checking of
the table.

have
a full disk or that the disk is not in very heavy use.

* `Connect Out' Slave connecting to master.

* `Creating tmp table' The thread is creating a temporary table to
hold a part of the result for the query.

* `deleting from main table' When executing the first part of a
multi-table delete and we are only deleting from the first table.

* `deleting from reference tables' When executing the second part of
a multi-table delete and we are deleting the matched rows from the
other tables.

* `Flushing tables' The thread is executing `FLUSH TABLES' and is
waiting for all threads to close their tables.

a short
time for the thread to die. If the thread is locked by some other
thread, the kill will take effect as soon as the other thread
releases its lock.

* `Sending data' The thread is processing rows for a `SELECT'
statement and is also sending data to the client.

* `Sorting for group' The thread is doing a sort to satisfy a `GROUP
BY'.

* `Sorting for order' The thread is doing a sort to satisfy a `ORDER
BY'.

`LOCK TABLE' can prevent opening a table until the command is
finished.

* `Removing duplicates' The query was using `SELECT DISTINCT' in
such a way that MySQL couldn't optimise that distinct away at an
early stage. Because of this MySQL has to do an extra stage to
remove all duplicated rows before sending the result to the client.

* `Reopen table' The thread got a lock for the table, but noticed
after getting the lock that the underlying table structure
changed. It has freed the lock, closed the table and is now trying
to reopen it.

* `Repair by sorting' The repair code is using sorting to create
indexes.

* `Repair with keycache' The repair code is using creating keys one
by one through the key cache. This is much slower than `Repair by
sorting'.

* `Searching rows for update' The thread is doing a first phase to
find all matching rows before updating them. This has to be done
if the `UPDATE' is changing the index that is used to find the
involved rows.

* `Sleeping' The thread is wating for the client to send a new
command to it.

* `System lock' The thread is waiting for getting to get a external
system lock for the table. If you are not using multiple mysqld
servers that are accessing the same tables, you can disable system
locks with the `--skip-external-locking' option.

* `Upgrading lock' The `INSERT DELAYED' handler is trying to get a
lock for the table to insert rows.

* `Updating' The thread is searching for rows to update and updating
them.

* `User Lock' The thread is waiting on a `GET_LOCK()'.

* `Waiting for tables' The thread got a notification that the
underlying structure for a table has changed and it needs to
reopen the table to get the new structure. To be able to reopen
the table it must however wait until all other threads have closed
the table in question.

This notification happens if another thread has used `FLUSH TABLES'
or one of the following commands on the table in question: `FLUSH
TABLES table_name', `ALTER TABLE', `RENAME TABLE', `REPAIR TABLE',
`ANALYZE TABLE' or `OPTIMIZE TABLE'.

* `waiting for handler insert' The `INSERT DELAYED' handler has
processed all inserts and are waiting to get new ones.

Most states are very quick operations. If threads last in any of these
states for many seconds, there may be a problem around that needs to be
investigated.

There are some other states that are not mentioned previously, but most
of these are only useful to find bugs in `mysqld'.

`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 |
+---------------------------------------------------------------------+

To list grants for the current session one may use `CURRENT_USER()'
function (new in version 4.0.6) to find out what user the session was
authenticated as. *Note `CURRENT_USER()': Miscellaneous functions.

`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. *Note `SET SQL_QUOTE_SHOW_CREATE': SET
OPTION.

`SHOW WARNINGS | ERRORS'
........................

SHOW WARNINGS [LIMIT row_count]
SHOW ERRORS [LIMIT row_count]

This command is implemented in MySQL 4.1.0.

It shows the errors, warnings and notes that one got for the last
command. The errors/warnings are reset for each new command that uses
a table.

The MySQL server sends back the total number of warnings and errors you
got for the last commend; This can be retrieved by calling
`mysql_warning_count()'.

Up to `max_error_count' messages are stored (Global and thread specific
variable).

You can retrieve the number of errors from `@error_count' and warnings
from `@warning_count'.

`SHOW WARNINGS' shows all errors, warnings and notes you got for the
last command while `SHOW ERRORS' only shows you the errors.

mysql> DROP TABLE IF EXISTS no_such_table;
mysql> SHOW WARNINGS;

|
+-------+------+-------------------------------+

Note that in MySQL 4.1.0 we have just added the frame work for warnings
and not many MySQL command do yet generate warnings. 4.1.1 supports all
kind of warnings for `LOAD DATA INFILE' and DML statements such as
`INSERT', `UPDATE' and `ALTER' commands.

For example, here is a simple case which produces conversion warnings
for a insert statement.

mysql> create table t1(a tinyint NOT NULL, b char(4));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(10,'mysql'),(NULL,'test'),(300,'open source');
Query OK, 3 rows affected, 4 warnings (0.15 sec)
Records: 3 Duplicates: 0 Warnings: 4

Maximum number of warnings can be specified using the server variable
`'max_error_count'', `SET max_error_count=[count]'; By default it is
64. In case to disable warnings, simply reset this variable to '0'. In
case if `max_error_count' is 0, then still the warning count represents
how many warnings have occurred, but none of the messages are stored.

For example, consider the following `ALTER' table statement for the
above example, which returns only one warning message even though total
warnings occurred is 3 when you set max_error_count=1.

mysql> set max_error_count=1;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t1 modify b char;
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 3

mysql>

`SHOW TABLE TYPES'
..................

SHOW TABLE TYPES

This command is implemented in MySQL 4.1.0.

mysql> SHOW TABLE TYPES;

The 'Support' option `DEFAULT' indicates whether the particular table
type is supported, and which is the default type. If the server is
started with `--default-table-type=InnoDB', then the InnoDB 'Support'
field will have the value `DEFAULT'.

`SHOW PRIVILEGES'
.................

SHOW PRIVILEGES

This command is implemented in MySQL 4.1.0.

`SHOW PRIVILEGES' shows the list of system privileges that the
underlying MySQL server supports.

[Назад] [Содержание] [Вперед]

Главная