Create your first instance and connect to it

Diese Seite ist noch nicht in deiner Sprache verfügbar. Englische Seite aufrufen

Prerequisites

You have a STACKIT customer account: Create a customer account
You have a STACKIT user account: Create a user account
You have a STACKIT project: Create a project

Prepare, order and create an instance

In this guide, you will create an instance and configure it. This guide shows the most common settings for a small developer environment. When following this guide, you will work with the STACKIT Portal. There are many other ways to manage instances. For a deeper understanding, please consult Create and manage instances for MariaDB.

Visit the STACKIT Portal.
On the sidebar, click on MariaDB.
On the bar on the top, click on Create MariaDB.

After completing the steps, you’ll see an overlay with three sections (General information, Plans and Custom parameters).

General information

Either leave the name for the instance as it is or insert a custom one.
On Instance version, select the MariaDB version you want to use. For new projects, we recommend to use the newest available version.

Plans

Leave the plan setting as it is (1 Node, 1 vCPU, 4GB RAM and 10GB Disk), unless you know in advance how much resources you’ll need. You can not change the plan afterwards.

Custom parameters

Leave the disk threshold settings as it is (80%).
Add your clients IPv4 address to the list of ACLs.

If you want to connect with the computer, with which you are browsing the Docs, use Cloudflare’s IP trace tool to determine your IPv4 address. Open your browser of choice and visit the Cloudflare Trace Tool. Copy the value behind ip. It should look like xxx.xxx.xxx.xxx. Now append /32 to it and add it as ACL entry. To do so, append it with a leading comma to the existing entries. For a deeper understanding of ACL entries, visit Create and manage instances for MariaDB.

After reviewing every setting, click on Order fee-based to create your new instance. You will receive a confirmation that your instance has been created. Your new instance will be added to the list of instances. After a few minutes, it will switch from Processing to Active.

Configure your instance

To make any changes to your instance, you need to select it. Please click on MariaDB in the sidebar, and then on your newly created instance. This displays the overview page of your instance.

Create credentials

In the sidebar, click on Credentials.
Click on Create credentials.
Copy the information from the dialog to a safe location.
Click on Close.

Connect to your new instance

For a newly created instance, connecting to the instance means connecting to the default database. To connect to it, you need a client software. In this guide we use the mycli command-line client. You can inform yourself on MariaDB Command-Line Client about this client. To learn more about MariaDB GUI clients, visit Graphical and enhanced clients on the official MariaDB website.

Follow the instructions of your operating system to install the mycli command-line client. After a successful installation, open a command line interpreter of your choice and connect to your database:

mycli <Connection String>

After your client has established a connection, you can execute the show databases command to verify that the connection has been established correctly. You need to paste the commands into your shell and execute them with pressing Enter. Now, start by entering the example commands:

show databases;

The answer should be:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| <Database Name>    |
+--------------------+

2 rows in set
Time: 0.028s
Time: 0.028s

Now your instance is set up and ready to use for your first application.

Define the structure, insert data and query it

To get familiar with MariaDB, you can insert and query some sample data. This mini tutorial uses two tables to show the strength of SQL.

At first, define the table structure:

CREATE TABLE movies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    year INT
);

CREATE TABLE ratings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    movie_id INT,
    user VARCHAR(100),
    stars INT CHECK (stars BETWEEN 1 AND 5),
    FOREIGN KEY (movie_id) REFERENCES movies(id)
);

The database should answer with a reply which is similar to:

Query OK, 0 rows affected
Time: 0.033s
Time: 0.033s

Query OK, 0 rows affected
Time: 0.033s
Time: 0.033s

Now, insert some sample data:

INSERT INTO movies (title, year) VALUES
    ('Inception', 2010),
    ('The Matrix', 1999),
    ('Interstellar', 2014);

INSERT INTO ratings (movie_id, user, stars) VALUES
    (1, 'Alice', 5),
    (1, 'Bob', 4),
    (2, 'Clara', 5),
    (2, 'David', 4),
    (3, 'Eve', 5);

The database should answer with a reply which is similar to:

Query OK, 3 rows affected
Time: 0.023s
Time: 0.023s

Query OK, 5 rows affected
Time: 0.021s
Time: 0.021s

We can use the database to compute the average rating for each movie:

SELECT m.title, AVG(r.stars) AS avg_rating
FROM movies m
JOIN ratings r ON m.id = r.movie_id
GROUP BY m.title;

You get the following output:

+--------------+------------+
| title        | avg_rating |
+--------------+------------+
| Inception    | 4.5000     |
| Interstellar | 5.0000     |
| The Matrix   | 4.5000     |
+--------------+------------+

3 rows in set
Time: 0.026s
Time: 0.026s

With this command, you instructed the database engine to join both existing tables and calculate the average ratings.

In this example, you executed some basic commands with the database. From here on, you can dig deeper with the How-tos.