MariaDB Backup
XLServer provides robust lifecycle management for MariaDB databases, including both standalone servers and Galera clusters.
Through native integration with Percona XtraBackup, ZFS snapshots, and XLServer orchestration, users can ensure fast backup, secure recovery, and efficient cloning of MariaDB environments.
⚙️ Prerequisites
Before linking a MariaDB database server to XLServer, ensure the following requirements are satisfied:
- ✅ MariaDB server is reachable from XLServer (network connectivity open).
- ✅ Binlog enabled in the source MariaDB database server (needed for incremental backup & recovery).
- ✅ Valid MariaDB user credentials with backup privileges and access to catalog system views.
- ✅ SSH access from XLServer to the host (or all nodes in case of a Galera cluster).
- ✅ MariaDB version ≥ 10.2 is supported.
- ✅ Sufficient free space exists in the datastore to hold golden image and incremental backups.
- ✅ NFS client is installed on all MariaDB hosts (required for backup and clone operations).
Note
The MariaDB user must have all privileges required for backup. Refer to the appendix “MariaDB User” for the SQL script to create this user.
Never link a database into the PUBLIC project, otherwise it will be visible to all XLServer users.
🔗 Linking a MariaDB Database Server
The first step is to link the MariaDB database server. This creates a golden image copy (full backup) that serves as the baseline for future incremental snapshots.
Step 1 — Navigate to Database Detail
Go to the MariaDB database server detail page.
If the server is not yet linked, you will see the linking option:

Step 2 — Start Linking Wizard
Click “Link Database” (or select Link from the Actions menu).
The Linking Wizard opens.
Step 3 — Validate Credentials
Enter MariaDB credentials, then click CHECK CONNECTION.
You can only proceed if the connection is successful.

Step 4 — Configure Linking Parameters
On the Linking Parameters page, provide the following:
- Mount Base: NFS mount point. Example: /mnt/link.
- Parallel Channels: Number of backup threads. Default 1, maximum32.
- XtraBackup Path: Path to XtraBackup binary on the server. If not provided, XLServer uses the bundled binary.
- Project: Project where this database belongs. By default, the project of the environment is selected.
- Default Snapshot Retention: Default retention for snapshots of this DB.
- Storage Optimization: Enable compression and/or deduplication to save storage (may impact performance).

Step 5 — Review Summary
The summary page shows all selected options:

Step 6 — Connect and Validate
Click CONNECT to start the linking process.
You can track progress in the Jobs page.

Step 7 — Verify Linked Database
Once linking is complete, navigate to the database page to view details:

📊 MariaDB Live Database Server Detail
Once linked, each MariaDB database server has a dedicated detail page with four main tabs:
- Summary
- Database Template
- Data Masking
- Performances
1. Summary Tab
The Summary tab provides three sections:
- Database Parameters
- Database Schemas
- Snapshots Calendar
Database Parameters
| Setting | Description | Default Value | Modifiable | 
|---|---|---|---|
| Project | Project where database is attached | — | — | 
| Environment | Environment of the database | — | — | 
| Vendor | Database vendor | MARIADB | — | 
| Database Server | Endpoint (hostname/IP:port) | — | — | 
| Version | MariaDB version | — | — | 
| Datadir | Path to database files | — | — | 
| Socket | Local socket path | — | — | 
| Backup Path | Base path for backup (corresponds to Mount Base) | — | ✅ | 
| Backup Strategy | Backup strategy attached to this DB | None | ✅ | 
| Parallel | Number of backup threads | 1 | ✅ | 
| Startup Timeout | Wait time for clone startup before failure | 120 seconds | ✅ | 
| Quota | Maximum clones allowed ( 0= unlimited) | 10 | ✅ | 
| Snapshot Retention | Default snapshot retention | 7 days | ✅ | 
| Database Type | Always Live Database | Live Database | — | 
| DB User | Database user credentials | — | ✅ | 
| Generic Post Script | Path to SQL script executed after clone creation | — | ✅ | 
| Connecting Mode | Mode of backup. Current: ONLINE. (Offline mode planned in future versions). | ONLINE | — | 
| Discovery | AUTO = discovered during environment refresh, MANUAL = added manually | AUTO | — | 
| Linked On | Date & time database was linked | — | — | 
| Last Sync Time | Time of last snapshot (synchronization) | — | — | 
| Last Refresh Time | Time of last parameter refresh | — | — | 
| Created By | User who added this database | — | — | 
Database Schemas
Lists all detected schemas with their sizes.
Snapshots Calendar
Calendar view with all snapshots:
- Green dot = successful snapshot
- Red dot = failed snapshot
2. Database Template Tab
Database templates allow applying standardized parameters to all clones of this database.
- Attach Template: Link a predefined template
- Modify Template: Switch to another template
- Detach Template: Remove association

