pengembangan-web-mp.com

Cara mengecilkan / membersihkan file ibdata1 di MySQL

Saya menggunakan MySQL di localhost sebagai "alat permintaan" untuk melakukan statistik dalam R, yaitu, setiap kali saya menjalankan skrip R, saya membuat database baru (A), membuat tabel baru (B), mengimpor data ke B , kirimkan pertanyaan untuk mendapatkan apa yang saya butuhkan, dan kemudian saya drop B dan drop A.

Ini berfungsi dengan baik untuk saya, tetapi saya menyadari bahwa ukuran file ibdata meningkat dengan cepat, saya tidak menyimpan apa pun di MySQL, tetapi file ibdata1 sudah melebihi 100 MB.

Saya menggunakan kurang lebih pengaturan MySQL default untuk pengaturan, apakah ada cara agar saya dapat secara otomatis mengecilkan/menghapus file ibdata1 setelah periode waktu yang tetap?

533
lokheart

Itu ibdata1 tidak menyusut adalah fitur MySQL yang sangat mengganggu. File ibdata1 tidak dapat benar-benar menyusut kecuali Anda menghapus semua database, menghapus file dan memuat kembali dump.

Tetapi Anda dapat mengkonfigurasi MySQL sehingga setiap tabel, termasuk indeksnya, disimpan sebagai file terpisah. Dengan cara itu ibdata1 tidak akan bertambah besar. Menurut komentar Bill Karwin ini diaktifkan secara default pada versi 5.6.6 dari MySQL.

Beberapa waktu yang lalu saya melakukan ini. Namun, untuk mengatur server Anda untuk menggunakan file terpisah untuk setiap tabel, Anda perlu mengubah my.cnf untuk mengaktifkan ini:

[mysqld]
innodb_file_per_table=1

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

Karena Anda ingin mendapatkan kembali ruang dari ibdata1 Anda sebenarnya harus menghapus file:

  1. Lakukan mysqldump dari semua database, prosedur, pemicu dll kecuali database mysql dan performance_schema
  2. Lepaskan semua basis data kecuali 2 basis data di atas
  3. Hentikan mysql
  4. Hapus file ibdata1 dan ib_log
  5. Mulai mysql
  6. Kembalikan dari dump

Ketika Anda memulai MySQL pada langkah 5 file ibdata1 dan ib_log akan dibuat ulang.

Sekarang Anda siap untuk pergi. Saat Anda membuat database baru untuk analisis, tabel-tabel tersebut akan ditempatkan di file ibd* terpisah, bukan di ibdata1. Karena Anda biasanya drop database segera setelah itu, file ibd* akan dihapus.

http://dev.mysql.com/doc/refman/5.1/en/drop-database.html

