Backup all MySQL Database by using mysqldump in separate files in TimeStamp directory - MySQL DBA Tutorial

Scenario:

You are working as MySQL DBA, you need to create script that should create a new TimeStamp Directory in /mysqlbackup directory and then take backup of all databases in separate files.

Solution:
The below script can be used to create new TimeStamp directory every time you will run the script and then it will run mysqldump for each of the database on your MySQL Server.

Replace the backupuser with your user name and 'MyPassword$123' with the password you are using for your user in MySQL server to run mysqldump.

#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 --user='backupuser' --password='MyPassword$123' -e 'Select distinct Table_schema from information_Schema.tables;')
do
echo "Performing backup of Database : "$db
  # backup each database in a separate file
  mysqldump --user='backupuser' --password='MyPassword$123' "$db" --skip-lock-tables> "$BACKUP_DIR/$db.sql"
done

Take a look in below picture, after running the above script, a directory is created and then backups are created for each MySQL Database to separate file.

Create MySQL Databases backups in separate files in TimeStamp directory

MySQL DBA Tutorial - Backup all databases in MySQL Server to separate files