# 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 3-day retention
- **High availability** - Read replicas and failover
- **Performance monitoring** - Real-time metrics and alerts
- **Easy scaling** - Vertical scaling, read replicas, and optional [storage autoscaling](https://docs.danubedata.ro/databases-overview#storage-autoscaling)
- **In-place upgrades** - Move to a newer MySQL version without recreating
- **Security** - Encrypted connections and data

## Supported Versions

- **MySQL 9.0** - Latest LTS, recommended
- **MySQL 9.1** - Innovation release
- **MySQL 8.4** - Previous LTS, stable
- **MySQL 8.0** - Previous LTS, stable

### Version Selection

**MySQL 9.0** (Recommended):
- Latest LTS features
- Best performance
- Active development
- Long-term support

**MySQL 9.1** (Innovation):
- Newest features, shorter support window

**MySQL 8.4**:
- Mature and stable
- Wide compatibility

**MySQL 8.0**:
- Mature and stable
- Wide compatibility
- Extended support

### Upgrading Versions

You can move an existing MySQL instance to a newer version in place — no need to recreate it or dump and reload:

1. Open the **Updates** tab on your instance
2. Choose an available target version
3. Start the upgrade — a snapshot is taken automatically first, so you can roll back if needed
4. Track progress live; the instance shows an **Updating** status while it runs

Upgrades follow supported paths (8.0 → 8.4 → 9.0/9.1). Take a manual snapshot first if you want an extra restore point.

## 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 (9.0 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.ro`
- **Port**: `3306`
- **Username**: Your master username
- **Password**: Master password (saved during creation)

### MySQL Command Line

```bash
mysql -h mysql-abc123.danubedata.ro \
      -P 3306 \
      -u admin \
      -p

# Enter password when prompted
```

### Connection String

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

### From Application

**PHP (PDO)**:
```php
<?php
$host = 'mysql-abc123.danubedata.ro';
$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)**:
```python
import mysql.connector

config = {
    'host': 'mysql-abc123.danubedata.ro',
    '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)**:
```javascript
const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'mysql-abc123.danubedata.ro',
  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)**:
```java
import java.sql.*;

public class MySQLExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://mysql-abc123.danubedata.ro: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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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**: 3 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

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

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

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

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

### Import Database

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

# Import compressed backup
gunzip < backup.sql.gz | mysql -h mysql-abc123.danubedata.ro \
                                -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
- **Host Isolation**: Runs on a separate host in the same datacenter
- **Reporting**: Run reports on replica
- **Backup Source**: Take backups from replica

### Using Replicas

**Application Configuration**:
```python
# Primary (writes)
PRIMARY_DB = {
    'host': 'mysql-abc123.danubedata.ro',
    'port': 3306,
}

# Replica (reads)
REPLICA_DB = {
    'host': 'mysql-abc123-replica.danubedata.ro',
    '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

```sql
-- 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**:
```bash
# Encrypted connection (skip hostname verification for internal traffic)
mysql -h mysql-abc123.danubedata.ro \
      -u admin \
      -p \
      --ssl-mode=REQUIRED \
      --ssl-verify-server-cert=OFF
```

**Modern MySQL clients - Full SSL with Client Certificates**:
```bash
# Encrypted connection with full certificate authentication
mysql -h mysql-abc123.danubedata.ro \
      -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)**:
```bash
# Encrypted connection (legacy syntax)
mysql -h mysql-abc123.danubedata.ro \
      -u admin \
      -p \
      --ssl \
      --ssl-verify-server-cert=0
```

**Check your MySQL client version**:
```bash
mysql --version
```

#### Application SSL Connections

**PHP (PDO)**:
```php
$dsn = "mysql:host=mysql-abc123.danubedata.ro;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)**:
```python
import mysql.connector

config = {
    'host': 'mysql-abc123.danubedata.ro',
    '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)**:
```javascript
const mysql = require('mysql2');
const fs = require('fs');

const connection = mysql.createConnection({
  host: 'mysql-abc123.danubedata.ro',
  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)**:
```java
String url = "jdbc:mysql://mysql-abc123.danubedata.ro: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 traffic (your private network provides 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:
```bash
mysql -h mysql-abc123.danubedata.ro -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:

```sql
-- 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**:
```bash
# Check firewall allows your IP
# Check credentials
# Verify endpoint and port

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

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

**Error: Too many connections**:
```sql
-- 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**:
```sql
-- 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**:
```sql
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](https://docs.danubedata.ro/databases-replicas)
- [Parameter Groups](https://docs.danubedata.ro/databases-parameters)
- [Database Backups](https://docs.danubedata.ro/databases-backups)
- [PostgreSQL Guide](https://docs.danubedata.ro/databases-postgresql)

Need help? Contact support through the dashboard.