firefrost-operations-manual/docs/services/luckperms-mysql-database.md

# LuckPerms MySQL Database Setup

**Date:** 2026-03-27  
**Server:** Command Center (63.143.34.217)  
**Database:** luckperms  
**Purpose:** Centralized permission storage for all 13 game servers

---

## Database Configuration

### MySQL Installation

**Installed:** 2026-03-27  
**Version:** MySQL 8.0 (Ubuntu 24.04)  
**Service:** systemd (mysql.service)

**Installation Commands:**
```bash
apt update
apt install mysql-server -y
systemctl start mysql
systemctl enable mysql
mysql_secure_installation
```

**Secure Installation Settings:**
- Password validator: Not enabled (allows custom passwords)
- Remove anonymous users: Yes
- Disallow root login remotely: Yes
- Remove test database: Yes
- Reload privilege tables: Yes

**Root Access:**
- MySQL 8.0 uses `auth_socket` plugin by default
- Root can login via: `sudo mysql` (no password needed)
- Root cannot login remotely (secure by default)

---

## LuckPerms Database

### Database Details

- **Name:** luckperms
- **Character Set:** utf8mb4
- **Collation:** utf8mb4_unicode_ci
- **Created:** 2026-03-27

### User Credentials

- **Username:** luckperms
- **Password:** Firefrost1234!!
- **Host:** % (allows connections from any IP)
- **Privileges:** ALL on luckperms.* database

### Creation Commands

```sql
-- Access MySQL as root
sudo mysql

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

-- Create user
CREATE USER 'luckperms'@'%' IDENTIFIED BY 'Firefrost1234!!';

-- Grant permissions
GRANT ALL PRIVILEGES ON luckperms.* TO 'luckperms'@'%';
FLUSH PRIVILEGES;

-- Verify
SHOW DATABASES;
SELECT User, Host FROM mysql.user WHERE User='luckperms';

-- Exit
exit
```

---

## Connection Details

### For LuckPerms Configuration

```yaml
storage-method: MySQL

data:
  address: 63.143.34.217:3306
  database: luckperms
  username: luckperms
  password: Firefrost1234!!
```

**OR in config format:**
```properties
storage-method=MySQL
data.address=63.143.34.217:3306
data.database=luckperms
data.username=luckperms
data.password=Firefrost1234!!
```

---

## Security Considerations

### Why Separate Database?

**Isolated from Pterodactyl database for:**

1. **Security Isolation**
   - Pterodactyl database contains sensitive panel data
   - LuckPerms database contains game permissions
   - Compromise of one doesn't affect the other

2. **Performance**
   - Pterodactyl handles panel queries
   - LuckPerms handles thousands of permission checks per second across 13 servers
   - Separation prevents performance degradation

3. **Backup/Recovery**
   - Can backup game permissions separately
   - Can restore/reset without affecting infrastructure
   - Independent maintenance windows

4. **Best Practice**
   - Industry standard: one database per application
   - Prevents dependency conflicts
   - Easier troubleshooting

### Network Security

**MySQL listens on:**
- Port: 3306 (default)
- Bind address: 0.0.0.0 (all interfaces - allows remote connections)

**Firewall considerations:**
- TX1 Dallas (38.68.14.26) needs access
- NC1 Charlotte (216.239.104.130) needs access
- Ensure UFW/iptables allows connections from these IPs

**Check current firewall status:**
```bash
ufw status
# OR
iptables -L -n | grep 3306
```

**If needed, allow specific IPs:**
```bash
ufw allow from 38.68.14.26 to any port 3306
ufw allow from 216.239.104.130 to any port 3306
```

---

## Game Server Integration

### Servers Using This Database

All 13 Firefrost Gaming servers connect to this central MySQL database:

**TX1 Dallas Servers (38.68.14.26):**
1. foundry.firefrostgaming.com
2. rad2.firefrostgaming.com
3. stoneblock4.firefrostgaming.com
4. vanilla.firefrostgaming.com
5. createplus.firefrostgaming.com
6. arseclectica.firefrostgaming.com

**NC1 Charlotte Servers (216.239.104.130):**
1. reclamation.firefrostgaming.com
2. society.firefrostgaming.com
3. emberproject.firefrostgaming.com
4. minecolonies.firefrostgaming.com
5. homestead.firefrostgaming.com
6. emcsubterratech.firefrostgaming.com
7. atm10.firefrostgaming.com

### Configuration Per Server

Each server's LuckPerms config at `/config/luckperms/luckperms.conf`:

