In previous post we explained How To “Install MySQL on CentOS 6” so the next step is to Create MySQL Database, Create MySQL User and Grant Privileges to the desired MySQL user!

The Syntax to Create MySQL Database is quite simple and straightforward! MySQL as the “world’s most popular open source database” is well documented and you can find all of the documentation published on the MySQL homepage.

Let’s start our Create MySQL Database guide!

1. Connect to MySQL

First step to Create MySQL Database is connecting to MySQL instance with root (or other privileged) MySQL user and password:

[root@foo1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.1.69 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>

2. Create MySQL database

Next step is to Create MySQL Database. I will create one with UTF-8 encoding format and call it “firstdb“:

mysql> CREATE DATABASE `firstdb` /*!40100 DEFAULT CHARACTER SET utf8 */;
 Query OK, 1 row affected (0.00 sec)

mysql> show databases;
 +------------------------------------+
 | Database                           |
 +------------------------------------+
 | information_schema                 |
 | firstdb                            |
 | mysql                              |
 | test                               |
 +------------------------------------+
 4 rows in set (0.00 sec)

TIP: Here is a little trick I use, if i forget the Create MySQL Database syntax. Run “show create database test” command and you will see the command the database “test” was created with!

mysql> show create database test;
 +-----------------+------------------------------------------------------------------------+
 | Database        | Create Database
 +-----------------+------------------------------------------------------------------------+
 | test            | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */
 +-----------------+------------------------------------------------------------------------+
 1 row in set (0.00 sec)

3. Create MySQL user

After successfull creation of the MySQL database we usually want to create a new MySQL user. I will create a user called “firstuser” with the password “HardPass“:

mysql> CREATE USER 'firstuser'@'localhost' IDENTIFIED BY 'HardPass';
Query OK, 0 rows affected (0.00 sec)

Do not forget the semicolon (;) at the end of MySQL commands!

4. Grant privileges

Now that the database “firstdb” and user “firstuser” are created we need to Grant Privileges on “firstdb” database to “firstuser“:

mysql> GRANT ALL PRIVILEGES ON firstdb.* TO 'firstuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

5. Flush privileges

For the MySQL server to reload the grant tables (permissions), we need to first Flush Privileges and then quit:

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> quit

6. Test permissions

All we want to do now is to check is the newly created MySQL user can login to MySQL instance and see the newly created database:

[root@foo1 ~]# mysql -u firstuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 5.1.69 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show databases;
 +------------------------------------+
 | Database                           |
 +------------------------------------+
 | information_schema                 |
 | firstdb                            |
 | test                               |
 +------------------------------------+