MySQL Database Instances

Complete guide to creating and managing MySQL database instances on DanubeData.

Overview

MySQL is the world's most popular open-source relational database. DanubeData provides fully managed MySQL instances with:

• Automated backups - Daily backups with 7-day retention
• High availability - Read replicas and failover
• Performance monitoring - Real-time metrics
• Easy scaling - Vertical and horizontal scaling
• Security - Encrypted connections and data

Supported Versions

• MySQL 8.4 - Latest LTS, recommended
• MySQL 8.0 - Previous LTS, stable

Version Selection

MySQL 8.4 (Recommended):

• Latest features
• Best performance
• Active development
• Long-term support

MySQL 8.0:

• Mature and stable
• Wide compatibility
• Extended support

Creating a MySQL Instance

Step-by-Step

1. Navigate to Databases in the main menu
2. Click Create Database Instance
3. Select MySQL as the engine
4. Choose version (8.4 recommended)
5. Select resource profile
6. Configure settings:
   • Instance name
   • Database name
   • Master username
   • Master password (auto-generated or custom)
   • Region
7. Optional: Attach parameter group
8. Optional: Attach firewall
9. Click Create Database

Configuration Tips

Instance Name:

• Use descriptive names: prod-mysql-users, staging-mysql-orders
• Include environment and purpose
• Use lowercase and hyphens

Database Name:

• Initial database name (default: danubedata)
• Create additional databases later
• Use meaningful names

Master Username:

• Default: admin
• Cannot be root, mysql, or admin in some versions
• Use strong usernames for security

Master Password:

• Auto-generated: Strong, random password
• Custom: Minimum 8 characters, mix of types
• Save it immediately - shown only once!

Connecting to MySQL

Connection Information

After creation, you'll receive:

• Endpoint: mysql-abc123.danubedata.com
• Port: 3306
• Username: Your master username
• Password: Master password (saved during creation)

MySQL Command Line

mysql -h mysql-abc123.danubedata.com \
      -P 3306 \
      -u admin \
      -p

# Enter password when prompted

Connection String

mysql://admin:password@mysql-abc123.danubedata.com:3306/database_name

From Application

PHP (PDO):

<?php
$host = 'mysql-abc123.danubedata.com';
$db   = 'database_name';
$user = 'admin';
$pass = 'your_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset;port=3306";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>

Python (mysql-connector):

import mysql.connector

config = {
    'host': 'mysql-abc123.danubedata.com',
    'port': 3306,
    'user': 'admin',
    'password': 'your_password',
    'database': 'database_name'
}

conn = mysql.connector.connect(**config)
cursor = conn.cursor()

cursor.execute("SELECT VERSION()")
version = cursor.fetchone()
print(f"MySQL version: {version[0]}")

cursor.close()
conn.close()

Node.js (mysql2):

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'mysql-abc123.danubedata.com',
  port: 3306,
  user: 'admin',
  password: 'your_password',
  database: 'database_name'
});

connection.connect((err) => {
  if (err) {
    console.error('Error connecting:', err);
    return;
  }
  console.log('Connected to MySQL');
});

// Query
connection.query('SELECT 1 + 1 AS solution', (err, results) => {
  if (err) throw err;
  console.log('Solution:', results[0].solution);
});

connection.end();

Java (JDBC):

import java.sql.*;

