How to create a new database and a new user in MySQL

We’re going to go through the most common and basic tasks regarding MySQL administration tasks. Basically we’re going to create a new database, a new user and then grant that user permissions to that database.

So let’s begin!

1. SSH to your server using your favorite client and log in as root or a primary user.

2. Once logged in, let’s log in to MySQL as the root user:

mysql - u root -p

2. You’ll be asked for the password. Enter it, and press Enter.

3. Let’s create the database. Enter the following:

CREATE DATABASE yourdatabasename;

Obviously, your database name should be replaced with… your desired database name.

4. Let’s create a new user! Enter the following:

CREATE USER 'yourusername'@'localhost' IDENTIFIED BY 'astrongpassword';

Where yourusername is the user name and astrongpassword is the password for that user. Make sure it’s a strong password.

5. Great! So we have a database and a user. But the user has no privileges to do anything to our database. Let’s fix this.

GRANT ALL PRIVILEGES ON yourdatabase.* TO 'yourusername'@'localhost';

This gives the user all permissions to the database. He can do whatever he wishes with that.

If this is not required and you want to maintain precise control over database access, you would replace ALL PRIVILEGES with SELECT or other privileges.

6. In order to apply those privileges there’s one more command you need to run:

FLUSH PRIVILEGES;

7. You’re done! Just exit mysql and have fun! Type:

exit

And that’s about it!

I hope this short guide on how to create a database, a user and granting privileges to that user on the created database has been useful!

Did you see my article about MySQL string Replace?