Creating MySQL Database and Assigning Permissions

I don’t work a whole lot with MySQL so it is easy for me to forget the syntax of the commands I need to use when setting up a server that needs a MySQL database or when maintaining a database. I know I will be using this article in the future, I hope it can be of good use to you. This article outlines on a Linux server how to create a MySQL database and user and grant all privileges on that database to the new user.


Log Into MySQL as Root user

The first thing you will need to do is login to MySQL as the root user or a user who has the ability to create databases and grant permissions.


Create Database User

Once logged into MySQL as root you will want to create a user that you want to own your soon to be created database.

Note if you are not familiar with MySQL syntax the “;” at the end of the line is required as it tells MySQL that is the end of your command.


Create the Database

Now you need to create your database:


Grant Permissions

Now grant your MySQL user privileges on that database:

The first part of that command is fairly straight forward, your granting all privileges on a database, but what you should note is the ‘database_name’.*  more specifically the “.*” that is telling MySQL that the privileges belong to that database and all the tables held within that database, with that information you can drill down to grant access to an individual table inside the database that a user has access to, but that is beyond the scope of this post.


The last step you will need to do to make all of this go into effect is flush the MySQL privileges.

Now you can exit out of MySQL and your new user and database are ready to go.


Be the first to comment

Leave a Reply

Your email address will not be published.