public class MySQLExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://mysql-abc123.danubedata.com:3306/database_name";
        String user = "admin";
        String password = "your_password";
        
        try {
            Connection conn = DriverManager.getConnection(url, user, password);
            System.out.println("Connected to MySQL");
            
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT VERSION()");
            
            while (rs.next()) {
                System.out.println("MySQL version: " + rs.getString(1));
            }
            
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Database Management

Create Databases

-- Create database
CREATE DATABASE myapp_production
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Use database
USE myapp_production;

-- Show databases
SHOW DATABASES;

-- Drop database
DROP DATABASE old_database;

Create Users

-- Create user
CREATE USER 'appuser'@'%' IDENTIFIED BY 'strong_password';

-- Grant privileges
GRANT ALL PRIVILEGES ON myapp_production.* TO 'appuser'@'%';

-- Grant specific privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_production.* TO 'readonly'@'%';

-- Apply changes
FLUSH PRIVILEGES;

-- Show users
SELECT User, Host FROM mysql.user;

-- Drop user
DROP USER 'olduser'@'%';

Create Tables

-- Create table with InnoDB (default)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Show tables
SHOW TABLES;

-- Describe table structure
DESCRIBE users;

-- Show create statement
SHOW CREATE TABLE users;

Performance Optimization

Indexes

-- Add index
CREATE INDEX idx_created_at ON users(created_at);

-- Add composite index
CREATE INDEX idx_status_date ON orders(status, created_at);

-- Add unique index
CREATE UNIQUE INDEX idx_email_unique ON users(email);

-- Show indexes
SHOW INDEX FROM users;

-- Drop index
DROP INDEX idx_old_index ON users;

Query Optimization

-- Explain query plan
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- Analyze table
ANALYZE TABLE users;

-- Optimize table
OPTIMIZE TABLE users;

-- Check table
CHECK TABLE users;

-- Repair table (if needed)
REPAIR TABLE users;

Slow Query Log

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- Queries > 2 seconds

-- Check slow query log status
SHOW VARIABLES LIKE 'slow_query%';

-- View slow queries
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

Monitoring & Metrics

Key Metrics

Monitor in the dashboard:

• CPU Usage: Database workload
• Memory Usage: Buffer pool and caches
• Connections: Active connections
• Queries/Second: Throughput
• Replication Lag: For replicas
• Slow Queries: Performance issues

Check Status

-- Show status variables
SHOW STATUS LIKE '%connection%';
SHOW STATUS LIKE '%thread%';
SHOW STATUS LIKE '%query%';

-- Show processlist
SHOW FULL PROCESSLIST;

-- Show variables
SHOW VARIABLES LIKE '%max_connections%';
SHOW VARIABLES LIKE '%buffer_pool%';

-- Engine status
SHOW ENGINE INNODB STATUS\G

Performance Schema

-- Enable performance schema
SET GLOBAL performance_schema = ON;

-- Top queries by execution time
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000000 as avg_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- Table I/O
SELECT 
    OBJECT_NAME,
    COUNT_READ,
    COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY COUNT_READ + COUNT_WRITE DESC;

Backup & Recovery

Automated Backups

• Frequency: Daily at 2 AM UTC
• Retention: 7 days
• Point-in-time recovery: Yes
• No performance impact: Backups from standby

Manual Snapshots

Create on-demand snapshots:

1. Go to database instance page
2. Click Snapshots tab
3. Click Create Snapshot
4. Enter name and description
5. Click Create

Export Database

# Export entire database
mysqldump -h mysql-abc123.danubedata.com \
          -u admin \
          -p \
          database_name > backup.sql

# Export specific tables
mysqldump -h mysql-abc123.danubedata.com \
          -u admin \
          -p \
          database_name table1 table2 > tables_backup.sql

# Export with compression
mysqldump -h mysql-abc123.danubedata.com \
          -u admin \
          -p \
          database_name | gzip > backup.sql.gz

# Export structure only (no data)
mysqldump -h mysql-abc123.danubedata.com \
          -u admin \
          -p \
          --no-data \
          database_name > structure.sql

Import Database

# Import from backup
mysql -h mysql-abc123.danubedata.com \
      -u admin \
      -p \
      database_name < backup.sql

# Import compressed backup
gunzip < backup.sql.gz | mysql -h mysql-abc123.danubedata.com \
                                -u admin \
                                -p \
                                database_name

Read Replicas

Creating Replicas

1. Go to database instance page
2. Click Add Replica
3. Select node and resource profile
4. Click Create Replica

Benefits

• Read Scaling: Offload SELECT queries
• Geographic Distribution: Place replicas near users
• Reporting: Run reports on replica
• Backup Source: Take backups from replica

Using Replicas

Application Configuration:

# Primary (writes)
PRIMARY_DB = {
    'host': 'mysql-abc123.danubedata.com',
    'port': 3306,
}

# Replica (reads)
REPLICA_DB = {
    'host': 'mysql-abc123-replica.danubedata.com',
    'port': 3306,
}

# Write to primary
write_conn = connect(PRIMARY_DB)
write_conn.execute("INSERT INTO users ...")

# Read from replica
read_conn = connect(REPLICA_DB)
users = read_conn.execute("SELECT * FROM users").fetchall()

Monitor Replication

-- On replica
SHOW REPLICA STATUS\G

-- Check lag
SELECT 
    TIMESTAMPDIFF(SECOND, last_applied_time, NOW()) AS lag_seconds
FROM performance_schema.replication_applier_status_by_worker;

Parameter Groups

Customize MySQL configuration:

1. Navigate to Parameter Groups
2. Click Create Parameter Group
3. Select MySQL version
4. Configure parameters
5. Attach to database instance

Common Parameters

Parameter	Description	Recommended
max_connections	Maximum connections	150-300
innodb_buffer_pool_size	InnoDB cache	70% of RAM
query_cache_size	Query cache	0 (disabled in 8.0+)
slow_query_log	Log slow queries	ON
long_query_time	Slow query threshold	2 seconds
max_allowed_packet	Max packet size	64M

Security

SSL/TLS Connections

All database instances support encrypted SSL/TLS connections. Use encrypted connections for maximum security.

MySQL CLI with SSL

Modern MySQL clients (5.7.11+) - Simple SSL:

# Encrypted connection (skip hostname verification for internal traffic)
mysql -h mysql-abc123.danubedata.com \
      -u admin \
      -p \
      --ssl-mode=REQUIRED \
      --ssl-verify-server-cert=OFF

Modern MySQL clients - Full SSL with Client Certificates:

# Encrypted connection with full certificate authentication
mysql -h mysql-abc123.danubedata.com \
      -u admin \
      -p \
      --ssl-ca=/etc/database-certs/mysql-abc123-ca.pem \
      --ssl-cert=/etc/database-certs/mysql-abc123-client-cert.pem \
      --ssl-key=/etc/database-certs/mysql-abc123-client-key.pem

Older MySQL clients (pre-5.7.11):

# Encrypted connection (legacy syntax)
mysql -h mysql-abc123.danubedata.com \
      -u admin \
      -p \
      --ssl \
      --ssl-verify-server-cert=0

Check your MySQL client version:

mysql --version

Application SSL Connections

PHP (PDO):

$dsn = "mysql:host=mysql-abc123.danubedata.com;port=3306;dbname=mydb";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_SSL_CA => '/etc/database-certs/mysql-abc123-ca.pem',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false, // Skip hostname verification
];
$pdo = new PDO($dsn, 'admin', $password, $options);

