Mysql command

From NetworkStuff

A CLI command available on most platforms with MySQL installed; used to launch the MySQL shell, used to administer and query MySQL databases.

Usage Syntax

mysql [options] [database_name]

Options and/or a database name do not need to be specified.

Common shell control options include;

• --delimiter=delimiter - Specifies the SQL statement delimiter, the default is ;
• --execute="statement" or -e "statement" - Execute the specified SQL statement and exit
• --force or -f - Continue even if an SQL error occurs

Common connectivity options include;

• --host=hostname or -h host_name - Connect to the MySQL server specified by hostname - Can this be an IP? Requires Confirmation

• --port=number or -P num - Specify the TCP/IP port number to use for the connection
• --compress or -C - Compress all information sent between the client and the server if both support compression
• --reconnect - If the connection to the server is lost, automatically try to reconnect. A single reconnect attempt is made each time the connection is lost
• --wait, -w - If the connection cannot be established, wait and retry instead of aborting

Common output options include;

• --html or -H - Display HTML output
• --xml, -X - Display XML output
• --silent, -s - Silent mode, which produces less output about what the program does. This option can be given multiple times to produce less and less output
• --verbose, -v - Verbose mode, which produces more output about what the program does. This option can be given multiple times to produce more and more output

Common authentication options include;

• --user=username or -u username - Specify a username
• --password[=password], -p[password] - Specify a password
• --safe-updates or -U - Only permit UPDATE and DELETE statements that specify which rows to modify using key values

Usage Examples

mysql

mysql -u testuser -p testdb

mysql -p -u testuser testdb

mysql -u testuser -p -e "SELECT user_id, user_name from user;" testdb - Login to MySQL using account testuser, prompt for password, execute the specified statement (which will list all Mediawiki users and their user IDs) using the testdb database and exit

mysql

Operational Keys

[CTRL]+C - Cancels (or kills) the current statement/command if one is running or exits if one is not.

[CTRL]+D - Exits the MySQL shell.

Interactive Functions

See the MySQL Shell Commands section for further information on MySQL shell interactive commands and statements.

Usage Notes

Options and/or a database name do not need to be specified.

SQL statements specified with the --execute= or -e option must be enclosed in quote marks "".

On Unix, the mysql client writes a record of executed statements to a history file.

      By default, the history file is named .mysql_history and is created in your home
      directory. To specify a different file, set the value of the MYSQL_HISTFILE
      environment variable.

      If you do not want to maintain a history file, first remove .mysql_history if it
      exists, and then use either of the following techniques:

      ·  Set the MYSQL_HISTFILE variable to /dev/null. To cause this setting to take
         effect each time you log in, put the setting in one of your shellâs startup
         files.

      ·  Create .mysql_history as a symbolic link to /dev/null:

         shell> ln -s /dev/null $HOME/.mysql_history
      You need do this only once.

Security Considerations

Don't specify a password on command line, let it prompt

Remove history file?

Typical Output

mysql -u testuser -p testdb

Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24824
Server version: 5.0.37 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 

mysql -u testuser -p --silent testdb

Enter password:
mysql> 

mysql -u testuser -p -e "SELECT user_id, user_name from user;" testdb

+---------+----------------+
| user_id | user_name      |
+---------+----------------+
|       8 | test-one       |
|      10 | test-two       |
|       7 | test-three     |
|      11 | test-four      |
|       4 | test-five      |
+---------+----------------+
5 rows in set (0.00 sec)

Linux Usage Notes

With most Linux or Unix commands;

• Non root users may need to prefix commands with the sudo command, for example: sudo chmod 644 *
• Brief help can be displayed using the -h or --help parameter, for example: chmod --help
• A full command manual can be displayed using the man command followed by the command name, for example: man chmod
• Sometimes 'info' pages are used instead of or to provide more information than man pages; for example: info chmod
• Version information can be display using the -v, -V or --version parameter, for example: chmod --version

Related Files

The mysql executable is normally found here: /usr/bin/mysql

Related Commands

Related Articles

Information on other MySQL Commands, MySQL Shell Commands and other MySQL articles

Information on Linux commands

Information on F5 BigIP commands

Information on Cisco commands

Information on Vyatta commands

Information on Extreme commands

Information on Zebra commands

Information on Secure Platform commands

Information on Blue Coat SGOS commands

Information on Nokia IPSO commands

We really do appreciate all feedback so please do send your comments, suggestions or corrections to steve#networkstuff.eu (replacing the # with an @)