The World of MySQL & MariaDB

MySQL CLI Login: Beginner Basics & Error Fixes Guide

Basic Operations
目次

1. Overview of Connecting to MySQL

There are multiple ways to connect to MySQL, but here we’ll introduce three representative methods.

1.1 Connecting Using the Command-Line Tool

Using a terminal (macOS or Linux) or Command Prompt (Windows) to connect directly from the MySQL client tool. The command-line tool is lightweight and flexible, suitable for remote server management and automation with scripts. It’s a connection method widely used by server administrators and developers.

1.2 Connecting Using a GUI Tool (e.g., MySQL Workbench)

GUI tools like MySQL Workbench allow visual operation, making query execution and data management intuitive. The interface is beginner-friendly and especially useful for managing complex database structures and visualizing data.

1.3 Connecting from Programs (e.g., PHP, Python)

In web application development, it’s common to connect to MySQL from programs such as PHP or Python. By using connection libraries, you can automatically execute queries against the database. Storing and retrieving data from the application becomes smooth.

2. Command-line MySQL login procedure

2.1 Starting the Command Prompt or Terminal

First, on Windows launch the “Command Prompt”, and on macOS or Linux launch the “Terminal”. These tools are included with each OS by default, so no special installation is required.

2.2 How to verify MySQL client tool installation

Once the terminal is open, 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 it is not installed, 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
-u option specifies the username, and the -p option prompts for a password. When you enter this command, a password prompt will appear.

2.4 Password entry considerations

After running the command, you will be prompted to enter a password. For security reasons, avoid typing the password directly on the command line and instead enter it at the prompt. Passwords entered at the prompt are not displayed on the screen, so enter them carefully.

3. Connecting Using Options

3.1 Connecting to a Specific Host

When connecting to a remote MySQL server, use the -h option to specify the host name.
mysql -h [host] -u [username] -p
This method is useful when managing multiple servers or when databases reside on different hosts.

3.2 Connecting by Specifying a Port Number

MySQL’s default port is 3306, but if it is configured to use a different port, use the -P option to specify the port number.
mysql -h [host] -P [port] -u [username] -p
Specifying the port number is commonly used for external connections, VPS, or cloud servers.

3.3 Logging In While Specifying a Database

When logging into MySQL, you can specify a particular database from the start. This method helps improve efficiency in environments with many databases.
mysql -u [username] -p [database]

4. Basic Operations After Logging In

4.1 Display List of Databases

After logging in, to display a list of databases on the MySQL server, run the following command.
SHOW DATABASES;

4.2 Select a Specific Database

To select the database you want to work with, use the USE command.
USE [database name];

4.3 Display List of Tables

To show the tables in the selected database, run the following command.
SHOW TABLES;

4.4 Display Table Contents

To view data from a specific table, use a SELECT statement.
SELECT * FROM [table name];
This method is handy for checking data.

4.5 How to Log Out

To log out of MySQL, enter the following command.
exit;

5. Common Errors and Their Solutions

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 host or port number may be incorrect.

5.2 Error Resolution

Below are specific solutions for the above errors.
  • Access denied for user：Recheck the username and password. Also verify the settings of security software and firewalls.
  • Can’t connect to MySQL server：Check the server status. If connecting remotely, also verify network settings and firewalls.
  • Too many connections error：Consult the administrator and adjust the max_connections setting.

6. Security Considerations

6.1 Password Management

Use a strong password that combines uppercase letters, lowercase letters, numbers, and symbols. Regularly changing it is also recommended.

6.2 Password Input on the Command Line

When logging in via the command line, avoid typing the password directly and use the -p option to prompt for input.

6.3 Cleaning Up Unnecessary Users and Permissions

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

7. Summary

In this article, we explained the steps for logging in to MySQL from the command line, basic operations, error handling, and security measures. Get comfortable with MySQL’s basic commands and achieve efficient database management.