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)

  • 1
    oh... I wish there was a safe way to do it, because not only the dump can contain errors, but also the database itself is getting not saved records if they occur duding the dump. It is happening occasionally, maybe when the record is saved at the same time when the according db table is being scan for dumping. Because of it, I'm afraid of making backup in a single transaction, I guess it will increase the chances of lost records. Oct 19, 2020 at 4:37

6 Answers 6


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:

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.

  • 1
    5 secs is just precautionary. You can can try lower values. Jun 20, 2012 at 3:33
  • 1
    Rolando - is ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query an expected error message?
    – user784637
    Jun 22, 2012 at 11:32
  • 1
    Did all the MySQL data come out in the the mysqldump ? Jun 22, 2012 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. Jun 22, 2012 at 11:59
  • 1
    Try my new suggestion and see if it goes well. Hopefully, there will be no error message. Jun 22, 2012 at 12:16
  • 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


Here's how I did it. It should work in all cases since it uses FLUSH TABLES WITH READ LOCK.



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

The shell sleep command is just to make sure that the background task running the mysql locking command is executed before the mysqldump starts. You could reduce it to 1 second and it should still be fine. Increase it to 30 seconds and try inserting a values in any table from another client during those 30 seconds you'll see it's locked.

There are 2 advantages in using this manual background locking, instead of using the mysqldump options --single-transaction and --lock-tables:

  1. This locks everything, if you have mixed MyISAM/InnoDB tables.
  2. You can do run other commands in addition to the mysqldump during the same locking period. It's useful, for instance, when setting up replication on a master node, because you need to get the binary log position with SHOW MASTER STATUS; at the exact state of the dump you created (before unlocking the database), to be able to create a replication slave.

If you want to do this for MyISAM or mixed tables without any downtime from locking the tables, you can set up a slave database, and take your snapshots from there. Setting up the slave database, unfortunately, causes some downtime to export the live database, but once it's running, you should be able to lock it's tables, and export using the methods others have described. When this is happening, it will lag behind the master, but won't stop the master from updating it's tables, and will catch up as soon as the backup is complete.


The suggestion of the mysql official documentation is that you should have a Master "M1" database and a Slave "S1" Database which is described in "Scenario 2: Backup with a Read-Only Slave" Backing Up a Master or Slave by Making It Read Only

You should set the slave database read only and perform th


if you have a very big MYISAM table and you need to dump the table without lock and avoid high server load, you can use the following script.



read -p "Dumping table ${my_db}.${my_table} to ${my_table}.sql?" yn
case $yn in
    [Yy]* ) break;;
    * ) echo "User cancel."; exit;;

my_count=$(mysql $my_db -u $my_user -p$my_password -se "SELECT count(*) FROM $my_table")
my_count=$(($my_count + 0))

if [ ! $my_count ]
    echo "No records found"

echo "Records in table ${my_db}.${my_table}: ${my_count}"

echo "" > $my_table.sql


for (( limit=0; limit<=$my_count; limit+=$my_step )); do
    progress=$((max_progress * ( limit + my_step) / my_count))

    echo -ne "Dumping ["
    for ((i=0; i<$progress; i ++)); do
        echo -ne "#"
    for ((; i<$max_progress; i ++)); do
        echo -ne "."

    mysqldump -u $my_user -p$my_password --complete-insert --no-create-info --opt --where="1 limit $limit , $my_step" $my_db $my_table >> $my_table.sql
    echo "" >> $my_table.sql

    echo -ne "] $((100 * ( limit + my_step ) / my_count)) %"
    echo -ne "\r"

    sleep 1


echo -ne "\n"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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