How to backup all database MySql

Backup all MySql Database using mysqldump utility.

This is how to  Wiki for taking all Database backup using script, Keep 10 days old backup and  scheduled it in CrobJob for automation.

Requirement - 

1- mysqldump utility            It's come by-defalut with mysql-client package.

2- zip utility                          It is to save database backup in compress format.

Step 1- 

Create a backup destination directory. 

[root@cent ~]# mkdir /dbbackup

Step 2-

Create a file name mysqlbackup.sh  and paste below syntax.

#!/bin/bash

# Add your backup dir location, password, mysql location and mysqldump location
DATE=$(date +%d-%m-%Y)
BACKUP_DIR="/dbbackup"
MYSQL_USER="root"
MYSQL_PASSWORD="*****"
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump

# To create a new directory into backup directory location
mkdir -p $BACKUP_DIR/$DATE

# get a list of databases
databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`

# dump each database in separate name
for db in $databases; do
echo $db
$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"
done

Save and Exit form file.

Step 3 -

Give Execute permission to the Script using below command.

[root@cent dbbackup]# chmod a+x mysqlbackup.sh

Step 4 -

Execute Script to test backup using below command.

[root@cent dbbackup]# ./mysqlbackup.sh

Command output would be like this

[root@cent dbbackup]# ./mysqlbackup.sh
amar
amar1
amar2
mysql
test
[root@cent dbbackup]#

Step 5-

Do ls -l to list newly created directory and all database zip file.

[root@cent dbbackup]# ls -l /dbbackup/09-08-2016/
total 164
-rw-r--r-- 1 root root    519 Aug  9 15:43 amar1.sql.gz
-rw-r--r-- 1 root root    520 Aug  9 15:43 amar2.sql.gz
-rw-r--r-- 1 root root    528 Aug  9 15:42 amar amar1.sql.gz
-rw-r--r-- 1 root root    519 Aug  9 15:43 amar.sql.gz
-rw-r--r-- 1 root root 144739 Aug  9 15:43 mysql.sql.gz
-rw-r--r-- 1 root root    519 Aug  9 15:43 test.sql.gz
[root@cent dbbackup]#

Backup coming successfully..

Step 6 -

Let's make CronTab entry to execute this script once daily in midnight. Like below

[root@cent dbbackup]# crontab -e

 0 2 * * 1-5 /dbbackup/mysqlbackup.sh

Save and Exit form CronTab.

In my Cron Entry detail as follow:

0     Minute
2     Hours 
*     day of month
*     month   
1-5      day of week
/dbbackup/mysqlbackup.sh  Script Path

Backup Script scheduled successfully. Now Monday to Friday All Database backup will happen in midnight 2:00 AM.

That's All

!!!Cheers!!!