How To Create A MySQL Database - Linux Guru

Photo credit Rubaitul Azad on Unsplash

MySQL is the most popular open-source and widely used relational database management system, MySQL is a free and open-source database service. It's easy to set up and run the database on local or cloud servers. In this post, I will explain to you some MySql useful commands to create MySQL database, backup MySQL database, and restore MySQL database.


How to create MySQL database using CLI

Login to MySQL as the root  user

mysql -u root -p


Create a database name mydb

create database mydb;
We have created our first MySQL database. Let's create a MySQL user to access the database.

 

How to create a MySQL user
We will create a user name vishal with the password vishal#123. Execute the below command to create a MySQL user

CREATE USER 'vishal@localhost' IDENTIFIED BY 'vishal#123';

Note: If you are connecting the database using an IP address then use a username like 'vishal'@'server-ip' and Use 'vishal'@'%' to access from any machine.


How to grant permission in MySQL
We have successfully created the database and user yet, Now it's time to grant privileges to the MySQL user to access the database. Currently, we will grant all permission to the database for users.

Grant all privileges to the user vishal, User will have full access control over database.

GRANT ALL PRIVILEGES ON database_name.* TO 'vishal'@'localhost';

Create a super user

 If you want to give permission to the user to access all databases. This command will grant all the permission on the database to the user.

GRANT ALL PRIVILEGES ON * . * TO 'vishal'@'localhost';


There are several more grant options available that you can use.
CREATE: The user can create a database and tables
SELECT: The user can retrieve the data
DELETE: The user can erase the table entries
INSERT: The user can add new entries to the tables
DROP: The user can drop entire tables and databases
UPDATE: The user can modify existing entries in tables


How to backup MySQL database
Run the below command to dump the MySQL database

mysqldump -u [username] -p [password] [database_name] > [backup_file.sql]

This command will create a backup for the database and save it to the backup_file.sql


How to restore MySQL database
Apply below command to restore a database using backup file.

mysql -u [username] -p [password] [database_name] < [backup_file.sql]

This command will import the backup in the database using backup file.


Hope you like the article, Like share, and comment on the post if you have any suggestions.

Vishal Vyas [Linux Guru]

Welcome to Linux Guru! Hello, friends. My name is Vishal Vyas, and I am a DevOps engineer with expertise in Linux and Cloud Computing. I am also a Certified Kubernetes Administrator with over 12 years of experience in the IT field, working with various technologies. Through this blog, I aim to share my technical knowledge on Linux, AWS, DevOps, and web technologies. I will be posting about what I have learned from the latest web technologies and similar topics.

Post a Comment

If you have any doubts, Please let me know

Previous Post Next Post