mysql is one of the MySQL client programs. It is a command-line program that acts as a text-based front end for the MySQL Server. It's used for issuing queries and viewing the results interactively from a terminal window.
MySQL client programs can be invoked from the command line, such as from a Windows console prompt or a Unix shell prompt. When you invoke a client program, you can specify options following the program name to control its behavior. Options also can be given in option files. Some options tell the client how to connect to the MySQL server. Other options tell the program what actions to perform.
To determine the options supported by a MySQL program, invoke it with the --help option. A full list of mysql commands can be obtained using the HELP command. For example, to find out how to use mysql, use this command:
shell> mysql --help
To run above command on Windows, you can do like below:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql --help
To determine the version of a program, use the --version option.
shell> mysql --version
In many cases, a given option has both a long and a short form. Long options consist of a word preceded by double dashes whereas short options consist of a single letter preceded by a single dash. For example, to display a program's version number, you can use the long --version option or the short -V option. These two commands are equivalent:
shell> mysql --version
shell> mysql -V
Options are case sensitive. --version is recognized by MySQL programs, but lettercase variations such as --Version or --VERSION are not. This applies to short options as well: -V and -v are both legal options, but mean different things.
To connect to a server using a client program, the client must know upon which host the server is running. A connection may be established locally to a server running on the same host as the client program, or remotely to a server running on a different host. To connect, you also must identify yourself to the server with a username and password. The primary options for connecting to the server specify the type of connection to make and identify the MySQL account that you want to use. The following tables summarize these two sets of options.
--protocol The protocol to use for the connection
--host The host where the server is running
--port The port number for TCP/IP connections
--shared-memory-base-name The shared-memory name for shared-memory connections
--socket The Unix socket filename or named-pipe name
--user The MySQL account username
--password The MySQL account password
Here are some examples that show how to specify connection parameters:
- Connect to the server using the default hostname and username values with no password:
- Connect to the local server via shared memory (this works only on Windows). Use the default username and no password:
shell> mysql --protocol=memory
- Connect to the server on the local host with a username of myname, asking mysql to prompt you for a password:
shell> mysql --host=localhost --password --user=myname
- Connect with the same options as the previous example, but using the corresponding short option forms:
shell> mysql -h localhost -p -u myname
- Connect to the server at a specific IP address, with a username of myname and password of mypass:
shell> mysql --host=192.168.1.33 --user=myname --password=mypass
- Connect to the server on the local host, using the default username and password and compressing client/server traffic:
shell> mysql --host=localhost --compress
After successful connection, you can select or change the default database while running mysql, issue a USE db_name statement, where db_name is the name of the database you'd like to use. The following statement makes world the default database:
mysql> USE world;
You can get current database name like below:
mysql> SELECT database();
To get all available databases, run the following command:
mysql> SHOW databases;
You may run any other valid sql statement like:
mysql> SELECT * FROM Country;
You can execute a statement directly from the command line by using the -e or --execute option. Keep in mind, no statement terminator is necessary unless the string following -e consists of multiple statements. In that case, separate the statements by semicolon characters.
shell> mysql -e "SELECT VERSION()"
To quit mysql, use q, QUIT, or EXIT: