Connect To Database Mysql Command Line

6 min read

How to Connect to a MySQL Database Using the Command Line

Connecting to a MySQL database from the command line is one of the most essential skills for developers, system administrators, and data analysts. On top of that, whether you are managing a local development environment or troubleshooting a production server, the MySQL client gives you direct, fast, and scriptable access to your data. This guide walks you through every step to connect to a MySQL database using the command line, from installing the client to fixing the most common connection errors.

What You Need Before You Start

Before attempting a connection, make sure you have the following ready:

  • MySQL server installed and running — either locally or on a remote host.
  • The MySQL client (mysql) — usually installed together with the server, or available as a separate package such as mysql-client or MariaDB's client.
  • Valid credentials — a username and password for an account that has permission to connect.
  • Host and port information — typically localhost and port 3306.
  • The database name (optional) — you can select it after connecting.

You can verify that the client is installed by running:

mysql --version

If the command returns a version number such as mysql Ver 8.0.x, you are ready to continue.

The Basic Syntax

The general form of the connection command looks like this:

mysql -h hostname -P port -u username -p database_name

Each flag has a specific role:

  • -h — the host where MySQL is running.
  • -P — the TCP port (capital P).
  • -u — the username.
  • -p — prompts for the password securely.
  • database_name — the default schema to use after login.

Connecting to a Local MySQL Server

The most common scenario is connecting to a server running on the same machine. Open your terminal (or Command Prompt / PowerShell on Windows) and type:

mysql -u root -p

After pressing Enter, you will be prompted to enter the password. Notice that the characters do not appear on screen while you type — this is normal and intentional for security.

Once authenticated, your prompt changes to something like:

mysql>

That mysql> prompt confirms you are inside the MySQL shell. To check which databases exist, run:

SHOW DATABASES;

To switch to a specific database:

USE my_database;

And to confirm the server details:

SELECT VERSION();
SELECT @@hostname;

Connecting to a Remote MySQL Server

When the database lives on another machine, you must specify the host address:

mysql -h 192.168.1.50 -u myuser -p

If the server listens on a non-standard port, add -P:

mysql -h db.example.com -P 3307 -u myuser -p

For remote connections to succeed, three conditions must be met:

  1. The MySQL user must be allowed to connect from your IP address (for example, 'myuser'@'192.168.1.%' instead of 'myuser'@'localhost').
  2. The server's bind-address in my.cnf must not be restricted to 127.0.0.1 only.
  3. The firewall must allow traffic on the MySQL port.

Useful Command-Line Options

The MySQL client supports many flags that make daily work easier:

  • -e "query" — execute a statement and exit, perfect for scripts:
    mysql -u root -p -e "SHOW DATABASES;"
    
  • --batch or -B — tab-separated output, ideal for piping into other tools.
  • --vertical or \G — displays rows vertically, helpful for wide result sets.
  • --database=db or -D db — selects the default database at login.
  • --safe-updates — prevents accidental UPDATE or DELETE without a WHERE clause.

Handling Passwords Securely

A common mistake is placing the password directly in the command:

mysql -u root -pMySecret123   # Avoid this!

This exposes the password in your shell history and in the process list visible to other users. Safer alternatives include:

  • Omit the password after -p and type it when prompted.
  • Store credentials in an encrypted login path:
    mysql_config_editor set --login-path=local --host=localhost --user=root --password
    
    Then connect simply with:
    mysql --login-path=local
    
  • Use a restricted .my.cnf file with chmod 600 so only your account can read it.

Troubleshooting Common Errors

ERROR 2003: Can't connect to MySQL server

This usually means the client cannot reach the server at all. Check that:

  • The MySQL service is running (sudo systemctl status mysql).
  • The hostname and port are correct.
  • No firewall is blocking the connection.

ERROR 1045: Access denied for user

The server responded, but your credentials were rejected. Verify the username, password, and that the account is permitted to connect from your host. You can inspect accounts with:

SELECT user, host FROM mysql.user;

ERROR 1049: Unknown database

You specified a database name that does not exist. Connect without the database first, then create it:

CREATE DATABASE my_database;

Socket errors on localhost

On Unix systems, localhost uses a socket file rather than TCP. If the socket path is wrong, force TCP with:

mysql -h 127.0.0.1 -u root -p

A Quick Workflow Example

Here is

a typical session that walks through creating a database, provisioning an account, and loading some data — all from the terminal:

# 1. Connect as an administrator
mysql -u root -p

# 2. Create the database and a dedicated user
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'app'@'localhost' IDENTIFIED BY 'Str0ng-Passw0rd';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'localhost';
FLUSH PRIVILEGES;
EXIT;

# 3. Verify the new account works and select a default schema
mysql -u app -p -D shop

# 4. Create a table and insert a row
CREATE TABLE products (
  id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name    VARCHAR(120) NOT NULL,
  price   DECIMAL(10,2) NOT NULL,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO products (name, price) VALUES ('Mechanical keyboard', 89.90);

# 5. Query it, with vertical output for readability
SELECT * FROM products\G

# 6. Back the database up before a risky change
mysqldump -u root -p --single-transaction shop > shop_backup.sql

A few habits worth building into that loop:

  • Always back up before a bulk UPDATE or DELETE. A mysqldump takes seconds and has saved countless afternoons.
  • Use --safe-updates during interactive sessions when you are experimenting, so an unqualified statement fails instead of silently rewriting a whole table.
  • Prefer -e in scripts rather than piping SQL via heredocs; it is easier to read and fails loudly with a non-zero exit code.
  • Grant the narrowest privileges that work. An application account rarely needs DROP or GRANT OPTION.

Conclusion

The MySQL command-line client looks unremarkable, but it is one of the most efficient ways to work with a database: no GUI to wait on, no connection dialogs, and output you can pipe straight into grep, awk, or a file. The essentials are small enough to learn in an afternoon — connect with mysql -u user -p, select a schema with -D, run one-off statements with -e, and switch to \G when rows get wide Practical, not theoretical..

What separates a beginner from a confident user is less about knowing more flags and more about knowing the failure modes. ERROR 2003 means the network or service is the problem, ERROR 1045 means the credentials or host grant is, and ERROR 1049 means you simply named a database that is not there yet. Once you can place an error in one of those buckets, troubleshooting stops being guesswork.

Finally, treat credentials with the same care you would give a production password anywhere else. Leave the password off the command line, let the client prompt you, and lean on mysql_config_editor or a chmod 600 option file for anything you do repeatedly. Master those few practices and the mysql prompt becomes less of a chore and more of a fast, reliable place to get work done Most people skip this — try not to..

You'll probably want to bookmark this section Small thing, real impact..

Fresh Out

Freshly Published

Readers Also Loved

Other Perspectives

Thank you for reading about Connect To Database Mysql Command Line. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home