How to Create A Database In MySQL Using The Command Line

How to Create a MySQL Database via command line

MySQL is a popular RDBMS that powers many websites and applications. Its compatibility with all popular environments, including cloud VPS and bare-metal servers, is a massive factor in its popularity.

Since MySQL is compatible with almost all operating systems and hardware configurations, chances are you’ll find yourself working in an environment where you only have access to GUI-based tools (a common scenario when working with legacy systems).

This short guide covers the scenario where you need to create a database in MySQL using the command line. We’ll also cover some related database management tasks you can run to work with the database tables.

You can apply the steps of this tutorial in almost all MySQL installations with similar results.

Let’s start with the prerequisites.

Table of Contents

  1. Prerequisites
  2. Create a Database in MySQL Using the Command Line
    1. Step 1: MySQL Command Line Client Installation
    2. Step 2: Access the MySQL Shell
    3. Step 3: Log Into the MySQL Server and Check Existing Databases
    4. Step 4: Create a MySQL Database Using the Command Line
  3. Verify Database Creation
  4. How To Delete A Database In MySQL Using Command Line
  5. Execute Simple Commands Using MySQL Shell
    1. Command 1: Check and Create a Database
    2. Command 2: List All Tables in a Database
    3. Command 3: Insert Data Into a Table
    4. Command 4: Retrieve Data From Tables
  6. Conclusion
  7. FAQs

Prerequisites

Our test server for this tutorial:

  • A VPS running Ubuntu 20.04
  • A non-root user with sudo privileges or Root User Access.

Create a Database in MySQL Using the Command Line

Let’s start creating a MySQL database using the CLI.

Step 1: MySQL Command Line Client Installation

When setting up MySQL, you get the option to install MySQL shell. If you didn’t set it up during the installation, we recommend installing it now before proceeding with the database creation.

MySQL shell installation packages are available for Windows, Linux, and macOS. Check here for the steps to install MySQL command line installation on Linux.

Step 2: Access the MySQL Shell

The first step is to connect the client to a particular user login.

Launch the Terminal in Linux or Mac environments or the Command Prompt in Windows Server.

Note that you may run the command immediately if the MySQL shell path is set in the environment variable. Otherwise, first, browse to the directory where the MySQL shell is installed with the CD command.

Having the MySQL location in the PATH environment variable allows you to conveniently launch commands without constantly using the path of the binary/executable.

We recommend setting the PATH variables to simplify the process.

In a Windows environment, MySQL is usually found in the Program Files folder (the location is usually C:/Program Files/MySQL/MySQL Server 5.7/bin). You can add the binary path to the PATH variable by following the instructions in the MySQL handbook.

Similarly, in Mac and Linux environments, the MySQL shell installation is usually located at /usr/local/mysql. We recommend using the following command to add this location to the PATH environment variable:

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

Step 3: Log Into the MySQL Server and Check Existing Databases

Next, launch the terminal and use the following command to log into the MySQL server.

#mysql -u root -p

Important: Use the -u flag to set the user as root and the -p flag to get the prompt for the password.

#mysql -u root -p command to create a database in mysql

Once you’ve entered the password at the prompt, you will be logged into the MySQL server (you can see the prompt has changed from the terminal to MySQL>).

View Existing Databases

Before creating a new database, viewing existing databases is always a good idea because you cannot have two databases with the same name.

Use the following command to see the list of current databases:

mysql> SHOW DATABASES;

mysql> SHOW DATABASES;

You will see all the available databases listed in the terminal.

Step 4: Create a MySQL Database Using the Command Line

Now, let’s go ahead and create a database with a simple command. For the demonstration, we’ll create a database named Redswitches_database.

Start with the following command:

mysql> CREATE DATABASE Redswitches_database;

mysql> CREATE DATABASE Redswitches database command to create a database in mysql

Even when the command executes correctly and creates the database, you’ll not see a detailed success message, except Query OK.

Verify Database Creation

Because of the absence of a detailed message, we recommend you verify that the database has been successfully created. You should once again list the available databases on the server with the SHOW command:

