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