How to backup MySql database

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.
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.
chmod a+x mysqlbackup.sh
Step 4 –
Execute Script to test backup using below command.
./mysqlbackup.sh
Step 5-
Do ls -l to list newly created directory and all database zip file.
ls -l /dbbackup/15-06-2017/
-rw-r–r– 1 root root    519 June  9 15:43 test1.sql.gz
-rw-r–r– 1 root root    520 June  9 15:43 kaustubh.sql.gz
Backup coming successfully..

Be the first to comment

Leave a Reply

Your email address will not be published.


*