CrunchBang - Code, Design & GNU/Linux

Create MySQL User Accounts from the Command Line

I have my LAMP install process down to fine art, which is a good thing considering the amount of Linux installs I have been performing lately. Anyhow, just recently I have been experiencing some issues with creating MySQL user accounts. Normally, for ease of use, I would simply login to phpMyAdmin and create a new user account, but, for some unknown reason, this method has not been working; maybe there is a compatibility issue between the latest versions of MySQL Server and phpMyAdmin available from the Ubuntu repositories?

Anyway, to get around this little problem, I have been creating new MySQL user accounts via the command line. It is a fairly straightforward procedure, but like a lot of administration tasks, it is not something I do on a daily basis. With this in mind, I thought it would be a good idea to record the process for future reference.

Follow the steps below to create a new user MySQL account. Note, these instructions assume that you have already set-up Apache, MySQL and PHP.

1. Open a terminal and login to MySQL as root:

mysql --user="root" --password="your_root_password"

2. Create a new user and password with the following command:

CREATE USER 'new_username'@'localhost' IDENTIFIED BY 'password_for_new_username';

3. Assign privileges to the new user, enter the command:

GRANT ALL ON *.* TO 'new_username'@'localhost';

4. Exit MySQL interface, enter:

exit

Notes: The above instructions will create a new user account on "localhost" and grant the user all privileges. While this is safe for my development environment, you would obviously want to grant only the necessary permissions in a production environment.

For more developer information, including the MySQL Reference Manual, visit: http://dev.mysql.com/