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 asmysql-clientor MariaDB's client. - Valid credentials — a username and password for an account that has permission to connect.
- Host and port information — typically
localhostand port3306. - 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:
- The MySQL user must be allowed to connect from your IP address (for example,
'myuser'@'192.168.1.%'instead of'myuser'@'localhost'). - The server's
bind-addressinmy.cnfmust not be restricted to127.0.0.1only. - 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;"--batchor-B— tab-separated output, ideal for piping into other tools.--verticalor\G— displays rows vertically, helpful for wide result sets.--database=dbor-D db— selects the default database at login.--safe-updates— prevents accidentalUPDATEorDELETEwithout aWHEREclause.
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
-pand type it when prompted. - Store credentials in an encrypted login path:
Then connect simply with:mysql_config_editor set --login-path=local --host=localhost --user=root --passwordmysql --login-path=local - Use a restricted
.my.cnffile withchmod 600so 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
UPDATEorDELETE. Amysqldumptakes seconds and has saved countless afternoons. - Use
--safe-updatesduring interactive sessions when you are experimenting, so an unqualified statement fails instead of silently rewriting a whole table. - Prefer
-ein 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
DROPorGRANT 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..