Knowledgebase

Creating database and user in mysql:
(change database_name, user_name and user_password as required).
Prerequisites: you need to know password of mysql root account,
and have permissions to execute mysqladmin and mysql programs.

1. Creating database:
> mysqladmin -u root -p create database_name

2. Connecting to mysql
> mysql -u root -p mysql

3. Creating user and setting password (change localhost to server name
if you want to connect from other servers) (in mysql):
mysql> insert into user (host, user, password)
values ('localhost', 'user_name', password('user_password'));

4. Giving permission for the new user to use the new database (in mysql):
mysql> insert into db (HOST, db, user, select_priv, insert_priv, update_priv, delete_priv, create_priv, drop_priv, index_priv, alter_priv, Create_tmp_table_priv, Lock_tables_priv)
values ('localhost', 'database_name', 'user_name', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

To allow connections from other hosts also add entry to host table:
insert into host (host, db, select_priv, insert_priv, update_priv, delete_priv, create_priv,
drop_priv, grant_priv, references_priv, index_priv, alter_priv) values
('host_name', 'db_name', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y');


5. Reloading permissions (in UNIX shell)
> mysqladmin -u root -p reload

That's it. You can confirm that the database and user were created by loging in:
> mysql -u user_name -p database_name


to reset root password: stop mysql and start using mysql_safe:
/mysqld_safe --skip-grant-tables &