Büyük veritabanı yedeği zaman aşımı olmadan; pratik rehber

Üç gigabaytlık bir veritabanınız var, mysqldump komutunu çalıştırıyorsunuz ve yirmi dakika bekledikten sonra terminal kesiliyor. Çıktı dosyası yarım ya da tamamen boş. SSH üzerindeyseniz Connection to server closed mesajı geliyor, panelden çalıştırdıysanız genellikle 504 Gateway Timeout benzeri bir şey görüyorsunuz. Sorun ne veritabanında ne de sunucuda; sorun, varsayılan komutun küçük veritabanları için yazılmış olması, birkaç gigabayt verisi olan bir şey için değil.

Büyük veritabanı yedeği neden iş ortasında kesiliyor

Bu kesilmenin arkasında birbirinden ayrı üç etken var ve bunları ayırmanız gerekiyor, çünkü her birinin çözümü farklı.

Birincisi, web katmanı zaman aşımı. phpMyAdmin veya bir PHP betiğiyle yedek alıyorsanız, max_execution_time genellikle 30 veya 60 saniyedir ve max_input_time da benzerdir. Veritabanınız on dakika istiyor. Sonuç, PHP logunda hiçbir hata bırakmayan eksik bir dosya, çünkü betik kendini başarılı sayıyor.

İkincisi, ağ zaman aşımı. Kendi sisteminizden uzak MySQL'e bağlanıyorsanız, sunucu tarafındaki net_read_timeout ve net_write_timeout varsayılan olarak 30 ve 60 saniyedir. 90 saniye süren ağır bir sorgu bağlantıyı kapatır.

Üçüncüsü ve en çok hafife alınanı: tablo kilidi. mysqldump varsayılan olarak --opt ile çalışır ve bunun içinde --lock-tables vardır. InnoDB'de bu kilit neredeyse işe yaramaz ama MyISAM'de tüm tabloyu kilitler ve siteniz yedek boyunca yanıt vermez. Siteniz yoğun trafikliyse, siz sadece yedek alırken kullanıcılar 500 hatası görür.

Birkaç gigabaytlık veritabanı için doğru mysqldump komutu

Bu, 5 ila 20 gigabaytlık veritabanlarında kullandığım komut:

mysqldump --single-transaction --quick --skip-lock-tables \
  --routines --triggers --events \
  --default-character-set=utf8mb4 \
  -u dbuser -p dbname | gzip -1 > backup-$(date +%F).sql.gz

Her seçeneğin bir nedeni var. --single-transaction, InnoDB'de tekrarlanabilir isolation level ile bir işlem açar ve tabloları kilitlemeden tutarlı bir snapshot verir. --quick, satırların tek tek çıktıya yazılmasını sağlar, tüm result set'in sunucu belleğinde toplanmasını değil; on milyon satırlı bir tabloda bu, başarı ile OOM kill arasındaki farktır. --skip-lock-tables ise o MyISAM kilidini kaldırır.

Çoğu kişinin bilmediği bir nokta: --single-transaction yalnızca InnoDB'de anlamlıdır. MyISAM tablolarınız varsa, bu seçenek size tutarlı bir snapshot vermez ve yedeğiniz mantıksal olarak tutarsız olabilir. Önce SHOW TABLE STATUS ile tablo türlerini kontrol edin.

gzip -1 hakkında da açıklayayım, çünkü genellikle -9 koyarlar. Seviye 9, metin veritabanında yaklaşık %15 ila 20 daha küçük dosya verir ama CPU'yu birkaç katına çıkarır ve yedek süresini uzatır. Aynı anda kullanıcıya hizmet veren bir sunucuda -1 daha akıllıca bir seçimdir. Yedek gece alınıyorsa ve sunucu sakinsa, -6 iyi bir dengedir.

Veritabanı başka bir sunucudaysa

Uzaktan bağlandığınızda, dump'tan önce şu iki satırı çalıştırın:

SET GLOBAL net_read_timeout = 600;
SET GLOBAL net_write_timeout = 600;

İstemci tarafında da büyük BLOB'lu bir tablonuz varsa --max-allowed-packet=512M ekleyin. Bu olmadan, yanıltıcı bir mesaj olan MySQL server has gone away hatası alırsınız; gerçek sorun sunucunun kesilmesi değil, paketin izin verilen sınırı aşmasıdır.

Parçalı geri yükleme; zaman aşımını atlatmanın yolu

Yedek almak işin yarısı. 8 gigabaytlık bir dosyayı paylaşımlı hosting'de mysql -u user -p db < backup.sql ile geri yüklemek genellikle başarısız olur, çünkü uzun bir SQL betiğinin çalıştırılması panelin zaman aşımını aşar.

Çözüm, dosyayı bölmek. gzip dosyanız varsa, önce açın ve sonra:

split -l 50000 backup.sql part_

Bu, dosyayı elli bin satırlık parçalara böler. Şimdi her parçayı ayrı ayrı içe aktarın:

for f in part_*; do mysql -u dbuser -p dbname < "$f" || break; done

