Book HomeMySQL and mSQLSearch this book

Chapter 17. MySQL and mSQL Programs and Utilities

Contents:

MySQL Utilities
mSQL Utilities

Both MySQL and mSQL come prepackaged with a wealth of programs and utilities to make interacting with the database server easier. Some of these programs are used by the end user to read and write from the database, while others are meant for the database administrator to maintain and repair the database as a whole.

17.1. MySQL Utilities

isamchk

isamchk [options]table [table...]

Performs operations on the database table files themselves (called ISAM files for Indexed Sequential Access Method). This utility is used to check and repair the files, as well as report information about them. You must provide the correct path to the ISAM file you wish to examine. The default location for the ISAM files is /usr/local/var/databasename/tablename.ISM.

Options

-?, --help

Display usage information.

-# debuglevel, --debug=debuglevel

Set the debugging level to debuglevel. The debugging library used by MySQL has many options. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html. The most common set of options is d:t:o,/tmp/debugfile.

-a, --analyze

Analyze the distribution of keys within a table and make adjustments if performance would be improved.

-d, --description

Display short description of a table.

-e, --extend-check

Perform additional checks on the integrity of a table. With this option, you can be absolutely sure that the table is not damaged.

-f, --force

Overwrite without warning files that already exist. Also automatically recover damaged tables without notice.

-i, --information

Display full statistics the table(s) being checked.

k=number, --keys-used=number

Update only number of keys used. This option is used mainly to disable the keys (-k=0) to speed up other operations on the table such as a bulk load.

-l, --no-symlinks

Do not repair a table that is a symlink.

-q, --quick

Speed up repair by not examining the data file.

-r, --recover

Perform general repair on the table. Does not fix duplicate keys which are supposed to be unique.

-o, --safe-recover

Use an older, slower method of recovery that can repair some things that "-r" will miss.

-O, --set-variable

Set an option variable. See Chapter 16, "MySQL and mSQL System Variables", for a full list of usable variables.

-s, --silent

Display only errors

-S, --sort-index

Sort the index block of the table.

-R=index, --sort-records=index

Sort the records of the table by index within the table.

-u, --unpack

Decompress a file packed with pack_isam.

-v, --verbose

Display extra information.

-V, --version

Display version information.

-w, --wait

If the table is locked, wait for it to be unlocked. Without this option, ismchk will exit if it encounters a locked table.

isamlog

isamlog [options] [logfile] [table]

Displays information about ISAM logs. An ISAM log is generated if the MySQL server is started with the -log-isam option. The information in the ISAM log can be used to recover damaged tables using the -r option. However, modifying the data files directly can be dangerous and you should always back up your data before doing so.

Options

-?, --help

Display usage information

-# debuglevel

Set debugging level. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html.

-c number

Examine only the last number commands.

-f number

Maximum number of open files. When repairing large logs isamlog can have a number of tables open at once. If you consistently run out of file descriptors on your system, this option will limit the number of files isamlog uses. Instead of using more files, it will juggle data between the open files and memory, resulting in slower operation.

-F directory

Directory containing the ISAM log file.

-i

Display additional information.

-o number

Offset number commands before examining the log.

-p

Remove components from the path.

-r

Ignore errors while examining log. This option allows you to recover all of the information within a log file.

-R datafile recordnumber

Open an ISAM data file (a file ending with .ISM) and retrieve the data at recordnumber.

-u

Update the tables using the log information.

-v

Display extra information about the process.

-V

Display version information.

-w file

Write all records found using -R to a file.

mysql

mysql [options] [database]

The MySQL command line monitor. This program is the most basic way to communicate with the MySQL server. SQL commands can be typed directly on the command line and the results are displayed on the screen. If database is supplied, it is automatically selected as the current database.

The command line monitor works much like a bash shell, because it uses the same GNU readline function that bash uses. For example, you can complete a word by using the tab key, press Ctrl-a to jump to the start of the current line or Ctrl-e to jump to the end, press Ctrl-r to perform a reverse search, and use the up arrow to retrieve the previous command.

