How to Create Database in MySQL from Command Line

Introduction to MySQL Database

MySQL is the most popular Relational Database Management System (RDBMS). Almost in every school of vacational information technology, it is widely studied. Most lecturer also obliged to teach MySQL in their college. A student without knowing MySQL is like a tree without fruit. It is because almost all application, use MySQL as database. Although the interface vary from web-based to Android app, the database still MySQL forever.

That is why to teach how to use MySQL always become an interesting job for everyone and also for every students of information technology to learn it. There is no job in IT without knowing MySQL. In designing an application also, the basic to do is designing tables in MySQL. This applies for any application that is requiring database to serve data.

Three Ways to Create Database in MySQL

• Using Command Prompt
• Using MySQL desktop client like Workbench
• Using web-based client like PhpMyAdmin
• Using Cpanel dashboard on shared web hosting

There are so many ways. Here we will dive into the basic classical way, that is, creating database with command prompt. The benefit of knowing this way is this method can be applied in any environtment both graphical and textual environtment, in any operating system, desktop or cloud.

However, before creating database we need to install it first. Log in to your server and update your list of installation software. In Ubuntu we do the following command:

sudo apt get update

The process of updating software list will run with this command. Then we would install MySQL by typing the following command:

sudo apt install mysql-server

When the installation process is finish, we can start MySQL service immediately:

sudo systemctl start mysql.service

Now, we can not run security installation anymore. It is because the change on technical aspect of MySQL. We can get error when trying to run command: mysql_secure_installation.

So, we log into Mysql prompt with method without password by typing sudo mysql

sudo mysql

If the command above failed (Access denied for user root@localhost), use this line to log in into MySQL with phpMyAdmin password:

mysql -uroot -p

We will be landed on MySQL command prompt. Type show databases;

mysql> show databases;

There will be four default database:

• information_scheme
• mysql
• performance_scheme
• sys

To create a database, simply type command below:

CREATE DATABASE db_mydata;
CREATE USER 'u_mydata'@'localhost' IDENTIFIED BY 'MyPassWord123';
GRANT ALL PRIVILEGES ON db_mydata.* TO 'u_mydata'@'localhost';
flush privileges;
quit;

Now, we check again with command show databases. The recently created database db_mydata has been listed on the showing result.

That’s all in creating database on MySQL.