patternsqlCritical
Safest way to perform mysqldump on a live system with active reads and writes?
Viewed 0 times
writesactivewithsystemmysqldumpwayliveperformreadsand
Problem
I'm not sure if this is true but I remember reading if you run the following command in linux
while reads and writes are being made to a database then the dump may contain errors.
Are there particular options in the command
mysqldump -u username -p database_name > backup_db.sqlwhile reads and writes are being made to a database then the dump may contain errors.
Are there particular options in the command
mysqldump to make sure this is done safely on a live system? I'm okay with reads/writes being disabled for our users for a few seconds (The database <50MB)Solution
All Data is InnoDB
This is what will give you an exact point-in-time snapshot of the data:
All 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
Give it a Try !!!
UPDATE 2012-06-22 08:12 EDT
Since you have <50MB of total data I have another option. Instead of launching a SLEEP command into the background to hold the global read lock for 86400 sec (that 24 hr) just to get the process ID and kill outside, let's try setting a 5 second timeout in mysql rather than in the OS:
This is a cleaner and simpler approach for very small databases.
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.sqlGive it a Try !!!
UPDATE 2012-06-22 08:12 EDT
Since you have <50MB of total data I have another option. Instead of launching a SLEEP command into the background to hold the global read lock for 86400 sec (that 24 hr) just to get the process ID and kill outside, let's try setting a 5 second timeout in mysql rather than in the 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.sqlThis is a cleaner and simpler approach for very small databases.
Code Snippets
mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sqlmysql -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.sqlSLEEP_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.sqlContext
StackExchange Database Administrators Q#19532, answer score: 115
Revisions (0)
No revisions yet.