angelcool Posted August 3, 2009 Share Posted August 3, 2009 Hello, Should I lock tables when doing a mysqldump ? Angel Link to comment https://forums.phpfreaks.com/topic/168677-when-to-lock-tables/ Share on other sites More sharing options...
fenway Posted August 3, 2009 Share Posted August 3, 2009 Always. Link to comment https://forums.phpfreaks.com/topic/168677-when-to-lock-tables/#findComment-889988 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 ? Link to comment https://forums.phpfreaks.com/topic/168677-when-to-lock-tables/#findComment-890032 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 Link to comment https://forums.phpfreaks.com/topic/168677-when-to-lock-tables/#findComment-890107 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. Link to comment https://forums.phpfreaks.com/topic/168677-when-to-lock-tables/#findComment-890915 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.