MySQL: Create a new database and “grant all” to a specific user

MySQL LogoTime and time again, I find myself looking up the “grant all” syntax after creating a new database in MySQL with mysqladmin. Since getting into the fold of using RubyOnRails, many sample databases will be created as I play along with the examples in my Agile book.

Currently, MySQL version is: 5.0.33-log FreeBSD port: mysql-server-5.0.33

Create a database with mysqladmin:
swinful@thinkpad> mysqladmin -u root -v create swinful_db

Create a database from mysql prompt as root:
swinful@thinkpad> mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.0.33-log FreeBSD port: mysql-server-5.0.33

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

mysql> create database swinful_db;
Query OK, 1 row affected (0.03 sec)

Grant all permissions to user swinful on the new db:
mysql> grant all on swinful_db.* to ‘swinful’@localhost identified by ‘swinfuls_password’;
Query OK, 0 rows affected (0.05 sec)

As you have noticed, the root password for MySQL is null. Since I am the only one using my laptop, this does not bother me. But, it is good habit to always use passwords.

Change MySQL root password with mysqladmin:
swinful@thinkpad> mysqladmin -u root password “roots_newpassword”

Now, if I try to connect as before it will fail:

swinful@thinkpad> mysql -u root
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

Interested to know if mysqladmin with let me make root’s password null again.

swinful@thinkpad> mysqladmin -u root password
mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘root’@’localhost’ (using password: NO)’
-=~=- swinful@thinkpad> mysqladmin -u root password “”
mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘root’@’localhost’ (using password: NO)’
-=~=- swinful@thinkpad> mysqladmin -u root password NULL
mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘root’@’localhost’ (using password: NO)’

Maybe I need to specify the root password:)

swinful@thinkpad> mysqladmin -u root -p password NULL
Enter password:

Ok, now the root password is “NULL”. Below is what I originally intended.

swinful@thinkpad> mysqladmin -u root -p password “”
Enter password:

Now I won’t break my Rails Depot app:)

mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.0.33-log FreeBSD port: mysql-server-5.0.33

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

Change root password from mysql prompt:

mysql> update user set password=PASSWORD(“roots_otherpassword”) where USER=”root”;
ERROR 1046 (3D000): No database selected

I need to be in the “mysql” database first.

mysql> use mysql
Database changed
mysql> update user set password=PASSWORD(“roots_otherpassword”) where USER=”root”;
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2 Changed: 2 Warnings: 0

And, flush the priveleges

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

Set the password back to null

mysql> update user set password=PASSWORD(“roots_otherpassword”) where USER=””;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0

And Test:

mysql> update user set password=PASSWORD(“”) where USER=”root”;
ERROR 1046 (3D000): No database selected
mysql> use master
ERROR 1049 (42000): Unknown database ‘master’

Opps, “use master” is for sybase:)
mysql> use mysql
Database changed
mysql> update user set password=PASSWORD(“”) where USER=”root”;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> quit
Bye

Advertisements
This entry was posted in MySQL. Bookmark the permalink.

18 Responses to MySQL: Create a new database and “grant all” to a specific user

  1. Thanks. Your comments between your code really helped me.

  2. swinful says:

    Patel, I am glad to be of assistance to you.

  3. Sebastien says:

    thanks for the info. I’m a newby and this definitivelly helps… The books tells you how to build a table and so one but they don’t give you these primary details!

  4. Seema Dave says:

    Thanks for nice information for MySQL.It is working. Great and please keep it up.

  5. What's wrong with me! says:

    Why it’s giving me:
    ERROR 1046 (3D000): No database selected
    when I’m selecting premissions (grant all)?

    • What's wrong with me! says:

      Now I’m fine. It was some kind of bug according to my Google session. ‘use mysql’ helped. Thanks.

  6. swinful says:

    :
    June 19, 2010 at 5:43 PM, What’s wrong with me! says:

    ERROR 1046 (3D000): No database selected

    Please post the full command-line executed.
    Thanks,
    swinful

  7. abajan says:

    Hi thanks, swinful. I had recently forgotten my MySQL root password but was able to use a resource other than this blog to reset it to nothing. However, not wanting to have no password set, on attempting to set up a fresh one I got a message basically stating that the command could not be executed with –skip-grant-tables in use (or something along those lines). To cut a long story short, the information contained in your blog entry above helped to resolve my situation.

    I’m using MySQL 5.1.36 (installed as part of WAMPSERVER 2.0)

  8. DV says:

    Was really helpful, Really nice for lookup.

    Thanks

  9. Pingback: access denied to view database (no replies) | Coders & Admins

  10. Anonymous says:

    Thanks buddy. I am new to the MySQL, and it helped me for sure

  11. johnlouisjordan says:

    Hi I have set up a new database as im moving a wordpress to a new host. I have imported the old DB all fine. Went to my url and it shows a Error establishing a database connection. I have try to grant all privileges but it is showing Access denied error please help. (Im not that good with database!)

  12. The reason is. After researching the subject of drinking water quality and of home water filters and purifiers, he set up a website where he
    proposes the results of his inquiry for all to consider, to reach independently their conclusions.
    Just because a food is labeled organic, it does not mean that
    it does not contain bacteria.

  13. Very nice article, totally what I wanted to find.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s