3. Data Masking Tab
The Data Masking tab allows attaching masking rules to protect sensitive data during cloning.
Column-Level Masking
- Define masking rules for specific columns.
- Use predefined or custom templates.







Table-Level Masking (Shuffling)
- Shuffle entire table rows to keep realistic but anonymized datasets.




4. Performances Tab
The Performances tab provides monitoring and tuning information:
- 
Snapshots created in the last 7 days 
  
  
- 
Key metrics: Disk usage, compression ratio, deduplication ratio, RPO 
  
- 
Pie charts: storage consumption and snapshot success ratio 
  
- 
Line charts: backup size, DB size, compression ratio, duration 
  
Note
Performance statistics help users detect issues early and optimize backup/clone operations.
🛠️ Actions on MariaDB Live Database
From the Actions menu, administrators can:
- Snapshot
- Refresh
- Create Virtual Database
- Create Physical Database
- Mount Snapshot
- Restore
- Disable / Enable
- Delete
Snapshot
Take an on-demand snapshot.




Snapshot-scoped actions include:
- Create Virtual DB
- Create Physical DB
- Mount Snapshot
- Restore
- Delete
Refresh
Refresh DB parameters (no snapshot taken).

Create Virtual Database
Instantly creates a virtual clone up to the last or a specific snapshot.
Create Physical Database
Creates a full physical clone with dedicated storage.
Mount Snapshot
Mounts snapshot content on source environment (for manual inspection/recovery).
When mounting, a staging clone is created. Backups are blocked until snapshot is unmounted.
Restore
Restore live DB from latest or specific snapshot.
Disable / Enable
Toggle availability of the database for backup & cloning.
Delete
Delete the live database from XLServer.

Note
A live DB Server cannot be deleted if clones are still attached. Delete clones first.
✏️ Edit Live Database Settings
- 
Modify settings individually (e.g., quota): 
 
- 
Or update multiple parameters at once: 
  
📅 Manage Snapshots
Snapshots calendar shows all recoverable points.

From snapshot detail, actions include:
- Create Virtual DB
- Create Physical DB
- Mount Snapshot
- Restore
- Delete
🎯 Backup Strategy
Assigning a backup strategy ensures continuous protection.
Assign Backup Strategy

Modify Backup Strategy

Remove Backup Strategy

🔑 Change Database Credentials
Update DB user credentials:

