Connect to MySQL database from command line

Let us understand how MySQL can be connected to the database using the command-line. This is done for clients like mysql or mysqldump.

The below command invokes mysql without specifying any explicit connection parameters −

mysql

Since there are no parameter options, the default values will be applied −

• The default host name is localhost.

• The default user name is ODBC on Windows.

• No password is sent because neither --password nor -p has been mentioned.

For mysql, the first non-option argument is considered the name of the default database. Since there is no such argument, mysql selects no default database.

To specify the host name, user name and password specifically, provide appropriate options on the command line. To select a default database, add a database-name argument. Let us see this with the command −

mysql --host=localhost --user=myname --password=password mydb
mysql -h localhost -u myname -ppassword mydb

Client programs determine the type of connection that needs to be made −

• If the host is not specified or it is a localhost, a connection to the local host occurs

• Else, the connection will use TCP/IP.

To ensure that the client makes a TCP/IP connection to the local server, ‘—host’ or ‘-h’ needs to be specified, with a host name value of 127.0.0.1 (instead of localhost). Instead of this, the IP address or name of the local server can also be mentioned. The transport protocol can be explicitly mentioned using the --protocol=TCP option. Let us see with the example −

Example

mysql --host=127.0.0.1
mysql --protocol=TCP

On Windows, MySQL client can be forced to use a named-pipe connection. This can be done by specifying the --pipe or --protocol=PIPE option. Otherwise, . (period) can also be mentioned as the host name.

Connections to remote servers will use TCP/IP. The command would connect to the server running on remote.example.com using the default port number (3306). Let us see it in action −

mysql --host=remote.example.com

To specify a port number explicitly, the --port or -P option can be used. Lets see how it can be done −

mysql --host=remote.example.com --port=13306

To cause the port number to be used, force a TCP/IP connection. It can be done using any one of the way −

mysql --port=13306 --host=127.0.0.1
(or)
mysql --port=13306 --protocol=TCP

It is possible to specify connection parameters without entering them on the command line every time a client program is invoked.