Statements can continue over multiple lines and are not acted upon until a command is given. When using full word commands (go, print, etc.) the command must be entered on a line by itself. Escape character commands (\g, \p, etc.) can be used at the end of any line. In addition, a semicolon can be used to end an SQL statement just like \g.

Commands

help, ?, \h

Display the list of commands.

clear, \c

Clear (ignore) the current statement.

edit, \e

Edit the current statement using the default editor.

exit.

Exit the program.

go, \g, ;

Send the current statement to the database server.

ego, \G

Send the current statement to the server and display the results vertically.

print, \p

Display the current statement.

quit, \q

Same as exit.

rehash, \#

Rebuild the index of completion terms.

status, \s

Display status information about the server and the current session.

use, \u

Select another database.

Options

-?, --help

Display usage information.

-# debuglevel. -debug=debuglevel

Set the debugging level. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html

-A, --no-auto-rehash.

Do not automatically rehash database information.

-B, --batch

Print results in `batch' mode. This provides minimally formatted output which can easily be used in other database applications.

-e statement, --execute=statement

Execute the given statement and quit. Automatically implies -B.

-f, --force

Do not stop processing when an SQL error is encountered.

-h host, --host=host

Connect to the database server on the specified host.

-n, --unbuffered

Do not buffer output between queries.

-O variable=value, --set-variable variable=value

Set an option variable. See Chapter 16, "MySQL and mSQL System Variables" for a full list of usable variables.

-p [password], --password[=password]

The password used to connect to the database server. If this option is used without a value, the password is asked from the command line.

-P port, --port=port

The port number used to connect to the database server.

-q, --quick

Display output as it comes from the server. If you suspend your terminal while using this option, the server could pause.

-r. -raw

Display output without any conversion. Only useful in conjunction with -B.

-s, --silent

Suppress some output.

-S file, --socket=file

The Unix socket file used to connect to the database server.

-t, --table

Display output in table format.

-T, --debug-info

Display debugging information when the program exits.

-u username, --user=username

Username used for connection with the database.

-v, --verbose

Display extra output.

-V, --version

Display version information

-w, --wait

Wait and attempt connection later if unable to connect to the database server.

mysqlaccess

mysqlaccess [options] [host] user database

Displays and modifies access rights for the MySQL server. You may examine the rights of users for any database and in connection with any host. Unix shell wildcards `*' and `?' may be used to match multiple hosts, users and databases. All actions are performed on a copy of the actual grant tables until a mysqlaccess --commit command is sent.

Options

-?, --help

Display usage information.

-b, --brief

Display results as a brief single line table.

--commit

Move changes from temporary table to the actual grant tables. You must run mysqladmin reload before the changes will take effect.

--copy

Renew the temporary table from the actual grant tables.

-d database, --db=database

The database to which to connect.

--debug=debuglevel

Set the debugging level (0 through 3).

-h host, --host=host

The host whose access rights are examined.

--howto

Usage examples for the program.

-H host, --rhost=host

Connect to a database server on a remote host.

--old-server

Connect to a pre-3.21 MySQL server.

-p password, --password=password

Check the password of the user being examined.

--plan

Display suggestions for future releases.

--preview

Show difference between temporary table and actual grant tables.

-P password, --spassword=password

Administrative password used to access the grant tables.

--relnotes

Display the release notes for the program.

--rollback

Undo the changes made to the temporary table.

-t, --table

Display results in full table format.

-u username, --user=username

User to be examined.

-U username. -superuser=username

Administrative username used to access the grant tables.

-v, --version

Display version information.

mysqladmin

mysqladmin [options] command [command...]

Performs operations that affect the database server as a whole. This utility is used to shutdown the database server, add and delete entire databases, and other administrative functions.

Commands

create database

Create a new database.

drop database

Remove and destroy a database.

extended-status

Report a fuller status from the server than the status command.

flush-hosts

Send all buffered information to the clients.

flush-logs

Flush all buffered log data.

flush-privileges

Same as reload.

flush-status

Clear the status variables.

flush-tables

Commit all buffered table operations.

kill thread-id [thread-id...]

Kill one or more mysqld server threads.

password password

Set the administration password for the database server.

ping

Check if the MySQL server is alive.

processlist

Show the active mysqld server threads.

reload

Reload access information from the grant tables.

