Accessing your MariaDB instance

MariaDB in Pukki is still in beta

This means that it hasn't been tested as extensively as PostgreSQL, and there might still be large changes to how Pukki manages MariaDB database instances.

Graphical user interface

You can find a non-comprehensive list of different graphical interfaces for using MariaDB on MariaDB's homepage.

MariaDB Connectors

MariaDB Connectors are used for creating database connections from applications, and are available for many popular programming languages. You can find more information regarding their usage and configuration in MariaDB's documentation:

Please pay special attention to SSL configuration - as Pukki only allows SSL connections to its MariaDB instances, you will have to set up configuration options related to that.

Command-line client mariadb and mysql

MariaDB's documentation on the command-line client

The recommended CLI client to use is mariadb. The mysql client does still work, but is usually a symbolic link to mariadb.

Example commands for accessing your database:

mariadb --ssl --password --host ${PUBLIC_IP} --user ${DATABASE_USER} ${DATABASE_NAME}

or

mysql --ssl --password --host ${PUBLIC_IP} --user ${DATABASE_USER} ${DATABASE_NAME}

--ssl means the MariaDB client connects using SSL. This is necessary as Pukki database instances enforce encrypted connections.
--password means the client prompts for a password. You can specify one on the command line (like --password=password), but that is considered insecure.
--host specifies the host address to connect to. In Pukki this is almost always your database instance's public IP address.
--user specifies which user to connect to the database as.
${DATABASE_NAME} specifies which database on the server to connect to.

Using command line with .my.cnf

If you are frequently connecting to the same database, it might be worthwhile to set up a .my.cnf configuration file in your home directory to store the necessary flags and options.

Create an empty .my.cnf file in your home directory, and restrict its access permissions:

touch ~/.my.cnf; chmod 600 ~/.my.cnf

Edit the configuration file with your favorite editor and add the following options:

[client]
user = your_username
password = your_password
host = your_host
database = your_database
ssl

As storing the password in a plaintext file isn't recommended, you can leave it empty to always prompt for the password when connecting:

[client]
user = your_user
host = your_database_public_ip
database = your_database
ssl
password

Export Your Current MariaDB Database

To migrate your database, you must first create a backup (or "dump") of your existing MariaDB instance. MariaDB provides the mariadb-dump utility, which exports a database to a plain SQL file. This format is commonly used for backups, migrations, and data archiving.

Database dumps can be used to migrate data away from Pukki or to keep an independent backup outside CSC services. This could be a useful tool that will help you to achieve your backup strategy.

Run the mariadb-dump command to create a SQL dump of your database:

mariadb-dump -h ${PUBLIC_IP} -u ${USERNAME} -p${PASSWORD} --ssl --ssl-verify-server-cert=OFF ${DATABASE_NAME} > database_backup.sql

-p${PASSWORD}: Password with no space after -p. If you use -p alone,mariadb-dump prompts for the password interactively.
--ssl: Enables an encrypted connection. Required for all Pukki MariaDB connections.
--ssl-verify-server-cert=OFF: This flag keeps the connection encrypted while skipping certificate validation. The mariadb-dump is stricter and requires this flag explicitly.
database_backup.sql: The name of the output file.

To dump all databases at once:

mariadb-dump -h ${PUBLIC_IP} -u ${USERNAME} -p${PASSWORD} --ssl --ssl-verify-server-cert=OFF --all-databases > all_databases_backup.sql

--all-databases: Exports all databases the user has access to.

Common issues with CLI connections

ERROR 2002 (HY000): Can't connect to MySQL server on '${PUBLIC_IP}' (115)

If a password prompt appears, but the client is afterwards stuck connecting for a long time, you should double-check that the host argument is correct, and that the firewall allows connections from your client's address.

ERROR 3159 (08004): Connections using insecure transport are prohibited while --require_secure_transport=ON.

You tried to connect to the database without --ssl.

ERROR 1045 (28000): Access denied for user 'username'@'yourhostname' (using password: YES)

Either your password or your username is wrong.

ERROR 1044 (42000): Access denied for user 'username'@'%' to database 'databasename'

Either the database specified does not exist, or the username specified has no access to it.

mariadb-dump: Got error: 2026: "TLS/SSL error: self-signed certificate" when trying to connect.

The --ssl flag alone isn't enough, you need to supply the client --ssl-verify-server-cert=OFF to skip certificate verification.

Accessing your Pukki MariaDB database from Puhti

Ensure your database instance allows network traffic from Puhti.
ssh onto Puhti and load the mariadb module
```
module load mariadb
```
Now you can connect to the database with the mariadb-client

Some useful SQL commands

List databases

SHOW DATABASES;

List tables

SHOW TABLES;

Show table descriptions

DESCRIBE $table_name;

Change database

USE DATABASE $database_name;

Example query

SELECT * FROM $table_name LIMIT 1;

Show all database settings

SHOW VARIABLES;

or if you want to show a subset you can use LIKE

SHOW VARIABLES LIKE 'innodb%';

Note that % here indicates a wildcard - this lists all variables that begin with innodb.

Import database dump

cat your_database_dump.sql | mariadb