MariaDB mysqldump guide — Using mysqldump for MariaDB database backups

The mysqldump utility is one of the most reliable tools for creating logical backups of MariaDB databases. As MariaDB maintains strong compatibility with MySQL, mysqldump works seamlessly across both database systems while offering specific optimizations for MariaDB environments. This guide covers everything you need to know about using mysqldump for MariaDB backup operations, from basic commands to advanced techniques that ensure data integrity and efficient storage management.

Understanding mysqldump basics

The mysqldump command-line utility creates logical backups by generating SQL statements that can recreate your database structure and data. Unlike physical backup tools that copy raw data files, mysqldump produces portable output that works across different MariaDB versions and even allows migration between database systems. This flexibility makes it an essential tool for database administrators managing MariaDB installations of any size.

Logical backups generated by mysqldump are human-readable SQL files containing CREATE TABLE statements, INSERT commands and other DDL/DML operations. This format allows you to inspect backup contents, selectively restore specific tables and modify data before restoration if needed. However, logical backups are generally slower to create and restore compared to physical backup methods.

mysqldump characteristics:

• Backup type: Logical (SQL statements)
• Portability: High
• Speed: Moderate for small databases
• Table locking: Configurable
• Compression: External tools required

Understanding these characteristics helps you determine when mysqldump is the right choice for your backup strategy. For databases under 10GB, mysqldump typically provides sufficient performance while offering maximum flexibility.

Creating basic MariaDB backups with mysqldump

Creating a backup with mysqldump requires minimal configuration. The basic syntax connects to your MariaDB server, reads the database contents and outputs SQL statements to a file or stdout. Authentication can be provided via command-line arguments, configuration files or environment variables.

To create a backup of a single database:

mysqldump -u root -p mydatabase > mydatabase_backup.sql

The -u flag specifies the username, -p prompts for a password and the database name follows. Output redirects to a file using the shell's redirection operator. For automated scripts, you can provide the password directly (though this is less secure):

mysqldump -u root -pYourPassword mydatabase > mydatabase_backup.sql

To backup multiple databases in a single operation:

mysqldump -u root -p --databases db1 db2 db3 > multiple_databases.sql

The --databases flag includes CREATE DATABASE statements in the output, making the backup self-contained. Without this flag, you must create the target database manually before restoration.

For backing up all databases on your MariaDB server:

mysqldump -u root -p --all-databases > all_databases_backup.sql

This command captures every database including system databases like mysql and information_schema. Use this approach for complete server migrations or disaster recovery scenarios.

Essential mysqldump options for MariaDB

The mysqldump utility offers numerous options that control backup behavior, data consistency and output format. Understanding these options helps you create backups suited to your specific requirements. Some options are particularly important for production environments where data integrity cannot be compromised.

Recommended options for InnoDB tables:

Option	Purpose
--single-transaction	Creates consistent backup without locking
--routines	Includes stored procedures and functions
--triggers	Includes trigger definitions
--events	Includes scheduled events
--quick	Retrieves rows one at a time (memory efficient)

The --single-transaction option is critical for InnoDB tables, which are the default storage engine in modern MariaDB versions. It starts a transaction before reading data, ensuring a consistent snapshot without blocking other database operations:

mysqldump -u root -p --single-transaction --routines --triggers mydatabase > backup.sql

For MyISAM tables or mixed storage engines, use --lock-tables instead:

mysqldump -u root -p --lock-tables mydatabase > backup.sql

This option locks all tables during the backup, preventing modifications but ensuring consistency. For large databases with MyISAM tables, consider scheduling backups during low-traffic periods.

To include additional database objects often forgotten in backups:

mysqldump -u root -p --single-transaction --routines --triggers --events mydatabase > complete_backup.sql

The --events flag captures scheduled events that might otherwise be lost during restoration.

Compressing mysqldump output

Backup files can grow large quickly, consuming valuable storage space and increasing transfer times. Compressing mysqldump output significantly reduces file sizes, often achieving 70-90% compression ratios for typical database content. MariaDB's SQL output compresses exceptionally well due to repetitive patterns in INSERT statements.

To compress backups using gzip:

mysqldump -u root -p --single-transaction mydatabase | gzip > mydatabase_backup.sql.gz

For better compression ratios with slightly slower speed, use bzip2:

mysqldump -u root -p --single-transaction mydatabase | bzip2 > mydatabase_backup.sql.bz2

Modern systems can use zstd for an excellent balance of speed and compression:

mysqldump -u root -p --single-transaction mydatabase | zstd > mydatabase_backup.sql.zst

