How to Connect to MySQL from Command Line in Windows

Introduction

MySQL is a popular open-source relational database application, and it is used for many servers worldwide. How you access the database depends on the operating system you use to connect.

This guide walks you through using the Windows Command line to connect to a MySQL database.

Prerequisites

• A Windows-based system with an administrator account.
• A local MySQL installation.
• A text editor (optional).

Starting MySQL from Windows Command Line

The Windows command line, also known as the Command Prompt, is a text-based interface used to execute various system commands and perform administrative tasks. It is one method of connecting to MySQL from Windows.

To open the command prompt, hold the Windows key and press R on your keyboard to open the Run command box. Type in cmd and press Enter to open the Windows Command Prompt.

A black command-line interface starts, with white text and a cursor for you to type.

After opening the Command Prompt, follow the steps below to connect to MySQL.

Step 1: Verify MySQL Is Running on Windows

Before connecting, ensure that the MySQL service is running. Follow the steps below:

1. In the Command Prompt, run the following command to display the list of all the running services:

net start

If MySQL is not on the list, you can start it using the Services panel.

2. Use the following command to open Services:

services.msc

A new window will launch and display the list of services available on your system.

3. Scroll down to find MySQL and check the status column. If the status is Stopped, right-click the MySQL service and select Start to start the service.

Step 2: Run MySQL Server Command-Line Client

The MySQL Server command-line client is a tool that allows users to interact with the MySQL database server using text-based commands. It is similar to the Windows Command Prompt.

To open the MySQL command-line client, press the Windows key and type:

mysql 8.0 command line client

Replace 8.0 with the MySQL version you installed on your system and click the Command Line Client app from the left pane to open it.

A new window opens, allowing you to enter your administrator password and connect to the MySQL server.

Step 3: Connect to Local MySQL Server

There are two ways to connect to a local MySQL server via the command line:

• Using the dedicated MySQL Command Line Client described in the previous step.
• Using the Windows Command Prompt/Windows PowerShell.

Connect Using the MySQL Command Line Client

Follow the steps outlined in the previous section to open the MySQL Command Line Client and provide your administrator password to log in to the MySQL server:

After connecting, you can start managing your database in the same window.

Connect Using Windows Command Prompt

Open the Windows Command Prompt or Windows PowerShell and use the syntax below to connect to MySQL:

mysql -u [username] -p

Replace [username] with the username for your MySQL installation. For example, to log in as root, run the following command:

mysql -u root -p

MySQL prompts you for your root password. Enter the password to connect to the MySQL server.

Step 4: Get a List of Databases on MySQL Server

Listing all databases in MySQL provides a clear view of all the databases available on the MySQL server. The action is especially useful in multi-database environments where multiple projects or applications are hosted on the same server.

To list all databases, connect to the MySQL server and run the following command:

SHOW DATABASES;

The command outputs the list of all databases on the MySQL server.

Step 5: How to Select and Edit Database

Selecting a database in MySQL is a key operation that involves choosing a specific database to work with and later making changes to its structure or data. Editing a database involves creating new tables, updating records, optimizing database performance, etc.

Select a Database

To select a database, connect to the MySQL server and use the following syntax:

USE [database_name];

For example, to select a database named mysql, run the following command:

USE mysql;

The output states that the database has been changed. After selecting a database, you can start making edits.

Create a Table

To create a new table in MySQL, use the syntax below:

CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    column1_name DATA_TYPE,
    column2_name DATA_TYPE
);

Replace the table, column names, and data types for the columns according to your needs. For example:

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The statement creates a table named users with four columns:

• id - An auto-incremented integer that serves as the primary key.
• name - A variable-length string that holds up to 100 characters.
• email - A variable-length string that holds up to 100 characters.
• created_at - A timestamp that defaults to the current date and time when a new row is created.

Add Data

After creating a new table, populate it with data. Use the INSERT INTO statement to add data to a table in MySQL. The syntax is:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

For example, to insert data into the table we created above, use the following statement:

INSERT INTO users (name, email) VALUES ('John Doe', 'john@phoenixnap.com');

The statement inserts a new row into the users table with the name column set to John Doe and the email column set to john@phoenixnap.com. It automatically assigns values to the other columns id and created_at based on their default settings.

Step 6: Create Windows Shortcut to Log in to MySQL

A handy way to quickly log in to your MySQL server in Windows is to create a login shortcut with your credentials and save it as a .bat file. Batch files are often used to automate repetitive tasks and execute multiple commands in sequence.

Follow the steps below to create a login shortcut:

1. Open a text editor such as Notepad.

2. Paste the following:

cmd /K "C:\Program Files\MySQL\MySQL Server X.X\bin\mysql.exe" -u[username] -p[password]

• Replace [username] with the username you want to use to connect and [password] with your login password.
• Make sure to change the path from C:\Program Files\MySQL\MySQL Server X.X\bin\mysql.exe to the actual mysql.exe file location on your system.

3. Press Ctrl+S to launch the Save dialog.

4. Select All files as the file type and type mysql.bat in the name field. Save the shortcut on your desktop (or another place that's easy to find).

The file is a small Windows executable file you can double-click to log into MySQL using the specified username and password.

Conclusion

After following the steps in this guide, you should be able to connect to a MySQL database using the Windows command line. Once you have logged into the MySQL server, the commands are the same regardless of the system you are using.

Next, see how to tune your MySQL database for the best performance. Alternatively, if you are not a MySQL fan, check out how to use PostgreSQL from the command line.