Tutorial Backup Database MySQL di VPS

 Berikut panduan cara backup database MySQL di VPS (Linux – Ubuntu/Debian/CentOS).

Gunakan SSH untuk menjalankan perintah berikut.

---

✅ 1. Backup Satu Database

mysqldump -u USERNAME -p NAMA_DATABASE > backup.sql

Contoh:

mysqldump -u root -p laravel_db > laravel_db_2025.sql

---

✅ 2. Backup Semua Database

mysqldump -u root -p --all-databases > all_db_backup.sql

---

✅ 3. Backup Beberapa Database Sekaligus

Buat file list:

echo "db1 db2 db3" > dblist.txt

Lalu:

mysqldump -u root -p --databases $(cat dblist.txt) > multi_backup.sql

---

✅ 4. Backup Dengan Kompresi (lebih kecil)

mysqldump -u root -p nama_db | gzip > backup.sql.gz

---

✅ 5. Backup dengan Auto-Date (otomatis timestamp)

mysqldump -u root -p nama_db > nama_db_$(date +"%Y-%m-%d").sql

---

✅ 6. Menjadwalkan Backup Otomatis (Cron Job)

Edit cron:

crontab -e

Tambah jadwal backup tiap pukul 02:00:

0 2 * * * mysqldump -u root -p'PASSWORD' nama_db | gzip > /backup/nama_db_$(date +\%F).sql.gz

Catatan: Jika cron tidak menerima password tanpa prompt, gunakan file .my.cnf.

berikut script backup MySQL otomatis + upload ke Google Drive menggunakan gdrive CLI (paling praktis untuk VPS).

---

✅ 1. Install gdrive (Google Drive CLI)

Unduh binary

wget -O gdrive https://drive.google.com/uc?id=0Bw0jVWTezT4Sa19wQmZ0bE5pWkE
chmod +x gdrive
sudo mv gdrive /usr/local/bin/

Login

gdrive about

Perintah itu akan menampilkan URL — buka di browser, login Google, lalu tempel kode autorisasi ke terminal.

---

✅ 2. Buat Folder Backup di Google Drive

gdrive mkdir mysql-backup

Catat folder ID yang muncul, misalnya:

Folder Id: 1A2B3C4D5E...

---

✅ 3. Buat Script Backup + Upload

Buat file:

sudo nano /usr/local/bin/mysql-backup.sh

Isi dengan script ini:

#!/bin/bash

# KONFIGURASI
USER="root"
PASS="password_mysql"
DB="nama_database"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +"%Y-%m-%d_%H-%M")
GDRIVE_FOLDER="ID_FOLDER_GOOGLE_DRIVE"

# Membuat folder backup lokal
mkdir -p $BACKUP_DIR

# Nama file
FILENAME="$DB-$DATE.sql.gz"

# Backup & kompres
mysqldump -u $USER -p$PASS $DB | gzip > $BACKUP_DIR/$FILENAME

# Upload ke Google Drive
gdrive upload --parent $GDRIVE_FOLDER $BACKUP_DIR/$FILENAME

# Hapus backup lokal lebih dari 7 hari
find $BACKUP_DIR -type f -mtime +7 -delete

Simpan ➜ Ctrl+O, Enter, Ctrl+X

Jadikan executable

chmod +x /usr/local/bin/mysql-backup.sh

---

✅ 4. Jadwalkan Backup Otomatis (Cron Job)

Backup tiap jam 02:00 pagi:

crontab -e

Tambahkan:

0 2 * * * /usr/local/bin/mysql-backup.sh