MySQL: Getting started

How to get started with MySQL

👋 Welcome to the Stackhero documentation!

Stackhero offers a ready-to-use MySQL 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 MySQL cloud hosting solution!

MySQL URLs

The simplest way to connect to your MySQL service is to use the MySQL URL format if your driver supports it:

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

For Ruby users, the MySQL URL format is slightly different:

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

Using MySQL with PHP

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

<?php

$hostname = '<XXXXXX>.stackhero-network.com';
$port = '<PORT>';
$user = 'root';
$password = '<ROOT_PASSWORD>';
$database = 'root'; // We do not recommend using the "root" database. This example is provided for demonstration purposes only. 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 MySQL with PHP and MySQLi (procedural style)

<?php

$hostname = '<XXXXXX>.stackhero-network.com';
$port = '<PORT>';
$user = 'root';
$password = '<ROOT_PASSWORD>';
$database = 'root'; // We do not recommend using the "root" database. This example is for demonstration purposes only. It is best practice 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 MySQL with PHP and PDO

<?php

$hostname = '<XXXXXX>.stackhero-network.com';
$port = '<PORT>';
$user = 'root';
$password = '<ROOT_PASSWORD>';
$database = 'root'; // We do not recommend using the "root" database. This is for demonstration purposes only. 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 like "Uncaught PDOException: PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages: error:0A000086:SSL routines::certificate verify failed",
  // ensure that the /etc/ssl/certs/ directory contains CA certificates.
  // See below for more information.
  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 encounter the error:

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

this is likely because the /etc/ssl/certs/ directory does not contain CA certificates.

If you have access to the system running your PHP code, you can install these certificates as follows:

  1. On Ubuntu/Debian, run sudo 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 to your computer: https://letsencrypt.org/certs/isrgrootx1.pem
  2. Add the isrgrootx1.pem file to your PHP project files.
  3. Comment out the line PDO::MYSQL_ATTR_SSL_CAPATH => '/etc/ssl/certs/'
  4. Uncomment the line PDO::MYSQL_ATTR_SSL_CA => 'isrgrootx1.pem'

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

If you encounter the error:

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

or a similar message such as:

Fatal error: Uncaught Error: Undefined constant PDO::MYSQL_*

this indicates that PDO was installed without MySQL support.

Solution for Ubuntu/Debian

To resolve this on Ubuntu or Debian-based systems, install the required PHP MySQL extension by running the following command:

sudo apt-get install php-mysql
Solution for Docker

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

RUN docker-php-ext-install pdo pdo_mysql

Using MySQL with Symfony and Doctrine

Edit the .env file and define the DATABASE_URL variable as follows:

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

Then, edit the config/packages/doctrine.yaml file and set 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 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 likely because the /etc/ssl/certs/ directory does not contain CA certificates.

If you have access to the system running your PHP code, you can install these certificates as follows:

  1. On Ubuntu/Debian, run sudo apt-get install ca-certificates
  2. On Alpine Linux, run apk add ca-certificates

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

  1. Download the certificate to your computer: https://letsencrypt.org/certs/isrgrootx1.pem
  2. Add the isrgrootx1.pem file to your Symfony project.
  3. Edit the config/packages/doctrine.yaml file and set the driver and options as follows:
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 MySQL with Laravel

Edit the file config/database.php and replace the mysql configuration with the following settings:

'mysql' => [
  'driver' => 'mysql',
  'host' => env('STACKHERO_MYSQL_HOST'),
  'port' => env('STACKHERO_MYSQL_PORT'),
  'username' => env('STACKHERO_MYSQL_USER'),
  'password' => env('STACKHERO_MYSQL_PASSWORD'),
  'database' => env('STACKHERO_MYSQL_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 an error such as "Uncaught PDOException: PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages: error:0A000086:SSL routines::certificate verify failed",
      PDO::MYSQL_ATTR_SSL_CAPATH => '/etc/ssl/certs/',
      // PDO::MYSQL_ATTR_SSL_CA => 'isrgrootx1.pem',
      PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
    ])
    : [],
],

Using MySQL with PHP CodeIgniter

In the database.php file, add the following configuration:

$db['default'] = array(
  'hostname' => getenv('STACKHERO_MYSQL_HOST'),
  'port'     => getenv('STACKHERO_MYSQL_PORT'),
  'username' => getenv('STACKHERO_MYSQL_USER'),
  'password' => getenv('STACKHERO_MYSQL_PASSWORD'),
  'database' => getenv('STACKHERO_MYSQL_USER'), // By convention, the database has the same name as the user.
  'dbdriver' => 'mysqli',
  'dbprefix' => '',
  'pconnect' => true,
  'char_set' => 'utf8',
  'dbcollat' => 'utf8_general_ci',
  'encrypt'  => array() // Important: activate TLS encryption
);

Connecting MySQL with PHP using environment variables

It is a best practice not to store your credentials in your source code but to use environment variables instead.

Below is an example for retrieving these credentials:

