[MySQL]: Client Programs
7.1 mysql ― The MySQL Command-Line Tool
mysql is a simple SQL shell (with GNU readline capabilities). It supports interactive and noninteractive use. When used interactively, query results are presented in an ASCII-table format. When used noninteractively (for example, as a filter), the result is presented in tab-separated format. The output format can be changed using command options.
If you have problems due to insufficient memory for large result sets, use the –quick option. This forces mysql to retrieve results from the server a row at a time rather than retrieving the entire result set and buffering it in memory before displaying it.
Using mysql is very easy. Invoke it from the prompt of your command interpreter as follows:
shell> mysql db_name
Or:
shell> mysql –user=user_name –password=your_password db_name
mysql Options:
mysql supports the following options, which can be specified on the command line or in the [mysql] and [client] option file groups.
–compress, -C
Compress all information sent between the client and the server if both support compression.
–database=db_name, -D db_name
The database to use. This is useful primarily in an option file.
–delimiter=str
Set the statement delimiter. The default is the semicolon character (“;”).
–execute=statement, -e statement
Execute the statement and quit.
–force, -f
Continue even if an SQL error occurs.
–host=host_name, -h host_name
Connect to the MySQL server on the given host.
–html, -H
Produce HTML output.
–password[=password], -p[password]
The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the –password or -p option on the command line, mysql prompts for one.
–port=port_num, -P port_num
The TCP/IP port number to use for the connection.
–quick, -q
Do not cache each query result, print each row as it is received. This may slow down the server if the output is suspended. With this option, mysql does not use the history file.
–skip-column-names, -N
Do not write column names in results.
–socket=path, -S path
For connections to localhost, the Unix socket file to use.
–tee=file_name
Append a copy of output to the given file. This option works only in interactive mode.
–user=user_name, -u user_name
The MySQL user name to use when connecting to the server.
–xml, -X
Produce XML output.
–connect_timeout
The number of seconds before connection timeout.
7.2 mysqladmin ― Client for Administering a MySQL Server
mysqladmin is a client for performing administrative operations. You can use it to check the server’s configuration and current status, to create and drop databases, and more.
create db_name
Create a new database named db_name.
drop db_name
Delete the database named db_name and all its tables.
flush-logs
Flush all logs.
flush-privileges (reload)
Reload the grant tables (same as reload).
flush-status
Clear status variables.
flush-tables
Flush all tables.
kill id,id,…
Kill server threads. If multiple thread ID values are given, there must be no spaces in the list.
ping
Check whether the server is available. The return status from mysqladmin is 0 if the server is running, 1 if it is not. This is 0 even in case of an error such as Access denied, because this means that the server is running but refused the connection, which is different from the server not running.
processlist
Show a list of active server threads. This is like the output of the SHOW PROCESSLIST statement. If the –verbose option is given, the output is like that of SHOW FULL PROCESSLIST.
status
Display a short server status message.
Uptime
The number of seconds the MySQL server has been running.
Threads
The number of active threads (clients).
Questions
The number of questions (queries) from clients since the server was started.
Slow queries
The number of queries that have taken more than long_query_time seconds.
Opens
The number of tables the server has opened.
Flush tables
The number of flush-*, refresh, and reload commands the server has executed.
Open tables
The number of tables that currently are open.
Queries per second avg
Average Queries executed in the server per second.
variables
Display the server system variables and their values.
version
Display version information from the server.
7.3 mysqlcheck ― A Table Maintenance Program
The mysqlcheck client performs table maintenance: It checks, repairs, optimizes, or analyzes tables. Each table is locked and therefore unavailable to other sessions while it is being processed, although for check operations, the table is locked with a READ lock only. Table maintenance operations can be time-consuming, particularly for large tables. If you use the –databases or –all-databases option to process all tables in one or more databases, an invocation of mysqlcheck might take a long time.
mysqlcheck is similar in function to myisamchk, but works differently. The main operational difference is that mysqlcheck must be used when the mysqld server is running, whereas myisamchk should be used when it is not. The benefit of using mysqlcheck is that you do not have to stop the server to perform table maintenance.
mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user. It determines which statements to use for the operation you want to perform, and then sends the statements to the server to be executed. For details about which storage engines each statement works with, see the descriptions for those statements
The MyISAM storage engine supports all four maintenance operations, so mysqlcheck can be used to perform any of them on MyISAM tables. Other storage engines do not necessarily support all operations. In such cases, an error message is displayed. For example, if test.t is a MEMORY table, an attempt to check it produces this result:
shell> mysqlcheck test t
test.t
note : The storage engine for the table doesn’t support check
Note : It is best to make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss. Possible causes include but are not limited to file system errors.
There are three general ways to invoke mysqlcheck:
shell> mysqlcheck [options] db_name [tbl_name …]
shell> mysqlcheck [options] –databases db_name …
shell> mysqlcheck [options] –all-databases
If you do not name any tables following db_name or if you use the –databases or –all-databases option, entire databases are checked.
Options:
–all-databases, -A
Check all tables in all databases. This is the same as using the –databases option and naming all the databases on the command line.
–analyze, -a
Analyze the tables and stores the key distribution for a table.
–auto-repair
If a checked table is corrupted, automatically fix it. Any necessary repairs are done after all tables have been checked.
–check, -c
Check the tables for errors. This is the default operation.
–check-only-changed, -C
Check only tables that have changed since the last check or that have not been closed properly.
–databases, -B
Process all tables in the named databases. Normally, mysqlcheck treats the first name argument on the command line as a database name and following names as table names. With this option, it treats all name arguments as database names.
–extended, -e
If you are using this option to check tables, it ensures that they are 100% consistent but takes a long time.
–fast, -F
Check only tables that have not been closed properly.
–force, -f
Continue even if an SQL error occurs.
–host=host_name, -h host_name
Connect to the MySQL server on the given host.
–medium-check, -m
Do a check that is faster than an –extended operation. This finds only 99.99% of all errors, which should be good enough in most cases.
–optimize, -o
Optimize the tables.
–password[=password], -p[password]
The password to use when connecting to the server.
–port=port_num, -P port_num
The TCP/IP port number to use for the connection.
–quick, -q
If you are using this option to repair tables, it tries to repair only the index tree. This is the fastest repair method.
–repair, -r
Perform a repair that can fix almost anything except unique keys that are not unique.
–socket=path, -S path
For connections to localhost, the Unix socket file to use
–tables
Override the –databases or -B option. All name arguments following the option are regarded as table names.
–user=user_name, -u user_name
The MySQL user name to use when connecting to the server.
7.4 mysqldump ― A Database Backup Program
Refer 11. Backup and Recovery
7.5 mysqlimport ― A Data Import Program
Refer 11.3.3 Dumping Data in Delimited-Text Format
7.6 mysqlshow ― Display Database, Table, and Column Information
The mysqlshow client can be used to quickly see which databases exist, their tables, or a table’s columns or indexes. mysqlshow provides a command-line interface to several SQL SHOW statements. The same information can be obtained by using those statements directly. For example, you can issue them from the mysql client program.
Invoke mysqlshow like this:
shell> mysqlshow [options] [db_name [tbl_name [col_name]]]
- If no database is given, a list of database names is shown
- If no table is given, all matching tables in the database are shown.
- If no column is given, all matching columns and column types in the table are shown.
The output displays only the names of those databases, tables, or columns for which you have some privileges.
If the last argument contains shell or SQL wildcard characters (“*”, “?”, “%”, or “_”), only those names that are matched by the wildcard are shown. If a database name contains any underscores, those should be escaped with a backslash to get a list of the proper tables or columns. “*” and “?” characters are converted into SQL “%” and “_” wildcard characters. This might cause some confusion when you try to display the columns for a table with a “_” in the name, because in this case, mysqlshow shows you only the table names that match the pattern. This is easily fixed by adding an extra “%” last on the command line as a separate argument.
Options:
–count
Show the number of rows per table. This can be slow for non-MyISAM tables.
–host=host_name
–password[=password]
–port=port_num
–socket=path
–keys
Show table indexes
–status
Display extra information about each table (SHOW TABLE STATUS)
7.7 mysqlslap ― Load Emulation Client