How to Log In to MySQL from the Command Line (Step-by-Step Guide for Beginners)

1. Overview of MySQL Connection Methods

There are multiple ways to connect to MySQL. In this section, we introduce three commonly used methods.

1.1 Connecting Using the Command Line Tool

This method connects directly to MySQL using the MySQL client tool through the terminal (macOS and Linux) or Command Prompt (Windows). Command line tools are lightweight and flexible, making them suitable for managing remote servers and automating tasks with scripts. This method is widely used by server administrators and developers.

1.2 Connecting Using a GUI Tool (Example: MySQL Workbench)

GUI tools such as MySQL Workbench allow you to operate visually, making it intuitive to execute queries and manage data. With a user-friendly interface, they are especially useful for beginners and for managing complex database structures or visualizing data.

1.3 Connecting from Programs (Example: PHP, Python)

In web application development, it is common to connect to MySQL using programming languages such as PHP or Python. By using connection libraries, queries can be executed automatically. This enables smooth data storage and retrieval directly from applications.

2. How to Log In to MySQL from the Command Line

2.1 Launching Command Prompt or Terminal

First, open “Command Prompt” on Windows or “Terminal” on macOS and Linux. These tools are built into the operating system, so no additional installation is required.

2.2 Checking if the MySQL Client Tool Is Installed

After opening the terminal, run the following command to check whether the MySQL client tool is installed:

mysql --version

If version information is displayed, the MySQL client is installed. If not, download and install the client tool from the official MySQL website.

2.3 Basic Login Command Syntax

The basic command to log in to MySQL is as follows:

mysql -u [username] -p

The -u option specifies the username, and the -p option prompts you to enter the password. After entering this command, you will be prompted to input your password.

2.4 Important Notes About Password Entry

After executing the command, you will be asked to enter your password. For security reasons, avoid entering your password directly in the command line. Instead, use the prompt for secure input. The password you enter at the prompt will not be displayed on the screen, so type carefully.

3. Connecting with Additional Options

3.1 Connecting to a Specific Host

When connecting to a remote MySQL server, use the -h option to specify the hostname:

mysql -h [hostname] -u [username] -p

This method is useful when managing multiple servers or when the database resides on a different host.

3.2 Connecting by Specifying a Port Number

The default MySQL port is 3306. If MySQL is configured to use a different port, use the -P option to specify it:

mysql -h [hostname] -P [port_number] -u [username] -p

Specifying a port number is commonly required when connecting externally or when using VPS or cloud servers.

3.3 Logging In with a Specific Database

You can specify a database at login. This is useful in environments with many databases where you want to improve efficiency.

mysql -u [username] -p [database_name]

4. Basic Operations After Login

4.1 Displaying the List of Databases

After logging in, run the following command to display the list of databases on the MySQL server:

SHOW DATABASES;

4.2 Selecting a Specific Database

To select the database you want to work with, use the USE command:

USE [database_name];

4.3 Displaying the List of Tables

To display tables in the selected database, run:

SHOW TABLES;

4.4 Displaying Table Contents

To view data in a specific table, use a SELECT statement:

SELECT * FROM [table_name];

This method is convenient when checking data.

4.5 Logging Out

To log out from MySQL, enter the following command:

exit;

5. Common Errors and How to Fix Them

5.1 Error Messages and Causes

• Access denied for user error (Error code 1045)
  → The username or password may be incorrect.
• Can’t connect to MySQL server error (Error code 2003)
  → The server may not be running, or the hostname or port number may be incorrect.

5.2 Solutions to Common Errors

Here are specific solutions for the errors above:

• Access denied for user:
  Double-check your username and password. Also review your security software and firewall settings.
• Can’t connect to MySQL server:
  Check the server status. For remote connections, verify network settings and firewall configuration.
• Too many connections error:
  Consult the administrator and adjust the max_connections setting if necessary.

6. Security Considerations

6.1 Password Management

Create strong passwords by combining uppercase letters, lowercase letters, numbers, and symbols. Regularly updating passwords is also recommended.

6.2 Entering Passwords in the Command Line

Do not enter your password directly in the command line when logging in. Instead, use the -p option to securely enter it via the prompt.

6.3 Removing Unnecessary Users and Permissions

Delete unnecessary users and grant only the minimum required privileges to reduce the risk of unauthorized access.

7. Summary

This article explained how to log in to MySQL from the command line, perform basic operations, troubleshoot common errors, and implement security measures. By mastering these fundamentals, you can manage databases more efficiently.