Salin dan Bagikan
MySQL Backup Database Administration
Cara Backup dan Restore Database MySQL dengan mysqldump: Panduan Lengkap - Panduan lengkap cara backup dan restore database MySQL menggunakan mysqldump dengan berbagai …

Cara Backup dan Restore Database MySQL dengan mysqldump: Panduan Lengkap

Cara Backup dan Restore Database MySQL dengan mysqldump: Panduan Lengkap

Backup adalah aspek paling kritis dalam administrasi database. Kehilangan data bisa berarti kehilangan bisnis. Setelah mengalami kecelakaan data yang hampir fatal, saya memahami betapa pentingnya strategi backup yang solid. Artikel ini akan membahas semua aspek backup dan restore MySQL menggunakan mysqldump.

Mengapa mysqldump?

mysqldump adalah tool bawaan MySQL yang handal karena:

  • Tersedia di semua instalasi MySQL
  • Mendukung berbagai format output
  • Dapat membackup schema dan data
  • Portabel antar versi MySQL
  • Mendukung kompresi dan enkripsi

Persiapan Sebelum Backup

1. Verifikasi Space Disk

df -h

Pastikan tersedia space minimal 2x ukuran database.

2. Cek Ukuran Database

SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema;

3. Buat User Backup

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupPass123!';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

Backup Single Database

1. Backup Basic

mysqldump -u backup_user -p database_name > backup_database_name.sql

2. Backup dengan Kompresi

mysqldump -u backup_user -p database_name | gzip > backup_database_name.sql.gz

3. Backup dengan Timestamp

mysqldump -u backup_user -p database_name > backup_$(date +%Y%m%d_%H%M%S).sql

Backup Multiple Database

1. Backup Beberapa Database Tertentu

mysqldump -u backup_user -p --databases db1 db2 db3 > multiple_databases_backup.sql

2. Backup Semua Database

mysqldump -u backup_user -p --all-databases > full_backup.sql

3. Backup dengan Options Lengkap

mysqldump -u backup_user -p \
    --all-databases \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --hex-blob \
    --lock-tables=false \
    > full_backup_$(date +%Y%m%d).sql

Backup Tabel Tertentu Saja

1. Backup Beberapa Tabel

mysqldump -u backup_user -p database_name table1 table2 > backup_tables.sql

2. Backup dengan Data Only

mysqldump -u backup_user -p --no-create-info database_name table_name > data_only.sql

3. Backup Schema Only

mysqldump -u backup_user -p --no-data database_name > schema_only.sql

Backup dengan Options Production-Ready

1. Single Transaction (untuk InnoDB)

mysqldump -u backup_user -p \
    --single-transaction \
    --quick \
    --lock-tables=false \
    database_name > backup.sql

2. Master Data (untuk Replication)

mysqldump -u backup_user -p \
    --master-data=2 \
    --single-transaction \
    --all-databases > replication_backup.sql

3. Backup dengan Enkripsi

mysqldump -u backup_user -p database_name | \
    openssl enc -aes-256-cbc -salt -k 'SecretPassword' | \
    gzip > backup_encrypted.sql.gz

Restore Database dari Backup

1. Restore Single Database

mysql -u root -p database_name < backup_file.sql

2. Restore ke Database Baru

mysql -u root -p -e "CREATE DATABASE new_database;"
mysql -u root -p new_database < backup_file.sql

3. Restore dari File Terkompresi

gunzip < backup_file.sql.gz | mysql -u root -p database_name

4. Restore Multiple Database

mysql -u root -p < multiple_databases_backup.sql

5. Restore Tabel Tertentu

mysql -u root -p database_name < table_backup.sql

Restore dengan Options Tambahan

1. Restore dengan Force

Lewati error dan lanjutkan:

mysql -u root -p --force database_name < backup_file.sql 2> restore_errors.log

2. Restore dengan Verbose

mysql -u root -p -v database_name < backup_file.sql

3. Restore dengan Sumber Terpisah

mysql -u root -p -h remote_server -P 3306 database_name < backup_file.sql

Backup Otomatis dengan Cron

1. Script Backup Harian

sudo nano /usr/local/bin/mysql_backup.sh

#!/bin/bash

# Konfigurasi
DB_USER="backup_user"
DB_PASS="BackupPass123!"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# Buat direktori jika belum ada
mkdir -p $BACKUP_DIR

# Backup semua database
mysqldump -u $DB_USER -p$DB_PASS \
    --all-databases \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --hex-blob > $BACKUP_DIR/full_backup_$DATE.sql

# Kompresi
gzip $BACKUP_DIR/full_backup_$DATE.sql

# Hapus backup lama
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

# Log
echo "Backup selesai: full_backup_$DATE.sql.gz" >> /var/log/mysql_backup.log

2. Beri Izin Eksekusi

sudo chmod +x /usr/local/bin/mysql_backup.sh
sudo mkdir -p /backup/mysql
sudo chown mysql:mysql /backup/mysql

3. Setup Cron Job

sudo crontab -e

Tambahkan untuk backup harian jam 2 pagi:

0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

Backup dengan MySQL Enterprise Backup (Alternatif)

Untuk database besar, pertimbangkan hot backup:

mysqlbackup --user=backup_user --password --backup-dir=/backup/mysql backup

Tips dan Best Practices

1. Test Restore Secara Berkala

# Test restore ke database test
mysql -u root -p -e "CREATE DATABASE test_restore;"
mysql -u root -p test_restore < backup_file.sql
mysql -u root -p -e "DROP DATABASE test_restore;"

2. Monitoring Backup

# Cek ukuran backup terbaru
ls -lh /backup/mysql/*.sql.gz | tail -5

# Cek log backup
tail -f /var/log/mysql_backup.log

3. Backup Offsite

Sync ke cloud storage:

# Contoh dengan rclone
rclone sync /backup/mysql remote:backup-bucket/mysql/

4. Verifikasi Integrity

# Cek file terkompresi
gunzip -t backup_file.sql.gz

# Verifikasi checksum
md5sum backup_file.sql.gz > backup_file.md5

Troubleshooting Restore

Error: “Unknown database”

Buat database terlebih dahulu:

CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Error: “Table already exists”

Gunakan opsi force atau hapus tabel:

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS table_name;
SET FOREIGN_KEY_CHECKS = 1;

Error: “Duplicate entry”

Kosongkan tabel sebelum restore:

TRUNCATE TABLE table_name;

Kesimpulan

mysqldump adalah tool backup yang powerful dan reliable. Dengan script otomatis dan strategi backup yang tepat, Anda dapat melindungi data bisnis dari kehilangan. Ingat: backup yang tidak pernah di-test adalah backup yang mungkin tidak berfungsi ketika dibutuhkan.

Artikel Terkait

Link Postingan : https://www.tirinfo.com/cara-backup-restore-mysql-mysqldump/

Hendra WIjaya
Tirinfo
4 minutes.
3 February 2026