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 mysqlslap is a program designed to emulate client load for a MySQL server and to report the timing of each stage. It works as if multiple clients are accessing the server. mysqlslap is available as of MySQL 5.1.4.   Invoke mysqlslap like this: [shell] #  mysqlslap [options]   Some options such as --create or --query enable you to specify a string containing an SQL statement or a file containing statements. If you specify a file, by default it must contain one statement per line. (That is, the implicit statement delimiter is the newline character.) Use the --delimiter option to specify a different delimiter, which enables you to specify statements that span multiple lines or place multiple statements on a single line. You cannot include comments in a file; mysqlslap does not understand them. mysqlslap runs in three stages:  

1. Create schema, table, and optionally any stored programs or data you want to using for the test. This stage uses a single client connection.
2. Run the load test. This stage can use many client connections.
3. Clean up (disconnect, drop table if specified). This stage uses a single client connection.

  Examples:   Supply your own create and query SQL statements, with 50 clients querying and 200 selects for each: mysqlslap --delimiter=";" \ --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" \ --query="SELECT * FROM a" --concurrency=50 –iterations=200   Tell the program to load the create, insert, and query SQL statements from the specified files, where the create.sql file has multiple table creation statements delimited by ';' and multiple insert statements delimited by ';'. The --query file will have multiple queries delimited by ';'. Run all the load statements, then run all the queries in the query file with five clients.   mysqlslap --concurrency=5 --iterations=5 –query=query.sql --create=create.sql   --delimiter=";" mysqlslap supports the following options, which can be specified on the command line or in the [mysqlslap]:   --auto-generate-sql, -a Generate SQL statements automatically when they are not supplied in files or using command options.   --auto-generate-sql-add-autoincrement Add an AUTO_INCREMENT column to automatically generated tables. This option was added in MySQL 5.1.18.   --auto-generate-sql-execute-number=N Specify how many queries to generate automatically. This option was added in MySQL 5.1.18.   --auto-generate-sql-secondary-indexes=N Specify how many secondary indexes to add to automatically generated tables. By default, none are added. This option was added in MySQL 5.1.18.   --auto-generate-sql-unique-query-number=N How many different queries to generate for automatic tests. For example, if you run a key test that performs 1000 selects, you can use this option with a value of 1000 to run 1000 unique queries, or with a value of 50 to perform 50 different selects. The default is 10. This option was added in MySQL 5.1.18.   --create=value The file or string containing the statement to use for creating the table. --delimiter=str, -F str The delimiter to use in SQL statements supplied in files or using command options.   --detach=N Detach (close and reopen) each connection after each N statements. The default is 0 (connections are not detached). This option was added in MySQL 5.1.21.   --engine=engine_name, -e engine_name The storage engine to use for creating tables.   --host=host_name, -h host_name Connect to the MySQL server on the given host.   --iterations=N, -i N The number of times to run the tests.   --number-char-cols=N, -x N The number of VARCHAR columns to use if --auto-generate-sql is specified.   --number-int-cols=N, -y N The number of INT columns to use if --auto-generate-sql is specified.   --only-print Do not connect to databases. mysqlslap only prints what it would have done. This option was added in MySQL 5.1.5.   --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.   --port=port_num, -P port_num The TCP/IP port number to use for the connection.   --query=value, -q value The file or string containing the SELECT statement to use for retrieving data.   --socket=path, -S path For connections to localhost, the Unix socket file to used.   --user=user_name, -u user_name The MySQL user name to use when connecting to the server. Check Out Our Related Courses Sql Tutorials Oracle Pl SQL Tutorials Top 50 Sql Server Interview Questions SQL Server DBA Training In Chicago