Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
For most Ubuntu servers, use mariadb-dump for a portable, straightforward backup. Use mariadb-backup instead when the database is large, remains online during backup, or requires faster restores. In either case, store backups outside MariaDB’s data directory, copy them off the server, and perform a test restore.
This guide covers single-database recovery, full-server backups, physical backups, incremental backups, binary-log point-in-time recovery, automation, and the failures that commonly make an apparently successful backup unusable.
Choose the right MariaDB backup method
Your choice depends mainly on database size, acceptable downtime, and restore speed. MariaDB describes logical backups as SQL statements and physical backups as copies of database files. Logical backups are generally more portable; physical backups are usually more operationally complex and more dependent on compatible versions and storage engines. See the MariaDB backup and restore overview.
| Method | Tool | Best for | Trade-offs |
|---|---|---|---|
| Logical | mariadb-dump |
Small and medium databases, migrations, table-level recovery | Large dumps can be slow to create and replay |
| Physical | mariadb-backup |
Large or production databases and shorter recovery times | Requires prepare and restore steps; compatibility matters |
| Binary logs | mariadb-binlog |
Point-in-time recovery after a base backup | Requires binary logging, retention, and careful log handling |
| Filesystem snapshot | LVM, ZFS, or cloud volume tools | Advanced infrastructure | Must be made database-consistent and depends on the storage platform |
Do not treat copying /var/lib/mysql while MariaDB is running as a general-purpose backup. The files may not represent a consistent recovery point.
#1 Best Overall
Understand what you are backing up
A database backup is not automatically a complete server disaster-recovery plan. Decide whether you need:
- One database, several named databases, or every database.
- Tables and rows.
- Stored procedures and functions.
- Triggers and Event Scheduler events.
- Users, grants, and roles.
- Binary logs for point-in-time recovery.
- MariaDB configuration in
/etc/mysql. - TLS certificates, encryption keys, custom plugins, AppArmor rules, and systemd overrides.
- Application files, environment variables, and deployment configuration.
mariadb-dump --all-databases includes all databases in the dump, but operational files still need separate treatment. INFORMATION_SCHEMA and performance_schema are not dumped by default; see the Ubuntu mariadb-dump manpage.
Check Ubuntu and MariaDB first
lsb_release -ds
mariadb --version
systemctl status mariadb --no-pager
sudo mariadb -e "SELECT VERSION();"If the dump client is missing, install it from Ubuntu’s repositories or your configured MariaDB repository:
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemssudo apt update
sudo apt install mariadb-clientFor physical backups, install the package that supplies mariadb-backup:
sudo apt update
sudo apt install mariadb-backupPackage names and versions vary by Ubuntu release and repository. Do not mix client and backup utilities from arbitrary releases without checking compatibility. The Ubuntu Noble mariadb-backup manpage documents the package for Ubuntu 24.04, but its displayed package version is release-specific.
Create a secure backup location
Keep backups outside /var/lib/mysql, and do not store the only copy on the same disk as the database.
sudo install -d -m 0700 -o root -g root /var/backups/mariadbThe directory should not be publicly accessible or readable by every local user. Completed backups should also be copied to a separate host, disk, region, or object-storage account. Consider encryption, retention generations, immutable storage, and monitoring.
Recommended Free Tools
Never put a password directly in a command such as mariadb-dump -uroot -pMyPassword. It can enter shell history or appear in process listings. Prefer Unix-socket authentication where appropriate, a protected MariaDB option file, or a secret-handling method suitable for your automation system.
Rank #2
- 12th Intel Alder Lake N95 Processor – The GMKtec G3 S Mini PC is powered by the 12th Gen Intel N95 processor with 4 cores, 4 threads, 6MB cache and a burst frequency up to 3.4GHz. Compared with N100/N5105/N5100/N5095, the N95 delivers up to 36% overall performance improvement. Perfect for routine tasks, office work, and home entertainment, this compact mini desktop is more convenient than traditional bulky PCs.
- 8GB RAM & 256GB SSD Storage – Pre-installed with 8GB DDR4 memory and a fast 256GB M.2 2242 SSD, the G3 S mini desktop offers quicker startup, smoother multitasking, and faster file transfers. Enjoy seamless performance whether you’re working on multiple applications, browsing, or streaming content.
- Rich Interfaces & Connectivity – The G3 S mini computer comes equipped with USB 3.2 (up to 10Gbps), dual HDMI 2.0 (4K@60Hz), and a 3.5mm audio jack. With support for WiFi 5, Bluetooth 5.0, and Gigabit Ethernet (RJ45 1000MbE), it connects easily with monitors, projectors, printers, office equipment, and other peripherals, making it versatile for both home and business use.
- Dual 4K Display Support – Featuring upgraded Intel UHD Graphics (up to 1000MHz), the G3 S supports 4K video playback and AV1 decoding for a smooth viewing experience. With dual HDMI outputs, you can connect two 4K@60Hz displays simultaneously, enabling efficient multitasking for work and entertainment.
- GMKtec WARRANTY - GMKtec offers a 1-year limited GMKtec's warranty for each mini PC, starting from the date of the purchase. All defects due to design and workmanship are covered. With a professional after sales team always ready to attend to your needs, you can simply relax and enjoy your mini PC.
Back up one database with mariadb-dump
This is the recommended starting point for most Ubuntu installations:
sudo install -d -m 0700 /var/backups/mariadb
sudo mariadb-dump
--single-transaction
--routines
--events
--triggers
--hex-blob
--databases appdb
| gzip > /var/backups/mariadb/appdb-$(date +%F-%H%M%S).sql.gzThe options have specific purposes:
--single-transactiontakes a consistent transaction snapshot for transactional tables such as InnoDB without holding ordinary table locks for the whole dump.--routinesincludes stored procedures and functions.--eventsincludes Event Scheduler events.--triggersmakes the intended trigger coverage explicit; triggers are included by default.--hex-blobrepresents binary columns safely.--databases appdbadds database creation andUSEstatements.gzipreduces storage use, at the cost of decompression during restore.
--single-transaction is primarily an InnoDB technique. It does not make MyISAM or other nontransactional tables consistent while they are being modified, and concurrent DDL can still affect a dump. MariaDB documents these limitations in the mariadb-dump documentation.
Back up all databases
sudo mariadb-dump
--single-transaction
--routines
--events
--triggers
--hex-blob
--all-databases
| gzip > /var/backups/mariadb/all-databases-$(date +%F-%H%M%S).sql.gzFor a broader server-recovery package, separately record configuration and installed packages:
sudo tar -czf
/var/backups/mariadb/mariadb-config-$(date +%F-%H%M%S).tar.gz
/etc/mysql
/etc/systemd/system/mariadb.service.d 2>/dev/null || true
dpkg-query -W 'mariadb*' >
/var/backups/mariadb/mariadb-packages-$(date +%F-%H%M%S).txtConfiguration archives may contain credentials, certificates, or key material. Protect them like the database backup.
Restore a logical backup
Restore a compressed dump made with --databases
gzip -dc /var/backups/mariadb/appdb-YYYY-MM-DD-HHMMSS.sql.gz
| sudo mariadbIf the dump contains only table and row statements and the target database already exists, specify it explicitly:
gzip -dc /path/to/backup.sql.gz | sudo mariadb appdbMariaDB’s basic pattern is to create a dump with mariadb-dump db_name > backup-file.sql and reload it with mariadb db_name < backup-file.sql.
Restore into a new database
sudo mariadb -e "CREATE DATABASE appdb_restore CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
gzip -dc /path/to/appdb.sql.gz | sudo mariadb appdb_restoreUse the source character set and collation unless you intentionally want to change them. Restoring to a new name is safer when you need to compare data before replacing the live database.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Restore all databases
gzip -dc /path/to/all-databases.sql.gz | sudo mariadbBefore overwriting an existing installation, stop the application, make a fresh backup of the current state, and inspect whether the dump contains DROP DATABASE, DROP TABLE, users, grants, routines, and events. Do not make destructive drops the default recovery step.
Verify the restore
sudo mariadb -e "SHOW DATABASES;"
sudo mariadb appdb_restore -e "SHOW TABLES;"
sudo mariadb appdb_restore -e "CHECK TABLE some_table;"Also perform application-level checks: log in, test representative reads and writes, check important row counts, verify routines, triggers, events, permissions, and record how long the restore took. A successful pipeline exit does not prove that the application can use the restored data.
Use mariadb-backup for larger production databases
mariadb-backup is the current MariaDB name; older material may call it mariabackup. It creates physical backups, supports online backups, and supports incremental InnoDB backups. It is a better fit when replaying a large SQL dump would exceed your recovery-time objective, but it requires a stricter workflow. See MariaDB’s full backup and restore documentation.
Create a full physical backup
sudo install -d -m 0700 -o mysql -g mysql /var/backups/mariadb/full
sudo mariadb-backup
--backup
--target-dir=/var/backups/mariadb/full
--user=mariadb_backupUse a dedicated backup account with the privileges required by your MariaDB version and enabled features. Do not assume one universal privilege grant is correct for every release. Use a protected option file when automation cannot use an interactive password prompt.
Free tools Windows power users keep installed
One-click scans. No signup required.
The target directory must be empty or nonexistent. Check that the command completed successfully and that the expected backup files exist.
Prepare the backup
sudo mariadb-backup
--prepare
--target-dir=/var/backups/mariadb/fullDo not copy an unprepared physical backup directly into the data directory. The prepare phase applies recovery information and makes the files suitable for restoration. MariaDB recommends preparing with the same mariadb-backup version used to create the backup.
Restore the physical backup
First stop MariaDB and preserve the current data directory:
sudo systemctl stop mariadb
sudo mv /var/lib/mysql /var/lib/mysql.before-restore
sudo install -d -o mysql -g mysql -m 0750 /var/lib/mysql
sudo mariadb-backup
--copy-back
--target-dir=/var/backups/mariadb/full
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mariadbThe restore directory must be empty. Keep /var/lib/mysql.before-restore until the restored server has been checked. MariaDB documents this stop, empty-directory, copy-back, ownership, and restart sequence. --move-back moves rather than copies the files, which can save space but consumes the backup artifact.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →If the service does not start, inspect journalctl, ownership, disk space, the configured datadir, AppArmor rules, MariaDB version, and any encryption keys or custom plugins required by the original server.
Rank #4
- OFFICE LIGHT GAMING MINI PC - GMKtec Nucbox G10 Series is equipped with the Ryzen 5 3500U, a 64-bit quad-core mid-range performance x86 mobile microprocessor. This processor is based on AMD's Zen+ microarchitecture and is fabricated on a 12 nm process. The 3500U operates at a base frequency of 2.1 GHz with a TDP of 15 W and a Boost frequency of 3.7 GHz. This APU supports up to 32 GB of dual-channel DDR4-2400 memory and incorporates Radeon Vega 8 Graphics operating at up to 1.2 GHz. 35% Performance increase over the similar Intel N-Series N150/N100/N97/N95 processor chips
- 16GB DDR4 + 1TB SSD - Installed with DDR4 16GB SO-DIMM RAM and a 1TB SSD, the Nucbox G10 mini pc supports memory expansion to 64GB RAM. Featured with Dual M.2 2280 PCIe 3.0 slots, supports dual storage slot expansion to 16TB SSD (2*8TB). (Upgrades not included) This model supports a configurable TDP-down of 12 W and TDP-up of 35 W
- 2.5GBE ETHERNET FAST NETWORK SPEEDS - Enjoy up to 2500Mbps data transmission speed without worrying about lagging. Ideal for working, gaming, and surfing the internet. Great for Untangle, Pfsense or as a server office PC
- MINI DESKTOP COMPUTER WITH TRIPLE DISPLAY SCREEN - Nucbox G10 integrates AMD Radeon Vega 8 1200 MHz GPU to deliver powerful graphics processing power to easily handle video editing, and playback, or casual gaming. And it can connect to 3 display screens simultaneously via HDMI 2.1 TMDS/ DPv1.4/ TYPE-C
- FAST WIRELESS INTERNET WIFI 5 + BT5.0 - Enjoy blazing WiFi 5 & Bluetooth 5.0 alongside a powerhouse selection of ports - dual USB 3.2, USB 2.0, stunning 4K@60Hz HDMI 2.1 TMDS, Full Function USB-C (PD/DP/Data), dedicated DisplayPort, 3.5mm audio, and PD Power Supply for seamless multitasking and premium connectivity
Incremental physical backups
An incremental backup contains changes since a base backup and cannot be restored independently. A typical incremental command is:
sudo mariadb-backup
--backup
--target-dir=/var/backups/mariadb/inc-01
--incremental-basedir=/var/backups/mariadb/full
--user=mariadb_backupTo restore an incremental chain, prepare the base backup and apply each incremental backup in order according to MariaDB’s version-specific procedure. Retain the complete chain: losing one required incremental can make later increments unusable. Test the entire chain, not only the newest directory.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Point-in-time recovery with binary logs
Point-in-time recovery is a combined process, not a feature of a dump file by itself. You need a base backup, binary logging enabled on the source, every required binary-log file after the base position, and a carefully selected recovery time or position.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
For a logical base dump, record binary-log coordinates:
sudo mariadb-dump
--all-databases
--single-transaction
--routines
--events
--triggers
--master-data=2
| gzip > /var/backups/mariadb/base-$(date +%F-%H%M%S).sql.gz--master-data=2 records replication coordinates as a comment. It does not create point-in-time recovery unless the corresponding binary logs are retained and replayable.
For a physical backup, inspect the recorded position:
cat /var/backups/mariadb/full/xtrabackup_binlog_infoAfter restoring the base backup, extract changes up to the desired point:
mariadb-binlog
--start-position=POSITION
--stop-datetime="2026-08-18 14:30:00"
/path/to/mariadb-bin.000001
/path/to/mariadb-bin.000002
> /tmp/roll-forward.sql
sudo mariadb < /tmp/roll-forward.sqlAdapt the filenames, position, GTID handling, and time zone to your server. A timestamp alone does not guarantee the exact business recovery point: transaction boundaries, application behavior, and event ordering matter. See MariaDB’s physical backup and recovery guidance.
Automate logical backups safely
Use a script rather than placing a long pipeline directly in cron. This example creates an atomic output file and removes files older than 14 days:
#!/usr/bin/env bash
set -Eeuo pipefail
BACKUP_DIR=/var/backups/mariadb
STAMP=$(date -u +%Y%m%dT%H%M%SZ)
TMP="$BACKUP_DIR/.all-$STAMP.sql.gz.tmp"
OUT="$BACKUP_DIR/all-$STAMP.sql.gz"
umask 077
mariadb-dump
--single-transaction
--routines
--events
--triggers
--hex-blob
--all-databases
| gzip -n > "$TMP"
mv "$TMP" "$OUT"
find "$BACKUP_DIR" -type f -name 'all-*.sql.gz' -mtime +14 -deleteMake the script executable, run it from a systemd timer or cron, and log failures. set -Eeuo pipefail prevents a failed dump from being mistaken for a successful compressed file. The temporary filename prevents an incomplete output from looking like a finished backup. Use UTC timestamps, monitor free space, copy successful files off-host, and choose retention based on your recovery-point objective, legal requirements, storage cost, and incident types.
Test every backup
- Choose a disposable VM, container, or isolated MariaDB host with a compatible version.
- Copy the backup without changing its contents.
- Restore it using the same documented procedure used during an incident.
- Check databases, tables, routines, triggers, events, users, grants, and important row counts.
- Run a representative application read and write.
- For physical backups, test the complete prepare and copy-back process.
- For point-in-time recovery, replay the base backup and binary logs to a known timestamp.
- Record restore duration and update the recovery procedure when it changes.
A backup that has never been restored is an unverified assumption. Replication is not a substitute: a replica can reproduce an accidental deletion, corruption, or bad migration.
Troubleshoot common failures
Authentication fails
Ubuntu installations may use Unix-socket authentication for the MariaDB root account. Check the account identity and client defaults:
sudo mariadb -e "SELECT USER(), CURRENT_USER();"
mariadb --print-defaults
mariadb-dump --helpAlso check whether the command is using the expected socket or host, whether the operating-system user is correct, and whether the backup account has the required privileges. Avoid using --skip-grant-tables except in a controlled emergency recovery procedure.
The dump restores but application features are missing
Verify that the dump included --routines, --events, and --triggers. Check users, grants, and roles separately; a restored schema can still be unusable if permissions were not restored.
MyISAM or mixed engines cause consistency problems
Inventory storage engines:
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'sys')
ORDER BY ENGINE, TABLE_SCHEMA, TABLE_NAME;For nontransactional tables, plan maintenance downtime, appropriate locking, or a physical backup strategy. Do not describe --single-transaction as a universal consistency guarantee.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchLarge dumps fail or report “server has gone away”
mariadb-dump
--single-transaction
--quick
--max-allowed-packet=1G
...--quick reads rows incrementally. A 1 GB packet limit is only an illustration: client and server limits, available memory, and unusually large rows must be considered together.
Physical restore will not start
sudo systemctl status mariadb --no-pager
sudo journalctl -u mariadb -b --no-pager
sudo ls -ld /var/lib/mysql
sudo find /var/lib/mysql -maxdepth 1 -printf '%u:%g %pn'Common causes include wrong ownership, a nonempty or partially restored data directory, incompatible versions, an incorrect datadir, AppArmor restrictions, insufficient disk space, missing encryption keys, or missing configuration.
Operational backup checklist
- Choose logical or physical backup according to restore requirements.
- Include routines, events, triggers, and required users and grants.
- Keep backups outside
/var/lib/mysqlwith restrictive permissions. - Check the command’s exit status and confirm the output is readable.
- Record file size or checksums.
- Copy completed backups off the Ubuntu host.
- Retain multiple generations and protect at least one copy from deletion or ransomware.
- Preserve binary logs if point-in-time recovery is required.
- Test restoration on an isolated system.
- Monitor backup failures, disk space, off-site-copy failures, and restore duration.
For small installations, a compressed, tested mariadb-dump copied to encrypted off-site storage is often sufficient. Larger databases may justify mariadb-backup and incremental chains. Paid object storage or backup software is optional; it does not replace consistent backups, encryption, retention, monitoring, and tested restores.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
