MariaDB دون ارتجاف

100%

GRIMOIRE❦

الكتاب مدونة البيبي مجلدات التوليف The Foundation of Iron (EN)

RATIO

OPÉRATION DINDON

دليل تقني عملي · Opération Dindon · يونيو 2026 · بالإنجليزية أصلًا

◆◆◆

MariaDB دون ارتجاف
الهجرة والتكرار بأقصى سرعة — XtraBackup · SSH Streaming

◆

◆ ملاحظة على النسخة العربية

هذه الدراسة دليل تقني عملي — سلاسل أوامر دقيقة، وملفّات تهيئة، وقيم مُحدَّدة (عناوين IP، وأسماء الحزم، ومعاملات سطر الأوامر). تُبقيها هذه الطبعة العربية بالإنجليزية الأصلية عمدًا: ترجمة أمر طرفية أو اسم معامل تُفقِده دقّته وتُخاطِر بجعله غير قابل للتنفيذ حرفيًا. النصّ الأصلي هو الأداة القابلة للاستخدام مباشرة — أي إعادة صياغة تُفقِده وزنه العملي.

◆◆◆

أمين غيتي — مهندس بنية تحتية و SRE
أستاذ سابق بمدرسة هندسة · مدرّب بنية تحتية
وثيقة عامة · CC BY-NC-SA 4.0 · Opération Dindon

RATIO

TECHNICAL TUTORIAL · OPÉRATION DINDON · JUNE 2026

◆◆◆

MARIADB
WITHOUT TREMBLING
Migration and Replication at Full Throttle
XtraBackup · SSH Streaming · Multi-TB with Zero Disk Saturation

◆

◆ CONTEXT OF THIS TUTORIAL

Duplicating several terabytes of production data cannot be improvised. If you saturate your disk or lock your tables, you are no longer an engineer — you are a saboteur. This tutorial documents the complete method: direct SSH streaming with no local storage, data preparation, binlog and GTID replication, post-deployment verification. Clean and direct.

VOLUME

Multi-TB

DOWNTIME

Zero

LOCAL STORAGE

Zero

◆◆◆

Amine RAITI — Infrastructure Architect & SRE
Former engineering school professor · Infrastructure instructor
Public document · CC BY-NC-SA 4.0 · AI Powered by Amine · Opération Dindon

RATIO

SECTION 1 · WHY XTRABACKUP — AND NOT THE OTHERS

CHOOSING THE RIGHT STRATEGY BEFORE TOUCHING ANYTHING

◆

◆ MIGRATION STRATEGY COMPARISON

mysqldump / mariadb-dump: simple, universal, human-readable. But it locks tables (or produces inconsistent data without locking), generates a SQL file to reimport line by line, and becomes impractical beyond a few dozen GB. Reserve for small databases or occasional migrations with no time constraint.
LVM snapshot: fast and consistent if the MySQL volume is on LVM. But requires prior LVM configuration, sufficient snapshot space, and a few-second maintenance window for the snapshot.
Galera Cluster (SST/IST): ideal for already-running multi-master clusters. Irrelevant for a one-shot source → target migration.
XtraBackup / Mariabackup: physical hot backup, without locking InnoDB tables, direct streaming to the target, consistency guaranteed by transaction logs. This is the strategy for multi-TB production databases with zero downtime.

⚠️ XTRABACKUP VS MARIABACKUP — DO NOT CONFUSE

From MariaDB 10.2+, Percona XtraBackup is no longer fully compatible. Use Mariabackup — the official MariaDB fork. Commands are nearly identical but the binaries differ: mariabackup instead of xtrabackup. Check version: mariadb --version. This tutorial uses xtrabackup syntax for readability — adapt according to your installed version.

◆ WHY SSH STREAMING

SSH streaming pipes data directly from SOURCE to TARGET without ever writing to the SOURCE disk. Three critical advantages:
— Zero disk saturation on source: a 3TB database does not require 3TB of free space on the source server.
— Real-time transfer: data arrives on the target as it is read, reducing total migration time.
— Single checkpoint: if the transfer fails, restart. No corrupted intermediate file to manage.
Prerequisites: passwordless SSH between the two servers (deployed public key) and sufficient network bandwidth between SOURCE and TARGET.

RATIO

SECTION 2 · PREPARATION — CHECKLIST BEFORE TOUCHING ANYTHING

ALIGNING THE STARS

◆

✅ PRE-MIGRATION CHECKLIST

