Create Zip Backup files for each Database by using mysqldump in MySQL or MairaDB

mysqldump is used to take logical backups of database/databases in MairaDB and Mysql. Below script will create a directory with timestamp and then create zip backup file for each database in MySQL or MairaDB.


# 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 where Table_schema like "Tech%";')
do
  # backup each database in a separate file
  mysqldump -u $MYSQL_USER --password=$MYSQL_PASS "$db" --skip-lock-tables | gzip > "$BACKUP_DIR/$db.sql.gz"
done

Video Demo : How to create zip backup file for each of the Database in MySQL or MariaDB

2 comments: