How to Create Database in MariaDB

In this MariaDB tutorial, we are going to learn about MariaDB Create Database. Here we will understand how to create a MariaDB Database and cover the following topics

• MariaDB Create Database
• How to Create Database And User in MariaDB
• Create Database From Command Line in MariaDB
• MariaDB Create Database Access Denied
• MariaDB Create Database From SQL File
• MariaDB Create Database And Table
• MariaDB Create Database Ubuntu
• MariaDB Create Database And User From Command Line

MariaDB Create Database

A database is a collection of data that has been arranged so that it can be readily accessed and controlled. To make it easier to access relevant information, you can organize data into tables, rows, and columns, as well as index it.

To create MariaDB Database, then you should type the following command.

CREATE DATABASE USA_book;

To access the list of all databases, you should type the following command.

SHOW DATABASES;

The created database i.e. USA_book can be seen in the list of all the databases.

Also, check: How To Check MariaDB Version

MariaDB Create Database and User

To create both MariaDB database and user, you need to follow the following steps

Step 1 – Create a database by typing the following command.

CREATE DATABASE USA_bookstore;

Here, USA_bookstore is the name of the database.

Step 2 – Create a new user and grant privileges to this user on the new database created.

GRANT ALL PRIVILEGES ON USA_bookstore.* TO 'Sophia'@localhost IDENTIFIED BY 'password2';

Here, Sophia is the user-created who has been granted permission for USA_bookstore.

Step 3 – Lastly, execute the following command in order to apply the changes.

FLUSH PRIVILEGES;

Also, read: How to create a user in MariaDB

MariaDB Create Database From Command Line

To create a MariaDB database via MySQL command-line, follow these steps:

Step 1 – Login to the MariaDB server using the root user and root password.

mysql -u root -p

And you need to enter the root password.

Step 2 – To see the list of all databases, type the following command.

SHOW DATABASE;

Step 3 – To create the database, type the following command.

CREATE DATABASE USA;

Here, the USA is the name of the database created.

Step 4 – To see the list of all databases created, type the following command.

SHOW DATABASES;

The database created is present in the list which verifies the successful creation of the database.

Read How to import CSV files in MariaDB

MariaDB Create Database Access Denied

Whenever accessing database shows an error, then you can do the following things to prevent or solve this error:

• Creating a new user if error corresponds to a particular user and grant all permission.
• Use OR REPLACE and IF NOT EXISTS, to prevent it to happen.

Create a New User and Grant All Permission

You will get an error particularly for a user, then you should create a new user and grant it all the permission using the following command.

GRANT ALL ON *.* to username@localhost IDENTIFIED BY 'password';

Replace username with user’s username and password with user’s password.

Note – You can also grant all permission to a specified database and can grant specific privileges (like select, update, delete) to a user.

OR REPLACE and IF NOT EXISTS

OR REPLACE – You can use this to replace the already existing database with the database if the name of the database is the same.

You can use this by typing the following command.

CREATE OR REPLACE DATABASE AUS_data;

IF NOT EXISTS – You can use this to create a database only and only if there is no database already present with the same name.

You can use this by typing the following command.

CREATE DATABASE IF NOT EXISTS AUS_data;

Read MariaDB Vs SQL Server

MariaDB Create Database From SQL File

For creating a MariaDB database from a MySQL file, you need to do the following things:

• Export MySQL Database
• Import MySQL Database to MariaDB Server and create database.

Export MySQL Database

For exporting the database, you can use the mysqldump command. After execution of the command, a backup will be created which can be easily moved. To start exporting, type the following command.

mysqldump -u username -p database_name > data-dump.sql

Here,

• username refers to the database user name.
• database_name must be replaced by the name of the database you want to export.
• data-dump.sql is the file that will be generated with all the database information.

The file can be seen in the root directory for your windows user.

Import MySQL Database to MariaDB Server and create a database

At first, we need to log in to the MariaDB server by entering the root password. To import MySQL file into the MariaDB Database, follow the below steps

Step 1 – After login, we need to create a MariaDB Database. For this, you need to type the following command.

CREATE DATABSE USA_universities;

Step 2 – To check whether the database has been created successfully, you need to type the following command.

SHOW DATABASES;

Step 3 – Now, we have a new database in which we can import the MySQL file. To do this, you need to type the following command.

mysql –u root –p USA_universities < data-dump.sql

Your MySQL file has been successfully imported into the MariaDB server.

Read How to Create Table in MariaDB

MariaDB Create Database And Table

To create both MariaDB database and user, you need to follow the following steps

Step 1 – Create a database by typing the following command.

CREATE DATABASE US_data;

Here, US_data is the name of the database.

Step 2 – To check whether the database i.e US_data created, type the following command.

SHOW DATABASES;

Step 3 – Before creating tables, you need to switch the database. For this, you need to type the following command.

USE US_data;

Step 4 – To create Tables, type the following command.

CREATE TABLE US_cityzen(
    ->id int,
    -> name varchar(20),
    -> age int,
    ->PRIMARY KEY(id));

Here, we created a table i.e. US_cityzen consisting of three columns(id, name, age) and id as the primary key(can’t be null).

Read How to Grant User Access to a MariaDB Database

MariaDB Create Database Ubuntu

To create a MariaDB database in Ubuntu, you need to follow these steps.

Step 1 – Login to the MariaDB server using the root user and root password.

mysql -u root -p

And you need to enter the root password.

Step 2 – To see the list of all databases, type the following command.

SHOW DATABASE;

Step 3 – To create the database, type the following command.

CREATE DATABASE Canada;

Here, Canada is the name of the database created.

Step 4 – To see the list of all databases created, type the following command.

SHOW DATABASES;

The database created is present in the list which verifies the successful creation of the database.

Read MariaDB query examples

MariaDB Create Database And User From Command Line

To create a MariaDB database and user via MySQL command-line, follow these steps:

Step 1 – Login to the MariaDB server using the root user and root password.

mysql -u root -p

And you need to enter the root password.

Step 2 – To see the list of all databases, type the following command.

SHOW DATABASE;

Step 3 – To create the database, type the following command.

CREATE DATABASE Australia;

Here, the USA is the name of the database created.

Step 4 – To see the list of all databases created, type the following command.

SHOW DATABASES;

The database created is present in the list which verifies the successful creation of the database.

Step 5 – Create a new user and grant privileges to this user on the new database created.

GRANT ALL PRIVILEGES ON Australia.* TO 'James'@localhost IDENTIFIED BY 'password5';

Here, James is the user-created who has been granted permission for Australia.

Step 6 – Lastly, execute the following command in order to apply the changes.

FLUSH PRIVILEGES;

You may like the following MariaDB tutorials:

• MariaDB LIKE Operator
• MariaDB query examples
• MariaDB varchar
• MariaDB Row_Number
• MariaDB Create Temporary Table From Select
• MariaDB Date Function with Examples

In this tutorial, we have learned about how to create MariaDB Databases. Additionally, we have covered the following topics

• MariaDB Create Database
• MariaDB Create Database And User
• MariaDB Create Database And User Command Line
• MariaDB Create Database Access Denied
• MariaDB Create Database UTF8
• MariaDB Create Database From SQL File
• MariaDB Create Database And Table
• MariaDB Create Database Ubuntu