bash script to remove data from mysql databases to prep for seeding

README.md

Overview

This bash script will create a backup of all databases on the given mysql server with structure only (NO DATA). It will save that into a backup file, then restore to the same server dropping, then creating each databse from the backup schema. Effectivley this will remove all data from all databases on the mysql server while leaving all mysql system databases alone.

USAGE

Replace the quoted values of the variables YOUR_HOST, YOUR_USER, and YOUR_PASS with your connection specific information. Then just run the script: bash remove-data.sh Note you may need to make the script executable first via chmod +x remove-data.sh

remove-data.sh

#!/bin/bash

# README
#
# This bash script will create a backup of all databases on the given mysql server with structure only
# (NO DATA).  It will save that into a backup file, then restore to the same server dropping, then creating
# each databse from the backup schema.  Effectivley this will remove all data from all databases on the mysql
# server while leaving all mysql system databases alone.
#
# USAGE
#
# Replace the quoted values of the variables YOUR_HOST, YOUR_USER, and YOUR_PASS with your connection
# specific information.  Then just run the script:  bash remove-data.sh
# Note you may need to make the script executable first via chmod +x remove-data.sh


YOUR_HOST='127.0.0.1'
YOUR_USER='DbUser'
YOUR_PASS='PassGoesHere'

DBS_TO_BACKUP=$(echo "show databases" | mysql --host=$YOUR_HOST --port=3306 --user=$YOUR_USER --password=$YOUR_PASS | grep -Ev "^(Database|mysql|performance_schema|information_schema)$")

mysqldump --host=$YOUR_HOST --port=3306 --user=$YOUR_USER --password=$YOUR_PASS --no-data --add-drop-database --databases $DBS_TO_BACKUP > ./backupfile.sql

mysql --host=$YOUR_HOST --port=3306 --user=$YOUR_USER --password=$YOUR_PASS < ./backupfile.sql

rm ./backupfile.sql

echo -en "\nDONE!\n"