Debian/Ubuntu Tips and Tricks


Debian/Ubuntu Tips and Tricks

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

Posted by chantra on May 21st, 2007

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

10 Responses to “How-To create a MySQL database and set privileges to a user”

  1. Right here is the right web site for anyone who would like to understand this topic.
    You understand a whole lot its almost tough to argue with you (not that I personally would
    want to…HaHa). You certainly put a new spin on a topic which has
    been discussed for decades. Great stuff, just excellent!

  2. phpMyAdmin INSTALLATION

    [root@sever Desktop]# download phpmyadmin version "phpMyAdmin-" or "phpMyAdmin-4.1.2-all-languages"

    [root@server html]# cp -rvf /root/Desktop/phpMyAdmin-3.5.1-english.tar.gz /var/www/html/
    {COPY phpMyAdmin.tar in /var/www/html}

    [root@server html]# cd /var/www/html

    [root@server html]# tar -xvf phpMyAdmin-3.5.1-english.tar.gz

    [root@server html]# rm -rvf phpMyAdmin-3.5.1-english.tar.gz

    [root@server html]# mv phpMyAdmin-3.5.1-english/ phpmyadmin

    [root@server html]# cd phpmyadmin/

    [root@server phpmyadmin]# cp

    [root@server phpmyadmin]# vim
    Do LINE NO.29 $cfg['Servers'][$i]['auth_type'] = 'http';

    [root@server phpmyadmin]# vim /etc/httpd/conf.d/phpmyadmin.conf

    Alias /phpmyadmin /var/www/html/phpmyadmin.conf

    Options Indexes
    Order allow,deny
    Allow from localhost

    My java version in my centos 6.4 64bit
    java 1.7.0_25

    mysql version 5.1.66 in my centos 6.4 64bit

    when i try to open the localhost/phpMyAdmin page i have the directory tree and not the welcome page…. also if i go to page localhost/phpMyAdmin/index.php i got a page with php and html code.. the code of index.php..
    do you have any suggestion for me? i miss any package?

    sir first I was try to install yum, and i had download latest epel rpm and i had install epel
    but after install epel when i install phpmyadmin by yum "yum install php phpmyadmin -y
    then i get error,
    package is not availabe, and some erorr caused of epel

    I have no internet where i update any repositry,
    I want to configure phpmyadmin withour internet, so i am try to install phpmyadmin by tar.gz file.
    SIr Please assist me!
    please resolve my problem

    Thank you very much

  3. I just want to say that I have used this guide numerous times.

    Every time I set up a new database I always forget how to do these simple steps so every time I come back here to find what I need.

    I think I have also used the "I forgot my root password" tutorial more than once too.

    Thank you for this, I really appreciate it!

  4. I just created my login page using php and it is running fine, but now I would like to have users that log in to view database that I have Created. Any body to help?

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>