How to Create MySQL Databases, User Accounts and Grant Privileges
MySQL is a powerful open-source relational database system that can be used to manage databases of various sizes.
In this tutorial we are going to look at how to create databases, create users and grant those users database permissions in MySQL. The article assumes that you have already got MySQL installed on your server and it has been configured.
Log into MySQL Server
Firstly you will need to log into your MySQL server as the root user.
mysql -u root -p
You will then be asked to enter the password you created for the root user account during the MySQL installation process.
Create a MySQL Database
From the MySQL console, we are going to create a database. This following command consists of two main parts; the name of the database and its default character encoding.
CREATE DATABASE newdatabase DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Before entering the above command replace
newdatabase with the name you wish to call your database.
Create a MySQL User and Grant Permissions
Now we are going to create a new user at localhost, set a password and grant it permissions to access the database. You can change localhost to whatever your particular set-up is though by default localhost should work for most cases.
GRANT ALL ON newdatabase.* TO 'dbuser'@'localhost' IDENTIFIED BY 'password';
Before running the above command you should change
newdatabase to the name of your previously created database,
dbuser should be changed to a desired one and the same should be done for the
Pro-tip - You should use a strong password generator like this one to create a random password and store it in a secure location. The username shouldn't be something super generic like “admin” either.
In the above command, we created a user and granted it all permissions on the database. “All" means that all of the following functions are granted:
CREATE- can create databases and tables
DROP- can drop database tables
DELETE- can delete records (rows) from a table
INSERT- can insert records (row) into tables
SELECT- can read the database
UPDATE- can update rows
If there are cases where you don't want to grant users all permissions, you can add permissions individually separated by commas.
GRANT CREATE, SELECT, UPDATE ON newdatabase.* TO 'dbuser'@'localhost' IDENTIFIED BY 'password';
Flush MySQL Privileges
Next, we need to flush MySQL privileges to update the MySQL server on the changes we just made.
Now we just need to leave the MySQL console by typing the following command:
You have successfully set-up a new database and user and granted necessary privileges for that user to manipulate the contents of the database.