refresh

Perform all buffered table operations and reopen the log files.

shutdown

Shutdown the database server.

status

Report the status of the server.

variables

Display the system variables used by the server.

version

Display the version number of the server.

Options

-?, --help

Display usage information.

-# debuglevel, --debug=debuglevel

Set the debugging level. See isamchk for more information.

-f, --force

Drop tables without confirmation. Also, do not quit if an error is encountered.

-h host, --host=host

Connect to the MySQL server on the given host.

-i seconds, --sleep=seconds

Perform the commands repeatedly, sleeping the given number of seconds between each run.

-p [password], --password=[password]

Password used to connect to database server. If this options is used without an argument, the password is asked from the command line.

-P port, --port=port

Port numbed used to connect to a remove database server.

-s, --silent

Do not give an error if unable to connect to the database server.

-S file, --socket=file

The Unix socket used to connect to the local database server.

-u username, --user=username

User used to connect to the database server.

-V, --version

Display version information for the mysqladmin program.

mysqlbug

mysqlbug

Report a bug in a MySQL program or utility. This program collects information about your MySQL installation and sends a detailed problem report to the MySQL team.

mysqld

mysqld [options]

The MySQL server daemon. All other programs interact with the database through this server, so it should be left running at all times (except when down for maintenance). The daemon is usually started from a script called safe_mysqld. This script sets the appropriate environment variables and launches mysqld with the necessary arguments.

Options

-?, -I, --help

Display usage information.

-# debuglevel, --debug=debuglevel

Set the debugging level. See isamchk for more information.

-b directory, --basedir=directory

The base directory used to determine all other directories.

--big-tables

Allow large result sets by saving temporary results in a file.

--bind-address=ip-number

The IP address the server binds to.

-h directory, --datadir=directory

The directory containing the database data files.

-l [logfile], --log[=logfile]

Log various information, including connections and errors. If no argument is provided, hostname.log is used as the log file, where hostname is the name of the server machine.

--log-isam[=logfile]

Log changes to the data (ISAM) files. If no argument is provided, isam.log is used as the log file. The log generated by this option can be read and manipulated with the isamlog utility.

--log-update[=number]

Log database updates. The log file is named hostname.num, where hostname is the name of the server machine and num is the argument to the option or a unique number if no argument is given.

-L=language, --language=language

The language (English, French, etc.) for the server to use.

-n, --new

Enable new (and possibly unsafe) routines.

-o, --old-protocol

Use the 3.20.x protocol.

-O variable=value, --set-variable variable=value

Set an option variable. See Chapter 16, "MySQL and mSQL System Variables" for a full list of usable variables.

--pid-file=file

The name of the file containing the process ID (PID) of the running server. The default value is hostname.pid where hostname is the name of the server machine.

-P port, --port=port

The network port number to use.

--secure

Enable network security checks which reduce database performance.

--skip-name-resolve

Use only IP numbers (not names) for connections. This increases network performance.

--skip-networking

Disable network connections and allow only local access.

--skip-thread-priority

Give all threads the same priority.

-S, --skip-new

Do not enable new (and possibly unsafe) routines.

-Sg

Disable access checking and allow all users full access to all databases.

-Sl

Do not perform thread locking.

--socket=file

The filename of the Unix socket

-T, --exit-info

Display debugging information when shutting down the server

--use-locking

Enable thread locking

-v, -V, --version

Display version information

mysqldump

mysqldump [options] database[table]

Outputs the contents of the given database (or table within a database) as a series of ANSI SQL commands. This command is handy for breaking up a database; use the -1 and -opt options.

Options

-?, --help

Display usage information.

-# debuglevel, --debug=debuglevel

Set the debugging level. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html.

--add-drop-table

Include a DROP TABLE statement before every CREATE TABLE.

--add-locks

Add LOCK TABLE statements around the data insertion statements.

--allow-keywords

Output column names that are also reserved keywords. This is not normally desirable as the column may conflict with the keyword.

-c, --compleat-insert

Output complete INSERT statements.

-C, --compress

Use data compression in the connection with the server.

--delayed

Use the INSERT DELAYED statement to insert rows.

-d, --no-data

