MySQL / MariaDB

MySQL is one of the most widely used open-source relational databases, and MariaDB is its fully compatible community fork. Piora supports both — choose based on your preference.

MySQL vs MariaDB

Feature	MySQL	MariaDB
Maintainer	Oracle	Community (original MySQL creator)
License	GPL + Commercial	GPL
Compatibility	—	Fully compatible with MySQL
Strengths	Stable, wide support	More open-source features, performance improvements

How to Choose

If you don’t have a strong preference, either works well. MariaDB offers better performance in some scenarios and is fully open-source. MySQL has broader enterprise support.

Creating a Database

1. Navigate to the “Databases” page in the dashboard
2. Select “MySQL” or “MariaDB”
3. Choose a version
4. Configure parameters:

Parameter	Description
Root password	MySQL root account password
Database name	Initially created database
Username	Application account
User password	Application account password

Connection Settings

# MySQL connection string
mysql://user:password@mysql-container:3306/myapp

# MariaDB connection string (same format)
mysql://user:password@mariadb-container:3306/myapp

Common Framework Examples

// Node.js (mysql2)
const mysql = require('mysql2/promise');
const connection = await mysql.createConnection(process.env.DATABASE_URL);

# Python (PyMySQL)
import pymysql
connection = pymysql.connect(
    host='mysql-container',
    user='user',
    password='password',
    database='myapp'
)

// PHP (PDO)
$pdo = new PDO(
    'mysql:host=mysql-container;dbname=myapp',
    'user',
    'password'
);

Performance Tuning

Key Parameters

Adjust the following MySQL parameters based on your VPS specs:

# Buffer pool size (recommended: 50-70% of available memory)
innodb_buffer_pool_size = 512M

# Log file size
innodb_log_file_size = 256M

# Maximum connections
max_connections = 150

# Query cache (removed in MySQL 8.0)
# MariaDB still supports it
query_cache_size = 64M

Data Import and Export

Export

# Export a single database
mysqldump -u root -p myapp > backup.sql

# Export all databases
mysqldump -u root -p --all-databases > all_backup.sql

Import

# Import a database
mysql -u root -p myapp < backup.sql

Common Issues

Character Encoding Problems

Ensure the database uses the UTF-8 character set:

-- Specify character set when creating database
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Check existing database character set
SHOW CREATE DATABASE myapp;

Character Set

Always use utf8mb4 instead of utf8. MySQL’s utf8 only supports 3-byte UTF-8 characters and cannot store emoji or other 4-byte characters.

Too Many Connections

If you encounter a Too many connections error:

1. Increase the max_connections setting
2. Check if your application properly closes database connections
3. Use connection pooling to manage connections