database/table non encripted can restore to database encripted
but database/table encripted cannot restore to database non encripted
A. Backup Database
1. list database
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| dbatools |
| information_schema |
| mysql |
| performance_schema |
| teguhth |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]>
2. Backup database full a database (example teguhth )
mysqldump -u root -p teguhth > db_teguhth.sql ;
mysqldump -u root -p teguhth --single-transaction --skip-lock-tables > db_teguhth.sql ;
A. Backup Database
1. list database
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| dbatools |
| information_schema |
| mysql |
| performance_schema |
| teguhth |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]>
2. Backup database full a database (example teguhth )
mysqldump -u root -p teguhth > db_teguhth.sql ;
mysqldump -u root -p teguhth --single-transaction --skip-lock-tables > db_teguhth.sql ;
3. backup all database
mysqldump -u root -p --all-databases > alldb_teguh.sql ;
mysqldump -u root -p --all-databases --skip-lock-tables > alldb_teguhskip.sql ;
mysqldump -u root -p --all-databases --single-transaction --skip-lock-tables > alldb_teguhskip.sql ;
4. backup table
mysqldump -u root -p teguhth barang > table_barang.sql ;
mysqldump -uroot -pxxx teguhth audit_table user_login_logs > backuptable.sql
4. backup table
mysqldump -u root -p teguhth barang > table_barang.sql ;
mysqldump -uroot -pxxx teguhth audit_table user_login_logs > backuptable.sql
[root@teguhth data]# mysqldump -u root -proot teguhth > db_teguhth.sql;
[root@teguhth data]# mysqldump -u root -proot --all-databases > alldb_teguh.sql;
[root@teguhth data]# mysqldump -u root -proot --all-databases --skip-lock-tables > alldb_teguhskip.sql;
[root@teguhth data]# mysqldump -u root -proot teguhth barang > table_barang.sql;
[root@teguhth data]# ls
alldb_teguhskip.sql alldb_teguh.sql db_teguhth.sql master my2Collector-master select.sql table_barang.sql tool
[root@teguhth data]# pwd
/data
[root@teguhth data]#
5. backup hanya schema
mysqldump -u root -p --no-data teguhth > teguhth_schema.sql
6. backup hanya schema pertable
mysqldump -u root -p --no-data teguhth barang customer > teguhth_schema_pertable.sql
7. backup hanya schema exclude table , or exluce data on a table
mysqldump -u root -p --no-data teguhth --ignore-table=teguhth.pembelian > teguhth_schema_exclude_table.sql
mysqldump -u root -p --no-data teguhth > teguhth_schema.sql
6. backup hanya schema pertable
mysqldump -u root -p --no-data teguhth barang customer > teguhth_schema_pertable.sql
7. backup hanya schema exclude table , or exluce data on a table
mysqldump -u root -p --no-data teguhth --ignore-table=teguhth.pembelian > teguhth_schema_exclude_table.sql
mysqldump -uroot -pxxx teguhth --ignore-table=teguhth.pembelian --ignore-table=teguhth.pasok > backupignore.sql
mysqldump -uroot -pxxx teguhth --ignore-table=teguhth.pembelian --ignore-table-data=teguhth.pasok > backupignore.sql
grep -i "CREATE TABLE \|INSERT INTO " backuptable.sql
8. backup hanya data saja
Perintah Backup Data Saja:
mysqldump -u root -p --no-create-info teguhth > teguhth_data.sql
9. table+data (tanpa function, procedure, trigger, view )
mysqldump -u root -p -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --no-create-db --no-create-info --single-transaction --events=false --routines=false --triggers=false --no-data=false TeguhDB | gzip -c > teguhthdb_withouttrigger.sql.gz;
Penjelasan opsi:
--no-create-db: Tidak menyertakan perintah CREATE DATABASE.
--no-create-info: Hanya backup data tanpa struktur tabel (hanya isi datanya).
--skip-triggers: Tidak menyertakan trigger dalam backup.
--routines=false: Tidak menyertakan fungsi dan prosedur yang ada.
--events=false: Tidak menyertakan event scheduler.
B. Restore Database
1. Create other database( example restore_db ) in one server
create database restore_db;
show databases;
2. Restore database example restore db teguth
mysql -u root -p restore_db < db_teguhth.sql
or
mysql -u root -p restore_db < alldb_teguh.sql
or
mysql -u root -p restore_db < alldb_teguhskip.sql
or
mysql -u root -p restore_db < table_barang.sql
[root@teguhth data]# mysql -u root -proot restore_db < db_teguhth.sql
[root@teguhth data]# mysql -u root -proot restore_db < alldb_teguh.sql
[root@teguhth data]# mysql -u root -proot restore_db < alldb_teguhskip.sql
[root@teguhth data]# mysql -u root -proot restore_db < table_barang.sql
[root@teguhth data]#
3. check database
show databases;
use restore_db;
select * from barang;
MariaDB [(none)]> use restore_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [restore_db]> select * from barang;
+-------------+-------------+---------------+-------------+
| KODE_BARANG | NAMA_BARANG | SATUAN_BARANG | STOK_BARANG |
+-------------+-------------+---------------+-------------+
| ELK-01 | RICE COOKER | BUAH | 20 |
| ELK-02 | LEMARI ES | UNIT | 8 |
| ELK-03 | TELEVISI | UNIT | 30 |
| ELK-04 | RADIO/TAPE | BUAH | 35 |
| ELK-05 | KOMPUTER | UNIT | 28 |
| ELK-06 | KIPAS ANGIN | BUAH | 38 |
+-------------+-------------+---------------+-------------+
6 rows in set (0.00 sec)
MariaDB [restore_db]>
4. using date output
mysqldump -uroot -proot teguhth > teguhth_$(date +%F).sql;
mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d").sql;
mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d_%H%M%S").sql;
mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d_%H%M%S_%N").sql;
mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d").sql;
mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d_%H%M%S").sql;
mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d_%H%M%S_%N").sql;
[root@teguhth tmp]# ls
[root@teguhth tmp]#
[root@teguhth tmp]#
[root@teguhth tmp]# mysqldump -uroot -proot teguhth > teguhth_$(date +%F).sql;
[root@teguhth tmp]# mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d").sql;
[root@teguhth tmp]# mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d_%H%M%S").sql;
[root@teguhth tmp]# mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d_%H%M%S_%N").sql;
[root@teguhth tmp]#
[root@teguhth tmp]# ls
teguhth_20230913_150623_142015568.sql teguhth_20230913_150623.sql teguhth_2023-09-13.sql teguhth_20230913.sql
[root@teguhth tmp]#
[root@teguhth tmp]#
[root@teguhth tmp]#
[root@teguhth tmp]# mysqldump -uroot -proot teguhth > teguhth_$(date +%F).sql;
[root@teguhth tmp]# mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d").sql;
[root@teguhth tmp]# mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d_%H%M%S").sql;
[root@teguhth tmp]# mysqldump -uroot -proot teguhth > teguhth_$(date +"%Y%m%d_%H%M%S_%N").sql;
[root@teguhth tmp]#
[root@teguhth tmp]# ls
teguhth_20230913_150623_142015568.sql teguhth_20230913_150623.sql teguhth_2023-09-13.sql teguhth_20230913.sql
[root@teguhth tmp]#
other version
backup
sample DB Source = TeguhDB
file DB = teguhthdb.sql.gz
Sample DB Destination = TeguhDB02
mysql -uroot -p -e "set global max_statement_time=0;"
mysqldump -uroot -p -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false TeguhDB | gzip -c > teguhthdb.sql.gz
mysql -uroot -p -e "set global max_statement_time=60;"
restore
mysql -uroot -p -e "set global max_statement_time = 0;"
gunzip -c < teguhthdb.sql.gz | mysql -uroot -p -f TeguhDB02
mysql -uroot -p -e "set global max_statement_time = 60;"
5. Backup all without default database
sample DB Source = TeguhDB
file DB = teguhthdb.sql.gz
Sample DB Destination = TeguhDB02
mysql -uroot -p -e "set global max_statement_time=0;"
mysqldump -uroot -p -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false TeguhDB | gzip -c > teguhthdb.sql.gz
mysql -uroot -p -e "set global max_statement_time=60;"
restore
mysql -uroot -p -e "set global max_statement_time = 0;"
gunzip -c < teguhthdb.sql.gz | mysql -uroot -p -f TeguhDB02
mysql -uroot -p -e "set global max_statement_time = 60;"
5. Backup all without default database
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address
infotgl=$(date)
echo ".::: Backup all database using custom in $Server with $ip_address :::."
echo ""
#!/bin/bash
#
clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;
echo ""
echo "Backup begin $infotgl"
backuppath="/backup";
mkdir -p $backuppath/$year-$month-$day;
path="$backuppath/$year-$month-$day";
dbname=$(mysql -uroot -proot -sNe "select schema_name from information_schema.schemata where schema_name not in('information_schema','performance_schema','mysql');");
for listdb in $dbname
do
mysqldump -uroot -proot -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false $listdb | gzip -c > "$path"/"$listdb"_"$year$month$day$hour$min$sec".sql.gz;
done;
ls -lh $path
echo ""
echo "Backup finish $infotgl"
echo ""
echo ".::: Delete backup retention :::."
#
echo "Delete begin $infotgl"
backuppath="/backup";
find $path/* -type d -mtime +30 -exec rm -rf {} \;
echo "Delete finish $infotgl"
echo ""
echo "Copyright by : Teguh Triharto"
echo "Website : https://www.linkedin.com/in/teguhth"
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address
infotgl=$(date)
echo ".::: Backup all database using custom in $Server with $ip_address :::."
echo ""
#!/bin/bash
#
clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;
echo ""
echo "Backup begin $infotgl"
backuppath="/backup";
mkdir -p $backuppath/$year-$month-$day;
path="$backuppath/$year-$month-$day";
dbname=$(mysql -uroot -proot -sNe "select schema_name from information_schema.schemata where schema_name not in('information_schema','performance_schema','mysql');");
for listdb in $dbname
do
mysqldump -uroot -proot -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false $listdb | gzip -c > "$path"/"$listdb"_"$year$month$day$hour$min$sec".sql.gz;
done;
ls -lh $path
echo ""
echo "Backup finish $infotgl"
echo ""
echo ".::: Delete backup retention :::."
#
echo "Delete begin $infotgl"
backuppath="/backup";
find $path/* -type d -mtime +30 -exec rm -rf {} \;
echo "Delete finish $infotgl"
echo ""
echo "Copyright by : Teguh Triharto"
echo "Website : https://www.linkedin.com/in/teguhth"
echo ""
other fix script
[root@teguhth backup]# cat backuptest.sh
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address
infotgl=$(date)
echo ".::: Backup all database using custom in $Server with $ip_address :::."
echo ""
#!/bin/bash
#
#clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;
echo "Backup begin $infotgl"
backuppath="/backup";
mkdir -p $backuppath/$year-$month-$day;
path="$backuppath/$year-$month-$day";
dbname=$(mysql -uroot -proot -sNe "select schema_name from information_schema.schemata where schema_name not in('information_schema','performance_schema','mysql');");
for listdb in $dbname
do
mysqldump -uroot -proot -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false $listdb | gzip -c > "$path"/"$listdb"_"$year$month$day$hour$min$sec".sql.gz;
done;
echo ""
ls -lh $path
echo ""
echo "Backup finish $infotgl"
echo ""
echo ".::: Delete backup retention :::."
echo ""
echo "Delete begin $infotgl"
backuppath="/backup";
find $path/* -type d -mtime +30 -exec rm -rf {} \;
echo ""
ls -lh $backuppath
echo ""
echo "Delete finish $infotgl"
echo ""
echo "Copyright by : Teguh Triharto"
echo "Website : https://www.linkedin.com/in/teguhth"
echo ""
[root@teguhth backup]#
6. backup pilihan
[root@teguhth-all devbackup]# pwd
/data/devbackup
[root@teguhth-all devbackup]# cat backup_all_maria.sh
#!/bin/bash
# ============================================================
# Backup Database MariaDB
# Usage:
# sh 1.sh <keyword_database>
#
# Example:
# sh 1.sh teguhth
# ============================================================
# ------------------------------------------------------------
# Menyimpan waktu mulai
# ------------------------------------------------------------
datexd=$(date "+%Y-%m-%d %H:%M:%S.%N")
epoch_start=$(date +%s)
echo "$datexd ->>> Start Process"
# ------------------------------------------------------------
# Validasi argument
# ------------------------------------------------------------
if [ -z "$1" ]; then
echo "Error: ACC name argument is required."
echo "Usage: $0 <keyword_database>"
exit 1
fi
# Keyword pencarian database
dblist="$1"
alias mysql=mariadb
alias mysqldump=mariadb-dump
# ------------------------------------------------------------
# Variable tanggal
# ------------------------------------------------------------
datex=$(date +"%Y%m%d")
datey=$(date +"%Y%m%d_%H%M%S")
# ------------------------------------------------------------
# Waktu proses kedua
# ------------------------------------------------------------
datexy=$(date "+%Y-%m-%d %H:%M:%S.%N")
#echo "$datexy ->>> Start Process"
# ------------------------------------------------------------
# Set max_statement_time = 0 selama backup
# ------------------------------------------------------------
mysql -uroot -pxxxx -e "SET GLOBAL max_statement_time=0;"
# ------------------------------------------------------------
# Cari database berdasarkan keyword
#
# -N = skip column name
# -B = batch mode
# ------------------------------------------------------------
#mapfile -t db_array < <(
# mysql -uroot -pxxxx -N -B \
# -e "SHOW DATABASES LIKE '%${dblist}%';"
#)
mapfile -t db_array < <(
mysql -uroot -pxxxx -N -B \
-e "SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME LIKE '%${dblist}%'
AND SCHEMA_NAME NOT LIKE '%_dmart'
AND SCHEMA_NAME NOT LIKE '%_archive'
AND SCHEMA_NAME NOT LIKE '%_temp'
ORDER BY SCHEMA_NAME;"
)
# ------------------------------------------------------------
# Cek apakah database ditemukan
# ------------------------------------------------------------
if [ ${#db_array[@]} -eq 0 ]; then
echo ""
echo "Database dengan keyword '${dblist}' tidak ditemukan."
echo ""
mysql -uroot -pxxxx -e "SET GLOBAL max_statement_time=60;"
exit 1
fi
# ------------------------------------------------------------
# Tampilkan daftar database
# ------------------------------------------------------------
echo ""
echo "Database yang ditemukan:"
echo "========================"
for i in "${!db_array[@]}"; do
printf "%2d. %s\n" "$((i+1))" "${db_array[$i]}"
done
echo ""
# ------------------------------------------------------------
# Minta user memilih database
# ------------------------------------------------------------
while true; do
read -rp "Masukkan nomor database yang mau dibackup [1-${#db_array[@]}]: " pilihan
# Cek apakah angka
if [[ "$pilihan" =~ ^[0-9]+$ ]]; then
# Cek range
if [ "$pilihan" -ge 1 ] &&
[ "$pilihan" -le "${#db_array[@]}" ]; then
break
fi
fi
echo "Pilihan tidak valid."
echo "Silakan masukkan nomor 1 sampai ${#db_array[@]}."
echo ""
done
# ------------------------------------------------------------
# Ambil nama database berdasarkan pilihan
# ------------------------------------------------------------
dblist="${db_array[$((pilihan-1))]}"
echo ""
echo "=========================================="
echo "Database yang dipilih : $dblist"
echo "=========================================="
echo ""
# ------------------------------------------------------------
# Directory backup
# ------------------------------------------------------------
backup_dir="/home/backup/$datex/$dblist"
mkdir -p "$backup_dir"
# ------------------------------------------------------------
# Backup pertama
# ------------------------------------------------------------
echo "Backup full '${dblist}_dmart' Start"
date
mysqldump -uroot -pxxxx \
-CfQq \
--max-allowed-packet=1G \
--hex-blob \
--order-by-primary \
--single-transaction \
--routines=true \
--triggers=true \
--no-data=false \
"${dblist}_dmart" |
gzip -c > "$backup_dir/${dblist}_dmart_${datey}_exit.sql.gz"
# ------------------------------------------------------------
# Backup kedua
# ------------------------------------------------------------
echo "Backup full '${dblist}_dmart' Finished"
date
echo
echo "Backup full '${dblist}' Start"
date
mysqldump -uroot -pxxxx \
-CfQq \
--max-allowed-packet=1G \
--hex-blob \
--order-by-primary \
--single-transaction \
--routines=true \
--triggers=true \
--no-data=false \
"${dblist}" |
gzip -c > "$backup_dir/${dblist}_${datey}_exit.sql.gz"
# ------------------------------------------------------------
# Kembalikan max_statement_time
# ------------------------------------------------------------
mysql -uroot -pxxxx -e "SET GLOBAL max_statement_time=60;"
echo "Backup full '${dblist}' Finished"
date
# ------------------------------------------------------------
# Tampilkan file backup
# ------------------------------------------------------------
echo ""
echo "File Backup database '$dblist'"
echo "Directory : $backup_dir"
echo ""
ls -lh "$backup_dir"
# ------------------------------------------------------------
# Finish Process
# ------------------------------------------------------------
dateyd=$(date "+%Y-%m-%d %H:%M:%S.%N")
epoch_end=$(date +%s)
echo ""
echo "$dateyd ->>> Finish Process"
# ------------------------------------------------------------
# Menghitung duration
# ------------------------------------------------------------
finish=$((epoch_end - epoch_start))
days=$((finish / 86400))
hours=$(((finish % 86400) / 3600))
minutes=$(((finish % 3600) / 60))
seconds=$((finish % 60))
# ------------------------------------------------------------
# Konversi tahun / bulan / hari
# ------------------------------------------------------------
yearsx=$((finish / 31536000))
remaining=$((finish % 31536000))
monthsx=$((remaining / 2592000))
remaining=$((remaining % 2592000))
daysx=$((remaining / 86400))
remaining=$((remaining % 86400))
hoursx=$((remaining / 3600))
remaining=$((remaining % 3600))
minutesx=$((remaining / 60))
secondsx=$((remaining % 60))
# ------------------------------------------------------------
# Format duration
# ------------------------------------------------------------
formatted_time=$(printf "%d:%02d:%02d:%02d" \
"$days" \
"$hours" \
"$minutes" \
"$seconds")
formatted_time2=$(printf "%d Tahun, %d Bulan, %d Hari, %02d:%02d:%02d" \
"$yearsx" \
"$monthsx" \
"$daysx" \
"$hoursx" \
"$minutesx" \
"$secondsx")
# ------------------------------------------------------------
# Output duration
# ------------------------------------------------------------
echo ""
echo "Duration Process"
echo "Start : $datexd"
echo "Finish : $dateyd"
echo "Duration 1 : $formatted_time"
echo "Duration 2 : $formatted_time2"
echo ""
echo "Copyright by : Teguh Triharto"
echo "Website : https://www.linkedin.com/in/teguhth"
echo ""
[root@teguhth-all devbackup]#
/data/devbackup
[root@teguhth-all devbackup]# cat backup_all_maria.sh
#!/bin/bash
# ============================================================
# Backup Database MariaDB
# Usage:
# sh 1.sh <keyword_database>
#
# Example:
# sh 1.sh teguhth
# ============================================================
# ------------------------------------------------------------
# Menyimpan waktu mulai
# ------------------------------------------------------------
datexd=$(date "+%Y-%m-%d %H:%M:%S.%N")
epoch_start=$(date +%s)
echo "$datexd ->>> Start Process"
# ------------------------------------------------------------
# Validasi argument
# ------------------------------------------------------------
if [ -z "$1" ]; then
echo "Error: ACC name argument is required."
echo "Usage: $0 <keyword_database>"
exit 1
fi
# Keyword pencarian database
dblist="$1"
alias mysql=mariadb
alias mysqldump=mariadb-dump
# ------------------------------------------------------------
# Variable tanggal
# ------------------------------------------------------------
datex=$(date +"%Y%m%d")
datey=$(date +"%Y%m%d_%H%M%S")
# ------------------------------------------------------------
# Waktu proses kedua
# ------------------------------------------------------------
datexy=$(date "+%Y-%m-%d %H:%M:%S.%N")
#echo "$datexy ->>> Start Process"
# ------------------------------------------------------------
# Set max_statement_time = 0 selama backup
# ------------------------------------------------------------
mysql -uroot -pxxxx -e "SET GLOBAL max_statement_time=0;"
# ------------------------------------------------------------
# Cari database berdasarkan keyword
#
# -N = skip column name
# -B = batch mode
# ------------------------------------------------------------
#mapfile -t db_array < <(
# mysql -uroot -pxxxx -N -B \
# -e "SHOW DATABASES LIKE '%${dblist}%';"
#)
mapfile -t db_array < <(
mysql -uroot -pxxxx -N -B \
-e "SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME LIKE '%${dblist}%'
AND SCHEMA_NAME NOT LIKE '%_dmart'
AND SCHEMA_NAME NOT LIKE '%_archive'
AND SCHEMA_NAME NOT LIKE '%_temp'
ORDER BY SCHEMA_NAME;"
)
# ------------------------------------------------------------
# Cek apakah database ditemukan
# ------------------------------------------------------------
if [ ${#db_array[@]} -eq 0 ]; then
echo ""
echo "Database dengan keyword '${dblist}' tidak ditemukan."
echo ""
mysql -uroot -pxxxx -e "SET GLOBAL max_statement_time=60;"
exit 1
fi
# ------------------------------------------------------------
# Tampilkan daftar database
# ------------------------------------------------------------
echo ""
echo "Database yang ditemukan:"
echo "========================"
for i in "${!db_array[@]}"; do
printf "%2d. %s\n" "$((i+1))" "${db_array[$i]}"
done
echo ""
# ------------------------------------------------------------
# Minta user memilih database
# ------------------------------------------------------------
while true; do
read -rp "Masukkan nomor database yang mau dibackup [1-${#db_array[@]}]: " pilihan
# Cek apakah angka
if [[ "$pilihan" =~ ^[0-9]+$ ]]; then
# Cek range
if [ "$pilihan" -ge 1 ] &&
[ "$pilihan" -le "${#db_array[@]}" ]; then
break
fi
fi
echo "Pilihan tidak valid."
echo "Silakan masukkan nomor 1 sampai ${#db_array[@]}."
echo ""
done
# ------------------------------------------------------------
# Ambil nama database berdasarkan pilihan
# ------------------------------------------------------------
dblist="${db_array[$((pilihan-1))]}"
echo ""
echo "=========================================="
echo "Database yang dipilih : $dblist"
echo "=========================================="
echo ""
# ------------------------------------------------------------
# Directory backup
# ------------------------------------------------------------
backup_dir="/home/backup/$datex/$dblist"
mkdir -p "$backup_dir"
# ------------------------------------------------------------
# Backup pertama
# ------------------------------------------------------------
echo "Backup full '${dblist}_dmart' Start"
date
mysqldump -uroot -pxxxx \
-CfQq \
--max-allowed-packet=1G \
--hex-blob \
--order-by-primary \
--single-transaction \
--routines=true \
--triggers=true \
--no-data=false \
"${dblist}_dmart" |
gzip -c > "$backup_dir/${dblist}_dmart_${datey}_exit.sql.gz"
# ------------------------------------------------------------
# Backup kedua
# ------------------------------------------------------------
echo "Backup full '${dblist}_dmart' Finished"
date
echo
echo "Backup full '${dblist}' Start"
date
mysqldump -uroot -pxxxx \
-CfQq \
--max-allowed-packet=1G \
--hex-blob \
--order-by-primary \
--single-transaction \
--routines=true \
--triggers=true \
--no-data=false \
"${dblist}" |
gzip -c > "$backup_dir/${dblist}_${datey}_exit.sql.gz"
# ------------------------------------------------------------
# Kembalikan max_statement_time
# ------------------------------------------------------------
mysql -uroot -pxxxx -e "SET GLOBAL max_statement_time=60;"
echo "Backup full '${dblist}' Finished"
date
# ------------------------------------------------------------
# Tampilkan file backup
# ------------------------------------------------------------
echo ""
echo "File Backup database '$dblist'"
echo "Directory : $backup_dir"
echo ""
ls -lh "$backup_dir"
# ------------------------------------------------------------
# Finish Process
# ------------------------------------------------------------
dateyd=$(date "+%Y-%m-%d %H:%M:%S.%N")
epoch_end=$(date +%s)
echo ""
echo "$dateyd ->>> Finish Process"
# ------------------------------------------------------------
# Menghitung duration
# ------------------------------------------------------------
finish=$((epoch_end - epoch_start))
days=$((finish / 86400))
hours=$(((finish % 86400) / 3600))
minutes=$(((finish % 3600) / 60))
seconds=$((finish % 60))
# ------------------------------------------------------------
# Konversi tahun / bulan / hari
# ------------------------------------------------------------
yearsx=$((finish / 31536000))
remaining=$((finish % 31536000))
monthsx=$((remaining / 2592000))
remaining=$((remaining % 2592000))
daysx=$((remaining / 86400))
remaining=$((remaining % 86400))
hoursx=$((remaining / 3600))
remaining=$((remaining % 3600))
minutesx=$((remaining / 60))
secondsx=$((remaining % 60))
# ------------------------------------------------------------
# Format duration
# ------------------------------------------------------------
formatted_time=$(printf "%d:%02d:%02d:%02d" \
"$days" \
"$hours" \
"$minutes" \
"$seconds")
formatted_time2=$(printf "%d Tahun, %d Bulan, %d Hari, %02d:%02d:%02d" \
"$yearsx" \
"$monthsx" \
"$daysx" \
"$hoursx" \
"$minutesx" \
"$secondsx")
# ------------------------------------------------------------
# Output duration
# ------------------------------------------------------------
echo ""
echo "Duration Process"
echo "Start : $datexd"
echo "Finish : $dateyd"
echo "Duration 1 : $formatted_time"
echo "Duration 2 : $formatted_time2"
echo ""
echo "Copyright by : Teguh Triharto"
echo "Website : https://www.linkedin.com/in/teguhth"
echo ""
[root@teguhth-all devbackup]#
No comments:
Post a Comment