```hocon
storage-method = mysql

data {
  address = "63.143.34.217:3306"
  database = "luckperms"
  username = "luckperms"
  password = "Firefrost1234!!"
  
  # Connection pool settings
  pool-settings {
    maximum-pool-size = 10
    minimum-idle = 10
    maximum-lifetime = 1800000
    keepalive-time = 0
    connection-timeout = 5000
  }
}
```

---

## Deployment Status

### Implementation Plan

**Phase 1: Prerequisites (COMPLETE ✅)**
- MySQL server installed on Command Center
- Database created
- User credentials configured
- Credentials stored in Vaultwarden

**Phase 2: Mod Deployment (IN PROGRESS ⏳)**
- **Responsible:** Holly (unicorn20089)
- **Status:** Delegated 2026-03-27
- **Guide Provided:** `docs/guides/server-side-mod-deployment-guide.md`
- **Tasks:**
  - Download required mods per server Minecraft version
  - Upload mods to each server via Pterodactyl Panel
  - Configure LuckPerms MySQL connection
  - Test each server
  - Repeat for all 13 servers

**Phase 3: Testing (PENDING)**
- Verify all servers connect to MySQL
- Test permission sync across servers
- Verify rank system works

---

## Maintenance

### Backup Procedures

**Manual Backup:**
```bash
# On Command Center
mysqldump -u luckperms -p luckperms > luckperms-backup-$(date +%Y%m%d).sql
```

**Restore from Backup:**
```bash
mysql -u luckperms -p luckperms < luckperms-backup-YYYYMMDD.sql
```

**Automated Backup (Recommended):**
```bash
# Add to crontab
0 2 * * * mysqldump -u luckperms -p'Firefrost1234!!' luckperms | gzip > /root/backups/luckperms-$(date +\%Y\%m\%d).sql.gz
```

### Monitoring

**Check database size:**
```bash
sudo mysql -e "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM information_schema.tables WHERE table_schema = 'luckperms' GROUP BY table_schema;"
```

**Check active connections:**
```bash
sudo mysql -e "SHOW PROCESSLIST;" | grep luckperms
```

**Check table status:**
```bash
sudo mysql luckperms -e "SHOW TABLES;"
sudo mysql luckperms -e "SELECT COUNT(*) FROM luckperms_players;"
sudo mysql luckperms -e "SELECT COUNT(*) FROM luckperms_permissions;"
```

---

## Troubleshooting

### Connection Refused

**Symptoms:** Game server can't connect to MySQL

**Checks:**
1. MySQL service running: `systemctl status mysql`
2. MySQL listening on 3306: `netstat -tlnp | grep 3306`
3. Firewall allows connections: `ufw status`
4. Credentials correct in server config

**Solution:**
```bash
# Ensure MySQL is running
systemctl start mysql

# Check bind address (should be 0.0.0.0 or specific IP)
grep bind-address /etc/mysql/mysql.conf.d/mysqld.cnf

# If bind-address is 127.0.0.1, change to 0.0.0.0
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Change: bind-address = 0.0.0.0
sudo systemctl restart mysql
```

### Access Denied for User

**Symptoms:** "Access denied for user 'luckperms'@'host'"

**Checks:**
1. Password correct
2. User has permissions
3. Host wildcard allows connection

**Solution:**
```sql
-- Verify user exists and host is '%'
SELECT User, Host FROM mysql.user WHERE User='luckperms';

-- Re-grant permissions if needed
GRANT ALL PRIVILEGES ON luckperms.* TO 'luckperms'@'%';
FLUSH PRIVILEGES;

-- If still failing, recreate user
DROP USER 'luckperms'@'%';
CREATE USER 'luckperms'@'%' IDENTIFIED BY 'Firefrost1234!!';
GRANT ALL PRIVILEGES ON luckperms.* TO 'luckperms'@'%';
FLUSH PRIVILEGES;
```

### Slow Queries

**Symptoms:** Permission checks lag, server TPS drops

**Diagnosis:**
```sql
-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

-- Check slow queries
sudo tail -f /var/log/mysql/slow-query.log
```

**Solutions:**
1. Increase connection pool size in LuckPerms config
2. Optimize MySQL configuration
3. Add database indexes (LuckPerms handles this automatically)
4. Upgrade server hardware if needed

---

## Related Documentation

- [Server-Side Mod Deployment Guide](../guides/server-side-mod-deployment-guide.md)
- [Subscription Automation Guide](../guides/subscription-automation-guide.md)
- [Pterodactyl Panel Configuration](pterodactyl-panel-configuration.md)
- [Vaultwarden Configuration](vaultwarden-configuration.md)

---

**Last Updated:** 2026-03-27  
**Documented By:** The Verifier (Chronicler #42)  
**Status:** ✅ Database ready, awaiting mod deployment by Holly