Connect to MySQL Server

Summary: in this tutorial, you will learn how to connect to MySQL Server using mysql command-line client and MySQL Workbench.

Once you have the MySQL Server installed, you can connect to it using any client program such as mysql command-line client and MySQL workbench.

Connect to MySQL Using mysql command-line client

The mysql is a command-line client program that allows you to interact with MySQL in interactive and non-interactive modes.

The mysql command-line client is typically located in the bin directory of MySQL’s installation directory such as C:\Program Files\MySQL\MySQL Server 8.0\bin on Windows.

To invoke the mysql program, you open the Command Prompt on Windows or Termina on Unix-like systems and navigate to the bin directory of the MySQL installation directory:

C:\Program Files\MySQL\MySQL Server 8.0\bin>Code language: plaintext (plaintext)

The Command Prompt is a command-line interpreter program available in Windows. The Command Prompt program allows you to interact with the computer through text-based commands for tasks like running programs, managing files, and configuring system settings.

If the bin directory is included in the PATH, you can use the mysql command from the Command Prompt without the need to navigate to the bin directory.

On Windows, the PATH is an environment variable that contains a list of directories. Windows OS uses these directories to locate executable files, making it easier to run applications from the Command Prompt without specifying the full file path.

To connect to the MySQL Server, you type this command on Command Prompt:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

In this command:

-u root means that you connect to the MySQL Server using the user root.

-p instructs mysql to prompt for a password.

You type the password for the root user and press Enter:

Enter password: ********Code language: SQL (Structured Query Language) (sql)

If everything is OK, you will connect to the MySQL Server and see the following command:

mysql>Code language: SQL (Structured Query Language) (sql)

To display available databases in the current server, you enter the SHOW DATABASES statement terminated by a semicolon (;) and press the Enter key:

show databases;Code language: SQL (Structured Query Language) (sql)

The mysql program will return the following output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

Here are the steps that occur behind the scenes:

• First, the mysql command-line client sends the query to the MySQL Server.
• Second, the MySQL server executes the query and sends the result back.
• Third, the mysql command-line client displays the result.

Connect to MySQL database server using MySQL Workbench

Step 1. Launch the MySQL Workbench.

You can connect to a MySQL Server using the Database > Connect to Database… menu or click the + button that is located next to the MySQL Connections.

Click the + button next to the MySQL Connections to continue.

Step 2. Enter the connection name e.g., Localhost. You can name it whatever makes sense to you. By default, the username is root. If you use a different user account, you can change it in the Username textbox.

Step 3. Click the Store in Vault ... button to enter the password for the provided user account. A window will display. You enter the password and click the OK button.

Step 4. Click the Test Connection button to test if the connection to the MySQL Server is successful or not. Then click the OK button if the connection is established successfully.

Step 5. Click the OK button to save the connection.

Step 6. Click the newly created connection under MySQL Connections to connect to the MySQL Server:

Step 7. MySQL Workbench display with the current schemas and a pane for entering queries:

To execute a query from the MySQL Workbench, you enter the query in the query tab (1) and click the Execute button from the toolbar (2).

MySQL Workbench will send the query to the MySQL Server and get the result back (3 + 4):

In this tutorial, you have learned how to connect to the MySQL Server using mysql command-line client and MySQL Workbench.