Backing up and restoring large databases can be challenging at times.
Recently, when I had to back up and restore a large database, I did a backup for each database table and restored them one by one using a bash script. In case of a restore failure, you only need to deal with the specific table backup that caused the failure.
Create a Backup Script
First, let’s create a bash script to back up the database table by table. Save the following script as backup_db.sh. This script will back up each table in your database to a separate .sql file.
#!/bin/bash
MYSQL_DB="wordpress_db"
BACKUP_DIR="/root/mysql/${MYSQL_DB}"
mkdir -p "$BACKUP_DIR"
TABLES=$(mysql -N -e "SHOW TABLES FROM ${MYSQL_DB}")
for TABLE in $TABLES
do
echo "Backing up table: $TABLE"
/usr/bin/mysqldump "${MYSQL_DB}" "$TABLE" > "$BACKUP_DIR/${TABLE}.sql"
done
echo "Backup completed. Files are stored in $BACKUP_DIR"
Run the Backup Script
After creating backup_db.sh, you can run it with:
bash backup_db.shOnce the script runs, you’ll have each table from your database saved as a separate .sql file in the specified backup directory.
Create a Restore Script
Now that you have a backup, you’ll need a way to restore these tables. The following script will restore each table from its corresponding .sql file.
#!/bin/bash
MYSQL_DB="wordpress_db"
BACKUP_DIR="/root/mysql/${MYSQL_DB}"
if [ ! -d "$BACKUP_DIR" ]; then
echo "Error: Backup directory does not exist."
exit 1
fi
for SQL_FILE in "$BACKUP_DIR"/*.sql
do
if [ -f "$SQL_FILE" ]; then
TABLE_NAME=$(basename "$SQL_FILE" .sql)
echo "Restoring table: $TABLE_NAME"
/usr/bin/mysql "${MYSQL_DB}" < "$SQL_FILE"
if [ $? -eq 0 ]; then
echo "Successfully restored $TABLE_NAME"
else
echo "Error restoring $TABLE_NAME"
fi
fi
done
echo "Restore process completed."MYSQL_DB: Again, replace “wordpress_db” with your actual database name.
Run the Restore ScriptAfter creating restore_db.sh, make it executable and run it:
bash restore_db.shFor transferring files between servers directly, see Server-to-Server File Transfer with PHP Script
Back to MySQL Backup
