MariaDB: Getting started

How to get started with MariaDB

👋 Welcome to the Stackhero documentation!

Stackhero offers a ready-to-use MariaDB cloud solution that provides a host of benefits, including:

  • Unlimited connections and transfers.
  • phpMyAdmin web UI included.
  • Effortless updates with just a click.
  • Optimal performance and robust security powered by a private and dedicated VM.

Save time and simplify your life: it only takes 5 minutes to try Stackhero's MariaDB cloud hosting solution!

MariaDB URLs

The simplest way to connect to your MariaDB service is by using the MySQL URL format, provided your driver supports it:

mysql://root:<ROOT_PASSWORD>@<XXXXXX>.stackhero-network.com:<PORT>/root?useSSL=true&requireSSL=true

For Ruby users, the MySQL URL differs slightly:

mysql2://root:<ROOT_PASSWORD>@<XXXXXX>.stackhero-network.com:<PORT>/root?reconnect=true&useSSL=true&requireSSL=true

Using MariaDB with PHP

Below are several examples using PHP to help you connect to MariaDB using different extensions. Remember that using the 'root' database is not recommended for production. Instead, consider creating a dedicated database and user.

Using MariaDB with PHP and MySQLi (object-oriented style)

<?php

$hostname = '<XXXXXX>.stackhero-network.com';
$port     = '<PORT>';
$user     = 'root';
$password = '<ROOT_PASSWORD>';
$database = 'root'; // It is not recommended to use the 'root' database. This is just for the example. The best practice is to create a dedicated database and user in phpMyAdmin and use them here.

$mysqli = mysqli_init();
$mysqliConnected = $mysqli->real_connect($hostname, $user, $password, $database, $port, NULL, MYSQLI_CLIENT_SSL);
if (!$mysqliConnected) {
  die('Connection Error: ' . $mysqli->connect_error);
}

echo 'Connection successful... ' . $mysqli->host_info . "\n";

$mysqli->close();

?>

Using MariaDB with PHP and MySQLi (procedural style)

<?php

$hostname = '<XXXXXX>.stackhero-network.com';
$port     = '<PORT>';
$user     = 'root';
$password = '<ROOT_PASSWORD>';
$database = 'root'; // It is not recommended to use the 'root' database. This is just for the example. The best practice is to create a dedicated database and user in phpMyAdmin and use them here.

$mysqli = mysqli_init();
$mysqliConnected = mysqli_real_connect($mysqli, $hostname, $user, $password, $database, $port, NULL, MYSQLI_CLIENT_SSL);
if (!$mysqliConnected) {
  die('Connection error: ' . mysqli_connect_error($mysqli));
}

echo 'Success: ' . mysqli_get_host_info($mysqli) . "\n";

mysqli_close($mysqli);

?>

Using MariaDB with PHP and PDO

<?php

$hostname = '<XXXXXX>.stackhero-network.com';
$port     = '<PORT>';
$user     = 'root';
$password = '<ROOT_PASSWORD>';
$database = 'root'; // It is not recommended to use the 'root' database. This is just for the example. The best practice is to create a dedicated database and user in phpMyAdmin and use them here.

$dsn = "mysql:host=$hostname;port=$port;dbname=$database";

$options = array(
  // If you encounter an error such as "Uncaught PDOException: PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages: error:0A000086:SSL routines::certificate verify failed", check whether the /etc/ssl/certs/ directory contains CA certificates.
  PDO::MYSQL_ATTR_SSL_CAPATH => '/etc/ssl/certs/',
  // PDO::MYSQL_ATTR_SSL_CA => 'isrgrootx1.pem',
  PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
);

$pdo = new PDO($dsn, $user, $password, $options);

$stm = $pdo->query('SELECT VERSION()');
$version = $stm->fetch();

echo 'You are connected to a database running version ' . $version[0] . "\n";

?>

Handle error "SSL operation failed with code 1"

If you receive the error:

Uncaught PDOException: PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages: error:0A000086:SSL routines::certificate verify failed

this is probably because the /etc/ssl/certs/ directory does not contain the CA certificates. If you have system access, you can install these certificates as follows:

  1. On Ubuntu, you can run:

    apt-get install ca-certificates

  2. On Alpine Linux, run:

    apk add ca-certificates

