angelcool Posted August 3, 2009 Share Posted August 3, 2009 Hello, Should I lock tables when doing a mysqldump ? Angel Quote Link to comment Share on other sites More sharing options...
fenway Posted August 3, 2009 Share Posted August 3, 2009 Always. Quote Link to comment Share on other sites More sharing options...
angelcool Posted August 3, 2009 Author Share Posted August 3, 2009 Do locking a db/table locks write queries or/and read queries ? Quote Link to comment Share on other sites More sharing options...
angelcool Posted August 3, 2009 Author Share Posted August 3, 2009 Sorry but I forgot to be more detailed in my last post. 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: [root@localhost ~]# 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. Angel Quote Link to comment Share on other sites More sharing options...
fenway Posted August 4, 2009 Share Posted August 4, 2009 So --lock-tables is appropriate -- or all-tables, depending on what you're doing. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.