Introduction

The MySQL command-line client is still one of the quickest ways to verify connectivity, inspect schemas, and run SQL without opening a graphical tool. Once you understand a few core flags, it becomes useful for local development, remote troubleshooting, and shell automation.

The Basic Interactive Login

The most common form is:

mysql -u app_user -p

This tells the client:

• '-u app_user: connect as MySQL user app_user'
• '-p: prompt for the password securely'

After authentication succeeds, you enter the interactive MySQL prompt and can run SQL immediately:

SELECT USER(), CURRENT_USER(), DATABASE();
SHOW DATABASES;

USER() shows how the client connected, while CURRENT_USER() shows the account MySQL actually authenticated. That distinction is useful when privileges seem surprising.

Connect to a Specific Host, Port, and Database

For remote servers or non-default ports, add host and port explicitly:

mysql -h db.example.internal -P 3307 -u reporting_user -p analytics

Here:

• '-h chooses the host'
• '-P chooses the TCP port'
• 'analytics selects the initial database'

If you omit the database name, you can choose it later:

USE analytics;
SHOW TABLES;

Specifying the database at connection time is convenient because your first query can run immediately.

Socket Versus TCP Matters

One subtle source of confusion is that local MySQL connections may use a Unix socket instead of TCP. On many systems:

• 'mysql -u user -p prefers the local socket'
• 'mysql -h 127.0.0.1 -u user -p forces TCP'

That difference matters when one path works and the other fails. A socket-based connection and a TCP connection can hit different authentication rules, different server instances, or different firewalls.

Run One Query and Exit

The client is also useful for shell scripts or quick checks where you do not want an interactive session. Use -e:

mysql -u app_user -p -D analytics -e "SELECT COUNT(*) FROM users;"

For script-friendly output, add batch flags:

mysql -u app_user -p -D analytics -N -B -e "SELECT id, email FROM users LIMIT 3;"

Here:

• '-D selects the database'
• '-N removes column headers'
• '-B enables batch output'

This is useful in health checks and deployment scripts.

Reuse Connection Settings Safely

If you connect often, storing non-sensitive defaults in an option file can save time:

1[client]
2user=app_user
3host=127.0.0.1
4port=3306
5database=analytics

After that, mysql can reuse those defaults. Some teams also store the password there, but that requires careful file permissions and is not appropriate on shared systems.

A safer habit is often to store host and username in the option file while still using -p for the password prompt.

Check Reachability Before Blaming Credentials

Not every login failure is a bad password. Before retrying repeatedly, check whether the server is reachable at all:

mysqladmin -h 127.0.0.1 -P 3306 -u app_user -p ping

If the server responds, the issue is more likely authentication or authorization. If it does not, the real problem may be:

• wrong host
• wrong port
• MySQL service not running
• firewall or network block

Also remember that MySQL accounts are scoped by user and client origin. app_user from localhost is not the same as app_user from another host pattern.

Common Pitfalls

• Using -psecret directly on the command line and exposing the password in shell history.
• Forgetting the difference between local socket connections and TCP connections.
• Connecting successfully but running queries against the wrong database.
• Assuming "Access denied" always means the password is wrong.
• Storing credentials in option files without protecting file permissions.

Summary

• 'mysql -u username -p is the standard starting point for interactive login.'
• Add -h, -P, and a database name for remote or non-default connections.
• Use -e for one-off SQL commands in scripts and terminal checks.
• Understand the difference between local socket and TCP connections.
• When login fails, check host, port, transport type, and account scope before blaming the password.