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.

For the purpose of this tutorial, I will explain how to create a database and user for the music player Amarok. In order to index its music collection, Amarok quand use a mysql backend.
The requirement for this set up is to have access to a database. We are going to create a database called amarok which will be accessible from localhost to useramarok idetified by the password amarok....

Obviously, we need to to have a mysql server installed as well as amarok:

$ sudo apt-get install mysql-server amarok

On a default settings, mysql root user do not need a password to authenticate from localhost. In this case, ou can login as root on your mysql server using:

$ mysql -u root

If a password is required, use the extra switch -p:

$ mysql -u root -p
Enter password:

Now that you are logged in, we create a database:

mysql> create database amarokdb;
Query OK, 1 row affected (0.00 sec)

We allow user amarokuser to connect to the server from localhost using the password amarokpasswd:

mysql> grant usage on *.* to amarokuser@localhost identified by 'amarokpasswd';
Query OK, 0 rows affected (0.00 sec)

And finally we grant all privileges on the amarok database to this user:

mysql> grant all privileges on amarokdb.* to amarokuser@localhost ;
Query OK, 0 rows affected (0.00 sec)

And that's it. You can now check that you can connect to the MySQL server using this command:

$ mysql -u amarokuser -p'amarokpasswd' amarokdb
Your MySQL connection id is 12
Server version: 5.0.38-Ubuntu_0ubuntu1-log Ubuntu 7.04 distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>