Mysqldump all tables from database in separate files

#!/bin/bash
USER=myuser
PASSWORD=mypwd
DATABASE=nameofdb

for i in $(mysql -uUser -pPASSWORD DATABASE -e "show tables;"|grep -v Tables_in_);do mysqldump -u$USER -p$PASSWORD $DATABASE $i > /destination/dir/$i".sql";done

tar -cjvf "backup_mysql_"$(date +'%Y%m%d')".tar.bz2" /destination/dir/*.sql

1. version 2

#!/bin/bash

# MySQL credentials and database information
USER="your_mysql_username"
PASSWORD="your_mysql_password"
DATABASE="your_database_name"
HOST="localhost"

# Backup directory
BACKUP_DIR="/path/to/your/backup/directory"

# Date format to append to the filename
DATE=$(date +%Y%m%d%H%M%S)

# MySQL dump command, don't forget to replace mysql with mysqldump if you're using MariaDB
MYSQLDUMP="/usr/bin/mysqldump"

# Check if backup directory exists
if [ ! -d "$BACKUP_DIR" ]; then
  mkdir -p "$BACKUP_DIR"
fi

# Get a list of all tables in the database
tables=$(mysql -u $USER -p$PASSWORD -h $HOST $DATABASE -e 'SHOW TABLES;' | awk '{ print $1}' | grep -v '^Tables' )

for table in $tables; do
  echo "Dumping $table"
  $MYSQLDUMP --user=$USER --password=$PASSWORD --host=$HOST $DATABASE $table > "$BACKUP_DIR/${DATABASE}_${table}_$DATE.sql"
done

echo "Backup completed!"