How to get MySQL on command prompt?

This tutorial explains how we can use MySQL from the Command Line (or terminal for macOS and Linux-based users) with example illustrations:

We can do almost everything from the shell through commands the same as what we can do in the UI clients like MySQL Workbench or TablePlus etc. UI tools are intuitive and ideal for query visualization, display, data export/import, etc.

However, the query/command-line interface is faster and is used by developers for quicker query execution.

=> Read Through ALL the MySQL Tutorials

What You Will Learn:

MySQL From The Command Line

Installing MySQL Command Line Client

We can choose MySQL shell to be installed during the installation of MySQL itself. If not, then we can choose to install the MySQL shell separately as well.

MySQL shell installations are available for Windows, Linux, and macOS operating systems. The installer is available as a .exe (for Windows), .dmg (for macOS) based systems & as an apt package for Linux.

Please refer to guides from MySQL’s official website for different OS versions:

Click here for a guide on installing MySQL Shell on Windows

Click here for a guide on installing MySQL Shell on MacOS

Click here for a guide on installing MySQL Shell on Linux

Connecting To MySQL Client

Once the MySQL shell is installed, follow the steps below to connect client against a given user login:

#1) Open the shell/terminal in Mac/Linux (or command prompt in Windows)

#2) If the MySQL shell path is added to the environment variable, you can execute the command directly, else you can first navigate to the directory where the MySQL shell is installed.
Having the MySQL location available in the PATH environment variable helps to invoke the commands easily without navigating to the location of the binary/executable always.

• For Windows, the installation happens inside the ‘ProgramFiles’ folder C:\Program Files\MySQL\MySQL Server 5.7\bin. You can choose to add the path of the binary to the PATH variable. Refer guide here.
• Similarly, for MAC and LINUX based users, the MySQL shell installation is available at /usr/local/mysql. You can add this location to a PATH environment variable, by running the below command:

EXPORT PATH=$PATH:/usr/local/mysql

#3) Now, in order to login to MySQL command line, with a given username and password, execute the command below:

mysql -u {USERNAME} -p

Here, USERNAME is the user with which you want to connect to the MySQL server. For example ‘root’.

Please note, we have just mentioned -p and not the actual password yet. This will just let the interpreter know that the user has a password to log in and will be entered in subsequent steps.

The actual command look like as below:

$ mysql -u root -p

#4) Press enter and notice that the terminal prompts you for a password. Enter the password (you won’t be able to see the password as the input is hidden to prevent any malicious attempts/social engineering to get to the password).

#5) Once the correct password is entered, you will be logged in to the shell, and reach MySQL prompt (which is ready to receive any MySQL commands).

If the password is entered incorrectly, ‘Access Denied’ message will appear as below:

Note: By default, the host which is connected to is localhost or local IP i.e. 127.0.0.1

In practice, almost all the time you will need to connect to some remote host. In order to do that we can specify the hostname using the -h flag.

mysql -u {USERNAME} -h {hostIP} -p

Examples Using MySQL Command Line

Test Data

We will use the below test data to understand the examples better:

CREATE DATABASE IF NOT EXISTS mysql_concepts;

CREATE TABLE `orders` (
  `order_id` INT NOT NULL,
  `customer_name` VARCHAR(255),
  `city` VARCHAR(255),
  `order_total` DECIMAL(5,2),
  `order_date` VARCHAR(255),
  PRIMARY KEY (order_id)
) 

CREATE TABLE `order_details` (
  `order_id` INT,
  `product_id` INT,
  `quantity` INT,
  FOREIGN KEY (product_id) REFERENCES product_details(product_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
)

CREATE TABLE `product_details` (
  `product_id` INT NOT NULL,
  `product_name` VARCHAR(100),
  PRIMARY KEY(product_id));
);

After connecting to the MySQL command line – execute the above queries.

Also Read =>> Use Of MySQL CREATE TABLE command

Executing Simple Commands Using MySQL Shell

Let’s see some common examples/commands using MySQL from the command line.

#1) Mysql create a database command line

MySQL [(none)]> CREATE DATABASE IF NOT exists mysql_concepts;
Query OK, 1 row affected (0.006 sec)

#2) Show all tables in a database

MySQL [mysql_concepts]> SHOW TABLES;

// Output
+--------------------------+
| Tables_in_mysql_concepts |
+--------------------------+
| order_details            |
| orders                   |
| product_details          |
+--------------------------+
3 rows in set (0.001 sec)

#3) Insert data into a table – Let’s try to insert a record in the product_details table.