If you do not have direct access to the system running your PHP code, you can install the certificate manually:

  1. Download the certificate: https://letsencrypt.org/certs/isrgrootx1.pem
  2. Add the isrgrootx1.pem file to your PHP project files.
  3. Comment out the line with PDO::MYSQL_ATTR_SSL_CAPATH => '/etc/ssl/certs/'
  4. Uncomment the line with PDO::MYSQL_ATTR_SSL_CA => 'isrgrootx1.pem'

Resolving "Fatal error: Uncaught Error: Undefined constant PDO::MYSQL_ATTR_SSL_CAPATH"

If you see an error such as:

Fatal error: Uncaught Error: Undefined constant PDO::MYSQL_ATTR_SSL_CAPATH

or a similar error mentioning an undefined constant for PDO MySQL attributes, it indicates that your PDO install lacks MySQL support.

Solution for Ubuntu/Debian

Install the required PHP MySQL extension with:

sudo apt-get install php-mysql
Solution for Docker

If you are using Docker, ensure MySQL support is included during the build process. Consider adding the following to your Dockerfile:

RUN docker-php-ext-install pdo pdo_mysql

Using MariaDB with Symfony and Doctrine

Edit the .env file and define the DATABASE_URL variable like so:

DATABASE_URL="mysql://<USER>:<PASSWORD>@XXXXXX.stackhero-network.com:<PORT>/<DATABASE>"

Next, update the config/packages/doctrine.yaml file, setting the driver and options as follows:

doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'
        driver: 'pdo_mysql'
        options:
            # PDO::MYSQL_ATTR_SSL_CAPATH
            1010: '/etc/ssl/certs'
            # PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT
            1014: true

Handle error "SSL operation failed with code 1"

If you encounter the error:

Uncaught PDOException: PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages: error:0A000086:SSL routines::certificate verify failed

this likely means that the /etc/ssl/certs/ directory does not contain the CA certificates. You can resolve this by installing them:

  • On Ubuntu/Debian, run:

    sudo apt-get install ca-certificates

  • On Alpine Linux, run:

    apk add ca-certificates

If you cannot install the certificates system-wide, you can manually add them:

  1. Download the certificate: https://letsencrypt.org/certs/isrgrootx1.pem

  2. Place the isrgrootx1.pem file in your Symfony project.

  3. Then, update the config/packages/doctrine.yaml file to use the local certificate:

    doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'
        driver: 'pdo_mysql'
        options:
            # PDO::MYSQL_ATTR_SSL_CA
            1009: 'isrgrootx1.pem'
            # PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT
            1014: true

Using MariaDB with Laravel

To set up MariaDB with Laravel, edit the config/database.php file and update the mysql configuration as follows:

'mysql' => [
  'driver'   => 'mysql',
  'host'     => env('STACKHERO_MARIADB_HOST'),
  'port'     => env('STACKHERO_MARIADB_PORT'),
  'username' => env('STACKHERO_MARIADB_USER'),
  'password' => env('STACKHERO_MARIADB_PASSWORD'),
  'database' => env('STACKHERO_MARIADB_USER'),
  'charset'  => 'utf8mb4',
  'collation'=> 'utf8mb4_unicode_ci',
  'prefix'   => '',
  'prefix_indexes' => true,
  'strict'   => true,
  'engine'   => null,
  'sslmode'  => 'require',
  'options'  => extension_loaded('pdo_mysql')
    ? array_filter([
      // If you encounter SSL errors such as "Uncaught PDOException: PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages: error:0A000086:SSL routines::certificate verify failed", refer to the instructions above.
      PDO::MYSQL_ATTR_SSL_CAPATH => '/etc/ssl/certs/',
      // PDO::MYSQL_ATTR_SSL_CA => 'isrgrootx1.pem',
      PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
    ])
    : [],
],

Using MariaDB with PHP CodeIgniter

In your database.php file, insert the following configuration:

$db['default'] = array(
  'hostname' => getenv('STACKHERO_MARIADB_HOST'),
  'port'     => getenv('STACKHERO_MARIADB_PORT'),
  'username' => getenv('STACKHERO_MARIADB_USER'),
  'password' => getenv('STACKHERO_MARIADB_PASSWORD'),
  'database' => getenv('STACKHERO_MARIADB_USER'); // By convention, the database name matches the username.
  'dbdriver' => 'mysqli',
  'dbprefix' => '',
  'pconnect' => TRUE,
  'char_set' => 'utf8',
  'dbcollat' => 'utf8_general_ci',
  'encrypt'  => array() // Important: activate TLS encryption
);

Connecting MariaDB with PHP using environment variables

It is best practice to avoid hardcoding your credentials. Instead, consider using environment variables. You can retrieve the credentials as follows:

$hostname = getenv('STACKHERO_MARIADB_HOST');
$port     = getenv('STACKHERO_MARIADB_PORT');
$user     = getenv('STACKHERO_MARIADB_USER');
$password = getenv('STACKHERO_MARIADB_PASSWORD');
$database = getenv('STACKHERO_MARIADB_USER'); // By convention, the database name matches the username.

Using MariaDB with WordPress

Connecting WordPress to Stackhero for MariaDB is straightforward. Edit the wp-config.php file and configure your database settings as follows:

define('DB_HOST', '<XXXXXX>.stackhero-network.com');
define('DB_PORT', '<PORT>');
define('DB_NAME', 'root');
define('DB_USER', 'root');
define('DB_PASSWORD', '<yourPassword>');

// Enable TLS encryption (also known as SSL)
define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);

The important part here is the activation of TLS encryption (also known as SSL). Without it, the connection will not work.

Using MariaDB with Node.js

In this example, we use the mysql2 package with promise support. First, install it with:

npm install mysql2

Then, use the following code:

const mysql = require('mysql2/promise');

(async () => {
  const db = await mysql.createConnection({
    host: '<XXXXXX>.stackhero-network.com',
    port: '<PORT>',
    user: 'root',
    password: '<ROOT_PASSWORD>'
  });

  // Create the database 'stackherotest' if it does not already exist
  await db.query('CREATE DATABASE IF NOT EXISTS stackherotest');

  // Create the table 'users' if it does not exist yet
  await db.query(
    'CREATE TABLE IF NOT EXISTS `stackherotest`.`users` (' +
      '`userId` INT UNSIGNED NOT NULL,' +
      '`name` VARCHAR(128) NOT NULL,' +
      '`address` TEXT NOT NULL,' +
      '`email` VARCHAR(265) NOT NULL' +
    ') ENGINE = InnoDB;'
  );

  // Insert a fake user
  await db.query(
    'INSERT INTO `stackherotest`.`users` (`userId`, `name`, `address`, `email`) VALUES ?',
    [
      [
        Math.round(Math.random() * 100000), // Generate a fake userId
        'User name',                         // 'name' column
        'User address',                      // 'address' column
        'user@email.com'                     // 'email' column
      ]
    ]
  );

  // Count the number of rows in the 'users' table
  const [usersCount] = await db.query('SELECT COUNT(*) AS `cpt` FROM `stackherotest`.`users`');
  console.log(`There are now ${usersCount[0].cpt} entries in the table "users"`);

  // Close the connection to MariaDB
  await db.end();

})().catch(error => {
  console.error('');
  console.error('🐞 An error occurred!');
  console.error(error);
  process.exit(1);
});

Using MariaDB with Node.js/NestJS/TypeORM

When connecting from Node.js, NestJS, or TypeORM, include the ssl option as shown:

TypeOrmModule.forRoot({
  type: 'mysql',
  host: '<XXXXXX>.stackhero-network.com',
  port: '<PORT>',
  username: 'root',
  password: '<ROOT_PASSWORD>',
  database: 'root',
  entities: [],
  synchronize: true,
  ssl: {}
});

Using MariaDB with Prisma

To connect with Prisma, add the sslaccept=strict option to ensure SSL encryption is enabled. For example, using the user "root" connecting to the database "root":

datasource db {
  provider = "mysql"
  url = "mysql://root:<ROOT_PASSWORD>@<XXXXXX>.stackhero-network.com:<PORT>/root?sslaccept=strict"
}

Using MariaDB with Django

If you are yet to install the mysqlclient module (used to connect to MariaDB), run:

pip install mysqlclient