Anda mungkin pernah melihat ini:
(http://bugs.mysql.com/bug.php?id=1341

Dengan menggunakan perintah ALTER TABLE <tablename> ENGINE=innodb atau OPTIMIZE TABLE <tablename> seseorang dapat mengekstraksi data dan mengindeks halaman dari ibdata1 ke file yang terpisah. Namun, ibdata1 tidak akan menyusut kecuali Anda melakukan langkah-langkah di atas.

Mengenai information_schema, itu tidak perlu dan tidak mungkin untuk dijatuhkan. Ini sebenarnya hanya sekelompok tampilan read-only, bukan tabel. Dan tidak ada file yang terkait dengan mereka, bahkan direktori basis data. informations_schema menggunakan memory db-engine dan dijatuhkan dan dibuat kembali saat stop/restart mysqld. Lihat https://dev.mysql.com/doc/refman/5.7/en/information-schema.html .

750
John P

Menambahkan ke jawaban John P ,

Untuk sistem linux, langkah 1-6 dapat dilakukan dengan perintah-perintah ini:

  1. mysqldump -u [username] -p[root_password] [database_name] > dumpfilename.sql
  2. DROP DATABASE [database_name];
  3. Sudo /etc/init.d/mysqld stop
  4. Sudo rm /var/lib/mysql/ibdata1
    Sudo rm /var/lib/mysql/ib_logfile (dan hapus ib_logfile lainnya yang mungkin dinamai ib_logfile0, ib_logfile1 dll ...)
  5. Sudo /etc/init.d/mysqld start
  6. create database [database_name];
  7. mysql -u [username]-p[root_password] [database_name] < dumpfilename.sql

Peringatan: instruksi ini akan menyebabkan Anda kehilangan database lain jika Anda memiliki database lain pada instance mysql ini. Pastikan langkah 1,2 dan 6,7 dimodifikasi untuk mencakup semua basis data yang ingin Anda simpan.

42
Vinay Vemula

Ketika Anda menghapus tabel innodb, MySQL tidak mengosongkan ruang di dalam file ibdata, itu sebabnya ia terus bertambah. File-file ini hampir tidak pernah menyusut.

Cara mengecilkan file ibdata yang ada:

http://dev.mysql.com/doc/refman/5.5/id/innodb-resize-system-tablespace.html

Anda bisa membuat skrip ini dan menjadwalkan skrip untuk dijalankan setelah periode waktu yang tetap, tetapi untuk setup yang dijelaskan di atas tampaknya beberapa tablespace adalah solusi yang lebih mudah.

Jika Anda menggunakan opsi konfigurasi innodb_file_per_table, Anda membuat beberapa tablespaces. Artinya, MySQL membuat file terpisah untuk setiap tabel, bukan satu file bersama. File-file terpisah ini disimpan dalam direktori database, dan dihapus ketika Anda menghapus database ini. Ini harus menghapus kebutuhan untuk mengecilkan/membersihkan file ibdata dalam kasus Anda.

Informasi lebih lanjut tentang beberapa tablespace:

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

33
titanoboa

Jika Anda menggunakan mesin penyimpanan InnoDB untuk (sebagian) tabel MySQL Anda, Anda mungkin telah menemukan masalah dengan konfigurasi defaultnya. Seperti yang mungkin Anda perhatikan di direktori data MySQL Anda (di Debian/Ubuntu -/var/lib/mysql) terdapat file bernama ‘ibdata1 ′. Ini memegang hampir semua data InnoDB (ini bukan log transaksi) dari instance MySQL dan bisa menjadi cukup besar. Secara default file ini memiliki ukuran awal 10Mb dan secara otomatis meluas. Sayangnya, dengan desain file data InnoDB tidak dapat menyusut. Itu sebabnya HAPUS, TRUNCATE, DROP, dll. Tidak akan mengklaim kembali ruang yang digunakan oleh file.

Saya pikir Anda dapat menemukan penjelasan dan solusi yang bagus di sana:

http://vdachev.net/2007/02/22/mysql-reducing-ibdata1/

14
Vik

Cepat menuliskan prosedur jawaban yang diterima di bash:

#!/usr/bin/env bash
DATABASES="$(mysql -e 'show databases \G' | grep "^Database" | grep -v '^Database: mysql$\|^Database: binlog$\|^Database: performance_schema\|^Database: information_schema' | sed 's/^Database: //g')"
mysqldump --databases $DATABASES -r alldatabases.sql && echo "$DATABASES" | while read -r DB; do
    mysql -e "drop database \`$DB\`"
done && \
    /etc/init.d/mysql stop && \
    find /var/lib/mysql -maxdepth 1 -type f \( -name 'ibdata1' -or -name 'ib_logfile*' \) -delete && \
    /etc/init.d/mysql start && \
    mysql < alldatabases.sql && \
    rm -f alldatabases.sql

Simpan sebagai purge_binlogs.sh dan jalankan sebagai root.

Tidak termasuk mysql, information_schema, performance_schema (dan direktori binlog).

Asumsikan Anda memiliki kredensial administrator di /root/.my.cnf dan bahwa database Anda hidup dalam direktori /var/lib/mysql default.

Anda juga dapat membersihkan log biner setelah menjalankan skrip ini untuk mendapatkan kembali lebih banyak ruang disk dengan:

PURGE BINARY LOGS BEFORE CURRENT_TIMESTAMP;

Jika tujuan Anda adalah untuk memonitor ruang kosong MySQL dan Anda tidak dapat menghentikan MySQL untuk mengecilkan file ibdata Anda, dapatkan melalui perintah status tabel. Contoh:

MySQL> 5.1.24:

mysqlshow --status myInnodbDatabase myTable | awk '{print $20}'

MySQL <5.1.24:

mysqlshow --status myInnodbDatabase myTable | awk '{print $35}'

Kemudian bandingkan nilai ini dengan file ibdata Anda:

du -b ibdata1

Sumber: http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

6
Cyno

Dalam versi baru dari resep mysql-server di atas akan menghancurkan basis data "mysql". Dalam versi lama itu berfungsi. Dalam beberapa tabel baru beralih ke jenis tabel INNODB, dan dengan demikian Anda akan merusaknya. Cara termudah adalah dengan membuang semua database Anda, uninstall mysql-server, tambahkan tetap my.cnf:

[mysqld]
innodb_file_per_table=1


erase all in /var/lib/mysql
install mysql-server
restore users and databases
4

Seperti yang sudah disebutkan, Anda tidak dapat menyusutkan ibdata1 (untuk melakukannya Anda perlu membuang dan membangun kembali), tetapi sering kali juga tidak perlu.

Menggunakan autoextend (mungkin pengaturan ukuran paling umum) ibdata1 mengalokasikan penyimpanan, bertambah setiap kali hampir penuh. Itu membuat menulis lebih cepat karena ruang sudah dialokasikan.

Ketika Anda menghapus data itu tidak menyusut tetapi ruang di dalam file ditandai sebagai tidak terpakai. Sekarang ketika Anda memasukkan data baru itu akan menggunakan kembali ruang kosong dalam file sebelum menumbuhkan file lebih jauh.

Jadi itu hanya akan terus tumbuh jika Anda benar-benar membutuhkan data itu. Kecuali Anda benar-benar membutuhkan ruang untuk aplikasi lain, mungkin tidak ada alasan untuk mengecilkannya.

0
steveayre