Take mysqldump with separate databases/files

Take mysqldump with separate databases/files

To take a back-up, run the mysqldump tool on each available databases,

[root@akwal ~]# mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > "$dbname".sql; done

Here is the screenshot with all related commands,

Mysqldump each database and compress the SQL file

[root@akwal ~]# mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > "$dbname".sql; [[ $? -eq 0 ]] && gzip "$dbname".sql; done

Or simple create FileName.sh (

• vi dbs_backup.sh, then give executable permission
• chmod 755 -R dbs_backup.sh
• then run this file 
• # ./dbs_backup.sh

it will create new backup of every databases.

and paste this content



#! /bin/bash

TIMESTAMP=$(date +"%F")
BACKUP_DIR="/backup/$TIMESTAMP"
MYSQL_USER="backup"
MYSQL=/usr/bin/mysql
MYSQL_PASSWORD="password"
MYSQLDUMP=/usr/bin/mysqldump

mkdir -p "$BACKUP_DIR/mysql"

databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`

for db in $databases; do
  $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/mysql/$db.gz"
done



Enjoy...!!!