Archive for May 31st, 2009

MySQL Command Line – Setting up a new database and user

Posted by shawson in mySQL on May 31, 2009

From the command line start up mySql;

mysql -u root -h localhost -p

you will be prompted to enter your root password.  Once you’re in you can go ahead and create the database;

CREATE DATABASE <data_base_name>;

BAMM- that’s your database done – you can see this by typing;

SHOW databases;

Next add a user and grant all privileges to that new database (and only that database);

GRANT ALL ON <data_base_name>
.* TO <user_name>@localhost IDENTIFIED BY '<password>';

Or, to be more specific (and secure!)

GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , ALTER ON <data_base_name> . * TO <user_name>@localhost IDENTIFIED BY '<password>';

The last line you need before you can use the account is;

FLUSH PRIVILEGES;

The PCTools.com website has a handy page for generating random passwords.

Note, the user created here will only have access when logging on from the local machine.  If you want a user which can logon from anywhere replace localhost with %.  Alternativly you can also specify specific ip addresses.

No Comments