creating mysql users without pain

On my computer, I often install web-applications which use a mysql database. I prefer to run different applications under different database accounts. Till now I used boring manual manipulations to create accounts, but now I worked out a better way.

CREATE DATABASE database;
GRANT ALL ON database.* TO user@localhost IDENTIFIED BY 'password';

New user is automatically created during executing "GRANT". Testing access:

$ mysql -u user -p database

18 April 2006, update

In a new hosting, mysql_connect failed, and mysql_error() said "Client does not support authentication protocol requested by server. Consider upgrading MySQL client in..."

This can be fixed by

SET PASSWORD FOR user@localhost = OLD_PASSWORD('password');

Categories: