Dua metode utama untuk membuat cadangan database adalah dengan menggunakan program mysqldump atau langsung menyalin file database (seperti menggunakan cp, cpio atau tar, dll.). Jika tabel database hilang atau rusak, penting untuk membuat cadangan database Anda. Jika terjadi kerusakan sistem, Anda pasti ingin dapat memulihkan tabel Anda ke kondisi saat kerusakan terjadi dengan kehilangan data sesedikit mungkin. Terkadang, administrator MySQL-lah yang menyebabkan kekacauan. Administrator sudah mengetahui bahwa tabel tersebut rusak dan mencoba mengeditnya secara langsung menggunakan editor seperti vi atau Emacs jelas bukan hal yang baik untuk tabel tersebut.
Dua metode utama untuk membackup database adalah dengan menggunakan program mysqldump atau langsung menyalin file database (seperti menggunakan cp, cpio atau tar, dll). Setiap metode memiliki kelebihan dan kekurangannya:
mysqldump bekerja dengan server MySQL. Metode penyalinan langsung dilakukan di luar server, dan Anda harus mengambil langkah-langkah untuk memastikan bahwa tidak ada klien yang mengubah tabel yang Anda salin. Jika Anda ingin menggunakan cadangan sistem file untuk membuat cadangan database, masalah yang sama akan terjadi: jika tabel database diubah selama proses pencadangan sistem file, subjek file tabel yang dicadangkan akan menjadi tidak konsisten, dan tabel akan menjadi tidak ada artinya untuk pemulihan di masa depan. Perbedaan antara pencadangan sistem file dan salinan langsung file adalah bahwa dengan salinan langsung file, Anda memiliki kendali penuh atas proses pencadangan, sehingga Anda dapat mengambil langkah-langkah untuk memastikan bahwa server membiarkan tabel tidak terganggu.
mysqldump lebih lambat daripada penyalinan langsung.
mysqldump menghasilkan file teks yang portabel untuk mesin lain, bahkan mesin dengan arsitektur perangkat keras berbeda. File yang disalin secara langsung tidak dapat di-porting ke mesin lain kecuali tabel yang Anda salin menggunakan format penyimpanan MyISAM. Tabel ISAM hanya dapat disalin pada mesin dengan struktur perangkat keras serupa. Format penyimpanan tabel MyISAM yang diperkenalkan di MySQL 3.23 memecahkan masalah ini karena formatnya tidak bergantung pada mesin, sehingga penyalinan file secara langsung dapat dipindahkan ke mesin dengan struktur perangkat keras yang berbeda. Selama dua kondisi terpenuhi: mesin lain juga harus menjalankan MySQL 3.23 atau lebih baru, dan file harus direpresentasikan dalam format MyISAM, bukan format ISAM.
Apa pun metode pencadangan yang Anda gunakan, jika Anda perlu memulihkan database, ada beberapa prinsip yang harus diikuti untuk memastikan hasil terbaik:
Terapkan pencadangan rutin. Tetapkan rencana dan patuhi itu.
Biarkan server melakukan pembaruan logging. Changelog akan membantu Anda saat Anda perlu memulihkan data setelah terjadi kerusakan. Setelah Anda menggunakan file cadangan untuk memulihkan data ke keadaan pada saat pencadangan, Anda dapat menerapkan kembali perubahan yang dibuat setelah pencadangan dengan menjalankan kueri di log pembaruan, yang akan memulihkan tabel dalam database ke keadaan mereka saat kecelakaan itu terjadi.
Dalam istilah pencadangan sistem file, file cadangan database mewakili dump penuh, sedangkan log pembaruan mewakili dump tambahan.
Gunakan skema penamaan yang konsisten dan mudah dipahami untuk file cadangan. Hal-hal seperti backup1, buckup2, dll. tidak terlalu berarti. Saat melakukan pemulihan, Anda akan membuang waktu untuk mencari tahu apa yang ada di dalam file. Mungkin berguna bagi Anda untuk menggunakan nama dan tanggal database untuk membentuk nama file cadangan. Misalnya:
%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02
%kebun binatang mysqldump >/usr/archives/mysql/menagerie.1999-10-02
Anda mungkin ingin mengompresi cadangan setelah membuatnya. Cadangan cenderung besar! Anda juga perlu membuat file cadangan Anda kedaluwarsa agar tidak memenuhi disk Anda, sama seperti Anda membuat file log Anda kedaluwarsa.
Cadangkan file cadangan Anda dengan cadangan sistem file. Jika Anda mengalami kerusakan total yang tidak hanya menghapus direktori data Anda, tetapi juga disk drive yang berisi cadangan basis data Anda, Anda akan berada dalam masalah besar.
Cadangkan juga changelog Anda.
Tempatkan file cadangan Anda pada sistem file yang berbeda dari yang digunakan untuk database Anda. Hal ini akan mengurangi kemungkinan terisinya sistem file yang berisi direktori data akibat pembuatan cadangan.
Teknik yang digunakan untuk membuat cadangan juga berguna untuk menyalin database ke komputer lain. Umumnya, database dipindahkan ke server yang berjalan di host lain, namun Anda juga bisa memindahkan data ke server lain di host yang sama.
1 Gunakan mysqldump untuk membuat cadangan dan menyalin database
Saat Anda menggunakan program mysqldumo untuk membuat file cadangan database, secara default, konten file berisi pernyataan CREATE yang membuat tabel yang dibuang dan pernyataan INSERT yang berisi data baris dalam tabel. Dengan kata lain, output yang dihasilkan oleh mysqldump nantinya dapat dijadikan input bagi mysql untuk membangun kembali databasenya.
Anda dapat membuang seluruh database ke dalam satu file teks sebagai berikut:
%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02
Awal dari file keluaran terlihat seperti ini:
# MySQL Dump 6.0# # Host: localhost Basis data: samp_db
#-------------------------#
Server versi 3.23.2-alpha-log## Struktur tabel untuk ketidakhadiran tabel
#CREATE TABLE absensi( student_id int(10) unsigned DEFAULT 0 BUKAN NULL, tanggal tanggal DEFAULT 0000-00-00 BUKAN NUL L,
PRIMARY KEY (student_id,date));## Membuang data untuk tabel ketidakhadiran #INSERT INTOabsence VALUES (3,1999-09-03);INSERT INTOabsence VALUE S (5,1999-09-03);INSERT INTOabsence VALUES (10,1999-09-08);......
Sisa file terdiri dari lebih banyak pernyataan INSERT dan CREATE TABLE. Jika Anda ingin mengompresi cadangan, gunakan perintah seperti berikut:
%mysqldump samp_db |.gzip >/usr/archives/mysql/samp_db.1999-10-02.gz
Jika Anda memiliki database yang besar, file keluarannya juga akan besar dan mungkin sulit untuk dikelola. Jika mau, Anda dapat membuat daftar nama tabel individual setelah nama database pada baris perintah mysqldump untuk membuang isinya, yang akan memecah file dump menjadi file yang lebih kecil dan lebih mudah dikelola. Contoh berikut menunjukkan cara membuang beberapa tabel dari database samp_db ke dalam file terpisah:
%mysqldump samp_db skor siswa acara ketidakhadiran >grapbook.sql
%mysqldump samp_db anggota presiden >hist-league.sql
Jika Anda membuat file cadangan yang dimaksudkan untuk digunakan untuk menyegarkan konten database lain secara berkala, Anda mungkin ingin menggunakan opsi --add-drop-table. Ini memberitahu server untuk menulis pernyataan DROP TABLE IF EXISTS ke file cadangan, dan kemudian, ketika Anda mengambil file cadangan dan memuatnya ke database kedua, Anda tidak akan mendapatkan kesalahan jika tabel sudah ada.
Jika Anda membuang database sehingga Anda bisa memindahkan database ke server lain, Anda bahkan tidak perlu membuat file cadangan. Pastikan database ada di host lain, lalu gunakan pipa untuk membuang database sehingga mysql bisa langsung membaca output mysqldump. Misalnya: Anda ingin menyalin database samp_db dari host pit-viper.snake.net ke boa.snake.net Anda dapat melakukannya dengan mudah seperti ini:
%mysqladmin -h boa.snake.net buat samp_db
%mysqldump samp_db |.mysql -h boa.snake.net samp_db
Di masa depan, jika Anda ingin me-refresh database di boa.snake.net lagi, lewati perintah mysqladmin, tetapi tambahkan --add-drop-table ke mysqldump untuk menghindari kesalahan tabel sudah ada: %mysqldump --add- drop-tabel samp_db |.mysql -h boa.snake.net samp_db
Opsi mysqldump berguna lainnya meliputi: kombinasi --flush-logs dan --lock-tables akan berguna untuk memeriksa database Anda. --lock-tables mengunci semua tabel yang Anda buang, dan --flush-logs menutup dan membuka kembali file log pembaruan. Log pembaruan baru hanya akan menyertakan kueri yang mengubah database dari titik cadangan. Ini akan mengatur waktu pencadangan pos pemeriksaan log pembaruan Anda. (Namun, jika Anda memiliki klien yang perlu melakukan pembaruan, mengunci semua tabel bukanlah ide yang baik untuk akses klien selama pencadangan.)
Jika Anda menggunakan --flush-logs untuk memeriksa cadangan, mungkin yang terbaik adalah membuang seluruh database.
Jika Anda membuang file terpisah, akan lebih sulit untuk menyinkronkan pos pemeriksaan log pembaruan dengan file cadangan. Selama pemulihan, Anda biasanya mengekstrak konten log pembaruan berdasarkan per basis data. Tidak ada opsi untuk mengekstrak pembaruan untuk tabel individual, jadi Anda harus mengekstraknya sendiri.
Secara default, mysqldump membaca seluruh isi tabel ke dalam memori sebelum menulis. Ini biasanya sebenarnya tidak diperlukan, dan bahkan hampir gagal jika Anda memiliki meja yang besar. Anda dapat menggunakan opsi --quick untuk memberi tahu mysqldump agar menulis setiap baris setiap kali ia mengambilnya. Untuk lebih mengoptimalkan proses penuangan, gunakan --opt daripada --quick. Opsi --opt mengaktifkan opsi tambahan untuk mempercepat pembuangan data dan membacanya kembali.
Menerapkan pencadangan dengan --opt mungkin merupakan metode yang paling umum karena keunggulan kecepatan pencadangan. Namun, berhati-hatilah, opsi --opt memerlukan biaya. --opt mengoptimalkan proses pencadangan Anda, bukan akses klien lain ke database. Opsi --opt mencegah siapa pun memperbarui tabel apa pun yang Anda buang dengan mengunci semua tabel sekaligus. Anda dapat dengan mudah melihat efeknya pada akses database secara umum. Jika database Anda biasanya sangat sering digunakan, cukup sesuaikan cadangannya sekali sehari.
Opsi yang memiliki efek kebalikan dari --opt adalah --dedayed. Opsi ini menyebabkan mysqldump menulis pernyataan INSERT DELAYED alih-alih pernyataan INSERT. --delayed berguna jika Anda memuat file data ke database lain dan Anda ingin operasi ini memiliki dampak minimal pada kueri yang mungkin muncul di database tersebut.
Opsi --compress berguna saat Anda menyalin database ke komputer lain karena mengurangi jumlah byte yang ditransfer melalui jaringan. Berikut ini contohnya. Perhatikan bahwa --compress diberikan untuk program yang berkomunikasi dengan server pada host jarak jauh, bukan untuk program yang terhubung ke host lokal:
%mysqldump --opt samp_db |.mysql --kompres -h boa.snake.net samp_db
mysqldump memiliki banyak opsi, lihat "Manual Referensi MySQL" untuk detailnya.
2 Metode pencadangan dan penyalinan menggunakan database penyalinan langsung
Cara lain untuk membuat cadangan database dan tabel yang tidak melibatkan mysqldump adalah dengan menyalin file tabel database secara langsung. Biasanya, ini dilakukan dengan menggunakan utilitas seperti cp, tar atau cpio. Contoh dalam artikel ini menggunakan cp.
Saat Anda menggunakan metode pencadangan langsung, Anda harus memastikan bahwa tabel tersebut tidak lagi digunakan. Jika server mengubah tabel saat Anda menyalinnya, salinannya tidak ada artinya.
Cara terbaik untuk memastikan integritas salinan Anda adalah dengan mematikan server, menyalin file, dan kemudian memulai ulang server. Jika Anda tidak ingin mematikan server, kunci server saat melakukan pemeriksaan tabel. Jika server sedang berjalan, batasan yang sama berlaku untuk menyalin file, dan Anda harus menggunakan protokol penguncian yang sama untuk "mendiamkan" server.
Dengan asumsi server sedang down atau Anda telah mengunci tabel yang ingin Anda salin, berikut ini menunjukkan cara mencadangkan seluruh database samp_db ke direktori cadangan (DATADIR mewakili direktori data server): %cd DATADIR%cp -r samp_db /usr /arsip/mysql
Satu tabel dapat dicadangkan sebagai berikut:
%cd DATADIR/samp_db%cp anggota.* /usr/archive/mysql/samp_db%skor cp.* /usr/archive/mysql/samp_db ....
Ketika Anda telah menyelesaikan pencadangan, Anda dapat memulai ulang server (jika Anda mematikannya) atau melepaskan kunci yang ditempatkan di atas meja (jika Anda membiarkan server tetap berjalan).
Untuk menyalin database dari satu mesin ke mesin lain menggunakan file salinan langsung, cukup salin file ke direktori data yang sesuai di host server lain. Pastikan file dalam format MyIASM atau kedua mesin memiliki struktur perangkat keras yang sama, jika tidak, database Anda akan memiliki konten yang aneh di mesin lain. Anda juga harus memastikan bahwa server di komputer lain tidak mengakses tabel database saat Anda menginstalnya.
3 Mereplikasi Basis Data
Replikasi mirip dengan menyalin database ke server lain, tetapi arti sebenarnya adalah untuk memastikan bahwa kedua database tersinkronisasi sepenuhnya secara real time. Fitur ini akan muncul di versi 3.23 dan belum terlalu matang, sehingga artikel ini tidak akan memperkenalkannya secara detail.
4 Pulihkan data dari cadangan
Kerusakan basis data dapat terjadi karena berbagai alasan dan pada tingkat yang berbeda-beda. Jika beruntung, Anda mungkin hanya merusak satu atau dua tabel (seperti pemadaman listrik), jika kurang beruntung, Anda mungkin harus mengganti seluruh direktori data (seperti kerusakan disk). Pemulihan juga diperlukan dalam situasi tertentu, seperti ketika pengguna menghapus database atau tabel secara tidak sengaja. Terlepas dari penyebab kejadian yang tidak menguntungkan ini, Anda perlu melakukan semacam pemulihan.
Jika tabel rusak tetapi tidak hilang, coba perbaiki dengan myisamchk atau isamchk. Jika kerusakan tersebut dapat diperbaiki dengan program perbaikan, Anda mungkin tidak perlu menggunakan file cadangan sama sekali. Untuk proses perbaikan tabel, lihat "Pemeliharaan dan Perbaikan Database".
Proses pemulihan melibatkan dua sumber informasi: file cadangan dan log perubahan Anda. File cadangan mengembalikan tabel ke keadaan saat pencadangan dilakukan. Namun, biasanya tabel telah diubah dalam waktu antara pencadangan dan masalah, dan log pembaruan berisi kueri yang digunakan untuk melakukan modifikasi ini. Anda dapat menggunakan file log sebagai input ke mysql untuk mengulangi pertanyaan. Inilah sebabnya mengapa Anda harus mengaktifkan log perubahan.
Proses pemulihan bervariasi tergantung pada seberapa banyak informasi yang harus Anda pulihkan. Faktanya, lebih mudah memulihkan seluruh database dibandingkan satu tabel karena lebih mudah menerapkan log pembaruan ke database dibandingkan ke satu tabel.
4.1 Pulihkan seluruh database
Pertama, jika database yang ingin Anda pulihkan adalah database mysql yang berisi tabel hibah, Anda perlu menjalankan server dengan opsi --skip-grant-table. Jika tidak, ia akan mengeluh bahwa tabel otorisasi tidak dapat ditemukan. Setelah Anda memulihkan tabel, jalankan mysqladmin flush-privileges untuk memberi tahu server agar memuat token otorisasi dan menggunakannya.
Salin isi direktori database ke beberapa lokasi lain jika Anda memerlukannya nanti.
Instal ulang database dengan file cadangan terbaru. Jika Anda menggunakan file yang dihasilkan oleh mysqldump, gunakan itu sebagai input ke mysql. Jika Anda menggunakan file yang disalin langsung dari database, salin langsung kembali ke direktori database. Namun, dalam kasus ini Anda harus menutup database dan memulai ulang sebelum menyalin file.
Gunakan log pembaruan untuk mengulangi kueri yang mengubah tabel database setelah pencadangan. Untuk log perubahan apa pun yang berlaku, teruskan sebagai masukan ke mysql. Menentukan opsi --one-database menyebabkan mysql mengeksekusi kueri hanya untuk database yang ingin Anda pulihkan. Jika Anda tahu bahwa Anda perlu menerapkan semua file log pembaruan, Anda dapat menggunakan perintah ini di direktori yang berisi log:
% ls -t -r -1 pembaruan.[0-9]* |.xargs cat |
Perintah ls menghasilkan daftar satu kolom file log pembaruan, diurutkan berdasarkan urutan pembuatannya oleh server (Ide: Jika Anda memodifikasi salah satu file, Anda akan mengubah urutan pengurutan, yang menyebabkan log pembaruan menjadi digunakan dalam urutan yang salah.)
Kemungkinan besar Anda akan menggunakan log perubahan tertentu. Misalnya, jika log pembaruan yang dihasilkan sejak pencadangan Anda diberi nama update.392, update.393, dll., Anda dapat menjalankannya kembali seperti ini:
%mysql --satu-database db_name < update.392
%mysql --satu-database db_name < update.393
.....
Jika Anda melakukan pemulihan dan menggunakan log pembaruan untuk memulihkan informasi yang hilang karena pernyataan DROP DATABASE, DROP TABLE, atau DELETE yang salah direkomendasikan, pastikan untuk menghapus pernyataan ini dari log pembaruan sebelum menggunakannya.
4.2 Memulihkan satu tabel
Memulihkan satu tabel lebih rumit. Jika Anda menggunakan file cadangan yang dihasilkan oleh mysqldump dan tidak berisi data untuk tabel yang Anda minati, Anda perlu mengekstraknya dari baris yang relevan dan menggunakannya sebagai masukan ke mysql. Ini adalah bagian yang mudah. Bagian tersulitnya adalah menarik fragmen dari log pembaruan yang hanya berlaku untuk tabel itu. Anda mungkin menemukan utilitas mysql_find_rows berguna untuk ini, yang mengekstraksi kueri multi-baris dari log perubahan.
Kemungkinan lainnya adalah menggunakan server lain untuk memulihkan seluruh database dan kemudian menyalin file tabel yang Anda inginkan ke database asli. Ini mungkin sangat mudah! Saat Anda menyalin file kembali ke direktori database, pastikan server database asli dimatikan.