Cara Backup Database di MySQL

Backup database wajib dilakukan sebelum instal ulang server atau sebagai langkah antisipasi apabila terjadi kegagalan sistem, kerusakan data, atau kesalahan konfigurasi.

Backup dapat dilakukan melalui:

• phpMyAdmin (GUI)
• CLI (Command Line Interface) menggunakan mysqldump

Backup Semua Database

#

Gunakan perintah berikut untuk membackup seluruh database dalam satu file:

mysqldump \
 -u root \
 -p \
 --all-databases \
 --single-transaction \
 --no-tablespaces \
 --quick > backup-$(date +%Y-%m-%d).sql

Backup Beberapa Database (Dipisah per Database)

#

Jika memiliki banyak database dan ingin memisahkan backup satu file per database, gunakan perintah berikut:

for x in `mysql -u root -p --skip-column-names --batch -e "SHOW DATABASES" | grep -Ev "(Database|information_schema|mysql|performance_schema|phpmyadmin|sys)"`; do
  mysqldump -u root -p --single-transaction --no-tablespaces --quick "$x" > "$x-$(date +%Y-%m-%d).sql"
done

Backup Semua Database Menggunakan Script

#

Script berikut akan:

• Membuat direktori backup jika belum ada
• Membackup setiap database
• Mengompresi hasil backup dengan bzip2

#!/bin/bash

BACKUPDIR="/home/dbbackups"
DATE=$(date +%Y-%m-%d)

if [ ! -d "$BACKUPDIR" ]; then
  mkdir -p "$BACKUPDIR"
  chmod 700 "$BACKUPDIR"
fi

for DB in `mysqlshow | grep -v "_schema" | awk -F "| " '{print $2}'`; do
  mysqldump --single-transaction --no-tablespaces --quick "$DB" \
  | bzip2 -9czq > "$BACKUPDIR/${DB}-${DATE}.sql.bz2"
done

Backup Struktur Database Saja (Tanpa Data)

#

Digunakan jika hanya ingin menyimpan struktur tabel (schema):

mysqldump \
 -u root \
 -p \
 --all-databases \
 --no-data \
 --single-transaction \
 --triggers \
 --routines \
 --events > struktur.sql

Backup Data Saja (Tanpa Struktur)

#

Digunakan jika struktur database sudah ada dan hanya ingin membackup data:

mysqldump \
 -u root \
 -p \
 --all-databases \
 --no-create-info \
 --complete-insert \
 --single-transaction \
 --triggers \
 --routines \
 --events > data_only.sql

Backup Routine dan Trigger Saja

#

Untuk membackup stored procedure, function, dan trigger pada satu database tertentu:

mysqldump \
 -u root \
 -p db_name \
 --routines \
 --triggers > db_backup.sql

Backup Database Menggunakan phpMyAdmin

#

1. Buka phpMyAdmin

2. Pilih Database

3. Klik menu Export

4. Klik Go untuk export default

   

5. Pilih Custom jika ingin:

   • Export struktur saja
   • Export data saja
   • Mengatur format output

     

Restore Semua Database

#

Jika file backup berisi seluruh database (--all-databases), gunakan perintah berikut:

mysql -u root -p < backup.sql

Restore Satu Database Saja

#

Jika file backup hanya berisi satu database, gunakan perintah berikut:

mysql -u root -p db_app < db_app-2026-01-04.sql

Jika file backup terkompresi, gunakan perintah berikut:

bzcat backup.sql.bz2 | mysql -u username -p db_name

Restore Menggunakan Perintah source (MySQL Shell)

#

Metode ini berguna jika ingin menjalankan restore secara interaktif.

1. Masuk ke MySQL shell dan pilih database:

mysql -u root -p nama_database

2. Jalankan perintah restore:

source backup.sql;

Pastikan path file benar (gunakan path absolut jika perlu).

Restore Struktur Saja

#

Digunakan jika ingin membuat ulang schema / tabel tanpa data.

Dari File Backup Lengkap

#

mysql -u root -p --no-data nama_database < backup.sql

Menggunakan sed (jika file tidak dipisah)

#

sed -n '/^CREATE TABLE/,/;$/p' backup.sql | mysql -u root -p nama_database

Restore Data Saja

#

Digunakan jika struktur database sudah ada.

Dari File Backup Lengkap

#

mysql -u root -p --no-create-info nama_database < backup.sql

Restore Menggunakan phpMyAdmin

#

1. Buka phpMyAdmin
2. Pilih Database tujuan
3. Klik menu Import
4. Pilih dan upload file .sql

   

5. Klik Go

Troubleshooting Error Saat Restore

#

1. Error: max_allowed_packet

#

Gejala:

ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet'

Solusi sementara (CLI):

mysql -u root -p --max_allowed_packet=512M nama_database < backup.sql

Solusi permanen (my.cnf / my.ini):

[mysqld]
max_allowed_packet=512M

[client]
max_allowed_packet=512M

Restart MySQL:

systemctl restart mysql

2. Error Timeout / Koneksi Terputus

#

Gejala:

MySQL server has gone away

Solusi:

[mysqld]
wait_timeout=28800
interactive_timeout=28800
net_read_timeout=600
net_write_timeout=600

Atau gunakan CLI (lebih stabil daripada phpMyAdmin).

3. Error: Unknown collation / Unknown character set

#

Solusi cepat:

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' backup.sql

Atau saat restore:

mysql -u root -p --default-character-set=utf8mb4 nama_database < backup.sql

4. Error: Table already exists

#

Solusi:

• Drop database terlebih dahulu:

DROP DATABASE nama_database;
CREATE DATABASE nama_database;

Atau edit file .sql dan pastikan ada:

DROP TABLE IF EXISTS nama_tabel;

5. Error: Access denied for user

#

Pastikan user memiliki privilege:

GRANT ALL PRIVILEGES ON nama_database.* TO 'root'@'localhost';
FLUSH PRIVILEGES;