-
Pencadangan dan Pemulihan MySQL Sabtu, 30/09/2006 - 14:21 — ya
Pencadangan dan pemulihan MySQL
Penulis/Penerjemah: Ye Jinrong (Email: ), Sumber: http://imysql.cn . Harap tunjukkan penulis/penerjemah dan sumber saat mencetak ulang. Ini tidak dapat digunakan untuk tujuan komersial.
Tanggal: 2006/10/01
Artikel ini membahas mekanisme pencadangan dan pemulihan MySQL dan cara memelihara tabel data, termasuk dua jenis tabel utama: MyISAM dan Innodb. Versi MySQL yang dirancang dalam artikel ini adalah 5.0.22.
Alat pencadangan gratis yang saat ini didukung oleh MySQL meliputi: mysqldump, mysqlhotcopy. Anda juga dapat menggunakan sintaks SQL untuk pencadangan: BACKUP TABLE atau SELECT INTO OUTFILE, atau cadangan log biner (binlog), atau langsung menyalin file data dan file konfigurasi terkait. Tabel MyISAM disimpan sebagai file, sehingga relatif mudah untuk dicadangkan. Beberapa metode yang disebutkan di atas dapat digunakan. Semua tabel di Innodb disimpan dalam file data yang sama ibdata1 (bisa juga berupa beberapa file, atau file ruang tabel independen), yang relatif sulit untuk dicadangkan. Solusi gratis dapat berupa menyalin file data dan membuat cadangan binlog .
1.mysqldump
1.1 Cadangan
mysqldump menggunakan mekanisme pencadangan tingkat SQL yang mengekspor tabel data ke dalam file skrip SQL. Ini relatif cocok untuk memutakhirkan antara versi MySQL yang berbeda.
Sekarang mari kita bicara tentang beberapa parameter utama mysqldump:
--kompatibel=nama
Ini memberi tahu mysqldump database mana atau versi server MySQL yang lebih lama yang kompatibel dengan data yang diekspor. Nilai dapat berupa ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, no_field_options, dll. Untuk menggunakan beberapa nilai, pisahkan dengan koma. Tentu saja, ini tidak menjamin kompatibilitas penuh, namun mencoba untuk kompatibel.
--lengkap-masukkan, -c
Data yang diekspor menggunakan metode INSERT lengkap termasuk nama field, yaitu semua nilai ditulis dalam satu baris. Melakukan hal ini dapat meningkatkan efisiensi penyisipan, namun mungkin dipengaruhi oleh parameter max_allowed_packet dan menyebabkan kegagalan penyisipan. Oleh karena itu parameter ini perlu digunakan dengan hati-hati, setidaknya saya tidak merekomendasikannya.
--default-character-set=rangkaian karakter
Tentukan kumpulan karakter mana yang akan digunakan saat mengekspor data. Jika tabel data tidak menggunakan kumpulan karakter latin1 default, maka opsi ini harus ditentukan saat mengekspor, jika tidak, karakter kacau akan terjadi setelah mengimpor data lagi.
--nonaktifkan-kunci
Perintahkan mysqldump untuk menambahkan pernyataan /*!40000 ALTER TABLE table DISABLE KEYS */; dan /*!40000 ALTER TABLE table ENABLE KEYS */; karena Indeks dibangun kembali setelah semua data dimasukkan. Opsi ini hanya cocok untuk tabel MyISAM.
--exended-insert = benar|salah
Secara default, mysqldump mengaktifkan mode --complete-insert, jadi jika Anda tidak ingin menggunakannya, gunakan saja opsi ini dan atur nilainya menjadi false.
--hex-gumpalan
Ekspor bidang string biner menggunakan format heksadesimal. Opsi ini harus digunakan jika ada data biner. Jenis bidang yang terpengaruh adalah BINARY, VARBINARY, dan BLOB.
--kunci-semua-tabel,-x
Sebelum memulai ekspor, kirimkan permintaan untuk mengunci semua tabel di semua database untuk memastikan konsistensi data. Ini adalah kunci baca global dan secara otomatis dimatikan dengan opsi --single-transaction dan --lock-tables.
--lock-tabel
Ini mirip dengan --lock-all-tables, tetapi mengunci tabel data yang sedang diekspor alih-alih mengunci semua tabel dalam database sekaligus. Opsi ini hanya berlaku untuk tabel MyISAM. Jika ini adalah tabel Innodb, Anda dapat menggunakan opsi --single-transaction.
--tidak-membuat-info, -t
Hanya ekspor data tanpa menambahkan pernyataan CREATE TABLE.
--tidak ada data,-d
Tidak ada data yang diekspor, hanya struktur tabel database yang diekspor.
--memilih
Ini hanyalah opsi cepat, setara dengan menambahkan --add-drop-tables --add-locking --create-option --disable-keys --exended-insert --lock-tables --quick --set- opsi rangkaian karakter. Opsi ini memungkinkan mysqldump mengekspor data dengan cepat, dan data yang diekspor dapat diimpor kembali dengan cepat. Opsi ini diaktifkan secara default, namun dapat dinonaktifkan dengan --skip-opt. Perhatikan bahwa jika Anda menjalankan mysqldump tanpa menentukan opsi --quick atau --opt, seluruh kumpulan hasil akan ditempatkan di memori. Masalah mungkin terjadi jika Anda mengekspor database yang besar.
--cepat,-q
Opsi ini berguna ketika mengekspor tabel besar. Ini memaksa mysqldump untuk mengeluarkan catatan yang diperoleh dari kueri server secara langsung alih-alih mengambil semua catatan dan menyimpannya di memori.
--rutinitas, -R
Ekspor prosedur tersimpan dan fungsi khusus.
--transaksi tunggal
Opsi ini mengirimkan pernyataan BEGIN SQL sebelum mengekspor data. BEGIN tidak memblokir aplikasi apa pun dan memastikan keadaan database yang konsisten selama ekspor. Ini hanya berfungsi dengan tabel transaksional seperti InnoDB dan BDB.
Opsi ini dan opsi --lock-tables saling eksklusif karena LOCK TABLES menyebabkan transaksi yang tertunda dilakukan secara implisit.
Untuk mengekspor tabel besar, opsi --quick harus digunakan secara bersamaan.
--pemicu
Juga ekspor pemicu. Opsi ini diaktifkan secara default, gunakan --skip-triggers untuk menonaktifkannya.
Silakan merujuk ke manual untuk rincian parameter lainnya. Saya biasanya menggunakan SQL berikut untuk membuat cadangan tabel MyISAM:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --exended-insert=false
--pemicu -R --hex-blob -x db_name > db_name.sql
Gunakan SQL berikut untuk membuat cadangan tabel Innodb:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --exended-insert=false
--triggers -R --hex-blob --transaksi tunggal db_name > db_name.sql
Selain itu, jika ingin menerapkan backup online, Anda juga dapat menggunakan parameter --master-data, sebagai berikut:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--set-karakter-default=utf8 --opt --master-data=1
--transaksi tunggal --flush-logs db_name > db_name.sql
Itu hanya meminta tabel kunci di awal, lalu menyegarkan binlog, dan kemudian menambahkan pernyataan CHANGE MASTER ke file yang diekspor untuk menentukan lokasi binlog dari cadangan saat ini cara ini untuk melakukannya.
1.2 Memulihkan File yang dicadangkan dengan mysqldump adalah skrip SQL yang dapat langsung diimpor. Ada dua cara untuk mengimpor data.
Gunakan klien mysql secara langsung, misalnya:
/usr/local/mysql/bin/mysql -uyejr -pyejr db_name < db_name.sql
Penggunaan sintaks SOURCE sebenarnya bukan sintaks standar SQL, melainkan fungsi yang disediakan oleh klien mysql, misalnya:
SUMBER /tmp/db_name.sql;
Di sini Anda perlu menentukan jalur absolut file, dan itu harus berupa file yang izin untuk dibaca oleh pengguna mysqld yang menjalankan (misalnya, tidak ada orang).
2. salinan panas mysql
2.1 Cadangan
mysqlhotcopy adalah program PERL yang aslinya ditulis oleh Tim Bunce. Ia menggunakan LOCK TABLES, FLUSH TABLES dan cp atau scp untuk membuat cadangan database dengan cepat. Ini adalah cara tercepat untuk mencadangkan database atau tabel tunggal, namun hanya dapat berjalan di mesin tempat file database (termasuk file definisi tabel data, file data, dan file indeks) berada. mysqlhotcopy hanya dapat digunakan untuk membuat cadangan MyISAM, dan hanya akan berjalan pada sistem mirip Unix dan NetWare.
mysqlhotcopy mendukung penyalinan beberapa database sekaligus, dan juga mendukung ekspresi reguler. Berikut beberapa contohnya:
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
db_name /tmp (ubah direktori database db_name
Salin ke /tmp
Turun)
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
db_nama_1 ... db_nama_n /tmp
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
db_name./regex/ /tmp
Silakan merujuk ke manual untuk penggunaan lebih detail, atau panggil perintah berikut untuk melihat bantuan mysqlhotcopy:
perldoc /usr/local/mysql/bin/mysqlhotcopy
Perhatikan bahwa jika Anda ingin menggunakan mysqlhotcopy, Anda harus memiliki izin SELECT dan RELOAD (untuk menjalankan FLUSH TABLES), dan Anda juga harus memiliki izin untuk membaca direktori datadir/db_name.
2.2 Pulihkan
Mysqlhotcopy mencadangkan seluruh direktori database. Saat digunakan, dapat disalin langsung ke datadir yang ditentukan oleh mysqld (ini dia /usr/local/mysql/data/). seperti pada contoh berikut:
root#cp -rf db_name /usr/local/mysql/data/
root#chown -R none:nobody /usr/local/mysql/data/ (ubah pemilik direktori db_name menjadi mysqld
pengguna berjalan)
3. Cadangan sintaksis SQL
3.1 Cadangan
Sintaks BACKUP TABLE sebenarnya mirip dengan prinsip kerja mysqlhotcopy. Keduanya mengunci tabel lalu menyalin file datanya. Bisa mencapai backup online, tapi efeknya kurang ideal, jadi tidak disarankan. Ini hanya menyalin file struktur tabel dan file data, tetapi tidak menyalin file indeks secara bersamaan, sehingga pemulihan lebih lambat.
contoh:
KEMBALI TABEL tbl_name KE '/tmp/db_name/';
Perhatikan bahwa Anda harus memiliki izin FILE untuk menjalankan SQL ini, dan direktori /tmp/db_name/ harus dapat ditulis oleh pengguna mysqld. File yang diekspor tidak dapat menimpa file yang ada untuk menghindari masalah keamanan.
SELECT INTO OUTFILE mengekspor data ke file teks biasa. Anda dapat menyesuaikan interval bidang untuk memfasilitasi pemrosesan data ini.
contoh:
PILIH * KE OUTFILE '/tmp/db_name/tbl_name.txt' DARI tbl_name;
Perhatikan bahwa Anda harus memiliki izin FILE untuk menjalankan SQL ini, dan file /tmp/db_name/tbl_name.txt harus dapat ditulis oleh pengguna mysqld. File yang diekspor tidak dapat menimpa file yang ada untuk menghindari masalah keamanan.
3.2 Untuk memulihkan file yang dicadangkan menggunakan metode BACKUP TABLE, Anda dapat menjalankan pernyataan RESTORE TABLE untuk memulihkan tabel data.
contoh:
KEMBALIKAN TABEL DARI '/tmp/db_name/';
Persyaratan izin serupa dengan yang dijelaskan di atas.
Untuk file yang dicadangkan menggunakan metode SELECT INTO OUTFILE, Anda dapat menjalankan pernyataan LOAD DATA INFILE untuk memulihkan tabel data.
contoh:
MUAT DATA INFILE '/tmp/db_name/tbl_name.txt' KE TABEL tbl_name;
Persyaratan izin serupa dengan yang dijelaskan di atas. Sebelum mengimpor data, tabel datanya harus sudah ada. Jika Anda khawatir tentang duplikasi data, Anda dapat menambahkan kata kunci REPLACE untuk mengganti catatan yang ada atau menggunakan kata kunci IGNORE untuk mengabaikannya.
4. Aktifkan log biner (binlog)
Metode penggunaan binlog relatif lebih fleksibel, menghemat kekhawatiran dan tenaga, dan juga dapat mendukung pencadangan tambahan.
Mysqld harus di-restart ketika binlog diaktifkan. Pertama, tutup mysqld, buka my.cnf, dan tambahkan baris berikut:
id server=1
log-bin = binlog
log-bin-index = binlog.index
Kemudian mulai mysqld. Binlog.000001 dan binlog.index akan dibuat selama operasi. File sebelumnya adalah mysqld yang mencatat semua operasi pembaruan pada data, dan file terakhir adalah indeks semua binlog, yang tidak dapat dihapus dengan mudah. Silakan lihat manual untuk informasi tentang binlog.
Saat Anda perlu melakukan pencadangan, Anda dapat menjalankan pernyataan SQL terlebih dahulu agar mysqld menghentikan penulisan ke binlog saat ini, lalu mencadangkan file secara langsung. Dengan cara ini, tujuan pencadangan tambahan dapat tercapai:
FLUSH LOGS; Jika Anda membuat cadangan server budak dalam sistem replikasi, Anda juga harus membuat cadangan file master.info dan relay-log.info.
File binlog yang dicadangkan dapat dilihat menggunakan alat mysqlbinlog yang disediakan oleh MySQL, seperti:
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001
Alat ini memungkinkan Anda untuk menampilkan semua pernyataan SQL dalam database tertentu, dan juga dapat membatasi rentang waktu, yang cukup memudahkan Silakan merujuk ke manual untuk detailnya.
Saat memulihkan, Anda dapat menggunakan pernyataan yang mirip dengan berikut ini:
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 | mysql -uyejr -pyejr db_name
Gunakan keluaran pernyataan SQL oleh mysqlbinlog secara langsung sebagai masukan untuk menjalankannya.
Jika Anda memiliki mesin yang menganggur, Anda sebaiknya menggunakan metode ini untuk mencadangkannya. Karena persyaratan kinerja mesin budak relatif rendah, biayanya rendah. Pencadangan tambahan dapat dicapai dengan biaya rendah dan sebagian dari tekanan permintaan data dapat dibagikan.
5. Pencadangan file data secara langsung Dibandingkan dengan metode sebelumnya, pencadangan file data adalah yang paling langsung, cepat, dan nyaman. Kerugiannya adalah pencadangan tambahan pada dasarnya tidak mungkin. Untuk memastikan konsistensi data, pernyataan SQL berikut perlu dijalankan sebelum membuat cadangan file:
FLUSH TABLES WITH READ LOCK yaitu membuang semua data di memori ke disk dan mengunci tabel data untuk memastikan tidak ada data baru yang ditulis selama proses penyalinan. Data yang dibackup dengan cara ini juga sangat mudah untuk dikembalikan, cukup salin kembali ke direktori database asli.
Perhatikan bahwa untuk tabel tipe Innodb, Anda juga perlu membuat cadangan file lognya, yaitu file ib_logfile*. Karena ketika tabel Innodb rusak, Anda dapat mengandalkan file log ini untuk memulihkannya.
6. Strategi pencadangan Untuk sistem dengan volume bisnis tingkat menengah, strategi pencadangan dapat ditentukan sebagai berikut: pencadangan penuh untuk pertama kali, pencadangan tambahan satu kali sehari, pencadangan penuh seminggu sekali, dan seterusnya. Untuk sistem yang penting dan sibuk, Anda mungkin memerlukan pencadangan penuh sekali sehari, pencadangan tambahan satu jam sekali, atau bahkan lebih sering. Untuk mencapai pencadangan online dan pencadangan tambahan tanpa mempengaruhi bisnis online, cara terbaik adalah dengan menggunakan mekanisme replikasi master-slave (replikasi) untuk membuat cadangan pada mesin budak.
7. Pemeliharaan data dan pemulihan bencana. Sebagai DBA (saya belum, haha), salah satu tugas terpenting adalah memastikan tabel data dapat digunakan dengan aman, stabil, dan kecepatan tinggi. Oleh karena itu, tabel data Anda perlu dipelihara secara berkala. Pernyataan SQL berikut berguna:
CHECK TABLE atau REPAIR TABLE, memeriksa atau memelihara tabel MyISAM
OPTIMASI TABEL, optimalkan tabel MyISAM
ANALYZE TABLE, menganalisa tabel MyISAM. Tentu saja perintah diatas semua dapat diselesaikan melalui tool myisamchk, dan tidak akan dijelaskan secara detail disini.
Tabel Innodb dapat didefragmentasi dan meningkatkan kecepatan pengindeksan dengan menjalankan pernyataan berikut:
ALTER TABLE tbl_name ENGINE = Innodb;
Ini sebenarnya adalah operasi NULL. Di permukaan, ini tidak melakukan apa pun, tetapi sebenarnya mengatur ulang fragmennya.
Tabel MyISAM yang umum digunakan dapat dipulihkan menggunakan metode yang disebutkan di atas. Jika indeks rusak, Anda dapat menggunakan alat myisamchk untuk membangun kembali indeks. Untuk tabel Innodb tidak begitu mudah karena menyimpan semua tabel dalam satu ruang tabel. Namun, Innodb memiliki mekanisme pemeriksaan yang disebut fuzzy checkpoint. Selama file log disimpan, kesalahan dapat diperbaiki berdasarkan file log. Anda dapat menambahkan parameter berikut di file my.cnf agar mysqld secara otomatis memeriksa file log ketika dimulai:
innodb_force_recovery = 4
Lihat manual untuk informasi tentang parameter ini.
8. Meringkas cadangan data dan menentukan strategi pencadangan yang tepat. Ini adalah sebagian kecil dari apa yang dilakukan DBA. Semuanya sulit pada awalnya.