Indonesia Website Awards
Indonesia Website Awards

Backup All MySQL Tables to Separate Files Automatically

Published by Lukems on

Continuing my previous article on Backup Entire MySQL Database to Separate Files Automatically. This time I will write how to Backup All MySQL Tables to Separate Files Automatically. Again, a database administrator is often asked to take data from all/some tables separately for certain analysis or processing. In order not to be complicated every time you type the mysqldump command, here I have created a script where by running this script, we don’t need to do backups one by one again. OK, you can use the script below:

Create a script file with the name tableDump , put it in /usr/bin/

vim /usr/bin/tableDump

then fill it with the script below :

Script for MySQL

#!/bin/bash
[ $# -lt 4 ] && echo "Usage: $(basename $0) <DB_HOST> <DB_USER> <DB_NAME> [<DIR>]" && echo "For example: $(basename $0) 172.0.0.1 root myDatabase /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
DB_name=$3
DIR=$4

## [id] menginputkan password pengguna database
## [en] input database user password
if [ -z "$DB_pass" ]; then
  echo
  echo -n "DB password: "
  read -s DB_pass
  echo
fi

## [id] deklarasi opsi untuk perintah mysqldump
## [en] declare option for mysqldump command
[ -n "$DB_options" ] || DB_options="--set-gtid-purged=OFF"
[ -n "$DIR" ] || DIR=.

## [id] membuat inputan user untuk pilihan backup semua tabel atau hanya beberapa
## [en] create user input for backup options of all tables or only some
echo "[id] Pilih 1 untuk backup semua tabel pada database $DB_name atau Pilih 2 untuk backup tabel tabel yang diinginkan: [1/2]"
echo "[en] Choose 1 to backup all tables in the database $DB_name or Choose 2 to backup the desired tables : [1/2]"
read -n 1 optionBackup
echo

## [id] validasi inputan hanya boleh 1 atau 2
## [en] input validation can only be 1 or 2
while true; do
    if [[ $optionBackup == 1 ]] || [[ $optionBackup == 2 ]];
    then
        break
    else
            echo '
[id] Pilih 1 atau 2 :
[en] choose 1 or 2 :
        '
        read -n 1 optionBackup
        echo
        continue
    fi
done

## [id] menjalankan fungsi sesuai inputan pengguna
## [en] perform functions according to user input
if [[ $optionBackup == 1 ]];
then
	TableDB=$(mysql -NBA -h localhost -u $DB_user -p$DB_pass -D $DB_name -e 'show tables')
	listTable=$(IFS=, ; echo "$TableDB")
else
	## [id] pengguna memasukkan nilai array untuk tabel apa saja yang mau di cadangkan
	## [en] user inputs array value for any table to be backed up
	echo "[id] Ketik nama Tabel yang ingin di Backup (gunakan spasi jika lebih dari 1) :"
	echo "[en] Type the name of the Table you want to backup (use spaces if more than 1) :"
	read -a TableDB
	listTable=$(IFS=, ; echo "${TableDB[*]}")
fi

## [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 backupan jadi file 7zip? [Y/n] :
[en] Do you want to archive your backup 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] Backupan tidak diarsipkan
  [en] Your backup not archived
"
fi

## [id] cetak informasi memulai pencadangan
## [en] print backup start information
echo
echo "[id] Mencadangkan table database '$DB_name' ke dalam file SQL terpisah kedalam folder='$DIR'"
echo "[en] Dumping table of database '$DB_name' into separate SQL command files into dir=$DIR"
echo

## [id] menjalankan fungsi backup dengan kondisi pilihan pengguna
## [en] performs the backup function with the conditions of the user's choice
if [[ $COMP == Y ]] || [[ $COMP == y ]];
then
	tbl_count=0
	for str in ${TableDB[@]};
	do
	  (( tbl_count++ ))
	  echo "DUMPING DATABASE: $tbl_count $str"
	  mysqldump --host="$DB_host" --result-file="$DIR/$DB_name-$str-`date '+%Y%m%d_%H%m%d'`.sql" --user="$DB_user" --password="$DB_pass" $DB_options $DB_name $str
	  7za a $DIR/$DB_name-$str-`date '+%Y%m%d_%H%m%d'`.7z $DIR/$DB_name-$str-`date '+%Y%m%d'`*.sql
	  rm -f $DIR/$DB_name-$str-`date '+%Y%m%d'`*.sql
	  du $DIR/$DB_name-$str-* -sh 
	done
else
	tbl_count=0
	for str in ${TableDB[@]};
	do
	  (( tbl_count++ ))
	  echo "DUMPING DATABASE: $tbl_count $str"
	  mysqldump --host="$DB_host" --result-file="$DIR/$DB_name-$str-`date '+%Y%m%d_%H%m%d'`.sql" --user="$DB_user" --password="$DB_pass" $DB_options $DB_name $str
	du $DIR/$DB_name-$str-* -sh
	done
fi
## [id] cetak informasi pencadangan selesai
## [en] print backup information complete
echo
echo "[id] ($tbl_count) tabel pada database $DB_name telah dicadangkan kedalam folder=$DIR" 
echo "[en] ($tbl_count) table on database $DB_name dumped into dir=$DIR"
echo "========================================================================"
ls -l $DIR

Script for MariaDB