Decompressing during restoration is equally straightforward:

gunzip < mydatabase_backup.sql.gz | mysql -u root -p mydatabase

Or with zcat for a more concise command:

zcat mydatabase_backup.sql.gz | mysql -u root -p mydatabase

Compression becomes essential when storing backups on cloud storage or transferring them across networks.

Backing up specific tables

Sometimes you need to backup only specific tables rather than entire databases. This approach is useful for archiving historical data, creating development datasets or backing up frequently changing tables more often than static ones. mysqldump supports table-level granularity through simple command-line arguments.

To backup specific tables from a database:

mysqldump -u root -p mydatabase table1 table2 table3 > selected_tables.sql

Tables are listed after the database name without any special flags. The output contains only the specified tables' structure and data.

For backing up table structure without data (useful for schema documentation or creating empty copies):

mysqldump -u root -p --no-data mydatabase > schema_only.sql

Conversely, to backup data without structure definitions:

mysqldump -u root -p --no-create-info mydatabase > data_only.sql

This option is helpful when you need to reload data into existing tables without modifying their structure.

To exclude specific tables from a database backup:

mysqldump -u root -p --ignore-table=mydatabase.logs --ignore-table=mydatabase.sessions mydatabase > backup_without_logs.sql

The --ignore-table option requires the full database.table format and can be repeated for multiple tables.

Restoring MariaDB backups

Restoration is the critical counterpart to backup creation. A backup has no value if you cannot restore it successfully when needed. mysqldump backups restore through the mysql client, which executes the SQL statements contained in the backup file. The restoration process recreates database objects and inserts data in the order specified by the backup.

To restore a backup to an existing database:

mysql -u root -p mydatabase < mydatabase_backup.sql

If the backup was created with --databases or --all-databases, the database creation statements are included:

mysql -u root -p < all_databases_backup.sql

For compressed backups, decompress and pipe directly to mysql:

gunzip < mydatabase_backup.sql.gz | mysql -u root -p mydatabase

To restore to a different database name than the original:

mysql -u root -p newdatabase < mydatabase_backup.sql

This requires the backup to not include CREATE DATABASE statements (created without --databases flag).

For large restorations, disable foreign key checks temporarily to speed up the process:

mysql -u root -p -e "SET FOREIGN_KEY_CHECKS=0; SOURCE /path/to/backup.sql; SET FOREIGN_KEY_CHECKS=1;"

Always verify your restoration by checking table counts and running application-level validation queries.

Automating mysqldump backups

Manual backup execution is error-prone and often forgotten during busy periods. Automating your backup process ensures consistent protection without relying on human memory. Linux systems use cron for scheduling, while the automation approach depends on your infrastructure and monitoring requirements.

Create a backup script that handles compression, naming and cleanup:

#!/bin/bash
BACKUP_DIR="/var/backups/mariadb"
DATE=$(date +%Y%m%d_%H%M%S)
DATABASE="mydatabase"

mysqldump -u backup_user -pSecurePassword123 \
  --single-transaction \
  --routines \
  --triggers \
  "$DATABASE" | gzip > "$BACKUP_DIR/${DATABASE}_${DATE}.sql.gz"

# Remove backups older than 7 days
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +7 -delete

Schedule this script with cron for daily execution:

0 3 * * * /usr/local/bin/backup_mariadb.sh >> /var/log/mariadb_backup.log 2>&1

This runs the backup at 3 AM daily and logs output for monitoring.

For more sophisticated automation with multiple storage destinations, encryption and notifications, consider using dedicated backup tools like Databasus that handle these requirements without custom scripting.

Using Databasus for MariaDB backups

While mysqldump provides reliable backup functionality, managing backups across multiple databases, storage destinations and schedules requires significant scripting effort. Databasus is a free, open source and self-hosted backup solution that automates the entire backup workflow for MariaDB databases. It provides a web interface for configuration, supports multiple storage backends and sends notifications on backup completion or failure.

To install Databasus using Docker:

docker run -d \
  --name databasus \
  -p 4005:4005 \
  -v ./databasus-data:/databasus-data \
  --restart unless-stopped \
  databasus/databasus:latest

After installation, access the dashboard at http://localhost:4005 and follow these steps to create your first MariaDB backup:

1. Add your database: Click "New Database" and enter your MariaDB connection details including host, port, username and password
2. Select storage: Choose where to store backups — local storage, S3, Google Drive, Dropbox, SFTP or other supported destinations
3. Select schedule: Configure backup frequency — hourly, daily, weekly, monthly or custom cron expression
4. Click "Create backup": Databasus validates your settings and begins the backup schedule