$hostname = getenv('STACKHERO_MYSQL_HOST');
$port = getenv('STACKHERO_MYSQL_PORT');
$user = getenv('STACKHERO_MYSQL_USER');
$password = getenv('STACKHERO_MYSQL_PASSWORD');
$database = getenv('STACKHERO_MYSQL_USER'); // By convention, the database has the same name as the user.

Using MySQL with WordPress

Connecting WordPress to Stackhero for MySQL is straightforward. Simply edit the wp-config.php file and configure the database 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>');

// Use 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 MySQL with Node.js

Using MySQL X protocol (xdevapi package)

In this example, we use the official xdevapi package to leverage the MySQL X protocol. To install it, run:

npm install @mysql/xdevapi

Below is a complete example that uses xdevapi:

const mysqlx = require('@mysql/xdevapi');

(async () => {
  // Connect to MySQL using MySQL X Protocol
  const session = await mysqlx.getSession({
    host: '<XXXXXX>.stackhero-network.com',
    port: '<PORT>',
    user: 'root',
    password: '<ROOT_PASSWORD>'
  });

  // Create a schema (database) if it does not exist
  const schemaExists = await session.getSchema('stackherotest').existsInDatabase();
  if (!schemaExists) {
    await session.createSchema('stackherotest');
  }

  // Create table 'users' if it does not exist
  const tableExists = await session
    .getSchema('stackherotest')
    .getTable('users')
    .existsInDatabase();
  if (!tableExists) {
    await session
      .sql('CREATE TABLE `stackherotest`.`users` '
        + '('
        + '`userId` INT UNSIGNED NOT NULL,'
        + '`name` VARCHAR(128) NOT NULL,'
        + '`address` TEXT NOT NULL,'
        + '`email` VARCHAR(265) NOT NULL'
        + ') '
        + 'ENGINE = InnoDB;')
      .execute();
  }

  // Insert a fake user
  await session
    .getSchema('stackherotest') // Database name
    .getTable('users') // Table name
    .insert('userId', 'name', 'address', 'email') // Column names
    .values(
      Math.round(Math.random() * 100000), // Generate a fake userId
      'User name', // column 'name'
      'User address', // column 'address'
      'user@email.com' // column 'email'
    )
    .execute();

  // Count the number of rows in table 'users'
  const usersCount = await session
    .getSchema('stackherotest') // Database name
    .getTable('users')
    .count();

  console.log(`There are now ${usersCount} entries in the table "users"`);

  // Close the connection to MySQL
  await session.close();

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

Using MySQL protocol (MySQL2 package)

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

npm install mysql2

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

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

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

  // Create 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', // column 'name'
        'User address', // column 'address'
        'user@email.com' // column 'email'
      ]
    ]
  );

  // Count the number of rows in table 'users'
  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 MySQL
  await db.end();

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

Using MySQL with Node.js, NestJS, or TypeORM

To connect from Node.js, NestJS, or TypeORM, include the ssl option as shown in this example:

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

Using MySQL with Prisma

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

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

Using MySQL with Django

If the mysqlclient module is not yet installed, install it as it will be used to connect to MySQL:

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

In this initial step, the password is stored directly in the settings.py file for testing purposes only. It is recommended to use a more secure approach as described later in this documentation.

Open the file settings.py and add the following:

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 intended for testing purposes only!

Once the connection is successful, you can use the recommended method to store credentials securely. In this example, django-environ is used to manage environment variables.

First, install django-environ using the command:

pip install django-environ

Then open the settings.py file and add the following:

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

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

Next, open or create the .env file in the same directory as settings.py and add the following:

STACKHERO_MYSQL_HOST=<XXXXXX>.stackhero-network.com
STACKHERO_MYSQL_PORT=<PORT>
STACKHERO_MYSQL_ROOT_PASSWORD=<ROOT_PASSWORD>

Finally, add .env to your .gitignore file to ensure your credentials are not committed to your Git repository:

echo ".env" >> .gitignore

Connecting MySQL with Java/Spring

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

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

Connecting MySQL with Groovy/Grails

The following example demonstrates how to connect your Grails application to MySQL:

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 the database you wish to connect to.
      username = "root" // It is advisable to create a dedicated user rather than using the "root" credentials.
      password = System.env.STACKHERO_MYSQL_ROOT_PASSWORD // It is advisable to create a dedicated user rather than using the "root" credentials.
      properties {
        maxActive = 50
        minEvictableIdleTimeMillis = 1800000
        timeBetweenEvictionRunsMillis = 1800000
        numTestsPerEvictionRun = 3
        testOnBorrow = true
        testWhileIdle = true
        testOnReturn = false
        validationQuery = "SELECT 1"
      }
    }
  }
}

Creating a user on MySQL using phpMyAdmin

A best practice is to create a dedicated user for your application rather than using the "root" user.

The simplest 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:

    1. Choose an account name (typically your application name)
    2. Click on Generate password to create a secure password (copy it to your clipboard)
    3. Check the option Create database with same name and grant all privileges

Once you validate the form, the new user will be created along with a corresponding database sharing the same name as the username.

Other articles for MySQL