Setting up a Database

With MariaDB installed, we can now create our first database. For this we’ll be performing the following steps:

1. Create the database which will store our SQL data.
2. Create a user account which will allow outside application access to the database.
3. Configure permissions to allow the user account access to the database.

Connect to MariaDB

Connect to MySQL using your username and password.

mysql -u root -p

Enter password: ********

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 344
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

Conventions

Select a name for your database. The name has to follow these conventions:

• Under 64 characters.
• Not contain / \ or . characters.
• Not contain characters that are not permitted in file names.
• Not end with the space character.

For larger environments its recommended that organizations come up with a database naming convention. This is especially important if running MariaDB for multiple databases.

Create Database

Create the new database, using the following command.

MariaDB [(none)]> create database my_new_database;
Query OK, 1 row affected (0.00 sec)

To verify your database has been successfully created, run: show databases;

MariaDB [(none)]> show databases;
+-----------------------+
| Database              |
+-----------------------+
| information_schema    |
| my_new_database       |
| mysql                 |
| performance_schema    |
+-----------------------+
6 rows in set (0.01 sec)

MariaDB [(none)]> 

Database User

Using one command, we’ll create a user account, specify the account’s password and assign it permission to our new database.

MariaDB [(none)]> GRANT ALL PRIVILEGES ON [database name].* TO '[username]'@'localhost' IDENTIFIED BY '[password]';

Reload the grant tables for the permission changes to take effect.

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

You have successfully created a new database and granted a single user account access to it.