Database Management in ISPConfig

?️ MySQL/MariaDB Database Management

Creating a Database

1. Navigate to Sites → Databases
2. Click "Add new Database"
3. Configure database settings:
   • Database name: Choose unique name (prefix added automatically)
   • Database quota: Set size limit (MB, -1 for unlimited)
   • Database user: Create new or select existing
   • Database password: Set strong password
   • Database charset: utf8mb4 (recommended)
   • Remote Access: Enable if needed (IP addresses)
   • Active: Yes
4. Save database

Database Naming Convention

ISPConfig uses prefixes for databases:

• Format: c[client_id]_[database_name]
• Example: c1_wordpress
• This ensures unique names across all clients

phpMyAdmin Access

Access phpMyAdmin to manage databases:

• URL: https://yourdomain.com:8080/phpmyadmin
• Alternative: https://yourdomain.com/phpmyadmin
• Username: Database username
• Password: Database password

Database Connection Settings

PHP Connection Example

<?php
// Database configuration
$db_host = 'localhost'; // or 127.0.0.1
$db_name = 'c1_database'; // Your database name with prefix
$db_user = 'c1_user';     // Your database username
$db_pass = 'password';    // Your database password

// MySQLi connection
$mysqli = new mysqli($db_host, $db_user, $db_pass, $db_name);

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// PDO connection
try {
    $pdo = new PDO("mysql:host=$db_host;dbname=$db_name;charset=utf8mb4", 
                   $db_user, $db_pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

WordPress wp-config.php

// Database settings for WordPress
define('DB_NAME', 'c1_wordpress');
define('DB_USER', 'c1_wpuser');
define('DB_PASSWORD', 'your_password');
define('DB_HOST', 'localhost');
define('DB_CHARSET', 'utf8mb4');
define('DB_COLLATE', '');

Remote Database Access

1. Edit database in ISPConfig
2. Add remote IPs in "Remote Access" field
3. Use % for wildcard (any IP)
4. Separate multiple IPs with comma
5. Save changes

Database Backup

Manual Backup via phpMyAdmin

1. Login to phpMyAdmin
2. Select database
3. Click "Export" tab
4. Choose export method (Quick or Custom)
5. Select format (SQL recommended)
6. Click "Go" to download

Command Line Backup

# Backup single database
mysqldump -u username -p database_name > backup.sql

# Backup with compression
mysqldump -u username -p database_name | gzip > backup.sql.gz

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

Database Restore

Via phpMyAdmin

1. Select target database
2. Click "Import" tab
3. Choose file to import
4. Verify format (SQL)
5. Click "Go"

Command Line Restore

# Restore database
mysql -u username -p database_name < backup.sql

# Restore compressed backup
gunzip < backup.sql.gz | mysql -u username -p database_name

Database Optimization

• Regular optimization improves performance
• Use phpMyAdmin "Operations" → "Optimize table"
• Enable query caching in MySQL configuration
• Monitor slow queries in logs
• Create indexes for frequently queried columns

Database User Privileges

ISPConfig grants these privileges by default:

• SELECT, INSERT, UPDATE, DELETE
• CREATE, DROP, INDEX, ALTER
• CREATE TEMPORARY TABLES
• LOCK TABLES
• EXECUTE, CREATE ROUTINE, ALTER ROUTINE