How-To create a MySQL database and set privileges to a user

MySQL is a widely spread SQL database management system mainly used on LAMP (Linux/Apache/MySQL/PHP) projects.

In order to be able to use a database, one needs to create: a new database, give access permission to the database server to a database user and finally grant all right to that specific database to this user.

This tutorial will explain how to create a new database and give a user the appropriate grant permissions.

Here's an example of what I did recently to actually create a new database and user. Of course the database name, username, and password have been changed:

mysql -u root -p
(here I enter 'my_root_password' to get through the mysql prompt)

create database my_database;

GRANT ALL PRIVILEGES 
ON my_database.* 
TO 'my_user'@'localhost'
IDENTIFIED BY 'my_password' 
WITH GRANT OPTION;

All of this is pretty much straight from the manual, but hopefully one more example helps.