Python (mysql-connector):

import mysql.connector

config = {
    'host': 'mysql-abc123.danubedata.com',
    'port': 3306,
    'user': 'admin',
    'password': 'your_password',
    'database': 'mydb',
    'ssl_ca': '/etc/database-certs/mysql-abc123-ca.pem',
    'ssl_verify_cert': True,
    'ssl_verify_identity': False  # Skip hostname verification
}
conn = mysql.connector.connect(**config)

Node.js (mysql2):

const mysql = require('mysql2');
const fs = require('fs');

const connection = mysql.createConnection({
  host: 'mysql-abc123.danubedata.com',
  port: 3306,
  user: 'admin',
  password: 'your_password',
  database: 'mydb',
  ssl: {
    ca: fs.readFileSync('/etc/database-certs/mysql-abc123-ca.pem'),
    rejectUnauthorized: false  // Skip hostname verification
  }
});

Java (JDBC):

String url = "jdbc:mysql://mysql-abc123.danubedata.com:3306/mydb" +
             "?useSSL=true" +
             "&trustCertificateKeyStoreUrl=file:///etc/database-certs/mysql-abc123-ca.pem" +
             "&verifyServerCertificate=false";
Connection conn = DriverManager.getConnection(url, "admin", "password");

Why Skip Hostname Verification?

