• 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

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.

Here's how I did it. It should work in all cases since it uses 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 !"

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.

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.

#!/bin/sh

my_user="user"
my_password="password"
my_db="vpn"
my_table="traffic"
my_step=100000

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

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 ]
then
    echo "No records found"
    exit
fi

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

echo "" > $my_table.sql

max_progress=60

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 "#"
    done
    for ((; i<$max_progress; i ++)); do
        echo -ne "."
    done

    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

done

echo -ne "\n"