How to Create a MySQL Database from the Command Line in Linux

MySQL is one of the most popular open-source relational database management systems used by developers and organizations around the world. With its high performance, reliability, and ease of use, MySQL has become the go-to choice for web applications of all sizes.

One of the first things you need to do when setting up a MySQL server is to create a database that will store your application‘s data. In this comprehensive guide, we will walk you through the steps to create a MySQL database from the command line in Linux.

Prerequisites

Before you can create a MySQL database, you need to:

• Have MySQL server installed on your Linux system. If not, run:

sudo apt install mysql-server

• Have root access or privileges to create databases.

• Know the basic MySQL commands.

Step 1 – Log in to the MySQL Server

To access the MySQL server, you need to log in using the mysql client command:

sudo mysql -u root -p

This will prompt you for the root password. Enter the password and hit enter to log in.

If you did not set a root password during installation, omit the -p flag from the command.

Once logged in successfully, you should see the MySQL monitor with the MySQL> prompt.

Step 2 – Create the Database

To create a new MySQL database, use the CREATE DATABASE statement:

CREATE DATABASE mydatabase;

Replace mydatabase with your desired database name.

Some points to note:

• Database names are case-insensitive in Linux. mydatabase and MyDatabase refer to the same database.

• Stick to using lowercase letters, numbers, and underscores. Avoid special characters and spaces.

• The statement ends with a semicolon ;

For example, to create a database named myapp:

CREATE DATABASE myapp;

Set Character Set and Collation

It‘s good practice to define the character set and collation when creating your database:

CREATE DATABASE myapp
  CHARACTER SET utf8
  COLLATE utf8_general_ci;

This sets the default character set to UTF-8 and the collation to utf8_general_ci for the myapp database.

Step 3 – Verify the Database was Created

To check that the new database was created successfully, use the SHOW DATABASES statement:

SHOW DATABASES;

This will display all databases on your MySQL server. You should see your newly created database in the output.

Step 4 – Grant User Privileges

By default, only the MySQL root user has full privileges to the new database. To allow other users to connect and manage the database, you need to explicitly grant privileges.

First, create a new user (or use an existing one):

CREATE USER ‘myuser‘@‘localhost‘ IDENTIFIED BY ‘password‘;

Then grant privileges to that user for the database:

GRANT ALL PRIVILEGES ON myapp.* TO ‘myuser‘@‘localhost‘; 

This allows the myuser user full privileges to connect locally and perform any operation like select, insert, update, delete on the myapp database.

The * is a wildcard to match all tables in the database. You can also grant privileges to specific tables only.

Step 5 – Switch Database Context

To start interacting with your new database, you need to switch the database context in the MySQL prompt:

USE myapp;

Now any subsequent commands like creating tables, inserting data etc. will apply to the myapp database.

Step 6 – Create Tables & Work With Data

With the database created, you can now create tables and start storing and managing data:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE
);

INSERT INTO users (name, email) 
VALUES (‘John‘, ‘john@example.com‘);

This creates a users table with some columns and inserts a row for the user John.

From here on you can execute any queries, build relationships between tables using foreign keys, manage users and permissions etc. based on your application requirements.

Step 7 – Delete or Drop the Database

If at any point you need to delete the database, use the DROP DATABASE statement:

DROP DATABASE myapp;

This will completely remove the database and all its tables and data from the MySQL server.

So only use this when you no longer need the database.

Conclusion

Creating MySQL databases from the Linux command line interface is quick and easy. With just a few SQL statements like CREATE DATABASE, GRANT privileges, USE database, you can set up the perfect development or production database.

Defining the correct character set and collation during creation avoids potential data consistency issues down the road. And granting focused user privileges reduces accidental data loss or corruption.

With this guide, you are now equipped not only to create databases but also delete, backup, restore or migrate them with confidence using Linux terminal.

So go forth and build awesome MySQL-backed applications!