Dump the code

Restore MySQL Database

Created 9 months ago
Posted By admin
4min read
Restoring or import a MySQL database from a saved copy can be done using the 'mysql' command-line tool. Here's the basic syntax:

mysql -u [username] -p[password] [database_name] < [filename.sql]

  •  '-u': Specifies the MySQL user.
  •  '-p': Prompts for the MySQL user's password. If the password is immediately provided after '-p' (without space), it avoids displaying the password on the command line.
  • [database_name]: Specifies the target database where you want to import the data.
  • '<': Reads and executes SQL statements from a file.

Here's an example:

mysql -u root -p mydatabase < backup.sql

Replace:
  •  'root' with your MySQL username.
  •  'mydatabase' with the name of the database you want to import into.
  •  'backup.sql' with the name of your SQL file.

After running this command, you will be prompted to enter the password for the MySQL user, and then the SQL file will be executed to populate the specified database with the data from the file.

Note: Make sure to create the target database ('mydatabase' in the example) before running the import command. If the database doesn't exist, you can create it using a MySQL client or another script before importing the data.

Bash script to restore database

Here's a simple example of a bash script that you can use to restore a MySQL database from a backup file:

#!/bin/bash

# MySQL database credentials
DB_USER="your_username"
DB_PASSWORD="your_password"
DB_NAME="your_database_name"

# Path to the SQL backup file
BACKUP_FILE="/path/to/your/backup.sql"

# Check if the backup file exists
if [ ! -f "$BACKUP_FILE" ]; then
    echo "Backup file not found: $BACKUP_FILE"
    exit 1
fi

# Decompress the backup file
gzip -d "$BACKUP_FILE"

# Path to the SQL backup decompressed file
BACKUP_UNCOMPRESSED_FILE="/tmp/mysql_backup/backup.sql"

# Check if the temporary directory exists or create it
TEMP_DIR="/tmp/mysql_backup/"
if [ ! -d "$TEMP_DIR" ]; then
    mkdir -p "$TEMP_DIR"
fi

# Restore the MySQL database
mysql -u"$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" < "$BACKUP_UNCOMPRESSED_FILE" > "$TEMP_DIR/restore_log.txt" 2>&1

# Check the exit status of the previous command
if [ $? -eq 0 ]; then
    echo "Database restored successfully."
else
    echo "Error restoring the database. Check the error message in $TEMP_DIR/restore_log.txt for details."
fi

Save the script to a file, for example, 'restore_database.sh', and then make it executable:

chmod +x restore_database.sh

To run the script:

./restore_database.sh

This script assumes that the 'mysql' command is in your system's PATH and that you have the necessary privileges to restore the database. Additionally, it's crucial to keep your database credentials secure, especially in production environments.

Restore Database from AWS S3

We can also download a MySQL database backup from an AWS S3 bucket, decompress the backup file, and then restore the MySQL database with the data from the backup. Here is the bash script.

#!/bin/bash

# MySQL database credentials
DB_USER="your_username"
DB_PASSWORD="your_password"
DB_NAME="your_database_name"

# AWS S3 bucket and path
S3_BUCKET="your_s3_bucket"
S3_PATH="your_s3_path"

BACKUP_DIR="/tmp/mysql_backup"

# Download the backup from AWS S3
aws s3 cp s3://$S3_BUCKET/$S3_PATH $BACKUP_DIR/db.sql.gz

# Path to the SQL backup file
BACKUP_FILE="/tmp/mysql_backup/db.sql.gz"

# Check if the backup file exists
if [ ! -f "$BACKUP_FILE" ]; then
    echo "Backup file not found: $BACKUP_FILE"
    exit 1
fi

# decompression file
gzip -d $BACKUP_FILE

# Path to the SQL backup decompressed file
BACKUP_UNCOMPRESSED_FILE="/tmp/mysql_backup/db.sql"

# Restore the MySQL database
mysql -u"$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" < "$BACKUP_UNCOMPRESSED_FILE"

# Check the exit status of the previous command
if [ $? -eq 0 ]; then
    echo "Database restored successfully."
else
    echo "Error restoring the database. Check the error message above for details."
fi
Topics

Mastering Nginx

27 articles

Bash script

2 articles

Crontab

2 articles