✅ Conclusion
XLServer provides complete lifecycle management for MariaDB and Galera cluster databases:
- Discovery and linking
- Golden image creation
- Incremental backups
- Snapshot-based cloning (virtual & physical)
- Data masking for security
- Performance visibility
This guarantees enterprise-grade protection with flexibility for development, testing, and recovery.
Appendix: Mariadb User (Required Privileges)
To allow XLServer to perform backups, snapshots, and recovery operations, you should create a dedicated Mariadb database user with the correct privileges. This user will be used when linking and snapshot the database in XLServer.
create-mariadb-backup-user.sh
#!/bin/bash
# Script to create MariaDB backup user with full privileges
# Usage: ./create-mariadb-backup-user.sh <username> <password>
set -e  # Exit on any error
# Color codes for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color
# Function to print colored output
print_status() {
    echo -e "${GREEN}[INFO]${NC} $1"
}
print_warning() {
    echo -e "${YELLOW}[WARNING]${NC} $1"
}
print_error() {
    echo -e "${RED}[ERROR]${NC} $1"
}
print_header() {
    echo -e "${BLUE}[HEADER]${NC} $1"
}
# Function to show usage
show_usage() {
    echo "Usage: $0 <username> <password> [options]"
    echo ""
    echo "Options:"
    echo "  -h, --help          Show this help message"
    echo "  -r, --root-user     MariaDB root username (default: root)"
    echo "  -s, --socket        MariaDB socket path (default: /var/lib/mysql/mysql.sock)"
    echo "  -H, --host          MariaDB host (default: localhost)"
    echo "  -P, --port          MariaDB port (default: 3306)"
    echo "  --drop-if-exists    Drop user if it already exists"
    echo "  --dry-run           Show SQL commands without executing them"
    echo ""
    echo "Examples:"
    echo "  $0 backup_user my_secure_password"
    echo "  $0 xlsbackup rachid --drop-if-exists"
    echo "  $0 backup_user password123 --host 127.0.0.1 --port 3306"
    echo "  $0 test_user test_pass --dry-run"
}
# Default values
ROOT_USER="root"
SOCKET="/var/lib/mysql/mysql.sock"
HOST="localhost"
PORT="3306"
DROP_IF_EXISTS=false
DRY_RUN=false
# Parse command line arguments
while [[ $# -gt 0 ]]; do
    case $1 in
        -h|--help)
            show_usage
            exit 0
            ;;
        -r|--root-user)
            ROOT_USER="$2"
            shift 2
            ;;
        -s|--socket)
            SOCKET="$2"
            shift 2
            ;;
        -H|--host)
            HOST="$2"
            shift 2
            ;;
        -P|--port)
            PORT="$2"
            shift 2
            ;;
        --drop-if-exists)
            DROP_IF_EXISTS=true
            shift
            ;;
        --dry-run)
            DRY_RUN=true
            shift
            ;;
        -*)
            print_error "Unknown option: $1"
            show_usage
            exit 1
            ;;
        *)
            if [[ -z "${USERNAME:-}" ]]; then
                USERNAME="$1"
            elif [[ -z "${PASSWORD:-}" ]]; then
                PASSWORD="$1"
            else
                print_error "Too many arguments"
                show_usage
                exit 1
            fi
            shift
            ;;
    esac
done
# Check if required parameters are provided
if [[ -z "${USERNAME:-}" ]]; then
    print_error "Username is required"
    show_usage
    exit 1
fi
if [[ -z "${PASSWORD:-}" ]]; then
    print_error "Password is required"
    show_usage
    exit 1
fi
# Validate username (basic validation)
if [[ ! "$USERNAME" =~ ^[a-zA-Z0-9_]+$ ]]; then
    print_error "Username must contain only alphanumeric characters and underscores"
    exit 1
