Linux · March 28, 2016 0

MySQL backup ignore tables

The script below is to export the database by ignoring specific tables


#!/bin/bash
PASSWORD=dbpass
HOST=hostname
USER=dbuser
DATABASE=dbname
DB_FILE=filename_to_export.sql

EXCLUDED_TABLES=(
    tbl1
    tbl2
    tbl3
)

IGNORED_TABLES_STRING=''

for TABLE in "${EXCLUDED_TABLES[@]}"
do :
   IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${TABLE}"
done

echo "Dump structure"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} --single-transaction --no-data ${DATABASE} > ${DB_FILE}

echo "Dump content"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} ${DATABASE} ${IGNORED_TABLES_STRING} >> ${DB_FILE}

Put the above code in a file and save as a script some where and make it executable
For example I put above code in a file mysqldb.sh and save it in home directory

$ nano ~/mysqldb.sh

Paste the code using ctrl+shift+v and ctrl+x to exit

Make it executable

$ chmod +x ~/mysqldb.sh

Now run the script it will export all the tables except the ignored ones.

$ ~/mysqldb.sh