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.

mysql -u root -p
password:
mysql>

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

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

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.

Now you need to create your database:

CREATE DATABASE 'database_name';

Now grant your MySQL user privileges on that database:

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

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

FLUSH 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.


*