How to Create MySQL Database, User, and Grant Permissions in Ubuntu

MySQL is a widely-used open-source relational database management system. Managing databases, users, and permissions are essential tasks for database administrators and developers. This guide will provide a detailed walkthrough on how to create a MySQL database, create a user, and grant necessary permissions in an Ubuntu environment.

Prerequisites

Before we start, ensure you have the following:

An Ubuntu system with MySQL installed
Access to a terminal with sudo privileges
Basic understanding of SQL and MySQL commands

Step-by-Step Guide

Step 1: Log in to MySQL

First, open your terminal and log in to the MySQL root user account. You can do this with the following command:

sudo mysql -u root -p

You will be prompted to enter the root user password.

Step 2: Create a New Database

Once logged in to the MySQL shell, create a new database using the following SQL command:

CREATE DATABASE mydatabase;

Replace mydatabase with your desired database name.

Step 3: Create a New MySQL User

To create a new user in MySQL, use the following command. Make sure to replace username and password with your desired username and a strong password:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Step 4: Grant Permissions to the User

Grant all necessary permissions to the newly created user on the database you just created. Replace username and mydatabase with your actual username and database name:

GRANT ALL PRIVILEGES ON mydatabase.* TO 'username'@'localhost';

To apply the changes, make sure to flush the privileges:

FLUSH PRIVILEGES;

Step 5: Verify User Permissions

To verify that the new user has the correct permissions, you can log out from the MySQL shell and log back in as the new user:

exit
mysql -u username -p

After logging in, you can check the granted privileges with the following command:

SHOW GRANTS FOR 'username'@'localhost';

Step 6: Testing Database Access

As the new user, you can now try accessing the database to ensure everything is set up correctly:

USE mydatabase;
SHOW TABLES;

If everything is set up correctly, you should be able to access the database without any issues.

Creating a MySQL database, user, and granting the necessary permissions is a straightforward process when you follow these steps. This guide ensures that you can manage your MySQL environment efficiently on an Ubuntu system. Proper user management and permission settings are crucial for maintaining the security and integrity of your database.