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 x days quondam backup too  scheduled it inward CrobJob for automation.

Requirement - 

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

2- cypher utility                          It is to salve database backup inward compress format.

Step 1- 

Create a backup goal directory. 

[root@cent  ]# mkdir /dbbackup 

Step 2-

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

#!/bin/bash  # Add your backup dir location, password, mysql place too mysqldump place DATE=$(date +%d-%m-%Y) BACKUP_DIR="/dbbackup" MYSQL_USER="root" MYSQL_PASSWORD="*****" MYSQL=/usr/bin/mysql MYSQLDUMP=/usr/bin/mysqldump  # To exercise a novel directory into backup directory place mkdir -p $BACKUP_DIR/$DATE  # become a listing of databases databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`  # dump each database inward course of pedagogy mention for db inward $databases; exercise echo $db $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz" done  

Save too Exit aeroplane file.

Step iii -

Give Execute permission to the Script using below command.

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

Step iv -

Execute Script to examination backup using below command.

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

Command output would travel similar this

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

Step 5-

Do ls -l to listing newly created directory too all database cypher file.

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

Backup coming successfully..

Step half dozen -

Let's brand CronTab entry to execute this script in 1 trial daily inward midnight. Like below

[root@cent dbbackup]# crontab -e 

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

Save too Exit aeroplane CronTab.

In my Cron Entry exceptional every bit follow:

 0     Minute ii     Hours  *     hateful solar daytime of calendar month *     calendar month    1-5      hateful solar daytime of calendar week /dbbackup/mysqlbackup.sh  Script Path

Backup Script scheduled successfully. Now Mon to Fri All Database backup volition occur inward midnight 2:00 AM.

That's All

!!!Cheers!!!

Sumber https://linuxhowtoguide.blogspot.com/