Chapter 17. MySQL and mSQL Programs and Utilities
Contents:
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
| 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
| 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
| 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
 |  |  |
| 16.2. mSQL System Variables |  | 17.2. mSQL Utilities |
Copyright © 2001 O'Reilly & Associates. All rights reserved.