How to Create a MySQL Database, User, and Grant All Privileges

If you’re starting with MySQL, one of the first things you’ll need to do is create a database, set up a user, and grant them the right permissions.

‍

This might sound complicated, but it’s actually pretty simple!

‍

Let’s go step by step.

Create a Database

First, you need to create a new database.

‍

Think of a database as a container where all your tables and data will be stored.

‍

The easiest way to do this is by using MySQL Workbench.

‍

Open Workbench, connect to your MySQL server, and run the following command in the SQL query editor:

‍

CREATE DATABASE my_database;

‍

This creates a database called my_database. You can name it whatever you like.

Create a New User

Now, let’s create a user who will have access to the database.

‍

Run this command:

‍

CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'my_secure_password';

‍

• 'my_user' → Replace this with your preferred username.
• 'localhost' → This means the user can only connect from the same machine as the database.
• 'my_secure_password' → Change this to a strong password.

Grant All Privileges to the User

By default, a new user has no access to the database. Let’s grant full permissions.

‍

Run this command:

‍

GRANT ALL PRIVILEGES ON my_database.* TO 'my_user'@'localhost';

‍

This gives the user full control over the my_database.

‍

Finally, apply the changes with:

‍

FLUSH PRIVILEGES;

Verify Everything Works

To check if the user has access, open Command Prompt on Windows or Terminal on macOS/Linux and try logging in:

‍

mysql -u my_user -p

‍

Enter your password, then switch to the database:

‍

USE my_database;

‍

If everything works, you’re all set.

Key Takeaway

Creating a database, a user, and granting privileges in MySQL is quick and easy when you know the steps.

‍

Just remember to use strong passwords and only grant the permissions users actually need.

‍

Now, you’re ready to start working with your new database.