DotNux – Unix/Linux Technical Mini Howto

Everytime I install a new PHP application, such as Joomla, drupal, php-nuke, zen-cart, oscommerce, etc., you have to install a new database.

If you have a SSH access to it, the following script will do a quick database creation job, with user name & password.

type ‘mysql’, and type the following SQL script.

mysql> create database [DATABASE_NAME];
Query OK, 1 row affected (0.06 sec)

mysql> create user '[USER_NAME]'@'localhost' identified by '[PASSWORD]';
Query OK, 0 rows affected (0.00 sec)


# SEE the comment below, regarding privilege

mysql> grant all privileges on [DATABASE_NAME].* to '[USER_NAME]'@'localhost';
Query OK, 0 rows affected (0.00 sec)


# Note
Now if you want to grant only limited privileges, like SELECT, INSERT, UPDATE, DELETE, then run the following:

mysql> grant SELECT,INSERT,UPDATE,DELETE,CREATE on [DATABASE_NAME].* to [USER_NAME]'@'localhost';
Query OK, 0 rows affected (0.00 sec)


You need ‘CREATE’, because, usually installers will try to create a database. You could also add ‘DROP’ so that the installer can delete any temporary tables that it has created.

and then finally, you need to flush privilege so that new permission can be in effect.

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


and, you can check the privileges to make sure it’s set correctly by doing this:

mysql> show grants for '[USER_NAME]'@'localhost';
+--------------------------------------------------------------------+
| Grants for [USER_NAME]@% |
+--------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO '[USER_NAME]'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)