Cara teraman untuk melakukan mysqldump pada sistem langsung dengan aktif membaca dan menulis?

Semua Data adalah InnoDB

Inilah yang akan memberi Anda cuplikan data waktu-waktu yang tepat:

mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql

--single-transaction menghasilkan pos pemeriksaan yang memungkinkan dump untuk menangkap semua data sebelum pos pemeriksaan saat menerima perubahan yang masuk. Perubahan yang masuk tersebut tidak menjadi bagian dari dump. Itu memastikan titik waktu yang sama untuk semua tabel.

--routines membuang semua prosedur tersimpan dan fungsi tersimpan

--triggers membuang semua pemicu untuk setiap tabel yang memilikinya

Semua Data adalah MyISAM atau Campuran InnoDB/MyISAM

Anda harus memberlakukan kunci baca global, melakukan mysqldump, dan melepaskan kunci global

mysql -uuser -ppass -Ae"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
sleep 5
mysql -uuser -ppass -ANe"SHOW PROCESSLIST" | grep "SELECT SLEEP(86400)" > /tmp/proclist.txt
SLEEP_ID=`cat /tmp/proclist.txt | awk '{print $1}'`
echo "KILL ${SLEEP_ID};" > /tmp/kill_sleep.sql
mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql
mysql -uuser -ppass -A < /tmp/kill_sleep.sql

Cobalah !!!

PEMBARUAN 22-06-2012 08:12 WIB

Karena Anda memiliki total data <50MB, saya memiliki opsi lain. Alih-alih meluncurkan perintah SLEEP ke latar belakang untuk menahan kunci baca global selama 86400 detik (24 jam itu) hanya untuk mendapatkan ID proses dan membunuh di luar, mari coba atur waktu tunggu 5 detik di mysql daripada di OS:

SLEEP_TIMEOUT=5
SQLSTMT="FLUSH TABLES WITH READ LOCK; SELECT SLEEP(${SLEEP_TIMEOUT})"
mysql -uuser -ppass -Ae"${SQLSTMT}" &
mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql

Ini adalah pendekatan yang lebih bersih dan sederhana untuk database yang sangat kecil.

• Untuk tabel InnoDB, Anda harus menggunakan --single-transaction opsi, sebagaimana disebutkan dalam jawaban lain.
• Untuk MyISAM ada --lock-tables .

Lihat dokumentasi resminya di sini

Begini cara saya melakukannya. Ini harus bekerja dalam semua kasus karena menggunakan FLUSH TABLES WITH READ LOCK .

#!/bin/bash

DB=example
DUMP_FILE=export.sql

# Lock the database and sleep in background task
mysql -uroot -proot $DB -e "FLUSH TABLES WITH READ LOCK; DO SLEEP(3600);" &
sleep 3

# Export the database while it is locked
mysqldump -uroot -proot --opt $DB > $DUMP_FILE

# When finished, kill the previous background task to unlock
kill $! 2>/dev/null
wait $! 2>/dev/null

echo "Finished export, and unlocked !"

Shell sleep perintah hanya untuk memastikan bahwa tugas latar belakang yang menjalankan perintah penguncian mysql dijalankan sebelum mysqldump dimulai. Anda bisa menguranginya menjadi 1 detik dan seharusnya masih baik-baik saja. Tingkatkan menjadi 30 detik dan coba sisipkan nilai di tabel mana pun dari klien lain selama 30 detik itu Anda akan melihatnya terkunci.

Ada 2 keuntungan dalam menggunakan penguncian latar belakang manual ini, daripada menggunakan mysqldump opsi --single-transaction dan --lock-tables :

1. Ini mengunci semuanya, jika Anda memiliki tabel campuran MyISAM/InnoDB.
2. Anda dapat menjalankan perintah lain selain mysqldump selama periode penguncian yang sama. Ini berguna, misalnya, saat menyiapkan replikasi pada node master, karena Anda perlu mendapatkan posisi log biner dengan SHOW MASTER STATUS; pada status yang tepat dari dump yang Anda buat (sebelum membuka kunci database), agar dapat membuat budak replikasi.