MySQL Tutorial - Backup each table to separate file for MySQL Databases by using mysqldump

Scenario:

You are working as MySQL DBA, you need to write the script that should create a new directory each time you run the script , the directory name should be "Database_TimeStamp" and then take backup of each of table from a database to this directory as separate file. 

Solution:

The below script can be used to take backup of all the tables from a databases in separate file for each of the table by using mysqldump.

# Change the database name 
MYSQL_DB="techbrotheritnew"
#Provide the backup directory path in which you would like to create new direcoty and backup tables from a Database. In my case I have directory /tmp
BACKUP_DIR=/mysqlbackup/$MYSQL_DB"_"$(date +%Y-%m-%dT%H_%M_%S);
#Check and create new directory if not exists
test -d "$BACKUP_DIR" || mkdir -p "$BACKUP_DIR"
# Get the Table List for the database you have provide above
for tb in $(mysql -B -s --user='backupuser' --password='MyPassword$123'  -e 'select table_name from information_Schema.tables where table_schema="'""$MYSQL_DB""'";')
do
echo "Taking backup of Table : "$tb
  # backup each table in a separate file
  mysqldump --user='backupuser' --password='MyPassword$123' "$MYSQL_DB" "$tb" --skip-lock-tables> "$BACKUP_DIR/$tb.sql"
done

Items to change in above script according to your environment

• Replace "backupuser" to your user name that you are using to run the mysqldump
• Replace "MyPassword$123" password with password for your user.

backup every table from MySQL Database to separate files by using mysqldump

MySQL DBA Tutorial - Backup each table to separate file from MySQL Database by using mysqldump