Bu yöntemin gizli avantajı şu: yedinci parça hata verdiğinde, sorunun nerede olduğunu bilirsiniz ve sıfırdan başlamanız gerekmez. Tek parça bir dosyayla, kırkıncı dakikadaki hata baştan başlamak demektir.

Pratik bir uyarı: split satır bazında böler ve çok satırlı bir INSERT'in ortasından bölebilir. Dump'ınız --extended-insert ile alınmışsa (ki varsayılandır), her satır genellikle tek satırdadır ve sorun çıkmaz. Ama --skip-extended-insert ile aldıysanız, önce --complete-insert ile yeniden alın.

Burada hata yapıyorlar

Gerçekten çok sık gördüğüm hata: yedeği --single-transaction ile alıyorlar, dosya sağlam, ama geri yükleme sırasında hedef veritabanında aynı isimde tablolar zaten varken sadece mysql db < backup.sql çalıştırıyorlar. Sonuç şu: CREATE TABLE, Table already exists hatası veriyor, betik devam ediyor ve sonunda tablolarının yarısı eski veri, yarısı yeni veri olan bir veritabanınız oluyor. Site açılıyor ama sayılar yanlış ve haftalarca fark etmeyebilirsiniz.

Belirtisi de tam olarak bu: geri yükleme "başarılı" raporlanıyor, çıktıda net bir hata yok, ama satır sayıları beklediğinizle uyuşmuyor. Geri yüklemeden önce her zaman hedef veritabanını boşaltın veya dump sırasında --add-drop-table kullanın.

Alternatifler; mysqldump nerede yetersiz kalıyor

20 gigabayt altındaki veritabanları için tercihim her zaman mysqldump, çünkü çıktı dosyası metindir ve içinden belirli bir tabloyu çekebilirsiniz. Bu hacmin üzerinde XtraBackup'ı tercih ederim; fiziksel yedek 100 gigabaytlık bir veritabanında saatler değil dakikalar sürer. Ama koşulu, yeterli boş disk alanınız olması, çünkü XtraBackup pratikte veri dosyalarının bir kopyasını oluşturur.

Linux hosting üzerindeyseniz ve root erişiminiz yoksa, pratikte aynı mysqldump ile sınırlı kalırsınız. O durumda yedeği daha küçük parçalara bölün ve her gece tüm veritabanını değil, yalnızca çok değişen tabloları alın.

Zamanınızı kurtaracak birkaç ipucu

• Her yedekten önce veritabanı boyutunu SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024) AS mb FROM information_schema.tables GROUP BY table_schema; ile kontrol edin. Gerçek sayıyı bilin, tahmini değil.
• Yedeği aynı sunucuda tutmayın. Disk dolarsa hem veritabanını hem de yedeği kaybedersiniz.
• Ayda bir kez geri yüklemeyi bir test veritabanında deneyin. Test edilmemiş yedek, yedek değildir.
• WordPress kullanıyorsanız ve zamanlanmış yedekleriniz çalışmıyorsa, sorun genellikle veritabanı değil cron'dur; WordPress wp-cron; neden çalışmıyor ve gerçek cron ile nasıl değiştirilir yazısına bakın.

Daha fazla komut ve hazır örnek için ServerNet dokümantasyonu ve bilgi bankası iyi bir kaynaktır. Yedekten önce sunucunun dışarıdan sağlıklı yanıt verdiğinden emin olmak istiyorsanız, ücretsiz araçlar da aynı sitede mevcuttur.

Sık sorulan sorular

mysqldump büyük veritabanında neden çok yavaş?

Çünkü varsayılan olarak tek iş parçacıklı çalışır ve tüm veriyi sunucudan okur, diske yazar ve sıkıştırır. 10 gigabaytlık bir veritabanında bu bir ila iki saat sürebilir. --quick ile bellek kullanımını azaltırsınız ama hızı birkaç katına çıkarmaz. Gerçek hız için mydumper veya XtraBackup'a geçmeniz gerekir.

Sadece bir tabloyu yedekleyebilir miyim?

Evet. Tablo adını veritabanı adından sonra yazın: mysqldump -u user -p dbname table_name > table.sql. Bu, siparişler veya loglar gibi çok değişen tablolar için çok kullanışlıdır ve günlük yedek boyutunu ciddi ölçüde azaltır.

Yedek sırasında MySQL server has gone away hatası ne anlama gelir?

İstemci ile sunucu arasında alışveriş edilen paketin max_allowed_packet'ten büyük olduğu ya da bağlantının ağ zaman aşımı nedeniyle kapandığı anlamına gelir. max_allowed_packet değerini hem sunucuda hem istemcide yükseltin ve net_write_timeout'u artırın. Büyük BLOB'lu bir tablonuz varsa, parametreleri ayarlamadığınız sürece bu hata neredeyse kaçınılmazdır.

gzip'lenmiş yedeği tamamen açmadan nasıl geri yüklerim?

Doğrudan pipe edebilirsiniz: gunzip < backup.sql.gz | mysql -u user -p dbname. Bu yöntem diskte ara dosya oluşturmaz ve sınırlı alan için uygundur. Ama iş ortasında hata verirse, hata noktasını tespit etmek daha zor olur; büyük veritabanları için aynı parçalı yöntem daha güvenlidir.