Warning: mysqli_query(): (HY000/3): Error writing file '/tmp/MYrcn0zk' (Errcode: 28 - No space left on device) in /usr/share/nginx/html/syslogs/wp-includes/class-wpdb.php on line 2349

MySQL sunucularda özellikle InnoDB  veritabanlarının zamanla şişen data dosyalarını shrink ederek küçültmek, gerek performansa olumlu etkisi gerekse de diskte kapladığı alandan tasarruf anlamında faydalı bir işlemdir.  MyISAM  kullanan bir db'de bu işlem “optimize table” ile yapılabilir ancak InnoDB veritabanlarında shrink işlemi için  MySQL tarafından sunulan herhangi bir araç ya da komut malesef yok. Bu nedenle data dosyasını küçültmek için, db'nin yedeğini aldıktan sonra drop etmek ve ibdata ve ib_logfile dosyalarını silip db'yi yeniden restore etmek gibi dolanbaçlı bir yöntem izlemek gerekiyor.

Yazının devamında işlemlerin nasıl yapılabileceğine detaylı olarak değinmek istiyorum. İlginizi çekerse incelemek isteyebilirsiniz.

UYARI!: İşlem database'in drop edilmesi gibi hata durumunda veri kaybına yol açabilecek tehlikeli adımlar içerdiğinden dolayı son derece dikkatli yapılması gereken bir konudur. Bu nedenle ne yaptığınız konusunda bilginiz yoksa aşağıda anlatılanları uygulamamanızı öneririm.

Yazının girişinde de bahsettiğim gibi data dosyasını küçültmek için, öncesinde güvenli bir yedeğini aldığımız db'yi drop edeceğiz, sonrasında mysql'i stop edip, ibdata ve ib_logfile* dosyalarını sileceğiz ve my.cnf dosyasına “innodb_file_per_table” ibaresini ekleyeceğiz. Bu ifade her bir innodb için ayrı bir ibd data dosyası oluşturulmasını sağlamaktadır. Sonrasında mysql'i yeniden başlatacağız ve bu işlem yeni ve daha küçük bir ibdata1 dosyasının oluşturulmasını sağlayacak, daha sonra  mysql üzerinde drop ettiğimiz db'yi yeniden create edip ilk başta aldığımız yedek dosyasını restore edeceğiz.  Böylece db'mizde bulunan her bir tablo için ayrı bir ibd dosyası oluşacak ve genel data dosyası da daha küçük bir ebata sahip olacak.

Başlamadan Önce

---

Ben her türlü işlemi komut satırından yapacağım ancak siz herhangi bir arayüz kullanıyorsanız yedek alma, db drop, create vs. gibi işlemleri ilgili arayüzden yapabilirsiniz.  Ayrıca, işlemin yapılacağı mySQL, CentOS 5.7 üzerinde çalışmaktadır ve öntanımlı mysql dizini /var/lib/mysql'dir.

Veri güvenliğini sağlamak için işlemlere başlamadan önce tüm verinin bulunduğu mysql dizinini bir kopyalayarak yedeklemenizi öneririm. Bu işlem aşağıdaki gibi yapılabilir:

Data dosyalarının yedeklenebilmesi için mysql server'ın stop durumda olması gerekiyor:

# /etc/init.d/mysqld stop

MySQL data dizinini örnek olarak /yedekler isimli dizine kopyalayalım:
(Elbette diskinizde yeterli alan olduğuna emin olun.)

# cp -pr /var/lib/mysql /yedekler/mysql_datadizini_21122011

Sonrasında mysql sunucuyu yeniden başlatalım:

# /etc/init.d/mysqld start

Bu şekilde shrink işlemi sırasında herhangi bir şeyin ters gitmesi durumunda sistemi eski haline getirebilecek önlemi almış oluyoruz.

Shrink ibdata1

---

Shrink işlemi için sırası ile aşağıdaki adımları izliyoruz:

mysqldump ile DB'nin yedeklenmesi

Veritabanının adını mydb olarak kabul ederek /yedekler dizinine dump etmek için aşağıdaki komutu kullanıyoruz:
Not: dump işlemini başlatmadan önce db'de değişiklik olmaması için veritabanını kullanan uygulamaları kapatmayı unutmayın.
Not2: Birden fazla veritabanınız varsa yedek işlemini tüm db'ler için ayrı ayrı yapmanız gerekir.

# mysqldump -u root -p mydb > /yedekler/mydb.21.12.2011.sql

Böylece db dump edilmiş olacaktır.

DB'nin Drop Edilmesi

DB isminin mydb olduğunu kabul ederek aşağıdaki şeklilde drop işlemini yapıyoruz.
Not: Birden fazla veritabanınız varsa drop işlemini tüm db'ler için ayrı ayrı yapmanız gerekir. 

Mysql konsoluna bağlanın:

# mysql -u root -p

Sonra db'yi drop edin:

 mysql> drop database mydb;

Son olarak komut satırından çıkın:

 mysql> quit;

MySQL'in Durdurulması

Bir sonraki adımda ibdata dosyası silineceği için mysql'i stop ediyoruz. (Bu işlem çok elzem değildir ancak temiz iş yapmış olmak için uygulamak en iyisidir.)

# /etc/init.d/mysqld stop

my.cnf: innodb_file_per_table

Tüm datanın tek bir ibdata dosyasında tutulması yerine her bir tablo için ayrı ibdata dosyası oluşturulması için /etc/my.cnf dosyasının innodb bölümüne “innodb_file_per_table” paramteresi eklenebilir. Bu parametre her bir tablo için /var/lib/mysql/mydb/ dizininde ayrı bir .idb dosyası oluşturulmasını sağlar.

innodb_file_per_table paramtere için detaylı bilgiye şu linkten ulaşabilirsiniz:
http://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html

Değişikliği yaptıktan sonra şimdiki adım eski ibdata dosyalarının silinmesidir.

Data Dosyalarının Silinmesi

Default kurulumlarda mysql dataları /var/lib/mysql dizini altında dururlar. Bu dizin içerisindeki ibdata1 ve ib_logfile isimli dosyaları siliyoruz.

# rm  /var/lib/mysql/ibdata1
# rm  /var/lib/mysql/ib_logfile*

MySQL'in Başlatılması

Dosyalar silindikten sonra mysql'i start ediyoruz.

# /etc/init.d/mysqld start

Mysql'in yeniden başlatılması bir önce sildiğiniz data dosyalarının yeniden oluşturulmasını sağlayacaktır.

DB'nin Create Edilmesi

İlk adımda aldığımız dump'ı restore etmeden önce mysql konsoluna bağlanarak db'nin aynı isimle create edilmesi gerekir:

# mysql -u root -p

Sonra db'yi create edin:

 mysql> create database mydb;

Son olarak komut satırından çıkın:

 mysql> quit;

DB'nin Restore Edilmesi

Not2: Birden fazla veritabanınız varsa restore işlemini tüm db'ler için ayrı ayrı yapmanız gerekir.

DB'nin restore edilmesinden sonra /var/lib/mysql/mydb/ dizininde her bir tablo için ayrı bir *.ibd dosyasının oluştuğunu göreceksiniz.

Böylece shrink işlemi tamamlanmış oluyor.