Connecting to MySQL Using Command Options

When working with MySQL, one of the most common tasks is connecting to the MySQL Server using a terminal or command prompt. To connect the MySQL database the community provides a command line tool called mysql which comes up with some command line arguments. Which helps to connect and interact with the database directly.

Prerequisites

Before connecting, make sure:

• The MySQL Server (mysqld) is running
• You have the correct username and password
• You know the host (e.g., localhost) and port (default is 3306)

Step 1: Start the MySQL Server

To connect the MySQL we need to make sure that the mysqld is running on the system. you can run this daemon process using the command. Where the options are the optional command line arguments with respect to the MySQL server. 

mysqld [options]

• If you get an error like 'mysqld' is not recognized, either:
• Add the MySQL bin folder to your system path
• Or navigate to the bin folder directly in your terminal

Step 2: Open the Command Line & Connect

Now, we can use mysql command line program along with its options to connect to this running server as follows,

mysql --host=<hostname> --port=<port> --user=<username> --password

Example:

mysql --host=localhost --user=root --password

Note: Do not type your password in the command itself. When you press enter, it will securely prompt you to enter the password.

Example With Real Setup

Have a MySQL server running with accurate configuration. You can find this configuration in mysql workbench > user and privileges tab

Imagine you have two users root and pma set up in MySQL Workbench under Users and Privileges. You can connect using either user as shown:

mysql --host localhost --user 
root --password=<your password>

In the above image, we are connecting as pma as specified earlier and localhost where my server is running. Even if you do not specify the password, it prompts for the password and it is unsafe to specify the password as a command line argument

Now you are connected to the server and that is indicated with a welcome message as shown in the above image.

Facts:

 1.  --host and --user is the mandatory arguments if you do not specify then it shows the error like this,

2. You can ignore the port option in which case it automatically finds it. You can find the port of the daemon in the workbench administration tab.

3. You can find multiple other command options with this,

mysql --help

4. Within the client we have the commands like quit, exit, connect, print, and many more which we can use to perform appropriate operations.