mysql> SHOW DATABASES; command.

Here is the output for our test server:

output command to create a database in mysql

As you can see, the list now includes Redswitches_database in the results.

At this point, you have a fresh database ready for use.

Also Read: How To Rename a Database in MySQL: 3 Easy Methods

How To Delete A Database In MySQL Using Command Line

How to Create a MySQL Database via command line 2

In addition to creating databases, deleting databases is a common activity. MySQL provides the DROP statement for deleting databases with a simple command.

mysql> DROP DATABASE Redswitches_database;

Important: This action is permanent, and recovering data from deleted databases can be very challenging. We recommend EXTREME CAUTION when using this command.

Given the seriousness of the action, the command will ask you for confirmation. Press ENTER to continue.

After deleting a database, you should always verify the deletion with the SHOW DATABASE command.

Execute Simple Commands Using MySQL Shell

In addition to creating and deleting databases, you can execute several commands using MySQL Shell. Let’s take a look at these commands.

Command 1: Check and Create a Database

Use the following command to check if the database with the given name exists. If the check fails, the command creates the database.

MySQL [(none)]> CREATE DATABASE IF NOT exists redswitches_db;

Query OK, 1 row affected (0.006 sec)

Command 2: List All Tables in a Database

Use the following command to list all tables in a database

MySQL [mysql_concepts]> SHOW TABLES;

// Output

+--------------------------+

| Tables_in_redswitches_db |

+--------------------------+

| company_details |

| emp_details |

+--------------------------+

3 rows in set (0.001 sec)

Command 3: Insert Data Into a Table

Use the following command to insert data into a table. For this demonstration, we’ll insert a record in the emp_details table.

MySQL [redswitches_db]> INSERT INTO `emp_details`

(`emp_id`,`emp_name`) VALUES

(1,’101’),(2,’ABC’);

// Output

Query OK, 2 rows affected (0.006 sec)

Records: 2 Duplicates: 0 Warnings: 0

Command 4: Retrieve Data From Tables

We’ll use the SELECT statement to retrieve data from the emp_details table. When used with *, the statement will fetch all records.

MySQL [redswitches_db]> SELECT * FROM emp_details;

+------------+--------------+

| emp_id | emp_name |

+------------+--------------+

| 1 | 101 |

| 2 | ABC |

+------------+--------------+

2 rows in set (0.000 sec)

Also Read: 3 Simple Methods to Delete Duplicate Rows in MySQL Database

Conclusion

This article covered the MySQL commands for creating databases using the command line. This is a quick way of creating databases without going through the GUI. We also mentioned how you could view the available databases and remove databases from the server.

If you have a MySQL-powered project, you need a robust infrastructure to handle the volume of user requests and deliver a smooth user experience.

RedSwitches offers the best dedicated server pricing and delivers instant dedicated servers, usually on the same day the order gets approved. Whether you need a dedicated server, a traffic-friendly 10Gbps dedicated server, or a powerful bare metal server, we are your trusted hosting partner.

FAQs

What is a Command Line Interface?

A Command Line Interface (CLI) is a text-based interface for interacting with a computer program or operating system. In a CLI, users interact with the system by typing commands into a terminal or command prompt, and the system responds with text-based output. CLI is an alternative to graphical user interfaces (GUIs), which use graphical elements like windows, icons, and buttons for interaction.

How do I start the MySQL Command Line Client?

You can start the MySQL Command Line Client by opening a terminal or command prompt and typing mysql -u username -p. Replace your username with your MySQL username. You’ll be prompted to enter your MySQL password.

What exactly is the MySQL command line tool?

MySQL is a bare SQL shell that allows you to alter the input line. It may be used in both interactive and noninteractive modes. When used interactively, query results are shown as an ASCII table. The output is tab-separated when used non-interactively (for example, as a filter).

How can I connect to a specific database using the MySQL command line?

Answer: Using the MySQL command-line client, we may specify the database we wish to connect (and all subsequent queries will be conducted on that database).

Run the following command on the terminal:

mysql -u root -p {databaseName}