Take backup of each DB in newly created timestamp directory by using mysqldump for MairaDB /MySQL

mysqldump is used to take logical backups of database/databases in MairaDB and Mysql. In this post we are going to cover the scenario in which you have to write the script that should create a directory by using date time and then take the backup of all the databases in newly created directory. Each database backup will be saved to it own .sql file.

# Provide the user name that you want to use for backups
MYSQL_USER="backupuser"
#Provide the Password for the backup user
MYSQL_PASS="Test123"
#Provide the backup directory path in which you would like to create new direcoty and backup databases. In my case I have directory /mysqlbackup
BACKUP_DIR=/mysqlbackup/$(date +%Y-%m-%dT%H_%M_%S);
#Check and create new directory if not exits
test -d "$BACKUP_DIR" || mkdir -p "$BACKUP_DIR"
# Get the database list
for db in $(mysql -B -s -u $MYSQL_USER --password=$MYSQL_PASS -e 'Select distinct Table_schema from information_Schema.tables;')
do
  # backup each database in a separate file
  mysqldump -u $MYSQL_USER --password=$MYSQL_PASS "$db" --skip-lock-tables> "$BACKUP_DIR/$db.sql"
done

How to backup each of DAtabase in newly created TimeStamp Directoy in MariaDB or MySQL