テーブル毎エクスポートインポートシェル(ダンプ)

• MySQL

(8.0.31)

vim expdp.conf
tab1
tab2

vim expdp.sh

#!/bin/bash

OUTFILE="expdp_tables.sh"
PASS="'password'"
USER="root"
DB="test"

: > "${OUTFILE}"
for TAB in $(cat expdp.conf) ; do
  echo "MYSQL_PWD=${PASS} mysqldump -u ${USER} ${DB} ${TAB} > ${TAB}.sql" >> "${OUTFILE}"
done

chmod +x "${OUTFILE}"
./"${OUTFILE}"

exit 0

vim impdp.conf
tab1
tab2

vim impdp.sh

#!/bin/bash

OUTFILE="impdp_tables.sh"
PASS="'password'"
USER="root"
DB="test"

: > "${OUTFILE}"
for TAB in $(cat impdp.conf) ; do
  echo "MYSQL_PWD=${PASS} mysql -u ${USER} ${DB} < ${TAB}.sql" >> "${OUTFILE}"
done

chmod +x "${OUTFILE}"
./"${OUTFILE}"

exit 0

• Oracle

(19c)

vim expdp.conf
TAB1
TAB2

vim expdp.sh

#!/bin/bash

OUTFILE="expdp_tables.sh"
CONN="test/test@pdb1"
DIR="ORA_DIR"

: > "${OUTFILE}"
for TAB in $(cat expdp.conf) ; do

  echo "expdp ${CONN} directory=${DIR} dumpfile=${TAB}.dmp logfile=${TAB}.log tables=${TAB} reuse_dumpfiles=yes" >> "${OUTFILE}"

done

chmod +x "${OUTFILE}"
./"${OUTFILE}"

exit 0

vim impdp.conf
TAB1
TAB2

vim impdp.sh

#!/bin/bash

OUTFILE="impdp_tables.sh"
CONN="test/test@pdb1"
DIR="ORA_DIR"

: > "${OUTFILE}"
for TAB in $(cat impdp.conf) ; do

  echo "impdp ${CONN} directory=${DIR} dumpfile=${TAB}.dmp logfile=${TAB}.log tables=${TAB} table_exists_action=replace" >> "${OUTFILE}"

done

chmod +x "${OUTFILE}"
./"${OUTFILE}"

exit 0

• PostgreSQL

(15)

vim expdp.conf
tab1
tab2

vim expdp.sh

#!/bin/bash

OUTFILE="expdp_tables.sh"
DB="test"

: > "${OUTFILE}"
for TAB in $(cat expdp.conf) ; do
  echo "pg_dump -Fp -c -t ${TAB} ${DB} > ${TAB}.sql" >> "${OUTFILE}"
done

chmod +x "${OUTFILE}"
./"${OUTFILE}"

exit 0

vim impdp.conf
tab1
tab2

vim impdp.sh

#!/bin/bash

OUTFILE="impdp_tables.sh"
DB="test"

: > "${OUTFILE}"
for TAB in $(cat impdp.conf) ; do
  echo "psql ${DB} -f ${TAB}.sql" >> "${OUTFILE}"
done

chmod +x "${OUTFILE}"
./"${OUTFILE}"

exit 0

• SQL Server

(2019)
OS: CentOS7

vim expdp.conf
tab1
tab2

vim expdp.sh

#!/bin/bash

OUTFILE="expdp_tables.sh"
HOST="localhost"
DB="test"
USER="sa"
PASS="'password'"

: > "${OUTFILE}"
for TAB in $(cat expdp.conf) ; do
  echo "bcp dbo.${TAB} out ${TAB}.dmp -S ${HOST} -d ${DB} -U ${USER} -P${PASS} -n" >> "${OUTFILE}"
done

chmod +x "${OUTFILE}"
./"${OUTFILE}"

exit 0

vim impdp.conf
tab1
tab2

vim impdp.sh

#!/bin/bash

OUTFILE="impdp_tables.sh"
HOST="localhost"
DB="test"
USER="sa"
PASS="'password'"

: > "${OUTFILE}"
for TAB in $(cat impdp.conf) ; do
  echo "sqlcmd -S ${HOST} -d ${DB} -U ${USER} -P${PASS} -Q "'"'"truncate table ${TAB}"'"' >> "${OUTFILE}"
  echo "bcp dbo.${TAB} in ${TAB}.dmp -S ${HOST} -d ${DB} -U ${USER} -P${PASS} -n" >> "${OUTFILE}"
done

chmod +x "${OUTFILE}"
./"${OUTFILE}"

exit 0