Backup restore database besar MySQL

Bismillaahirrohmaanirrohiim…

Tujuan penulisan ini adalah untuk:

Melakukan backup data besar MySQL
Dilakukan menggunakan command line
Restore file SQL ke database

Artinya jika hanya backup restore database kecil menggunakan PHPMyAdmin sudah cukup mudah.

Oke, langkah pertama adalah buka Command Prompt kalau di windows, ketik CMD lalu enter

Arahkan pointer ke mysql bin, contoh

cd D:\xampp\mysql\bin

Perintah Backup Database besar dengan Command Line

mysqldump -u root -p namadatabase > "D:\lokasi_backup\namadatabase.sql"

Perintah Backup table besar MySQL

mysqldump -u root -p namadatabase namatable > "D:\lokasi_backup\namatable.sql"

Saat perintah di atas dijalankan, akan ada prompt notifikasi untuk memasukkan password MySQL.

Untuk melakukan bypass agar tidak ada peringatan untuk memasukkan username atau password, silahkan modifikasi file my.ini

[mysqldump]
quick
max_allowed_packet = 16M
password=123
user=root

Maka perintah backupnya akan berubah menjadi

mysqldump -u root namadatabase > "D:\lokasi_backup\namadatabase.sql"

atau

mysqldump namadatabase > "D:\lokasi_backup\namadatabase.sql"

Perintah Restore database besar mySQL

mysql database_name -u root -p namadatabase < "D:\lokasi_backup\namadatabase.sql"

pakai cara di atas terkadang ada masalah, yaitu jika kita restore file ke 2, ke 3, dst, yang di import cuma yang pertama.

bisa juga dengan menggunakan perintah di bawah ini (DIREKOMENDASIKAN).

mysql -u root
show databases;
use namadatabase;
source "D:\lokasi_backup\namadatabase.sql"

Updated:

Perintah backup dan restore hanya berbeda tanda > (untuk backup) dan < (untuk restore), jadi harus hati-hati saat menjalankan perintahnya.

mysqldump -u root -p namadatabase > "D:\lokasi_backup\namadatabase.sql"
dan
mysqldump -u root -p namadatabase < "D:\lokasi_backup\namadatabase.sql"

jika gagal, coba tidak pakai tanda petik.

Adakalanya perintah backup atau restore di atas gagal, jadi harus masuk ke MySQL menggunakan command LINE:

//masuk MySQL
mysql -u userdatabase -p
//masukkan password
use namadatabase
//untuk restore 
source /home/domain.com/public_html/database.sql

Tambahan info:

1. Kadangkala saat import data besar ada masalah, yaitu “MySQL Error 1153 – Got a packet bigger than ‘max_allowed_packet’ bytes”, ini perlu merubah file my.ini atau my.cnf pada parameter max_allowed_packet atau max max yang lainnya.

Kemudian masuk mysql via command line, dan jalankan perintah di bawah ini:

set global net_buffer_length=1000000;
set global max_allowed_packet=1000000000;

Setelah itu bisa direstart mysqlnya.

2. Jika errornya set foreign_key_checks = on error, maka import tidak bisa dilakukan via phpmyadmin, langsung via command line.

3. Setelah import sukses, jika ada error SELECT command denied to user ‘pma’@’localhost’ for table ‘pma_table_uiprefs’, saat klik tabel di phpmyadmin, maka perlu di reset cookienya dengan cara klik icon keluar disamping icon home pada phpmyadmin pojok kiri atas.

Demikian, semoga bermanfaat.