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/
