How to perform backup and recovery in MariaDB and what are the backup strategies and tools?

Backup and recovery in MariaDB are critical for data security. Here are the main backup and recovery methods:

1. Logical Backup (mysqldump)

Full Backup:

bash
# Backup all databases
mysqldump -u root -p --all-databases > all_databases.sql

# Backup specific database
mysqldump -u root -p database_name > database_name.sql

# Backup specific table
mysqldump -u root -p database_name table_name > table_name.sql

# Backup and compress
mysqldump -u root -p database_name | gzip > database_name.sql.gz

Incremental Backup:

bash
# Enable binary logging
# my.cnf configuration
log-bin = mysql-bin
binlog-format = ROW

# Backup binary logs
mysqlbinlog mysql-bin.000001 > binlog_backup.sql

Recover Data:

bash
# Restore full backup
mysql -u root -p < all_databases.sql

# Restore specific database
mysql -u root -p database_name < database_name.sql

# Restore compressed backup
gunzip < database_name.sql.gz | mysql -u root -p database_name

# Apply binary logs
mysqlbinlog mysql-bin.000001 | mysql -u root -p

2. Physical Backup (Mariabackup)

Full Backup:

bash
# Create backup
mariabackup --backup --target-dir=/backup/full \
  --user=root --password=password

# Prepare backup
mariabackup --prepare --target-dir=/backup/full

# Restore backup
mariabackup --copy-back --target-dir=/backup/full

Incremental Backup:

bash
# Create full backup
mariabackup --backup --target-dir=/backup/full \
  --user=root --password=password

# Create incremental backup
mariabackup --backup --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full --user=root --password=password

# Prepare backup
mariabackup --prepare --target-dir=/backup/full
mariabackup --prepare --target-dir=/backup/full \
  --incremental-dir=/backup/inc1

3. Snapshot Backup

bash
# Use LVM snapshot
lvcreate -L 10G -s -n mysql_snapshot /dev/vg0/mysql
mount /dev/vg0/mysql_snapshot /mnt/backup
rsync -av /mnt/backup/ /backup/mysql/
umount /mnt/backup
lvremove /dev/vg0/mysql_snapshot

4. Automated Backup Script

bash
#!/bin/bash
# backup.sh

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup/mariadb"
MYSQL_USER="root"
MYSQL_PASSWORD="password"

# Create backup directory
mkdir -p $BACKUP_DIR

# Full backup
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD --all-databases \
  --single-transaction --quick --lock-tables=false \
  | gzip > $BACKUP_DIR/all_$DATE.sql.gz

# Keep backups from the last 7 days
find $BACKUP_DIR -name "all_*.sql.gz" -mtime +7 -delete

echo "Backup completed: all_$DATE.sql.gz"

5. Backup Strategy Recommendations

1. Full Backup: Execute daily at midnight
2. Incremental Backup: Execute hourly
3. Binary Logs: Keep in real-time
4. Remote Backup: Regularly sync to remote server
5. Backup Verification: Regularly test recovery process

6. Recovery Considerations

1. Stop MariaDB service before recovery
2. Ensure sufficient disk space
3. Verify data integrity after recovery
4. Record recovery process and time point
5. Verify recovery process in test environment first

Through proper backup strategies and recovery processes, you can maximize the security and reliability of MariaDB data.