□ Identical MariaDB versions on SOURCE and TARGET (mariadb --version)
□ Identical XtraBackup/Mariabackup versions on both servers
□ TARGET disk space ≥ SOURCE datafiles size + 20% margin (du -sh /var/lib/mysql)
□ Passwordless SSH from SOURCE to TARGET and TARGET to SOURCE
□ All critical tables are InnoDB (if MyISAM present, see warning below)
□ Binlog enabled on SOURCE (SHOW VARIABLES LIKE 'log_bin')
□ GTID enabled or not — decide replication method before starting
□ Firewall: MySQL port (3306 or custom) open between SOURCE and TARGET

⚠️ MyISAM DANGER — READ BEFORE USING --no-lock

--no-lock is safe ONLY if all tables are InnoDB. A single MyISAM table produces silent inconsistency — the backup will appear to succeed but MyISAM data may be corrupted. Check: SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='your_db' AND ENGINE != 'InnoDB'; If non-empty result: convert to InnoDB or use --lock-ddl-per-table instead of --no-lock.

◆ CREATING USERS ON THE SOURCE

SQL — RUN ON SOURCE

-- User for XtraBackup (physical backup)
CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'SuperStrongPass';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT
  ON *.* TO 'xtrabackup'@'localhost';

-- User for ongoing replication
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'REPL_PASSWORD';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

RATIO

SECTION 3 · SSH STREAMING — ASPIRATION BY VACUUM

WE PIPE DATA DIRECTLY — NOTHING TOUCHES THE SOURCE DISK

◆

◆ THE STREAMING COMMAND — RUN ON TARGET

BASH — RUN ON TARGET (it pulls data from SOURCE)

ssh root@source-db-server "bash -c '
  ulimit -n 1048576
  xtrabackup --backup \
    --user=xtrabackup \
    --password="SuperStrongPass" \
    --socket=/var/run/mysqld/mysql.sock \
    --no-lock \
    --parallel=4 \
    --stream=xbstream
'" | xbstream -x -C /backup/source-db-server

◆ OPTION-BY-OPTION BREAKDOWN

ulimit -n 1048576: raises the maximum number of simultaneously open file descriptors. On large databases with many tables, XtraBackup can hit the system default limit (1024) and fail silently. This line prevents it.
--no-lock: removes the global FLUSH TABLES WITH READ LOCK. Production continues uninterrupted. ONLY safe with 100% InnoDB (see Section 2).
--parallel=4: number of parallel read threads. Adjust based on available CPUs on SOURCE. Start at 4, increase if network bandwidth is not the bottleneck.
--stream=xbstream: XtraBackup proprietary streaming format, more efficient than tar for MySQL files.
--socket: path to the MariaDB Unix socket. Varies by distribution: /var/run/mysqld/mysql.sock (Debian/Ubuntu) or /tmp/mysql.sock (CentOS/RHEL). Verify: SHOW VARIABLES LIKE 'socket';
xbstream -x -C /backup/source-db-server: stream extraction on TARGET into the target directory. Ensure it exists and has sufficient space.

✅ MONITOR THE TRANSFER IN PROGRESS

# On TARGET — monitor progress
watch -n 5 "du -sh /backup/source-db-server"

# On SOURCE — monitor XtraBackup process
tail -f /var/log/mysql/xtrabackup.log

RATIO

SECTION 4 · PREPARE AND RESTORE — THE METAMORPHOSIS

MAKING DATA LIVE — THEN INJECTING IT

◆

◆ STEP 1 — PREPARE: TRANSACTION LOG CONSOLIDATION

The physical backup captured in a live stream contains potentially inconsistent data pages — transactions open at capture time. --prepare applies the redo log and rolls back incomplete transactions, making the backup consistent and restorable. This step is mandatory. Without it, MariaDB will refuse to start on the restored files.

BASH — RUN ON TARGET

# Consolidation — makes the backup consistent
xtrabackup --prepare --target-dir=/backup/source-db-server

# Verify preparation completed without error
echo $? # must return 0

◆ STEP 2 — RESTORE: DEPLOYMENT ON TARGET

⚠️ STOP MARIADB ON TARGET BEFORE RESTORE

systemctl stop mariadb — The data directory must be empty. Never restore onto a running MariaDB instance.

BASH — RUN ON TARGET

