MariaDB Databases

Every Opalstack server includes a managed instance of MariaDB, a community-developed fork of the MySQL relational database management system.

Your Opalstack dashboard lets you manage MariaDB databases and database users.

Adding MariaDB Databases

  1. Click MariaDB in the dashboard sidebar.

  2. Click the green "Create MariaDB" button in the upper right corner of the page. A form will appear.

  3. Enter the desired name for your new database.

  4. Select the server for your new database.

  5. Select the desired character set for your database.

  6. Select the database users that will be allowed to access the database.

  7. When all inputs are valid a green Create MariaDB button will apear on the form. Click the button to save your new database.

Deleting MariaDB Databases

  1. Click MariaDB in the dashboard sidebar.

  2. Click the delete icon (a trashcan/bin) for your database in the right edge of the database list. A confirmation prompt will appear.

  3. Click the red "Delete MariaDB" button in the confirmation prompt.

  4. After you confirm the database item will turn red while the operation is pending and will disappear a few seconds later when the operation is complete.

Adding MariaDB Users

  1. Click on MariaDB in the dashboard sidebar.

  2. Click the green Create MariaDB User button near the top-right corner of the page. A form will appear.

  3. Enter a name for your MariaDB user in the form.

  4. Select the server where the new MariaDB user will be created. This must be the same server as the database that the user will connect to.

  5. Enable the External Access option if you need to be able to connect to the database directly from your own computer or other hosts.

  6. Click the green Create MariaDB User button on the form to save your new MariaDB user.

  7. The new MariaDB user will be ready after a few seconds. You can retrieve the initial database user password from your dashboard notice log.

