For a Database Administrator, backing up the database must be a normal thing to do and become a routine. Either manually or automatically, it is very important to back up the database you manage. Then, if in 1 database server there are a lot of databases and you need to back up several of them separately, it will usually be done by backing them up one by one. To make things easier, I have created a script to backup entire MySQL database into separate files automatically. Where by running this script, we do not need to do backups one by one again. OK, you can use the script below:
Create a script file with the name databaseDump , put it in /usr/bin/
vim /usr/bin/databaseDump
then fill it with the script below :
Script For MySQL
#!/bin/bash
[ $# -lt 2 ] && echo "Usage: $(basename $0) <DB_HOST> <DB_USER> [<DIR>]" && echo "For example: $(basename $0) 172.0.0.1 root /home/backup" && exit 1
## [id] deklarasi variabel untuk perintah mysqldump, nilai diambil saat menjalankan skrip
## [en] declare variable for mysqldump command, value is fetched when running script
DB_host=$1
DB_user=$2
DIR=$3
## [id] deklarasi opsi untuk perintah mysqldump
## [en] declare option for mysqldump command
[ -n "$DB_options" ] || DB_options="--set-gtid-purged=OFF --add-drop-database --routines"
[ -n "$DIR" ] || DIR=.
## [id] pengguna memasukkan nilai array untuk database apa saja yang mau di cadangkan
## [en] user inputs array value for any database to be backed up
echo "[id] Ketik nama database yang ingin di Backup (gunakan spasi jika lebih dari 1) :"
echo "[en] Type the name of the database you want to backup (use spaces if more than 1) :"
read -a DB
listDB=$(IFS=, ; echo "${DB[*]}")
## [id] opsi apabila database ingin di arsipkan menjadi file 7zip
## [en] option if the database wants to be archived into a 7zip file
read -n 1 -p "
[id] Apakah kamu ingin mengarsipkan database jadi file 7zip? [Y/n] :
[en] Do you want to archive the database into a 7zip file? [Y/n] : " COMP
if [[ $COMP == Y ]] || [[ $COMP == y ]];
then
echo
echo "
[id] Okay, data akan diarsipkan!
[en] Okay, the data will be archived!
"
else
echo
echo "
[id] Database tidak diarsipkan
[en] Database not archived
"
fi
## [id] menginputkan password pengguna database
## [en] input database user password
if [ -z "$DB_pass" ]; then
echo
echo -n "DB password: "
read -s DB_pass
fi
## [id] cetak informasi memulai pencadangan
## [en] print backup start information
echo
echo "[id] Mencadangkan database ke dalam file SQL terpisah untuk database '$listDB' kedalam folder='$DIR'"
echo "[en] Dumping database into separate SQL command files for database '$listDB' into dir=$DIR"
if [[ $COMP == Y ]] || [[ $COMP == y ]];
then
tbl_count=0
for str in ${DB[@]};
do
(( tbl_count++ ))
echo "DUMPING DATABASE: $tbl_count $str"
mysqldump --host="$DB_host" --result-file="$DIR/$str-`date '+%Y%m%d_%H%m%d'`.sql" --user="$DB_user" --password="$DB_pass" $DB_options $str
7za a $DIR/$str-`date '+%Y%m%d_%H%m%d'`.7z $DIR/$str-`date '+%Y%m%d'`*.sql
rm -f $DIR/$str-`date '+%Y%m%d'`*.sql
du $DIR/$str-* -sh
done
else
tbl_count=0
for str in ${DB[@]};
do
(( tbl_count++ ))
echo "DUMPING DATABASE: $tbl_count $str"
mysqldump --host="$DB_host" --result-file="$DIR/$str-`date '+%Y%m%d_%H%m%d'`.sql" --user="$DB_user" --password="$DB_pass" $DB_options $str
du $DIR/$str-* -sh
done
fi
## [id] cetak informasi pencadangan selesai
## [en] print backup information complete
echo "[id] $tbl_count database ($listDB) telah dicadangkan kedalam folder=$DIR"
echo "[en] $tbl_count database ($listDB) dumped into dir=$DIR"
echo "========================================================================"
ls -l $DIR
Script For MariaDB
#!/bin/bash
[ $# -lt 2 ] && echo "Usage: $(basename $0) <DB_HOST> <DB_USER> [<DIR>]" && echo "For example: $(basename $0) 172.0.0.1 root /home/backup" && exit 1
## [id] deklarasi variabel untuk perintah mysqldump, nilai diambil saat menjalankan skrip
## [en] declare variable for mysqldump command, value is fetched when running script
DB_host=$1
DB_user=$2
DIR=$3
## [id] deklarasi opsi untuk perintah mysqldump
## [en] declare option for mysqldump command
[ -n "$DB_options" ] || DB_options="--add-drop-database --routines"
[ -n "$DIR" ] || DIR=.
## [id] pengguna memasukkan nilai array untuk database apa saja yang mau di cadangkan
## [en] user inputs array value for any database to be backed up
echo "[id] Ketik nama database yang ingin di Backup (gunakan spasi jika lebih dari 1) :"
echo "[en] Type the name of the database you want to backup (use spaces if more than 1) :"
read -a DB
listDB=$(IFS=, ; echo "${DB[*]}")
## [id] opsi apabila database ingin di arsipkan menjadi file 7zip
## [en] option if the database wants to be archived into a 7zip file
read -n 1 -p "
[id] Apakah kamu ingin mengarsipkan database jadi file 7zip? [Y/n] :
[en] Do you want to archive the database into a 7zip file? [Y/n] : " COMP
if [[ $COMP == Y ]] || [[ $COMP == y ]];
then
echo
echo "
[id] Okay, data akan diarsipkan!
[en] Okay, the data will be archived!
"
else
echo
echo "
[id] Database tidak diarsipkan
[en] Database not archived
"
fi
## [id] menginputkan password pengguna database
## [en] input database user password
if [ -z "$DB_pass" ]; then
echo
echo -n "DB password: "
read -s DB_pass
fi
## [id] cetak informasi memulai pencadangan
## [en] print backup start information
echo
echo "[id] Mencadangkan database ke dalam file SQL terpisah untuk database '$listDB' kedalam folder='$DIR'"
echo "[en] Dumping database into separate SQL command files for database '$listDB' into dir=$DIR"
if [[ $COMP == Y ]] || [[ $COMP == y ]];
then
tbl_count=0
for str in ${DB[@]};
do
(( tbl_count++ ))
echo "DUMPING DATABASE: $tbl_count $str"
mysqldump -u $DB_user -h $DB_host -p$DB_pass $DB_options $str > $DIR/$str-`date '+%Y%m%d_%H%m%d'`.sql
7za a $DIR/$str-`date '+%Y%m%d_%H%m%d'`.7z $DIR/$str-`date '+%Y%m%d'`*.sql
rm -f $DIR/$str-`date '+%Y%m%d'`*.sql
du $DIR/$str-* -sh
done
else
tbl_count=0
for str in ${DB[@]};
do
(( tbl_count++ ))
echo "DUMPING DATABASE: $tbl_count $str"
mysqldump -u $DB_user -h $DB_host -p$DB_pass $DB_options $str > $DIR/$str-`date '+%Y%m%d_%H%m%d'`.sql
du $DIR/$str-* -sh
done
fi
## [id] cetak informasi pencadangan selesai
## [en] print backup information complete
echo "[id] $tbl_count database ($listDB) telah dicadangkan kedalam folder=$DIR"
echo "[en] $tbl_count database ($listDB) dumped into dir=$DIR"
echo "========================================================================"
ls -l $DIR
Once saved, update the permissions with the command :
chmod +rwx /usr/bin/databaseDump
When it’s done, you can use the script by running
databaseDump
The process will be like the animated GIF below :
perform backups and archive files
perform backups without archiving files
Okay, that’s how to backup entire mysql database into separate files automatically using a script that has been made to make it easier to do. Hopefully this information can be useful. Thank you: D
Read Also :