 | MySQL & mSQL |  |
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.
| 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.
Display usage information.
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.
Analyze the distribution of keys within a table and make adjustments if performance would be improved.
Display short description of a table.
Perform additional checks on the integrity of a table. With this option, you can be absolutely sure that the table is not damaged.
Overwrite without warning files that already exist. Also automatically recover damaged tables without notice.
Display full statistics the table(s) being checked.
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.
Do not repair a table that is a symlink.
Speed up repair by not examining the data file.
Perform general repair on the table. Does not fix duplicate keys which are supposed to be unique.
Use an older, slower method of recovery that can repair some things that "-r" will miss.
Set an option variable. See Chapter 16, "MySQL and mSQL System Variables", for a full list of usable variables.
Display only errors
Sort the index block of the table.
Sort the records of the table by index within the table.
Decompress a file packed with pack_isam.
Display extra information.
Display version information.
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.
Display usage information
Set debugging level. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html.
Examine only the last number commands.
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.
Directory containing the ISAM log file.
Display additional information.
Offset number commands before examining the log.
Remove components from the path.
Ignore errors while examining log. This option allows you to recover all of the information within a log file.
Open an ISAM data file (a file ending with .ISM) and retrieve the data at recordnumber.
Update the tables using the log information.
Display extra information about the process.
Display version information.
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.
Display the list of commands.
Clear (ignore) the current statement.
Edit the current statement using the default editor.
Exit the program.
Send the current statement to the database server.
Send the current statement to the server and display the results vertically.
Display the current statement.
Same as exit.
Rebuild the index of completion terms.
Display status information about the server and the current session.
Select another database.
Display usage information.
Set the debugging level. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html
Do not automatically rehash database information.
Print results in `batch' mode. This provides minimally formatted output which can easily be used in other database applications.
Execute the given statement and quit. Automatically implies -B.
Do not stop processing when an SQL error is encountered.
Connect to the database server on the specified host.
Do not buffer output between queries.
Set an option variable. See Chapter 16, "MySQL and mSQL System Variables" for a full list of usable variables.
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.
The port number used to connect to the database server.
Display output as it comes from the server. If you suspend your terminal while using this option, the server could pause.
Display output without any conversion. Only useful in conjunction with -B.
Suppress some output.
The Unix socket file used to connect to the database server.
Display output in table format.
Display debugging information when the program exits.
Username used for connection with the database.
Display extra output.
Display version information
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.
Display usage information.
Display results as a brief single line table.
Move changes from temporary table to the actual grant tables. You must run mysqladmin reload before the changes will take effect.
Renew the temporary table from the actual grant tables.
The database to which to connect.
Set the debugging level (0 through 3).
The host whose access rights are examined.
Usage examples for the program.
Connect to a database server on a remote host.
Connect to a pre-3.21 MySQL server.
Check the password of the user being examined.
Display suggestions for future releases.
Show difference between temporary table and actual grant tables.
Administrative password used to access the grant tables.
Display the release notes for the program.
Undo the changes made to the temporary table.
Display results in full table format.
User to be examined.
Administrative username used to access the grant tables.
| 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.
Create a new database.
Remove and destroy a database.
Report a fuller status from the server than the status command.
Send all buffered information to the clients.
Flush all buffered log data.
Same as reload.
Clear the status variables.
Commit all buffered table operations.
Kill one or more mysqld server threads.
Set the administration password for the database server.
Check if the MySQL server is alive.
Show the active mysqld server threads.
Reload access information from the grant tables.
Perform all buffered table operations and reopen the log files.
Shutdown the database server.
Report the status of the server.
Display the system variables used by the server.
Display the version number of the server.
Display usage information.
Set the debugging level. See isamchk for more information.
Drop tables without confirmation. Also, do not quit if an error is encountered.
Connect to the MySQL server on the given host.
Perform the commands repeatedly, sleeping the given number of seconds between each run.
Password used to connect to database server. If this options is used without an argument, the password is asked from the command line.
Port numbed used to connect to a remove database server.
Do not give an error if unable to connect to the database server.
The Unix socket used to connect to the local database server.
User used to connect to the database server.
| 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.
Display usage information.
Set the debugging level. See isamchk for more information.
The base directory used to determine all other directories.
Allow large result sets by saving temporary results in a file.
The IP address the server binds to.
The directory containing the database data files.
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 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 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.
The language (English, French, etc.) for the server to use.
Enable new (and possibly unsafe) routines.
Use the 3.20.x protocol.
Set an option variable. See Chapter 16, "MySQL and mSQL System Variables" for a full list of usable variables.
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.
The network port number to use.
Enable network security checks which reduce database performance.
Use only IP numbers (not names) for connections. This increases network performance.
Disable network connections and allow only local access.
Give all threads the same priority.
Do not enable new (and possibly unsafe) routines.
Disable access checking and allow all users full access to all databases.
Do not perform thread locking.
The filename of the Unix socket
Display debugging information when shutting down the server
Enable thread locking
| 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.
Display usage information.
Set the debugging level. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html.
Include a DROP TABLE statement before every CREATE TABLE.
Add LOCK TABLE statements around the data insertion statements.
Output column names that are also reserved keywords. This is not normally desirable as the column may conflict with the keyword.
Output complete INSERT statements.
Use data compression in the connection with the server.
Use the INSERT DELAYED statement to insert rows.
Do not dump the data, just the table creation statements.
Uses the multiple-value form of the INSERT statement which can speed up data insertion.
Do not exit if an error is encountered.
Flush buffered log data before dumping the table(s).
When dumping with -T, this delimiter is placed on both sides of each field.
When dumping with -T, this delimiter is placed before any special character as an escape character.
When dumping with -T, this delimiter is used after every field (default is tab).
Connect to a database server on a remote host.
Lock the tables before dumping.
When dumping with -T, this delimiter is used after every line.
Do not dump the table creation statements, just the data.
Set an option variable. See Chapter 16, "MySQL and mSQL System Variables" for a full list of usable variables.
Adds the most common and useful command line options: --add-drop-table, --add-locks, --extended-insert, --quick, and --use-locks.
The password used to connect to the database server. If no argument is given, the password is asked from the command line.
The port used to connect to a remove database server.
Display all data immediately, without buffering.
The Unix socket used to connect to the local server.
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.
Username used to connect to the database server.
Display information about the state of the process while dumping the data.
Display version information.
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.
Display usage information.
Set the debugging level. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html.
Delete all data currently in the table before inserting the new data.
Do not exit if an error is encountered.
Indicates that the fields in the data file are terminated by a string.
Indicates that the fields in the data file are enclosed by a string.
Indicates that the fields in the data file could also be enclosed by another string.
The string used as escape characters in the data file.
Connect to a database server on a remote host.
Ignore the new data if it conflicts with an existing unique key.
Lock the tables before inserting the data.
Password used to connect to the database server. If no argument is given, the password is asked from the command line.
Port used to connect to a remove database server.
If the new data conflicts with an existing unique key, replace the old data.
Suppress some output.
The Unix socket used to connect to the local database server.
The username used to connect to the database server.
Display information about the process while inserting the data.
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.
Display usage information.
Set the debugging level. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html.
Connect to a remote database server.
Display the keys of a table.
Password used to connect to the database server. If no argument is given, the password is asked from the command line.
Port used to connect to a remote database server.
The Unix socket used to connect to the local database server.
Username used to connect to the database server.
|  | |
| 16.2. mSQL System Variables |  | 17.2. mSQL Utilities |
Copyright © 2001 O'Reilly & Associates. All rights reserved.