Bash Script Easy Import Database

Script For Without MySQL Password

Below script to accept a command-line argument for the path to the MySQL dump file. The script with an option to pass the source dump file path:

#!/bin/bash

# MySQL Database Details
DB_NAME="your_database_name"
DB_USER="your_database_user"

# Check if the source dump file path is provided as a command-line argument
if [ "$#" -ne 1 ]; then
    echo "Usage: $0 <path/to/source/dump/file.sql>"
    exit 1
fi

SOURCE_DUMP_FILE="$1"

# Drop existing database
mysql -u"$DB_USER" -e "DROP DATABASE IF EXISTS $DB_NAME;"

# Check if the drop database command was successful
if [ $? -eq 0 ]; then
    echo "Database '$DB_NAME' dropped successfully."
else
    echo "Error: Failed to drop database '$DB_NAME'. Check the MySQL credentials or existing connections."
    exit 1
fi

# Create a new database with utf8mb4 character set and utf8mb4_unicode_ci collation
mysql -u"$DB_USER" -e "CREATE DATABASE $DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# Check if the create database command was successful
if [ $? -eq 0 ]; then
    echo "Database '$DB_NAME' created successfully."
else
    echo "Error: Failed to create database '$DB_NAME'. Check the MySQL credentials or existing connections."
    exit 1
fi

# Import MySQL dump file into the new database without a password
pv "$SOURCE_DUMP_FILE" | mysql -u"$DB_USER" "$DB_NAME"

# Check if the import command was successful
if [ $? -eq 0 ]; then
    echo "Import into '$DB_NAME' completed successfully."
else
    echo "Error: Failed to import data into '$DB_NAME'. Check the MySQL credentials or the dump file."
    exit 1
fi

echo "Database drop, create, and import completed successfully."

You can run the script with the path to the MySQL dump file as an argument:

./your_script.sh /path/to/your/source/dump/file.sql

This makes the script more flexible and allows you to specify the source dump file at runtime.

Script For With MySQL Password

You can modify the script to handle both cases – with and without a password. You can check whether the DB_PASSWORD variable is set, and if it is set, include the password in the MySQL commands. If it’s not set, omit the password from the commands.

#!/bin/bash

# MySQL Database Details
DB_NAME="your_database_name"
DB_USER="your_database_user"
DB_PASSWORD=""  # Set the password if needed

# Check if the source dump file path is provided as a command-line argument
if [ "$#" -ne 1 ]; then
    echo "Usage: $0 <path/to/source/dump/file.sql>"
    exit 1
fi

SOURCE_DUMP_FILE="$1"

# Drop existing database
mysql -u"$DB_USER" $([ -n "$DB_PASSWORD" ] && echo "-p'$DB_PASSWORD'") -e "DROP DATABASE IF EXISTS $DB_NAME;"

# Check if the drop database command was successful
if [ $? -eq 0 ]; then
    echo "Database '$DB_NAME' dropped successfully."
else
    echo "Error: Failed to drop database '$DB_NAME'. Check the MySQL credentials or existing connections."
    exit 1
fi

# Create a new database with utf8mb4 character set and utf8mb4_unicode_ci collation
mysql -u"$DB_USER" $([ -n "$DB_PASSWORD" ] && echo "-p'$DB_PASSWORD'") -e "CREATE DATABASE $DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# Check if the create database command was successful
if [ $? -eq 0 ]; then
    echo "Database '$DB_NAME' created successfully."
else
    echo "Error: Failed to create database '$DB_NAME'. Check the MySQL credentials or existing connections."
    exit 1
fi

# Import MySQL dump file into the new database
pv "$SOURCE_DUMP_FILE" | mysql -u"$DB_USER" $([ -n "$DB_PASSWORD" ] && echo "-p'$DB_PASSWORD'") "$DB_NAME"

# Check if the import command was successful
if [ $? -eq 0 ]; then
    echo "Import into '$DB_NAME' completed successfully."
else
    echo "Error: Failed to import data into '$DB_NAME'. Check the MySQL credentials or the dump file."
    exit 1
fi

echo "Database drop, create, and import completed successfully."

Now, the script checks if DB_PASSWORD is set, and if it is, it includes the password in the MySQL commands. If DB_PASSWORD is not set (empty), it omits the password. This way, the script is flexible and can be used with or without a MySQL password.

An Advanced Script

With prompt options:

#!/bin/bash

# Prompt user for MySQL Database Details
read -p "Enter the database name: " DB_NAME
read -p "Enter the path to the source dump file (e.g., /path/to/source/dump/file.sql): " SOURCE_DUMP_FILE
read -p "Enter the database user: " DB_USER
read -s -p "Enter the database password (press Enter if password is empty): " DB_PASSWORD

# Drop existing database
mysql -u"$DB_USER" -p"$DB_PASSWORD" -e "DROP DATABASE IF EXISTS $DB_NAME;"

# Check if the drop database command was successful
if [ $? -eq 0 ]; then
    echo "Database '$DB_NAME' dropped successfully."
else
    echo "Error: Failed to drop database '$DB_NAME'. Check the MySQL credentials or existing connections."
    exit 1
fi

# Create a new database with utf8mb4 character set and utf8mb4_unicode_ci collation
mysql -u"$DB_USER" -p"$DB_PASSWORD" -e "CREATE DATABASE $DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# Check if the create database command was successful
if [ $? -eq 0 ]; then
    echo "Database '$DB_NAME' created successfully."
else
    echo "Error: Failed to create database '$DB_NAME'. Check the MySQL credentials or existing connections."
    exit 1
fi

# Import MySQL dump file into the new database with the provided password
pv "$SOURCE_DUMP_FILE" | mysql -u"$DB_USER" -p"$DB_PASSWORD" "$DB_NAME"

# Check if the import command was successful
if [ $? -eq 0 ]; then
    echo "Import into '$DB_NAME' completed successfully."
else
    echo "Error: Failed to import data into '$DB_NAME'. Check the MySQL credentials or the dump file."
    exit 1
fi

echo "Database drop, create, and import completed successfully."

Just run the file:

./your_script.sh /path/to/your/source/dump/file.sql

Leave a comment

Your email address will not be published. Required fields are marked *