Export MySQL/MariaDB Database in Tables

1_database_export_import_readme.txt

Written on Debian Based Systems

usage: mysql_[import|export].sh DATABASENAME

Splits Database in to multplie files, based on Table Names
schema.sql will be also created!

i've an my_import.cnf / my_export.cnf or exists, this files will be used as Credentails, default is the debian default file.

Files: schema.sql, routines_triggers.sql
export/*.sql

Added export_later folder, so u can copy export.sql files to this location, which can be imported later ;-)

my_import.cnf_my_export.cnf

[client]
user=DATABASE_USER
password=DATABASE_PASSWORD
host=DATABASE_HOST (localhost / 127.0.0.1)
port=DATABASE_PORT (Default: 3306)

mysql_export.sh

#!/bin/bash

DATABASENAME=$1
if [ -z "$DATABASENAME" ]; then
  echo "no database defined"
  exit 1;
fi

MYCNF=/etc/mysql/debian.cnf
if [ -f "my_export.cnf" ]; then
  MYCNF=$(realpath my_export.cnf)
fi

LIST=$(mysql --defaults-file=$MYCNF $DATABASENAME -e "SHOW TABLES;" -N | xargs | sort)

mysqldump --defaults-file=$MYCNF --skip-triggers --no-data $DATABASENAME > schema.sql
mysqldump --defaults-file=$MYCNF --routines --triggers --no-create-info --no-data $DATABASENAME > routines_triggers.sql

rm -rf export export_later

mkdir -p export export_later

for t in $LIST; do
  echo "export $t"
  mysqldump --defaults-file=$MYCNF --skip-triggers --no-create-info -e $DATABASENAME $t > export/${t}.sql
  date
done

mysql_import.sh

#!/bin/bash

DATABASENAME=$1
if [ -z "$DATABASENAME" ]; then
  echo "no database defined"
  exit 1;
fi

MYCNF=/etc/mysql/debian.cnf
if [ -f "my_import.cnf" ]; then
  MYCNF=$(realpath my_import.cnf)
fi

mysql --defaults-file=$MYCNF $DATABASENAME < schema.sql
date
for t in $(find export -iname "*.sql" -type f | sort); do
  echo "import $(basename $t)"
  mysql --defaults-file=$MYCNF $DATABASENAME < $t
  date
done
for t in $(find export_later -iname "*.sql" -type f | sort); do
  echo "import (later) $(basename $t)"
  mysql --defaults-file=$MYCNF $DATABASENAME < $t
  date
done
mysql --defaults-file=$MYCNF $DATABASENAME < routines_triggers.sql