How to backup every table from a database to separate file by using mysqldump in mysql or mairadb

How to backup all the tables form a database to separete files in mysql or mairadb by using mysqldump

The below script can be used to backup all the tables from a database in mysql or mairadb. The script will create a new directory "DatabaseName_Date_T_Time" everytime you will run. Inside the directory single file will be created for each of the table.

# Provide the user name that you want to use for backups
MYSQL_USER="backupUserName"
#Provide the Password for the backup user
MYSQL_PASS="Password for BackupUserName"
#Provide DBName from which you want to backup all tables
MYSQL_DB="YourDatabaseName"
#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=/tmp/$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 -u $MYSQL_USER --password=$MYSQL_PASS  -e 'select table_name from information_Schema.tables where table_schema="'""$MYSQL_DB""'";')
do
  # backup each table in a separate file
  mysqldump -u $MYSQL_USER --password=$MYSQL_PASS "$MYSQL_DB" "$tb" --skip-lock-tables> "$BACKUP_DIR/$tb.sql"
done

Video Demo : How to backup every table from a database to separate file by using mysqldump