Changing MariaDB User Passwords

  1. Click on MariaDB in the dashboard sidebar. Your existing MariaDB users are listed in the lower half of the page.

  2. Click the password icon (a key) for your user in the right edge of the database user list. A form will appear.

  3. Enter the new database user password in the field labeled "New Password".

    Passwords must meet the following requirements:

    • Passwords must be from 10 to 64 characters in length
    • Passwords must contain 1 or more of each of the following types of characters:
      • Lowercase letters (a-z)
      • Uppercase letters (A-Z)
      • Numerals (0-9)
      • Special Characters (+-*/\.,:;!?#$%&@=^_~|<>()[]{})

  4. Enter your new password again in the field labeled "Verify Password".

    When you've entered identical valid passwords in both fields a purple "Change Password" button will appear.

  5. Click the "Change Password" button to save your new password.

Deleting MariaDB Users

  1. Click on MariaDB in the dashboard sidebar. Your existing MariaDB users are listed in the lower half of the page.

  2. Click the delete icon (a trashcan/bin) for your user in the right edge of the datatabase user list. A confirmation form will appear.

  3. Click the red button to confirm the operation, or click the cancel button to cancel it.

  4. After you delete the MariaDB user its status will update while the operation is pending, and the user will disappear a few seconds later when the operation is complete.

Managing MariaDB user access

You can control access to your MariaDB databases by editing the database in your dashboard.

  1. Click on MariaDB in the dashboard sidebar.

  2. Click the pencil icon for the database that you want to modify. A form will appear.

  3. Use the form to assign your MariaDB users as needed by selecting them in the lists.

    • Read/write users can view and modify data in your database
    • Read-only users can only view data in your database.

    You can select multiple users by using CTRL-click (Windows and Linux) or CMD-click (macOS).

  4. Click the green "Update MariaDB" button to save your changes.

  5. The database item will turn purple while the change is pending and then back to dark blue when the change is complete.

Managing external access

  1. Click on MariaDB in the dashboard sidebar. Your existing MariaDB users are listed in the lower half of the page.

  2. Click the external access toggle for your database user in the database user list to set your external access as desired.

Connecting to MariaDB Databases

The parameters used to connect to a MariaDB database on your Opalstack server are:

  • Host: localhost for local connections made directly on the server, or opalN.opalstack.com for remote connections from an external host (replace 'opalN' with your Opalstack server hostname, available on the dashboard dashboard). Note that you must enable external access on your MariaDB database before you can make remote connections to your database.
  • Port: 3306
  • Socket: /var/lib/mysql/mysql.sock
  • Database name: your MariaDB database name
  • Database user name: your MariaDB user name
  • Database user password: your MariaDB user password

Note

Many applications use a default configuration with the host as localhost and the port and socket as mentioned above so those parameters might be optional or not present in your application settings. Consult your application's documentation for its specific configuration requirements.

Connection Examples

The following examples use mydb as the MariaDB database name, mydbuser as the database user name, and mydbpassword as the database user password. Replace them with your own database name and credentials.

Command line client

First log in to a SSH session as a shell user on your server, then...

mysql -p -u mydbuser mydb

...and enter your database user password when prompted.

Python

First install the mysqlclient library in your environment...

source env/bin/activate
pip install mysqlclient

... then in your Python code:

import MySQLdb as mysql
conn = mysql.connect(host='localhost', user='mydbuser',
                     password='mydbpassword', database='mydb')

PHP

In your PHP code:

$conn = new mysqli("localhost", "mydbuser", "mydbpassword", "mydb");

Javascript

First install the mysql package into your Node.js project directory...

npm install mysql

... then in your Javascript code:

var mysql = require('mysql');
var conn = mysql.createConnection({
    host: 'localhost',
    user: 'mydbuser',
    password: 'mydbpassword',
    database: 'mydb'
});
conn.connect();

Ruby

First install the mysql2 gem...

gem install mysql2

... then in your Ruby code:

require 'mysql2'
conn = Mysql2::Client.new(:host => "localhost", :username => "mydbuser",
                          :database => "mydb", :password => "mydbpassword")

Connecting to a MariaDB Database Remotely via a SSH Tunnel

Opalstack's dashboard gives you the option to enable external access for your MariaDB databases, but for additional security we recommend that you make remote connections through a SSH tunnel.

To use a SSH tunnel for MariaDB access:

  1. Create a MariaDB and user.

  2. Create a shell user.

  3. On the client machine making the remote connection (for example, your own computer) open your terminal application and run the following command to establish the tunnel, replacing myuser@opalN.opalstack.com with your shell username and Opalstack server hostname:

    ssh myuser@opalN.opalstack.com -L 3306:127.0.0.1:3306 -N

    Note

    • If you have a MySQL server already running on the client machine, then you will need to change the local port used by the tunnel. The following example uses port 3307 as the local port: ssh myuser@opalN.opalstack.com -L 3307:127.0.0.1:3306 -N
    • If you're using PuTTY for your SSH connections, use 3306 as the source port and 127.0.0.1:3306 as the destination under "Category > Connection > SSH > Tunnels".

  4. Finally, on the client machine configure your application to connect to your database with the following parameters:

    • Host: 127.0.0.1
    • Port: 3306 (or whatever your local port is)
    • Database name: your MariaDB database name
    • Database user name: your MariaDB database user name
    • Database user password: your MariaDB database user password

Connecting to a MariaDB Database over the Web

Your Opalstack service includes a web-based database administration tool called Adminer which is similar to phpMyAdmin.

To access Adminer:

  1. Go to https://SERVERNAME.opalstacked.com/adminer/ in your browser, replacing SERVERNAME with your Opalstack server name (for example "opal1" or "vps2").

  2. Complete the login form as follows:

    • Server: MariaDB
    • Username: your MariaDB user name
    • Password: your MariaDB user password
    • Database: your MariaDB database name.

  3. Click the Login button to log in to your database with the credentials you provided in step 2.

Exporting and Importing MariaDB Databases

This section shows how to export data from, and import data to, your MariaDB databases on Opalstack. The examples use mydb as the database name, mydbuser as the database username, and mydb.sql as the filename for the imported/exported data - be sure to replace those values with the names of your own database and database user.

Exporting MariaDB Databases

You can export a MariaDB database by executing the following command in a SSH session on your Opalstack server:

mysqldump -p -u mydbuser mydb > mydb.sql

Enter your MariaDB database user password when prompted.

When the command is complete, the exported data will be stored in the file mydb.sql in your SSH session's current working directory.

Importing MariaDB Databases

You can import a MariaDB database by executing the following command in a SSH session on your Opalstack server:

mysql -p -u mydbuser mydb < mydb.sql

Enter your MariaDB database user password when prompted.

When the command is complete, the imported data will be stored in your mydb MariaDB database.

Scheduled Backups for MariaDB Databases

This document shows how to create scheduled backups of a MariaDB database on Opalstack.

The examples use mydb as the database name, mydbuser as the database user name, and mydbpassword as the database user password - be sure to replace those values with the names of your own database, user, and password.

  1. Log in to a SSH session on your Opalstack server.

  2. Run the following commands to create the files and directories used by your scheduled MariaDB backups:

    export DBNAME=mydb
mkdir -m 700 -p ~/.local/bin
mkdir -m 700 -p ~/.local/etc/mariadb_backups
mkdir -m 700 -p ~/backups/mariadb
touch ~/.local/etc/mariadb_backups/$DBNAME.cnf
chmod 600 ~/.local/etc/mariadb_backups/$DBNAME.cnf
touch ~/.local/bin/backup_$DBNAME
chmod 700 ~/.local/bin/backup_$DBNAME

  3. Edit the database credentials file named ~/.local/etc/mariadb_backups/mydb.cnf and add the following lines:

    [client]
password='mydbpassword'

  4. Edit the script ~/.local/bin/backup_mydb and add the following lines that will run your backup:

    #!/bin/bash

export DBNAME=mydb
export DBUSER=mydbuser

/bin/mysqldump --defaults-file=$HOME/.local/etc/mariadb_backups/$DBNAME.cnf \
    -u $DBUSER $DBNAME \
    > $HOME/backups/mariadb/$DBNAME-$(date +\%Y\%m\%d\%H\%M).sql \
    2>> $HOME/backups/mariadb/$DBNAME.log

  5. Run crontab -e and create a crontab entry to schedule the backup. The following example will schedule the backup to run daily at 3:30 AM UTC (server time):

    30 3 * * * $HOME/.local/bin/backup_mydb

    Please see the following pages for general help with cron jobs:

Once the above steps are complete, your backup will run at the schedule time to create a backup of your MariaDB database in ~/backups/mariadb on the server.