How to Access MySQL from the Command Line

This guide shows you how to connect to a MySQL server directly from your terminal or command prompt, run SQL queries, and manage databases without needing a graphical tool. You’ll learn the exact commands to log in, check what’s there, and execute common operations—all from the command line.

What You’ll Need

• A MySQL server installed and running (locally or on a remote host).
• The MySQL command-line client (mysql) installed on your computer. It comes with the MySQL server package, or you can install it separately.
• Valid credentials: a username (usually root for local development) and a password.
• Access to a terminal (Linux/macOS) or Command Prompt/PowerShell (Windows).

Step-by-Step

1. Open your terminal – On Linux/macOS, open Terminal. On Windows, open Command Prompt or PowerShell.
2. Check if the MySQL client is installed – Type mysql --version and press Enter. If you see something like mysql Ver 8.0.36 for Linux on x86_64, you’re good. If not, install it using your package manager (e.g., sudo apt install mysql-client on Ubuntu, brew install mysql-client on macOS, or download from the official MySQL site on Windows).
3. Connect to MySQL – Use the basic syntax: mysql -u username -p. Replace username with your MySQL username (e.g., root). The -p flag tells MySQL to prompt for a password. Example: mysql -u root -p. Press Enter, then type your password when prompted. You’ll see the mysql> prompt, meaning you’re in.
4. Connect to a specific database (optional) – If you know which database you want to use, add the database name at the end: mysql -u username -p database_name. For example: mysql -u root -p mydb.
5. Connect to a remote MySQL server – Add the -h flag with the hostname or IP address: mysql -h 192.168.1.100 -u username -p. You can also specify a port with -P (uppercase) if it’s not the default 3306, e.g., mysql -h host -P 3307 -u user -p.
6. Run your first query – Once connected, type SHOW DATABASES; and press Enter. This lists all databases on the server. End every SQL statement with a semicolon ;.
7. Select a database to work with – Use USE database_name; (e.g., USE mysql;).
8. View tables in the selected database – Type SHOW TABLES; to see all tables.
9. Run a simple SELECT query – For example, SELECT * FROM your_table_name LIMIT 10; will show the first 10 rows of that table.
10. Exit the MySQL prompt – Type EXIT; or press Ctrl+D (Linux/macOS) or Ctrl+Z then Enter (Windows).

Common Mistakes

• Forgetting the semicolon – SQL statements won’t execute until you end them with a semicolon. If you forget, the prompt shows a continuation -> instead of mysql>.
• Using the wrong password or username – Double-check with your system administrator or the MySQL installation logs.
• Typing -p with a space – -p password is incorrect; you should use -p and then enter the password when prompted, or -ppassword (no space) if you must include it in the command (but this is insecure).
• Using the wrong host – If connecting remotely, ensure the host is reachable and MySQL is configured to allow remote connections (bind-address).
• Case sensitivity – On Linux, database and table names are case-sensitive. Use the exact case.

Tips

• Use -p without a password in the command – This avoids exposing the password in your shell history.
• Create a configuration file – For frequent connections, create a ~/.my.cnf file with your credentials (chmod 600) to avoid typing them each time.
• Use --ssl-mode=REQUIRED – When connecting remotely over untrusted networks, add this flag to encrypt the connection.
• Learn the help – Type help; or \h at the mysql prompt to see available commands.
• Use batch mode for scripts – For automation, use mysql -u user -p database < script.sql to run a file of SQL commands.

Troubleshooting

• Error: “Can’t connect to local MySQL server through socket” – The MySQL server may not be running. Start it with sudo systemctl start mysql (Linux) or brew services start mysql (macOS).
• Error: “Access denied for user ‘root’@’localhost'” – Check the username and password. If you forgot the root password, you may need to reset it (search for “reset MySQL root password” for your OS).
• Error: “Command not found” – The MySQL client isn’t in your PATH. On Windows, add the MySQL bin directory to PATH, or use the full path like C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe.
• Error: “Unknown database” – The database name you specified doesn’t exist. Use SHOW DATABASES; to see available ones.
• Connection hangs – If connecting to a remote host, check firewall rules, network connectivity, and that MySQL is listening on the expected port (netstat -tlnp | grep 3306 on Linux).

Frequently Asked Questions

How do I exit the MySQL command line?

Type EXIT; or QUIT; and press Enter, or use the shortcut Ctrl+D (Linux/macOS) or Ctrl+Z then Enter (Windows).

What is the default MySQL port?

The default port is 3306. If your server uses a different port, specify it with the -P flag (uppercase) when connecting.

How do I connect as a different user?

Use the -u flag followed by the username, e.g., mysql -u admin -p.

Can I run a SQL file from the command line?

Yes, use input redirection: mysql -u user -p database_name < file.sql. This executes all commands in the file.

How do I see all MySQL commands available?

At the mysql prompt, type help; or \h to list client commands. For SQL syntax help, type help contents;.

Is it safe to type my password in the command line?

No, it’s safer to use the -p flag without a password so you’re prompted interactively. If you must include it, use -pYourPassword with no space, but be aware it may be visible in shell history.

Related

• How to Choose a Big Battery Phone in India
• How to Choose an AI Coding Assistant
• Browse Internet Kodi: What It Means & How to Do It