Mysql Dump and Import

Introduction

Managing databases effectively requires regular backups to prevent data loss. This guide will show you how to use MySQL commands to back up and restore your database. Additionally, a script is provided to automate daily backups and remove old backups after a specified period.

1. Database Export

To export a MySQL database, use the following command:

mysqldump -u <USER> -p <DATABASE> > <BACKUP_FILE>.dmp

This command creates a file containing the structure and data of the specified database.

2. Database Import

To restore a previously exported database, use:

mysql -u <USER> -p <DATABASE> < <BACKUP_FILE>.dmp

This restores the database using the structure and data stored in the backup file.

3. Backup with Compression

If you want to compress the backup to save storage space, use:

mysqldump -u <USER> -p <DATABASE> | gzip > <BACKUP_FILE>.dmp.gz

4. Automated Daily Backups with Old Backup Removal

The following Bash script creates a daily database backup and removes backups older than 14 days:

#!/bin/bash

DATE=`date +%Y-%m-%d`
DATABASE="my_database"
USER="my_user"
PASS="my_password"

# Create backup
mysqldump -u $USER -p$PASS $DATABASE | gzip > /var/opt/mysqldump/daily-backup-$DATE.gz

# Remove old backups
find /var/opt/mysqldump -name "daily-backup-*" -atime +14 -exec rm {} \;

This script can be scheduled as a Cron job to automate daily backups efficiently.