MySQL [mysql_concepts]> INSERT INTO `product_details` 
(`product_id`,`product_name`) VALUES 
(1,'Biscuits'),(2,'Chocolates');

// Output
Query OK, 2 rows affected (0.006 sec)
Records: 2  Duplicates: 0  Warnings: 0

#4) Retrieve data from tables – Let’s use a SELECT statement to retrieve data from the product_details table.

MySQL [mysql_concepts]> SELECT * FROM product_details;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
|          1 | Biscuits     |
|          2 | Chocolates   |
+------------+--------------+
2 rows in set (0.000 sec)

Executing SQL Scripts Using MySQL Command Line

A lot of times, we have SQL script files (having .sql) extension and need to be executed. For example, bulk entry/edits into the database.

In this section, we will have a look at examples to execute the .sql files through the MySQL command line.

We will insert records into the product_details table through a SQL script file.
Create a file named product_details.sql using the following data:

INSERT INTO `product_details` (`product_id`,`product_name`) VALUES (3,'Beverages');
INSERT INTO `product_details` (`product_id`,`product_name`) VALUES (4,'Clothing');

We can use the source command and specify the full path of the SQL file.

Once you are logged in to the shell, you could run the below command:

mysql -u {USERNAME} -p

So, you can see above, we executed the statements contained in the product_details.sql file and verified by executing the SELECT statement (which shows the 2 new entries that were there in the product_details.sql file).

Exporting Query output from MySQL Command Line

Let’s now see how we can save the output of a query. For example, to a CSV file.

While running on the command line, the output is by default displayed inline in the terminal or command window. When we want to save the output to, for example, a CSV file we can use the file output operator ‘>’

Let’s have a look at an example where we take the input from a SQL file and write the output to a CSV file.

Create a .sql file which have a SELECT query to get all rows from the product_details table. Save this file as get_product_details.sql

mysql -u {USERNAME} -p

Let’s now execute this file and save the output in a file named product_details.csv

We can use a command like:

mysql -u {USERNAME} -p

Example:

mysql -u {USERNAME} -p

For the above, you will be prompted to enter the password. Once access is granted, the query will be executed and an output file named test.csv is generated with details of the SELECT query.

Frequently Asked Questions

Q #1) How do I install MySQL from the command line?

Answer: MySQL shell installers are available for different operating systems like Windows, OSx, Linux, etc.

>> Refer to the details here.

Optionally, the MySQL command line/shell could also be installed as a component when MySQL server installation is done.

Q #2) How do you connect to a remote MySQL server from your client by command prompt?

Answer: MySQL command line provides the flexibility of connecting to a server on a remote host as well as local host. If no hostname is specified then it assumes that you are trying to make a connection to the localhost (or 127.0.0.1)

For connecting to a remote host, you can mention the host IP or hostname using the ‘-h’ command. (Also to connect to a specific port you can use the -P flag)

mysql -u {USERNAME} -p

For example:

mysql -u {USERNAME} -p

Q #3) How can I directly connect to a particular database using MySQL Command line?

Answer: By using the MySQL command-line client, we can directly specify the database we want to connect (and all further queries would be run on that database)

Execute the below command on the terminal:

mysql -u {USERNAME} -p

After the above command, once you enter the correct password, you will be directly connected to the databaseName that was specified (because you have access grants to the database that’s mentioned).

For example: Connecting to a database named mysql_concepts directly while starting MySQL from the command prompt, we can use:

mysql -u {USERNAME} -p

Q #4) How do you output MySQL query results in CSV format?

Answer: MySQL command line provides options to accept input in the form of .sql script and we can output the corresponding results to a CSV file while running from the terminal itself.

mysql -u {USERNAME} -p

Q #5) How do I export a MySQL database from the command line?

Answer: In order to export the MySQL database from the command line, you can use the “mysqldump” command.

mysql -u {USERNAME} -p

Once you run the above command on the terminal, it will prompt you for the password. On entering the correct password, you will see a file named mysql_concepts.sql created in the user directory with CREATE / INSERT scripts for the mysql_concepts database.

Suggested Reading =>> Steps to Create a Database in MySQL

Conclusion

In this tutorial, we learned about using the MySQL command line. We learned about different ways we can connect to the MySQL shell and how we can connect to a particular database directly, how we can execute SQL script files and export the output to CSV files.

MySQL command line is used by developers and DevOps team for quickly executing queries and avoiding GUI as the shell or command line is lightweight and doesn’t consume a lot of memory/resources as compared to a graphical UI client such as MySQL workbench.

How to get MySQL in command

How do I get SQL command prompt?

How do I go back to MySQL in command prompt?

How to start MySQL server from command