Cwp Database

CWP Database Management

Manage MySQL/MariaDB, PostgreSQL, and MongoDB databases on CWP servers. Handle database creation, user management, performance tuning, upgrades, and recovery.

Database Services Overview

Service Detection

Detect which database services are installed:

# MariaDB (installed by default)
systemctl is-active mariadb 2>/dev/null && echo "MariaDB: active" || echo "MariaDB: not running"

# PostgreSQL (optional)
systemctl is-active postgresql 2>/dev/null && echo "PostgreSQL: active" || echo "PostgreSQL: not installed"

# MongoDB (optional)
systemctl is-active mongod 2>/dev/null && echo "MongoDB: active" || echo "MongoDB: not installed"

MariaDB/MySQL

Configuration Files

Access phpMyAdmin

• URL: https://SERVER_IP:2030/phpMyAdmin/
• Credentials: Use root password from /root/.my.cnf

Common Database Operations

# Access MySQL CLI
mysql -u root -p

# Access with stored credentials
mysql

# Show databases
mysql -e "SHOW DATABASES;"

# Create database
mysql -e "CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# Create user
mysql -e "CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';"

# Grant privileges
mysql -e "GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';"
mysql -e "FLUSH PRIVILEGES;"

# Import database
mysql -u username -p dbname < dump.sql

# Export database
mysqldump -u root -p dbname > dump.sql

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

Database Management via API

# Create database via CWP API
/scripts/cwp_api databasemysql add USERNAME DBNAME

# Delete database
/scripts/cwp_api databasemysql delete USERNAME DBNAME

# List databases
/scripts/cwp_api databasemysql list USERNAME

Performance Tuning

Edit /etc/my.cnf.d/server.cnf under [mysqld]:

# Connection settings
max_connections = 500           # Default is 151
max_user_connections = 45       # Per-user for shared hosting
wait_timeout = 600              # Close idle connections

# Buffer settings
innodb_buffer_pool_size = 1G    # 50-70% of available RAM
innodb_log_file_size = 256M     # InnoDB log size
max_allowed_packet = 256M       # For large imports/exports
tmp_table_size = 64M            # Temporary table size
max_heap_table_size = 64M       # Memory table size

# Query cache (MariaDB 10.1 and earlier)
query_cache_type = 1
query_cache_size = 64M

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

After changes, restart MariaDB:

systemctl restart mariadb

Repair and Optimize Tables

# Repair MyISAM tables
/scripts/mysql_fix_myisam_tables

# Check all databases
mysqlcheck -u root -p --all-databases --check

# Optimize all databases
mysqlcheck -u root -p --all-databases --optimize

# Analyze all tables
mysqlcheck -u root -p --all-databases --analyze

MariaDB Upgrade

Upgrade between MariaDB versions following the official path:

# Example: MariaDB 10.2 to 10.6
# Step 1: Update repository
sed -i 's/10.2/10.6/g' /etc/yum.repos.d/mariadb.repo

# Step 2: Stop MariaDB
systemctl stop mariadb mysql mysqld

# Step 3: Remove old packages
systemctl disable mariadb
rpm --nodeps -ev MariaDB-server

# Step 4: Install new version
yum clean all
yum -y update "MariaDB-*"
yum -y install MariaDB-server

# Step 5: Start and upgrade
systemctl enable mariadb
systemctl start mariadb
mysql_upgrade --force

Warning: Always back up all databases before upgrading. Test on a staging server first.

MariaDB Upgrade Paths

Remote MySQL Access

# Whitelist IP permanently in CSF
csf -a 10.10.23.124 "mysql remote connection"

# Whitelist IP temporarily (24 hours)
csf -ta 86400 10.10.23.124 "mysql remote connection"

# Allow MySQL remote connections in my.cnf
# bind-address = 0.0.0.0

# Create remote user
mysql -e "CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'password';"
mysql -e "GRANT ALL PRIVILEGES ON dbname.* TO 'remoteuser'@'%';"
mysql -e "FLUSH PRIVILEGES;"

InnoDB Crash Recovery

When MariaDB fails to start due to InnoDB corruption:

# Step 1: Add to my.cnf under [mysqld]
innodb_force_recovery = 1

# Step 2: Start MariaDB
systemctl start mariadb

# Step 3: Dump all databases
mysqldump -u root -p --all-databases > /root/emergency_backup.sql

# Step 4: Stop, remove InnoDB files, restart fresh
systemctl stop mariadb
rm -f /var/lib/mysql/ib_logfile*
systemctl start mariadb

# If level 1 fails, increment: 2, 3, 4, 5, 6
# Level 4+ may cause data loss

PostgreSQL

Installation

# Install via CWP Admin panel
# SQL Services -> PostgreSQL Installer

# Or install phpPgAdmin
sh /scripts/install_phpPgAdmin

Configuration Files

Common Operations

# Access PostgreSQL CLI
su - postgres
psql

# Create database
createdb dbname

# Create user
createuser -P username

# Grant privileges
psql -c "GRANT ALL PRIVILEGES ON DATABASE dbname TO username;"

# Backup database
pg_dump dbname > dbname.sql

# Restore database
psql dbname < dbname.sql

# Check PostgreSQL status
systemctl status postgresql

Enable PHP PostgreSQL Support

Recompile PHP with --with-pgsql and --with-pdo-pgsql flags via PHP Switcher.

Performance Tuning

Edit /var/lib/pgsql/data/postgresql.conf:

shared_buffers = 256MB              # 25% of RAM
work_mem = 4MB                      # Per-operation memory
maintenance_work_mem = 64MB         # For VACUUM, CREATE INDEX
effective_cache_size = 768MB        # 75% of RAM
max_connections = 100               # Adjust based on needs

# Restart after changes
systemctl restart postgresql

MongoDB

Installation

# Install via CWP Admin panel
# SQL Services -> MongoDB Manager
# Select MongoDB version (2 or 3)

Configuration Files

Common Operations

# Access MongoDB shell
mongo

# Show databases
show dbs

# Use database
use dbname

# Create collection
db.createCollection("collectionname")

# Insert document
db.collectionname.insert({key: "value"})

# Find documents
db.collectionname.find()

# Backup database
mongodump --out /backup/path

# Restore database
mongorestore /backup/path

# Check MongoDB status
systemctl status mongod

Configuration

Edit /etc/mongod.conf:

storage:
  dbPath: /var/lib/mongo
  journal:
    enabled: true

systemLog:
  destination: file
  path: /var/log/mongodb/mongod.log
  logAppend: true

net:
  port: 27017
  bindIp: 127.0.0.1  # Change for remote access

# Restart after changes
systemctl restart mongod

Troubleshooting

Diagnostic Commands

# Check MariaDB status
systemctl status mariadb

# View MariaDB error log
tail -50 /var/log/mysql/error.log

# Check connections
mysql -e "SHOW PROCESSLIST;"

# Check variables
mysql -e "SHOW VARIABLES LIKE 'max_connections';"

# Check InnoDB status
mysql -e "SHOW ENGINE INNODB STATUS\G"

# Database sizes
mysql -e "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM information_schema.tables GROUP BY table_schema;"

# Check database
/scripts/checkdb

Additional Resources

• references/mysql.md -- MariaDB/MySQL configuration and tuning guide
• references/postgresql.md -- PostgreSQL installation and configuration
• references/mongodb.md -- MongoDB setup and management