.::: Backup & Restore MariaDB MySQL using Shell Script in Linux :::.

1. create user backup

mysql -u root -p

CREATE USER 'backupuser'@'%' IDENTIFIED BY 'password';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON *.* TO 'backupuser'@'%';
GRANT RELOAD ON *.* TO 'backupuser'@'%';
FLUSH PRIVILEGES;

CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON *.* TO 'backupuser'@'localhost';
GRANT RELOAD ON *.* TO 'backupuser'@'localhost';
FLUSH PRIVILEGES;
 
2. copy script to
[root@teguhth ~]# cd /data/script/
[root@teguhth script]# ls
backup_full.sh  backup_transaction.sh
[root@teguhth script]#


3. run script backup full
[root@teguhth script]# cat backup_full.sh | grep DB_NAME=
DB_NAME="teguhth"
#DB_NAME="secretdb"
[root@teguhth script]# sh backup_full.sh
+------------+-----------------+
| @@hostname | @@version       |
+------------+-----------------+
| teguhth    | 10.5.21-MariaDB |
+------------+-----------------+

tar: Removing leading `/' from member names
/home/backup/full/teguhth/teguhth_backup_full_20230727_140842.sql
Backup penuh telah berhasil pada 2023-07-27_14:08:42.
[root@teguhth script]#
[root@teguhth script]# cat backup_full.sh | grep DB_NAME=
#DB_NAME="teguhth"
DB_NAME="secretdb"
[root@teguhth script]# sh backup_full.sh
+------------+-----------------+
| @@hostname | @@version       |
+------------+-----------------+
| teguhth    | 10.5.21-MariaDB |
+------------+-----------------+

tar: Removing leading `/' from member names
/home/backup/full/teguhth/secretdb_backup_full_20230727_140918.sql
Backup penuh telah berhasil pada 2023-07-27_14:09:18.
[root@teguhth script]#
 

4. check file backup

 
5. backup transaction

[root@teguhth script]# cat backup_transaction.sh | grep DB_NAME=
#DB_NAME="teguhth"
DB_NAME="secretdb"
[root@teguhth script]# sh backup_transaction.sh
+------------+-----------------+
| @@hostname | @@version       |
+------------+-----------------+
| teguhth    | 10.5.21-MariaDB |
+------------+-----------------+
tar: Removing leading `/' from member names
/home/backup/transaction/teguhth/20230727/secretdb_transaksi_141227.sql
Backup transaksi telah berhasil pada 2023-07-27_14:12:27.
[root@teguhth script]#
[root@teguhth script]#
 

6.check backup transaction file
 
7. query for backup_full
[root@teguhth script]#  cat backup_full.sh
#!/bin/bash
# backup full

#year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;

#backuppath="/backupdb/${year}/${month}/${day}";

# Konfigurasi
IP_DB="localhost"
DB_USER="backupuser"
DB_PASS="password"
#DB_NAME="teguhth"
DB_NAME="secretdb"
DB_PORT=3306
BACKUP_DIR="/home/backup/full/teguhth"

# mkdir -p /home/backup/full/teguhth

mysql -h$IP_DB -u $DB_USER -p$DB_PASS -e 'SELECT @@hostname,@@version;'

echo ''
echo ''
# Pastikan direktori backup ada
mkdir -p "$BACKUP_DIR"

# Buat nama file backup dengan timestamp
backup_file="$BACKUP_DIR/"$DB_NAME"_backup_full_$(date +%Y%m%d_%H%M%S).sql"

# Jalankan perintah mysqldump untuk melakukan backup penuh
# mysqldump -u $DB_USER -p$DB_PASS -P$DB_PORT --single-transaction --routines --triggers --all-databases > "$backup_file"
mysqldump -h$IP_DB -u $DB_USER -p$DB_PASS -P$DB_PORT --single-transaction --routines --triggers $DB_NAME > "$backup_file"

# Kompres file backup menjadi file .tar.gz
tar -czvf "$backup_file.tar.gz" "$backup_file"

# Hapus file SQL mentah setelah dikompresi
rm "$backup_file"

# Selesai
echo "Backup penuh telah berhasil pada $(date +%Y-%m-%d_%H:%M:%S)."


[root@teguhth script]#

8. query for backup_transaction

[root@teguhth script]# cat backup_transaction.sh
#!/bin/bash
# backup transaction

# Konfigurasi
IP_DB="localhost"
DB_USER="backupuser"
DB_PASS="password"
#DB_NAME="teguhth"
DB_NAME="secretdb"
DB_PORT=3306
BACKUP_DIR="/home/backup/transaction/teguhth"

# mkdir -p /home/backup/transaction/teguhth

mysql -h$IP_DB -u $DB_USER -p$DB_PASS -e 'SELECT @@hostname,@@version;'

# Pastikan direktori backup ada
mkdir -p "$BACKUP_DIR"

# Buat folder berdasarkan tanggal
backup_folder="$BACKUP_DIR/$(date +%Y%m%d)"

# Pastikan folder backup dengan tanggal sudah ada
mkdir -p "$backup_folder"

# Buat nama file backup dengan timestamp
backup_file="$backup_folder/"$DB_NAME"_transaksi_$(date +%H%M%S).sql"

# Jalankan perintah mysqldump untuk melakukan backup transaksi
mysqldump -h$IP_DB -u $DB_USER -p$DB_PASS -P$DB_PORT $DB_NAME > "$backup_file"

# Kompres file backup menjadi file .tar.gz
tar -czvf "$backup_file.tar.gz" "$backup_file"

# Hapus file SQL mentah setelah dikompresi
rm "$backup_file"

# Selesai
echo "Backup transaksi telah berhasil pada $(date +%Y-%m-%d_%H:%M:%S)."
[root@teguhth script]#