Connecting to the MySQL Server Using Command Options

Let us see how command line options can be used to establish connection with the MySQL server for clients like mysql or mysqldump.

For a client program to be able to connect to the MySQL server, it must use proper connection parameters, like the name of the host where the server is running, the user name and password of the MySQL account. Every connection parameter has a default value, but it can be overridden when necessary using program options specified on the command line or in an option file.

Invoke mysql

Command to invoke mysql without specifying any explicit connection parameters is −

mysql

Since there are no parameter options, the default values are applied.

• The default host name is localhost. On Unix, it has a special meaning.

• The default user name is ODBC on Windows. On Unix, the login name on Unix.

• No password is sent because neither --password nor -p is provided-.

• For mysql, the first argument is considered as the name of the default database. There is no such argument, hence mysql doesn’t select any default database.

Imvoke - Specify host name, user name, and password

To specify the host name, user name, and password explicitly, appropriate options have to be provided on the command line.It has been shown below −

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

The password value is optional.

• If a --password or -p option is present, and a password value is mentioned, there shouldn’t be any space between --password= or -p and the password that follows it.

• If --password or -p doesn’t specify a password value, the client program prompts the user to enter the password. The password doesn’t get displayed when it is entered.

Type of Connection

Next step is for client programs to determine the type of connection that needs to be made. To ensure that the client makes a TCP/IP connection to the local server only, the --host or -h option is used to specify a host name with the value of 127.0.0.1 (instead of localhost). Instead of this, the IP address or name of the local server can also be provided. The transport protocol can be explicitly mentioned even for localhost using the --protocol=TCP option. Some examples have been shown below −

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

If connections need to be made to remote servers, then use TCP/IP. This command would help connect to the server that runs on remote.example.com using the default port number which is 3306. It has been shown below −

mysql --host=remote.example.com

If the user wishes to display the port number specifically, the - -port or –P option needs to be mentioned −

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