How to Create a Database in MariaDB

MariaDB is a free open-source software for managing relational databases. It’s guaranteed to stay that way, too, since it’s owned by a non-profit organization, MariaDB Foundation, which relies on community sponsorships. That’s precisely why it was created as a fork of MySQL; the original developers feared what would happen to MySQL after Oracle Corporation acquired it in 2009. For that reason, MariaDB is compatible with it, and uses the same commands and APIs, making it a full-fledged alternative for creating databases in MySQL. With that said, let’s show how to create a database in MariaDB.

1. Prepare to access your server

Before we start, we’d like to point out there’s a beginner-friendly way. It includes using a compatible GUI (Graphical User Interface)-based software such as phpMyAdmin and making a database with it. If that’s not your forte, before signing in to MariaDB and using any commands, you must access the command-line interface like this:

MariaDB 10.4 and earlier

If you are creating a database for local testing, you must install either:

• MariaDB packages for Windows or Linux (https://downloads.mariadb.org/mariadb/+releases/)
• MariaDB repositories (https://downloads.mariadb.org/mariadb/repositories/) for Linux distributions
• Homebrew, a free package manager for macOS

Afterward, launch Command Prompt or PowerShell (with “PowerShell Cmdlets”, a third-party tool we aren’t affiliated with) on Windows. On Mac or Linux, open your Terminal.

What about web hosting?

If you’re using a remote web hosting server, you’ll need to use SSH (Secure Shell) to access it. For this to happen, you must generate a private and public key by logging in to your web hosting cPanel, and going to SSH Access under “Security”. After you authorize access, a new option, Terminal, appears in the cPanel. Alternatively, you can use the SSH application preinstalled on Mac and Linux distributions. For Windows, you need an SSH client such as PuTTY, a popular free option.

MariaDB 10.5 and later

If you’re using MariaDB version 10.5 and later, the instructions above apply but there’s a bonus option too – MariaDB is natively supported by cPanel. So, on web hosting providers that use it, you can avoid using SSH or any installations, and access the command-line interface immediately.

2. Connect to your MariaDB server

Now that the command-line interface is open, connecting to the MariaDB server works in this fashion:

Local

Type mysql before pressing Enter/Return to connect with the default credentials. You can also use the default defaults like the method below: mysql -h localhost -u username. Replace “username” with ODBC on Windows or your login user name on Unix/Debian-based systems.

Web

Web hosting providers assign you a set of credentials for connecting to the server. So, either check your e-mail, cPanel, or contact Customer Service. After obtaining them, enter them like this: mysql -h hostname -u username -ppassword --ssl

• -h represents a host. Enter the hostname (e.g., host.website-name.com) or IP address you were given.
• -u represents a username you were assigned.
• -p represents a password. You can enter your password right away (like-pyour-password with no space in-between) but this isn’t secure. Our example prompts you to enter a password.
• –ssl enables TLS (Transport Layer Security) encryption. Since there are a lot of variables, follow your web hosting provider’s instructions for specifics. You can also type --skip-ssl, but we don’t recommend it.

3. Create a database using MariaDB

The example above connects you to the server, but no database in particular. This is great, whether some databases exist, like with WordPress already installed, or there are none at all. You can now type this to create a database via MariaDB: CREATE DATABASE db_name.

Note. This is case insensitive (i.e., you can use “create database” instead) and will create a database with default server collation and character set without a comment.

There’s a database under that name already. What to do?

If you fear this will happen for the next one, use this line to get a warning: CREATE DATABASE IF NOT EXISTS db_name; With that said, if it’s important, back the database up first. Then, do this based on the MariaDB version:

• MariaDB 10.1.2 and older: Type DROP DATABASE IF EXISTS db_name; CREATE DATABASE db_name;
• MariaDB 10.1.3 and newer: Enter CREATE OR REPLACE DATABASE db_name

Customizing a database

If you’re building a multi-lingual database, and you forgot to add a character set and collation when creating the server, don’t fret. You can fix it in one of these ways:

• Change it on server level: SET character_set_server = 'character_set'; then press Return or Enter. Type this: SET collation_server = 'collation_name';
• Add it when creating a new database: CREATE DATABASE db_name CHARACTER SET = 'character_set' COLLATE = collation_name';
• Editing an already created database: ALTER DATABASE db_name CHARACTER SET = 'character_set' COLLATE = collation_name';
• Adding a comment (MariaDB 10.5.0 and later): CREATE DATABASE db_name COMMENT 'The description of your database';

Note. Replace “db_name”, “collation_name”, and “character_set” with actual names, e.g., “danish_citizens”, “utf8”, and “utf8_danish_ci” respectively.

4. Create your first table in MariaDB

Congratulations, you created a database via MariaDB. Unfortunately, it’s currently empty, so you should create your first table inside MariaDB by typing:

CREATE TABLE table_name (column1 INT, column2 VARCHAR) CHARACTER SET 'character_set' COLLATE 'collation_name';

Replace “column1 ” and ”column2 ” with the names of columns.

Type INT if the column contains numbers and VARCHAR for characters. Optionally, add a maximum number of characters allowed in that column, e.g., “VARCHAR(255)”.

Add clauses to the table

If your table needs to contain multiple columns, each with its collations and/or character sets, that’s possible too. Here’s how that works in MariaDB:

CREATE TABLE table_name (column1 VARCHAR(255) COLLATE 'collation_name', column2 VARCHAR(255) CHARACTER SET 'character_set');

Note. You can also use both collation and character set in one table column. If you don’t enter any clauses in a column, database defaults are used instead.

Alter an existing table

If you make a mistake but the table is created, use:

ALTER TABLE table_name CONVERT TO CHARACTER SET character_set COLLATE collation_name;

This will change the data type, which can lead to errors. For example, ASCII needs 1 byte per character, while UTF8 needs 3 bytes per character. We suggest altering the column individually:

• Text: ALTER TABLE table_name MODIFY ascii_text_column TEXT CHARACTER SET utf8;
• Numbers: ALTER TABLE table_name MODIFY ascii_number_column VARCHAR(number) CHARACTER SET utf8;