How to Backup Large MySQL Database Table by Table

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.sh

Once 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 Script

After creating restore_db.sh, make it executable and run it:

bash restore_db.sh

For transferring files between servers directly, see Server-to-Server File Transfer with PHP Script