If you encounter the error Exception: Can not find valid pkg-config name when installing mysqlclient, install the libmysqlclient package. On Ubuntu/Debian, you can run:

apt-get update && apt-get install --no-install-recommends -y libmysqlclient-dev

Initially, you may store your credentials directly in the settings.py file for testing as shown below. Note that this approach is not secure for production.

DATABASES = {
  'default': {
    'ENGINE': 'django.db.backends.mysql',
    'HOST': '<XXXXXX>.stackhero-network.com',
    'PORT': '<PORT>',
    'OPTIONS': {
      'ssl_mode': 'REQUIRED',
    },
    'NAME': 'root',
    'USER': 'root',
    'PASSWORD': '<ROOT_PASSWORD>'
  }
}

Be careful: this example is not recommended for production and is for testing purposes only!

Once the connection works, it is best to use environment variables to store your credentials. For example, using django-environ, first install it:

pip install django-environ

Then update settings.py as follows:

import environ
env = environ.Env()
environ.Env.read_env()

DATABASES = {
  'default': {
    'ENGINE': 'django.db.backends.mysql',
    'HOST': env('STACKHERO_MARIADB_HOST'),
    'PORT': env('STACKHERO_MARIADB_PORT'),
    'OPTIONS': {
      'ssl_mode': 'REQUIRED',
    },
    'NAME': 'root',
    'USER': 'root',
    'PASSWORD': env('STACKHERO_MARIADB_ROOT_PASSWORD')
  }
}

Create or update the .env file (in the same directory as settings.py) with the following:

STACKHERO_MARIADB_HOST=<XXXXXX>.stackhero-network.com
STACKHERO_MARIADB_PORT=<PORT>
STACKHERO_MARIADB_ROOT_PASSWORD=<ROOT_PASSWORD>

Finally, add .env to your .gitignore to prevent committing sensitive information:

echo ".env" >> .gitignore

Connecting MariaDB with Java/Spring

To connect your Spring application, set the environment variable SPRING_DATASOURCE_URL with your database URL, prefixed with jdbc:. For example:

SPRING_DATASOURCE_URL=jdbc:mysql://root:<ROOT_PASSWORD>@<XXXXXX>.stackhero-network.com:<PORT>/root?useSSL=true&requireSSL=true

Connecting MariaDB with Groovy/Grails

The following example shows how to configure your Grails application to connect to MariaDB:

dataSource {
  pooled = true
  driverClassName = "com.mysql.cj.jdbc.Driver"
  dialect = org.hibernate.dialect.MySQL8Dialect
  // SSL-specific properties
  properties {
    useSSL = true
    requireSSL = true
    verifyServerCertificate = true
    sslMode = "REQUIRED"
  }
}

environments {
  production {
    dataSource {
      dbCreate = "none"
      url = "jdbc:mysql://" + System.env.STACKHERO_MYSQL_HOST + ":" + System.env.STACKHERO_MYSQL_PORT + "/root?useSSL=true&requireSSL=true&verifyServerCertificate=true&sslMode=required" // Replace '/root' with your intended database name.
      username = "root" // It is advisable to create a dedicated user instead of using 'root'.
      password = System.env.STACKHERO_MYSQL_ROOT_PASSWORD // Consider creating a dedicated user.
      properties {
        maxActive = 50
        minEvictableIdleTimeMillis = 1800000
        timeBetweenEvictionRunsMillis = 1800000
        numTestsPerEvictionRun = 3
        testOnBorrow = true
        testWhileIdle = true
        testOnReturn = false
        validationQuery = "SELECT 1"
      }
    }
  }
}

Creating a user on MariaDB using phpMyAdmin

It is good practice to create a dedicated user for your application rather than using the 'root' user. The easiest way to do this is via phpMyAdmin:

  1. In phpMyAdmin, click on User accounts at the top.
  2. Click on Add user account.
  3. Complete the user creation form:
    • Choose an account name (typically your application name).
    • Click on Generate password for a secure password, then copy it.
    • Check the Create database with same name and grant all privileges option.

After submitting, a new user will be created along with a database that shares the same name as the username.

Differences between MariaDB and MySQL

MariaDB is an independent fork created by the open source community after Oracle acquired MySQL in 2010. For most purposes, MariaDB and MySQL are very similar.

Other articles for MariaDB