MySQL database and user creation

$mysql -u root -p

Log in to MySQL, enter password.

select user from mysql.user;

List existing users.

create user 'username'@'localhost' identified by 'user_password';

Create user, setting their password. User is restricted to log in from the local machine.

create database database_name;

Create the database.

grant all privileges on database_name.* to 'username'@'localhost';

Grant rights to the user for the database.


  1. The create user command is never stored in the console history, I suppose for security, but it adds difficulty.

  2. The root database user is no longer accessible from a normal Linux user account. There is all sorts of “solutions” on the web for this, but mostly just added confusion. To resolve, create a new admin account with full privileges. Use this admin account instead of the root database account when logging in from a normal Linux user account:

$ sudo mysql -u root -p
[sudo] password for xxxx:  <this is the sudo enabled Linux account password>
Enter password:  <this is the root database account password>

mysql> create user 'admin'@'localhost' identified by 'admin_password';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to 'admin'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)