Databasus uses AES-256-GCM encryption for backup files, ensuring your data remains secure even when stored on shared cloud storage. The platform supports MariaDB versions 10 and 11, handles compression automatically and provides detailed logs for troubleshooting backup issues.

Handling large databases

Large databases present unique challenges for mysqldump. Backup duration increases linearly with data volume, and memory consumption can become problematic without proper configuration. Several techniques help manage large database backups effectively while maintaining data consistency.

Strategies for large databases:

Strategy	Benefit	Trade-off
--quick option	Reduces memory usage	Slightly slower
Parallel table dumps	Faster backup time	More complex scripting
Compression	Smaller files	CPU overhead
Incremental approach	Reduced backup window	Requires binary logs

The --quick option retrieves rows one at a time rather than buffering the entire result set in memory:

mysqldump -u root -p --single-transaction --quick mydatabase > backup.sql

For very large databases, consider backing up tables in parallel using multiple mysqldump processes:

mysqldump -u root -p --single-transaction mydatabase table1 | gzip > table1.sql.gz &
mysqldump -u root -p --single-transaction mydatabase table2 | gzip > table2.sql.gz &
wait

This approach requires careful coordination to maintain consistency across tables with foreign key relationships.

When mysqldump becomes impractical due to database size (typically above 50-100GB), consider physical backup tools like Mariabackup that copy data files directly rather than generating SQL statements.

Security considerations

Backup files contain your complete database contents, making them attractive targets for attackers. Protecting backup files requires attention to storage permissions, network transfer security and credential management. A security breach through backup files can be just as damaging as a direct database compromise.

Store credentials in a configuration file rather than command-line arguments:

# ~/.my.cnf
[mysqldump]
user=backup_user
password=SecurePassword123

Set restrictive permissions on the configuration file:

chmod 600 ~/.my.cnf

Then run mysqldump without exposing credentials:

mysqldump --single-transaction mydatabase > backup.sql

Encrypt backup files before storing them on shared or cloud storage:

mysqldump -u root -p mydatabase | gzip | openssl enc -aes-256-cbc -salt -pbkdf2 > backup.sql.gz.enc

To decrypt and restore:

openssl enc -d -aes-256-cbc -pbkdf2 -in backup.sql.gz.enc | gunzip | mysql -u root -p mydatabase

Create a dedicated backup user with minimal required privileges:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'SecurePassword123';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, PROCESS ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

This user can read all data for backups but cannot modify anything, limiting potential damage from compromised credentials.

Troubleshooting common issues

Even well-configured backup systems encounter problems occasionally. Understanding common mysqldump errors and their solutions helps you resolve issues quickly and maintain backup reliability. Most problems relate to permissions, connectivity or resource constraints.

"Access denied" errors typically indicate incorrect credentials or insufficient privileges. Verify the user can connect and has SELECT permission on target tables:

mysql -u backup_user -p -e "SELECT 1 FROM mydatabase.mytable LIMIT 1;"

"Lock wait timeout exceeded" occurs when mysqldump cannot acquire necessary locks. For InnoDB tables, ensure you're using --single-transaction. For MyISAM tables, schedule backups during low-activity periods.

"Out of memory" errors happen when mysqldump buffers large result sets. Add the --quick option to stream results instead of buffering:

mysqldump -u root -p --quick mydatabase > backup.sql

"Got packet bigger than max_allowed_packet" indicates rows exceeding the packet size limit. Increase the limit temporarily:

mysqldump -u root -p --max_allowed_packet=512M mydatabase > backup.sql

Slow backup performance often results from missing indexes on large tables or network latency for remote databases. For remote servers, consider running mysqldump on the database server itself and transferring the compressed file afterward.

Conclusion

The mysqldump utility remains an essential tool for MariaDB database backups, offering reliability, portability and flexibility that physical backup methods cannot match. Understanding its options and best practices enables you to create backup strategies suited to databases of any size. For small to medium databases, mysqldump with proper options like --single-transaction and compression provides excellent protection with minimal complexity.

As your backup requirements grow more complex — multiple databases, various storage destinations, encryption and monitoring — dedicated backup management tools like Databasus reduce operational burden while ensuring consistent backup execution. Whether using mysqldump directly or through automated platforms, the key is regular testing of your restoration procedures. A backup strategy is only as good as your ability to restore from it when disaster strikes. Implement your backup automation now and verify it works before you actually need it.