All Data is InnoDB
This is what will give you an exact point-in-time snapshot of the data:
mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql
--single-transaction
produces a checkpoint that allows the dump to capture all data prior to the checkpoint while receiving incoming changes. Those incoming changes do not become part of the dump. That ensures the same point-in-time for all tables.--routines
dumps all stored procedures and stored functions--triggers
dumps all triggers for each table that has themAll Data is MyISAM or Mix of InnoDB/MyISAM
You will have to impose a global read lock, perform the mysqldump, and release the global lock
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
Give it a Try !!!
沒有留言:
張貼留言