What parameter to use with mysqldump to lock tables ?

Discussion in 'Server Operation' started by Angelito, Aug 4, 2009.

  1. Angelito

    Angelito New Member

    Locking tables when doing a mysqldump is a good practice, but

    When locking tables, do users are still able to read from DB ?

    My logic dictates to allow users only to read records and NOT to allow any insert, updates, etc.(or any query that will modify db/table content) while backup is going on; this way the backup will be consistent.

    I got the following in bash:
    [[email protected] ~]# mysqldump --help | grep lock
      --add-locks         Add locks around insert statements.
                          --skip-disable-keys --skip-add-locks
      -x, --first-slave   Deprecated, renamed to --lock-all-tables.
                          using --lock-all-tables or --master-data: in this case
                          moment all tables are locked. So if you want your dump
                          should use --lock-all-tables or --master-data with
      -x, --lock-all-tables
                          by taking a global read lock for the duration of the
                          --lock-tables off.
      -l, --lock-tables   Lock all tables for read.
                          --lock-all-tables on, unless --single-transaction is
                          specified too (in which case a global read lock is only
                          of the dump.Option automatically turns --lock-tables off.
      --opt               Same as --add-drop-table, --add-locks, --create-options,
                          --quick, --extended-insert, --lock-tables, --set-charset,
                          automatically turns off --lock-tables.
      --skip-opt          Disable --opt. Disables --add-drop-table, --add-locks,
                          --lock-tables, --set-charset, and --disable-keys.
    add-locks                         TRUE
    lock-all-tables                   FALSE
    lock-tables                       TRUE

    Not sure what parameter to use.
    I have googled "MySQL backup tutorial" but none of the tutorials found explain this.

    I will really appreciate that someone experienced help me clarify this.

  2. falko

    falko Super Moderator ISPConfig Developer

    Use --lock-tables.

  3. Angelito

    Angelito New Member

    When locking tables, do users are still able to read from DB ?

    Thank you falko!.

    When locking tables, do users are still able to read from DB ?
    I think the answer is yes when using mysqldump.

    This is from the man page of mysqldump:

    --lock-tables, -l

    Lock all tables before starting the dump. The tables are locked with
    READ LOCAL to allow concurrent inserts in the case of MyISAM tables.
    For transactional tables such as InnoDB and BDB,
    --single-transaction is a much better option, because it does not
    need to lock the tables at all.

    mysqldump will get a read lock.

    This is from MySQL website:

    If a session obtains a READ lock on a table, that session (and all other sessions) can only read from the table. If a session obtains a WRITE lock on a table, only the session holding the lock can write to the table (that session can also read from the table); other sessions are blocked from reading or writing the locked table until the lock has been released.

    Well this is interesting to know.

  4. falko

    falko Super Moderator ISPConfig Developer

    No, they won't be able to read from the database until the mysqldump process has finished.

Share This Page