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 user amarok 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 [email protected] 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 [email protected] ; 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>