Create a MySQL Database, Username, Password, and Privileges from the command line

Posted by Doug on August 30, 2011, in Support | 13 Comments

Step 1: Login to MySQL ( you will need an account )

user@server:~$ mysql -u mysql_user -p
Enter password:

Step 2: Create the Database

mysql > create database db_name;

Step 3: Verify that it’s there

mysql > show databases;

Step 4: Create the User

mysql > create user db_user;

Step 5: Grant privileges while assigning the password

mysql > grant all on db_name.* to 'db_user'@'localhost' identified by 'db_password';

*Note: The localhost field usually doesn’t have to be edited, but you can set it to the specific address.

The above example grants all privileges, obviously. But you will likely want to limit privileges under many circumstances. These parameters include select, insert, and delete.
Choose all that apply and separate by comma, thusly:

mysql > grant select, insert, delete on db_name.* to 'db_user'@'localhost' identified by 'db_password';

How to Change the MySQL root Password in Linux or OSX via Command Line

If you have forgot the MySQL root password, can’t remember or want to break in….. you can reset the mysql database password from the command line in either Linux or OS X as long as you know the root user password of the box you are on:

Stop MySQL

1 2 3

sudo /usr/local/mysql/support-files/mysql.server stop

Start it in safe mode:

1 2 3

sudo mysqld_safe --skip-grant-tables

This will be an ongoing command until the process is finished so open anothershell/terminal window, log in without a password:

1 2 3

mysql -u root

1 2 3

UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';

Change the lowercase password to what you want – with single quotes.

1 2 3

FLUSH PRIVILEGES;

1 2 3

\q

Start MySQL

1 2 3

sudo /usr/local/mysql/support-files/mysql.server start