MySQL auto-generates SSL certificates with generic names that don't match the actual hostname. This causes hostname verification to fail. By skipping hostname verification while still using SSL:

• ✅ Connections are encrypted (TLS/SSL protects data in transit)
• ✅ Safe for internal cluster traffic (Kubernetes NetworkPolicies provide network isolation)
• ✅ Works immediately with auto-generated certificates
• ⚠️ No hostname verification (but MITM attacks are unlikely in private networks)

SSL Certificate Locations

SSL certificates are automatically provisioned to VPS instances:

• CA Certificate: /etc/database-certs/{database-name}-ca.pem
• Client Certificate: /etc/database-certs/{database-name}-client-cert.pem
• Client Key: /etc/database-certs/{database-name}-client-key.pem

Certificates are automatically extracted when databases start and distributed to all team VPS instances.

Verify SSL Connection

Check that SSL is active:

mysql -h mysql-abc123.danubedata.com -u admin -p \
  --ssl-mode=REQUIRED --ssl-verify-server-cert=OFF \
  -e "SHOW STATUS LIKE 'Ssl_cipher';"

You should see an SSL cipher like TLS_AES_256_GCM_SHA384.

User Permissions

Follow least privilege:

-- Application user (read/write)
CREATE USER 'app'@'%' IDENTIFIED BY 'strong_pass';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'%';

-- Read-only user (reporting)
CREATE USER 'readonly'@'%' IDENTIFIED BY 'strong_pass';
GRANT SELECT ON mydb.* TO 'readonly'@'%';

-- Admin user (schema changes)
CREATE USER 'dbadmin'@'%' IDENTIFIED BY 'strong_pass';
GRANT ALL PRIVILEGES ON mydb.* TO 'dbadmin'@'%';

FLUSH PRIVILEGES;

Firewall Rules

Restrict database access:

1. Create firewall
2. Add rules:
   • Allow port 3306 from application servers only
   • Deny from 0.0.0.0/0
3. Attach to database instance

Troubleshooting

Connection Issues

Error: Can't connect:

# Check firewall allows your IP
# Check credentials
# Verify endpoint and port

# Test connection
telnet mysql-abc123.danubedata.com 3306

# Check from application server
mysql -h mysql-abc123.danubedata.com -u admin -p

Error: Too many connections:

-- Check current connections
SHOW PROCESSLIST;

-- Check max connections
SHOW VARIABLES LIKE 'max_connections';

-- Kill idle connections
KILL <process_id>;

-- Increase max_connections in parameter group

Performance Issues

Slow queries:

-- Find slow queries
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

-- Missing indexes
SHOW INDEX FROM your_table;

-- Add indexes
CREATE INDEX idx_column ON your_table(column);

High CPU:

• Check for missing indexes
• Optimize queries
• Enable query cache (if MySQL 5.7)
• Consider upgrading instance size

High memory:

• Tune innodb_buffer_pool_size
• Check for memory leaks in queries
• Optimize table structures

Data Issues

Recover deleted data:

• Restore from automated backup
• Restore from snapshot
• Use point-in-time recovery

Table corruption:

CHECK TABLE your_table;
REPAIR TABLE your_table;

Best Practices

Schema Design

1. Use InnoDB engine (default)
2. Add appropriate indexes
3. Use correct data types
4. Normalize when needed
5. Use foreign keys for referential integrity

Query Optimization

1. Use EXPLAIN to analyze queries
2. Add indexes for frequent queries
3. Avoid SELECT *
4. Use LIMIT for large result sets
5. Batch INSERT/UPDATE operations

Connection Management

1. Use connection pooling
2. Close connections when done
3. Set appropriate timeouts
4. Monitor active connections

Security

1. Use strong passwords
2. Enable SSL/TLS
3. Limit user permissions
4. Use firewalls
5. Regular security updates

Monitoring

1. Monitor key metrics daily
2. Set up alerts
3. Review slow query log
4. Check replication lag
5. Monitor disk space

Next Steps

• Database Replicas
• Parameter Groups
• Database Backups
• PostgreSQL Guide

Need help? Contact support through the dashboard.