fi
# Validate password length
if [[ ${#PASSWORD} -lt 6 ]]; then
    print_warning "Password is less than 6 characters. Consider using a stronger password."
fi
print_header "MariaDB Backup User Creation Script"
echo "Username: $USERNAME"
echo "Host: $HOST"
echo "Port: $PORT"
echo "Drop if exists: $DROP_IF_EXISTS"
echo "Dry run: $DRY_RUN"
echo ""
# Create temporary SQL file
TEMP_SQL=$(mktemp /tmp/mariadb_user_XXXXXX.sql)
trap "rm -f $TEMP_SQL" EXIT
# Generate SQL commands
cat > "$TEMP_SQL" << EOF
-- MariaDB Backup User Creation Script
-- Generated on $(date)
-- Username: $USERNAME
EOF
# Add drop commands if requested
if [[ "$DROP_IF_EXISTS" == true ]]; then
    cat >> "$TEMP_SQL" << EOF
-- Drop existing users if they exist
DROP USER IF EXISTS '$USERNAME'@'%';
DROP USER IF EXISTS '$USERNAME'@'localhost';
EOF
fi
# Add user creation and privilege grants
cat >> "$TEMP_SQL" << EOF
-- Create user for remote connections (%)
CREATE USER '$USERNAME'@'%' IDENTIFIED BY '$PASSWORD';
GRANT SELECT ON *.* TO '$USERNAME'@'%';
GRANT INSERT ON *.* TO '$USERNAME'@'%';
GRANT UPDATE ON *.* TO '$USERNAME'@'%';
GRANT DELETE ON *.* TO '$USERNAME'@'%';
GRANT CREATE ON *.* TO '$USERNAME'@'%';
GRANT RELOAD ON *.* TO '$USERNAME'@'%';
GRANT PROCESS ON *.* TO '$USERNAME'@'%';
GRANT LOCK TABLES ON *.* TO '$USERNAME'@'%';
GRANT ALTER ON *.* TO '$USERNAME'@'%';
GRANT SHOW VIEW ON *.* TO '$USERNAME'@'%';
GRANT SHUTDOWN ON *.* TO '$USERNAME'@'%';
GRANT BINLOG MONITOR ON *.* TO '$USERNAME'@'%';
-- Create user for localhost connections
CREATE USER '$USERNAME'@'localhost' IDENTIFIED BY '$PASSWORD';
GRANT SELECT ON *.* TO '$USERNAME'@'localhost';
GRANT INSERT ON *.* TO '$USERNAME'@'localhost';
GRANT UPDATE ON *.* TO '$USERNAME'@'localhost';
GRANT DELETE ON *.* TO '$USERNAME'@'localhost';
GRANT CREATE ON *.* TO '$USERNAME'@'localhost';
GRANT RELOAD ON *.* TO '$USERNAME'@'localhost';
GRANT PROCESS ON *.* TO '$USERNAME'@'localhost';
GRANT LOCK TABLES ON *.* TO '$USERNAME'@'localhost';
GRANT ALTER ON *.* TO '$USERNAME'@'localhost';
GRANT SHOW VIEW ON *.* TO '$USERNAME'@'localhost';
GRANT SHUTDOWN ON *.* TO '$USERNAME'@'localhost';
GRANT BINLOG MONITOR ON *.* TO '$USERNAME'@'localhost';
-- Flush privileges to ensure changes take effect
FLUSH PRIVILEGES;
-- Verify user creation
SELECT 'Users created successfully:' as Status;
SELECT user, host FROM mysql.user WHERE user = '$USERNAME';
EOF
# Show SQL commands if dry run
if [[ "$DRY_RUN" == true ]]; then
    print_header "SQL Commands (Dry Run Mode):"
    cat "$TEMP_SQL"
    print_status "Dry run completed. No changes were made to the database."
    exit 0
fi
# Function to execute SQL
execute_sql() {
    local connection_method="$1"
    print_status "Connecting to MariaDB using $connection_method..."
    if [[ "$connection_method" == "socket" ]]; then
        mariadb -u "$ROOT_USER" -p --socket="$SOCKET" < "$TEMP_SQL"
    else
        mariadb -u "$ROOT_USER" -p -h "$HOST" -P "$PORT" < "$TEMP_SQL"
    fi
}
# Try to connect and execute SQL
print_status "Creating backup user '$USERNAME'..."
if [[ -S "$SOCKET" ]] && [[ "$HOST" == "localhost" ]]; then
    # Try socket connection first for localhost
    if execute_sql "socket"; then
        print_status "Successfully connected via socket"
    else
        print_warning "Socket connection failed, trying TCP..."
        execute_sql "tcp"
    fi
else
    # Use TCP connection
    execute_sql "tcp"
fi
print_status "Backup user '$USERNAME' created successfully!"
print_status "Testing connection..."
# Test the new user connection
if mariadb -u "$USERNAME" -p"$PASSWORD" -h 127.0.0.1 -e "SELECT USER(), CONNECTION_ID();" 2>/dev/null; then
    print_status "User connection test successful!"
else
    print_warning "User connection test failed. Please verify manually."
fi
print_header "Next Steps:"
echo "1. Test the backup user:"
echo "   mariadb -u $USERNAME -p -h 127.0.0.1"
echo ""
echo "2. Use in backup scripts:"
echo "   mysqldump -u $USERNAME -p -h 127.0.0.1 --all-databases > backup.sql"
echo ""
echo "3. For Xtrabackup/MariaBackup:"
echo "   mariabackup --backup --user=$USERNAME --password=PASSWORD --target-dir=/backup/path"
echo ""
echo "4. Create PERCONA_SCHEMA if needed for compatibility:"
echo "   mariadb -u root -p -e \"CREATE DATABASE IF NOT EXISTS PERCONA_SCHEMA;\""
echo "   mariadb -u root -p -e \"GRANT ALL PRIVILEGES ON PERCONA_SCHEMA.* TO '$USERNAME'@'%', '$USERNAME'@'localhost';\""
print_status "Script completed successfully!"