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>

Use Jetty to run Maven web applications in NetBeans

Jetty is a great web server. It is fast to start up and that makes it very useful during development. Jetty has a Maven plugin we can use in our Maven web application project. This way we can use Jetty to test our JSP files. We can change the JSP file and hit the Refresh button of our browser to see the result immediately. We can even compile a Java class file and Jetty will restart so we can test the result in our browser.
Let's see how we can do this in NetBeans. We go to File | New Project, select Maven Project and click the Next button.
In the next dialog window we select Maven Webapp Archetype and click the Next button.
Now we can fill in the values for our project and press the Finish button.
NetBeans creates a new project for us. To add Jetty Maven plugin we must open the pom.xml file for our project. In the build section we must add the following code:

 
  org.mortbay.jetty
  maven-jetty-plugin
  6.1.14
  
   5
  
 

At line 6 we define how often Jetty will scan the source directories to look for changes. Because we add this definition we can change JSP files and see the result in our web browser without restarting the server.
Okay, we have defined Jetty for our project, but how can we run Jetty in NetBeans? We go to File | Project Properties and select the Actions category. We must look for the action Run project and select it. In the Execute goals field we add jetty:run. This will start Jetty if we run our project. We can close the dialog window.
Now we go to Run | Run Project to start Jetty. In the Output window of NetBeans we see the following text:

Starting jetty 6.1.14 ...
2009-02-11 10:20:20.610::INFO:  jetty-6.1.14
2009-02-11 10:20:20.982::INFO:  No Transaction manager found - if your webapp requires one, please configure one.
2009-02-11 10:20:21.335::INFO:  Started SelectChannelConnector@0.0.0.0:8080
Started Jetty Server
Starting scanner at interval of 5 seconds.

At line 3 we see Started SelectChannelConnector@0.0.0.0:8080 this means Jetty is available at port 8080. We open a web browser and open http://localhost:8080/webappjetty/ and we see the following:
We go back to NetBeans and open the file index.jsp. We add the following to the file:

It works!

We switch back to our web browser and reload the page and we get: