Script to export all non-view db-tables from a MySQL database to TSV files.

mysql_to_tsv.sh

#!/usr/bin/env bash

# Script to export all tables (other than "views") from a MySQL database to TSV files.
# You'll be prompted for the password for each export.
# WILL overwrite existing files in the output directory.

set -euo pipefail  # ensures pipeline fails if any command in the pipeline fails

# user-configurable variables ---------------------------------------
MYSQL_BIN="/PATH/TO/mysql"
DB_USER="DB_USERNAME"
DB_HOST="DB_SERVER"
DB_NAME="DB_NAME"
OUTDIR="/PATH/TO/OUTPUT_DIRECTORY"

# Set to "true" if you want no header row in TSV outputs.
SKIP_COLUMN_NAMES="false"
# end user-configurable variables -----------------------------------

mkdir -p "$OUTDIR"  # creates parent-dirs if necessary, and doesn't complain if they already exist

MYSQL_BASE_ARGS=(
  -p
  "--user=$DB_USER"
  "--host=$DB_HOST"
  --enable-cleartext-plugin
  --default-character-set=utf8mb4
  --init-command="SET NAMES utf8mb4"
  "$DB_NAME"
)

# --batch: tab-separated output.
# --raw: avoids some escaping/formatting surprises.
# --quick: stream rows instead of loading entire table into memory.
# --column-names: include column names in output.
MYSQL_EXPORT_ARGS=(--batch --raw --quick --column-names)

if [ "$SKIP_COLUMN_NAMES" = "true" ]; then
  MYSQL_EXPORT_ARGS+=(--skip-column-names)
fi

# Get all table names in the database -------------------------------
tables="$(
  "$MYSQL_BIN" "${MYSQL_BASE_ARGS[@]}" -N -B \
    -e "SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = DATABASE()
          AND table_type = 'BASE TABLE'
        ORDER BY table_name;"
)"

# Export each table -------------------------------------------------
while IFS= read -r table_name; do
  [ -z "$table_name" ] && continue

  "$MYSQL_BIN" "${MYSQL_BASE_ARGS[@]}" "${MYSQL_EXPORT_ARGS[@]}" \
    -e "SELECT * FROM \`$table_name\`;" \
    > "$OUTDIR/$table_name.tsv"
done <<< "$tables"