Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm not sure if this is true but I remember reading if you run the following command in linux

mysqldump -u username -p database_name > backup_db.sql

while 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)

share|improve this question
add comment

2 Answers

up vote 11 down vote accepted

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 them

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

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 !!!

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.sql

This is a cleaner and simpler approach for very small databases.

share|improve this answer
1  
5 secs is just precautionary. You can can try lower values. –  RolandoMySQLDBA Jun 20 '12 at 3:33
1  
Did all the MySQL data come out in the the mysqldump ? –  RolandoMySQLDBA Jun 22 '12 at 11:56
1  
I'm not sure about the error message. This is only a guess, but that might have come from the one-line script that kills the user-defined SLEEP function call I mentioned in the second script. –  RolandoMySQLDBA Jun 22 '12 at 11:59
1  
Try my new suggestion and see if it goes well. Hopefully, there will be no error message. –  RolandoMySQLDBA Jun 22 '12 at 12:16
1  
As long as the mysqldump goes to completion, the error message just becomes an annoyance. –  RolandoMySQLDBA Jun 22 '12 at 12:26
show 8 more comments

For InnoDB tables, you should use the --single-transaction option, as mentioned in another answer. For MyISAM there is --lock-tables.

See the official documentation here:

http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_lock-tables

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.