Do not dump the data, just the table creation statements.

-e, --extended-insert

Uses the multiple-value form of the INSERT statement which can speed up data insertion.

-f, --force

Do not exit if an error is encountered.

-F, --flush-logs

Flush buffered log data before dumping the table(s).

--fields-enclosed-by=delimeter

When dumping with -T, this delimiter is placed on both sides of each field.

--fields-escaped-by=delimeter

When dumping with -T, this delimiter is placed before any special character as an escape character.

--fields-terminated-by=delimeter

When dumping with -T, this delimiter is used after every field (default is tab).

-h hostname, --host=hostname

Connect to a database server on a remote host.

-l, --lock-tables

Lock the tables before dumping.

--lines-terminated-by=delimeter

When dumping with -T, this delimiter is used after every line.

-t, --no-create-info

Do not dump the table creation statements, just the data.

-O variable=value, --set-variable variable=value

Set an option variable. See Chapter 16, "MySQL and mSQL System Variables" for a full list of usable variables.

--opt

Adds the most common and useful command line options: --add-drop-table, --add-locks, --extended-insert, --quick, and --use-locks.

-p [password], --password[=password]

The password used to connect to the database server. If no argument is given, the password is asked from the command line.

-P port, --port=port

The port used to connect to a remove database server.

-q, --quick

Display all data immediately, without buffering.

-S file, --socket=file

The Unix socket used to connect to the local server.

-T directory, --tab=directory

Generates a tab-separated file with the table data along with a file containing the SQL table creation statements. The files are outputted to the given directory.

-u username, --user=username

Username used to connect to the database server.

-v, --verbose

Display information about the state of the process while dumping the data.

-V, --version

Display version information.

-w statement, --where=statement

Outputs only the rows that satisfy the given SQL WHERE clause.

mysqlimport

mysqlimport [options] database [file]

Reads a file of data in a variety of common formats (such as comma delimited or fixed width) and inserts the data into a database. A table with the same name as the file must exist in the database with enough columns of the appropriate type to store the data.

Options

-?, --help

Display usage information.

-# debuglevel, --debug=debuglevel

Set the debugging level. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html.

-d, --delete

Delete all data currently in the table before inserting the new data.

-f, --force

Do not exit if an error is encountered.

--fields-terminated-by=string

Indicates that the fields in the data file are terminated by a string.

--fields-enclosed-by=string

Indicates that the fields in the data file are enclosed by a string.

--fields-optionally-enclosed-by=string

Indicates that the fields in the data file could also be enclosed by another string.

--fields-escaped-by=string

The string used as escape characters in the data file.

-h hostname, --host=hostname

Connect to a database server on a remote host.

-I, --ignore

Ignore the new data if it conflicts with an existing unique key.

-l, --lock-tables

Lock the tables before inserting the data.

-p [password], --password[=password]

Password used to connect to the database server. If no argument is given, the password is asked from the command line.

-P port, --port=port

Port used to connect to a remove database server.

-r, --replace

If the new data conflicts with an existing unique key, replace the old data.

-s, --silent

Suppress some output.

-S file, --socket=file

The Unix socket used to connect to the local database server.

-u username, --user=username

The username used to connect to the database server.

-v, --verbose

Display information about the process while inserting the data.

-V, --version

Display version information.

mysqlshow

mysqlshow [options] [database] [table] [field]

Displays the layout of the requested database, table or field. If no argument is given, a list of all of the databases is given. With one argument the layout of the given database is show. With two arguments, a table within the database is displayed. If all three arguments are present, the information about a specific field within a table is presented.

Options

-?, --help

Display usage information.

-# debuglevel, --debug=debuglevel

Set the debugging level. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html.

-h hostname, --host=hostname

Connect to a remote database server.

-k, --keys

Display the keys of a table.

-p [password], --password]=password]

Password used to connect to the database server. If no argument is given, the password is asked from the command line.

-P port, --port=port

Port used to connect to a remote database server.

-S file, --socket=file

The Unix socket used to connect to the local database server.

-u username, --user=username

Username used to connect to the database server.

-V, --version

Display version information.



Library Navigation Links

Copyright © 2001 O'Reilly & Associates. All rights reserved.