#!/bin/bash
[ $# -lt 4 ] && echo "Usage: $(basename $0) <DB_HOST> <DB_USER> <DB_NAME> [<DIR>]" && echo "For example: $(basename $0) 172.0.0.1 root myDatabase /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
DB_name=$3
DIR=$4

## [id] menginputkan password pengguna database
## [en] input database user password
if [ -z "$DB_pass" ]; then
  echo
  echo -n "DB password: "
  read -s DB_pass
  echo
fi

## [id] deklarasi opsi untuk perintah mysqldump
## [en] declare option for mysqldump command
[ -n "$DB_options" ] || DB_options=""
[ -n "$DIR" ] || DIR=.

## [id] membuat inputan user untuk pilihan backup semua tabel atau hanya beberapa
## [en] create user input for backup options of all tables or only some
echo "[id] Pilih 1 untuk backup semua tabel pada database $DB_name atau Pilih 2 untuk backup tabel tabel yang diinginkan: [1/2]"
echo "[en] Choose 1 to backup all tables in the database $DB_name or Choose 2 to backup the desired tables : [1/2]"
read -n 1 optionBackup
echo

## [id] validasi inputan hanya boleh 1 atau 2
## [en] input validation can only be 1 or 2
while true; do
    if [[ $optionBackup == 1 ]] || [[ $optionBackup == 2 ]];
    then
        break
    else
            echo '
[id] Pilih 1 atau 2 :
[en] choose 1 or 2 :
        '
        read -n 1 optionBackup
        echo
        continue
    fi
done

## [id] menjalankan fungsi sesuai inputan pengguna
## [en] perform functions according to user input
if [[ $optionBackup == 1 ]];
then
	TableDB=$(mysql -NBA -h $DB_host -u $DB_user -p$DB_pass -D $DB_name -e 'show tables')
	listTable=$(IFS=, ; echo "$TableDB")
else
	## [id] pengguna memasukkan nilai array untuk tabel apa saja yang mau di cadangkan
	## [en] user inputs array value for any table to be backed up
	echo "[id] Ketik nama Tabel yang ingin di Backup (gunakan spasi jika lebih dari 1) :"
	echo "[en] Type the name of the Table you want to backup (use spaces if more than 1) :"
	read -a TableDB
	listTable=$(IFS=, ; echo "${TableDB[*]}")
fi

## [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 backupan jadi file 7zip? [Y/n] :
[en] Do you want to archive your backup 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] Backupan tidak diarsipkan
  [en] Your backup not archived
"
fi

## [id] cetak informasi memulai pencadangan
## [en] print backup start information
echo
echo "[id] Mencadangkan table database '$DB_name' ke dalam file SQL terpisah kedalam folder='$DIR'"
echo "[en] Dumping table of database '$DB_name' into separate SQL command files into dir=$DIR"
echo

## [id] menjalankan fungsi backup dengan kondisi pilihan pengguna
## [en] performs the backup function with the conditions of the user's choice
if [[ $COMP == Y ]] || [[ $COMP == y ]];
then
	tbl_count=0
	for str in ${TableDB[@]};
	do
	  (( tbl_count++ ))
	  echo "DUMPING DATABASE: $tbl_count $str"
	  mysqldump -u $DB_user -h $DB_host -p$DB_pass $DB_options $DB_name $str > $DIR/$DB_name-$str-`date '+%Y%m%d_%H%m%d'`.sql
	  7za a $DIR/$DB_name-$str-`date '+%Y%m%d_%H%m%d'`.7z $DIR/$DB_name-$str-`date '+%Y%m%d'`*.sql
	  rm -f $DIR/$DB_name-$str-`date '+%Y%m%d'`*.sql
	  du $DIR/$DB_name-$str-* -sh 
	done
else
	tbl_count=0
	for str in ${TableDB[@]};
	do
	  (( tbl_count++ ))
	  echo "DUMPING DATABASE: $tbl_count $str"
	  mysqldump -u $DB_user -h $DB_host -p$DB_pass $DB_options $DB_name $str > $DIR/$DB_name-$str-`date '+%Y%m%d_%H%m%d'`.sql
	du $DIR/$DB_name-$str-* -sh
	done
fi
## [id] cetak informasi pencadangan selesai
## [en] print backup information complete
echo
echo "[id] ($tbl_count) tabel pada database $DB_name telah dicadangkan kedalam folder=$DIR" 
echo "[en] ($tbl_count) table on database $DB_name dumped into dir=$DIR"
echo "========================================================================"
ls -l $DIR

Once saved, update the permissions with the command :

chmod +rwx /usr/bin/tableDump

If so, you can use the script by running

tableDump

The process will be like the animated GIF below :

Backup all database tables and archive them

Backup Entire MySQL Tables to Separate Files Automatically

Backup all database tables without being archived

Backup certain tables and archive thembackup all MySQL tables to separate files automatically

Backup certain tables without being archived

Okay, that’s how to backup all mysql tables to separate files automatically using a script that has been made to make it easier to do. Hopefully this information can be useful. Especially for Database Administrators who want to use a fast way to back up the desired tables. Thank you so much for reading 😀

Categories: Database
Tags:

0 Comments

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *

Related Posts

Backup Seluruh Database MySQL ke File Terpisah Secara Otomatis
Database

Backup Entire MySQL Database into Separate Files Automatically

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, Read more…

how to install mysql 8 on linux
Database

How to Install MySQL 8 on Linux (CentOS 7 and Ubuntu)

MySQL is one of the open-source RDBMS (Relational DataBase Management System) software which is included in the top 3 most popular and most widely used worldwide. Currently, the most recent version of MySQL is MySQL Read more…

clear mysql binary logs
Database

How to Clear MySQL Binary Logs

Binary Log in MySQL serves to store the entire series of queries that run on MySQL. Whether it’s a DDL, DML or DCL query, it will be stored in the mysql binary log. This Binary Read more…