mysql_backup2csv.sh

#!/usr/bin/env bash
set -euo pipefail

usage() {
    cat <<EOF
Usage: $(basename "$0") <db_name> [user] [output_dir]

Arguments:
  db_name     MySQL database to export (required)
  user        MySQL user to connect as       (default: root)
  output_dir  Directory for CSV output files (default: /var/lib/mysql-files)
EOF
    exit 1
}

[[ -z "${1:-}" ]] && usage
DB_NAME="${1}"
DB_USER="${2:-root}"
OUTPUT_DIR="${3:-/var/lib/mysql-files}"

mkdir -p "$OUTPUT_DIR"

echo "Starting native CSV export for database: $DB_NAME..."

TABLES=$(mysql -u "$DB_USER" -B -N -e "SHOW TABLES FROM $DB_NAME;")

while read -r TABLE; do
    [[ -z "$TABLE" ]] && continue

    echo "  -> Exporting: $TABLE"
    CSV_FILE="$OUTPUT_DIR/$TABLE.csv"

    # MySQL's INTO OUTFILE will fail securely if the file already exists.
    # We must delete the old one first.
    rm -f "$CSV_FILE"

    mysql -u "$DB_USER" -e "
        SELECT * INTO OUTFILE '$CSV_FILE'
        FIELDS TERMINATED BY ','
        ENCLOSED BY '\"'
        ESCAPED BY '\"'
        LINES TERMINATED BY '\n'
        FROM $DB_NAME.$TABLE;
    "
done <<<"$TABLES"

echo "Export complete. Files are located in: $OUTPUT_DIR"