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!"