# Empty the data directory (IRREVERSIBLE — double-check)
rm -rf /var/lib/mysql/*

# Restore files from the prepared backup
xtrabackup --copy-back   --target-dir=/backup/source-db-server   --datadir=/var/lib/mysql

# Fix permissions (MANDATORY)
chown -R mysql:mysql /var/lib/mysql

# Start MariaDB
systemctl start mariadb
systemctl status mariadb

◆ NASSIHA — DO NOT FORGET PERMISSIONS

XtraBackup restores files with the permissions of the user running the command (often root). Without chown -R mysql:mysql, MariaDB will refuse to start with a permissions error. This is the most frequent mistake at this stage.

RATIO

SECTION 5 · REPLICATION — THE SACRED LINK

CONNECTING SOURCE AND TARGET — TWO METHODS

◆

◆ RETRIEVING REPLICATION COORDINATES

XtraBackup created the file xtrabackup_binlog_info in the backup directory. It contains the exact binlog position at capture time.

BASH — READ COORDINATES

cat /backup/source-db-server/xtrabackup_binlog_info
# Example output:
mysql-bin.000123    456789    0-1-12345
# Column 1: binlog file
# Column 2: position (classic method)
# Column 3: GTID position (if GTID enabled)

◆ METHOD A — CLASSIC BINLOG (WITHOUT GTID)

SQL — RUN ON TARGET

STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='source-db-server',
  MASTER_PORT=3306,
  MASTER_USER='repl_user',
  MASTER_PASSWORD='REPL_PASSWORD',
  MASTER_LOG_FILE='mysql-bin.000123',
  MASTER_LOG_POS=456789;
START SLAVE;

◆ METHOD B — GTID (RECOMMENDED MARIADB 10.2+)

More robust — survives restarts and failovers without manual position reconfiguration.

SQL — RUN ON TARGET

STOP SLAVE;
-- Inject GTID position from xtrabackup_binlog_info (3rd column)
SET GLOBAL gtid_slave_pos = '0-1-12345';
CHANGE MASTER TO
  MASTER_HOST='source-db-server',
  MASTER_PORT=3306,
  MASTER_USER='repl_user',
  MASTER_PASSWORD='REPL_PASSWORD',
  MASTER_USE_GTID=slave_pos;
START SLAVE;

RATIO

SECTION 6 · VERIFICATION — DO NOT LEAVE BEFORE CONFIRMING

SHOW SLAVE STATUS — FULL BREAKDOWN

◆

◆ THE VERIFICATION COMMAND

SQL — RUN ON TARGET

SHOW SLAVE STATUS\G

Slave_IO_Running: Yes — the I/O thread is connected to SOURCE and reading the binlog. If No: network issue or incorrect credentials.
Slave_SQL_Running: Yes — the SQL thread is applying events on TARGET. If No: SQL error, check Last_SQL_Error.
Seconds_Behind_Master: 0 — TARGET is synchronised. A non-zero value indicates replication lag — normal just after start, must converge to 0.
Last_IO_Error / Last_SQL_Error — if non-empty: read and address before continuing.

⚠️ COMMON ERRORS AND FIXES

Error 1236 — Could not find first log file: the binlog position in xtrabackup_binlog_info is stale (binlogs have been purged on SOURCE). Fix: purge_logs_before or redo the backup.
Error 1062 — Duplicate entry: a row already exists on TARGET. If non-critical: SET GLOBAL slave_skip_errors = 1062; and restart the slave. Investigate the root cause.
Error 2003 — Can't connect to MySQL server: check the firewall on SOURCE, bind-address in my.cnf (bind-address = 0.0.0.0 or TARGET IP).
Slave_IO_Running: Connecting: incorrect credentials or repl_user not authorised from TARGET IP. Check SHOW GRANTS FOR 'repl_user'@'%';

✅ POST-DEPLOYMENT MONITORING

# Monitor lag catchup in real time
watch -n 2 "mysql -e 'SHOW SLAVE STATUS\G' | grep -E 'Running|Behind|Error'"

# Verify consistency of a table between SOURCE and TARGET
# On SOURCE:
SELECT COUNT(*), MAX(id) FROM my_table;
# On TARGET (after Seconds_Behind_Master = 0):
SELECT COUNT(*), MAX(id) FROM my_table;

◆◆◆

If you saturate the disk or lock the tables, you are no longer an engineer — you are a saboteur. This strategy avoids both. Clean and direct.

وَرَبُّنَا الرَّحْمَٰنُ الْمُسْتَعَانُ عَلَىٰ مَا يَصِفُونَ