Automating MySQL Database Creation and Import in Docker: A Comprehensive Guide

Managing databases within a containerized environment can be both tedious and error-prone, especially when you’re working with multiple databases. This guide provides a bash script that automates the process of creating MySQL databases and importing SQL files within a Docker container. We’ll explore the functionality of the script, discuss enhancements, and cover how to tailor it to your needs.

The Problem

Developers often find themselves repeatedly creating databases and importing SQL files manually. In a Docker environment, interacting with the container to execute such commands adds another layer of complexity. This can quickly become inefficient, especially in environments that require frequent database resets or handling multiple SQL files.

The Solution

Our bash script automates the entire process of creating databases and importing SQL files. The script can:

Create databases if they don’t already exist.
Import SQL data from files located within a specific folder.
Execute all commands inside a Docker container.

This approach not only saves time but also ensures consistency across environments.

Script Overview

Here's an improved and flexible bash script for automating MySQL database creation and import:

#!/bin/bash

# Configuration
MYSQL_USER="root"
MYSQL_PASSWORD="secret"
MYSQL_PORT=3306
SQL_FOLDER="sqls/my-app"
DOCKER_COMPOSE_LOCATION="$HOME/app/docker/www/commons/docker-commons"
SKIP_ERRORS=false
STOP_ON_ERROR=false

# Function to execute MySQL commands inside Docker container
run_in_container() {
    cd "$DOCKER_COMPOSE_LOCATION" || exit
    docker compose exec -T mysql bash -c "
        if [ ! -d \"$SQL_FOLDER\" ]; then
            echo \"SQL folder not found inside container: $SQL_FOLDER\"
            exit 1
        fi
        for sql_file in $SQL_FOLDER/*.sql; do
            [ -e \"\$sql_file\" ] || continue
            db_name=\${sql_file##*/}
            db_name=\${db_name%.sql}
            echo \"Processing: \$db_name\"
            mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -P$MYSQL_PORT -e \"CREATE DATABASE IF NOT EXISTS \\\`\$db_name\\\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci\"

            if mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -P$MYSQL_PORT \$db_name < \"\$sql_file\"; then
                echo \"Successfully imported \$db_name\"
            else
                if [ \"$STOP_ON_ERROR\" = true ]; then
                    echo \"Error importing \$db_name. Stopping execution.\"
                    exit 1
                elif [ \"$SKIP_ERRORS\" = true ]; then
                    echo \"Error importing \$db_name. Skipping...\" >&2
                else
                    echo \"Error importing \$db_name. Continuing...\" >&2
                fi
            fi
            echo \"------------------\"
        done
    "
}

# Check for Docker installation
if ! command -v docker &> /dev/null; then
    echo "Docker is not installed. Please install it and try again."
    exit 1
fi

# Check if Docker Compose location exists
if [ ! -d "$DOCKER_COMPOSE_LOCATION" ]; then
    echo "Docker Compose directory not found: $DOCKER_COMPOSE_LOCATION"
    exit 1
fi

# Make the script executable
chmod +x import_sql_files.sh

# Run the script
run_in_container

Key Features

MySQL Port Configuration: You can now configure the MySQL port to allow interaction with MySQL servers running on different ports within Docker.
Skip Errors or Stop on Error: The script can be configured to either stop execution or skip over failed imports using the STOP_ON_ERROR and SKIP_ERRORS flags.
Progress Monitoring: For each SQL file processed, the script outputs the current status, making it easier to monitor progress.
Directory and File Existence Checks: The script checks whether the SQL folder exists within the Docker container and skips any missing or empty files.

Enhancements

1. Memory and Execution Time Tracking

We can track memory and execution time for more granular insights into resource usage during execution. To track memory usage, tools like time or the /usr/bin/time command can be added around the command invocation.

Example:

/usr/bin/time -v docker compose exec -T mysql bash -c "..."

2. Custom Collation

Add flexibility by allowing custom database collation to be defined:

COLLATION="utf8mb4_unicode_ci"
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "CREATE DATABASE IF NOT EXISTS \`$db_name\` CHARACTER SET utf8mb4 COLLATE ${COLLATION:-utf8mb4_unicode_ci}"

3. Error Logging

Incorporate error logging to capture any issues that arise during execution.

log_error() {
    echo "[ERROR] $(date): $1" >> import_errors.log
}

# Example usage
if ! mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -P$MYSQL_PORT \$db_name < \"\$sql_file\"; then
    log_error "Failed to import $db_name"
fi

4. Progress Bar

Implementing a progress bar improves user experience, particularly when importing large databases. The following uses the pv utility to show the progress:

pv -f $sql_file | mysql -u$MYSQL_USER -p$MYSQL_PASSWORD $db_name

5. Parallel Processing

For environments with large SQL files, the GNU parallel utility can be used to import multiple databases simultaneously:

export -f run_in_container
find "$SQL_FOLDER" -name "*.sql" | parallel run_in_container {}

6. Dry Run Mode

Implementing a dry-run mode helps preview actions without making any changes:

if [ "$DRY_RUN" = true ]; then
    echo "[DRY RUN] Would create database: $db_name"
    continue
fi

7. Environment Variable Configuration

To improve security, avoid hardcoding MySQL credentials by using environment variables:

MYSQL_USER=${MYSQL_USER:-root}
MYSQL_PASSWORD=${MYSQL_PASSWORD:-secret}
MYSQL_PORT=${MYSQL_PORT:-3306}

This enables flexibility, allowing you to define credentials in the environment, rather than in the script itself.

Security Considerations

Storing credentials in scripts can be insecure. To mitigate this:

Use Docker secrets or environment variables to store sensitive information.
Avoid exposing sensitive credentials in logs.
Consider using .env files with environment variables to handle MySQL credentials.

Pros and Cons

Pros:

Automation: Reduces manual effort by automating database creation and data imports.
Docker-Friendly: Integrates seamlessly with Docker, improving development workflows.
Customizable: Easily configurable to accommodate multiple projects and environments.
Error Handling: Includes error handling mechanisms, like skipping errors or stopping on failure.
Monitor Progress: Outputs information for each processed SQL file, improving visibility into the process.

Cons:

Security: Credentials stored in plain text. Better to use environment variables or Docker secrets.
No Rollback: No automatic rollback if errors occur during the import process.
Specific Dependencies: Assumes the Docker Compose setup is in place and that MySQL is running in a container.

How to Use the Script

Save the Script: Save the bash script as import_sql_files.sh.
Make Executable: Run the following command to make the script executable:

   chmod +x import_sql_files.sh

Run the Script: Execute the script using:

   ./import_sql_files.sh

Best Practices

Backup Your Databases: Always back up databases before running bulk imports or making structural changes.
Test in Staging: Run the script in a staging environment before deploying it to production.
Version Control: Keep SQL files and the script under version control to track changes.
Monitor Imports: Monitor the logs for any errors or issues during the import process.

Conclusion

This script significantly simplifies database management in a Dockerized MySQL environment, automating tasks that are often performed manually. With enhancements like progress monitoring, error handling, and parallel processing, this script can be a powerful addition to your development toolkit. However, always ensure security best practices when handling sensitive credentials and database access.

For more detailed Docker Compose configurations, refer to the docker-commons project.