skip-lock-tables and mysqldump

Question

Daily we run mysql dumps on about 50 individual databases, package them up and then store them offsite. Some of these databases are rather large and contain myisam tables (which CANNOT be changed so suggesting it is pointless).. I have been reading up on using the skip-lock-tables option when doing a dump but have not read what the downside would be. All I see are basically different iterations of "it could have adverse effects if data is inserted to a table while it is dumping."

What are these adverse effects? Does it just mean we will miss those queries upon a restore or will it mean the dump file will be broken and useless? I honestly could care less if we lose NEW data posted after the dump has started as I am just looking for a snapshot in time.

Can I rely on these database dumps to contain all the data that was saved before issuing the dump.

Answer 1

--skip-lock-tables parameter instructs the mysqldump utility not to issue a LOCK TABLES command before obtaining the dump which will acquire a READ lock on every table. All tables in the database should be locked, for improved consistency in case of a backup procedure. Even with skip-lock-tables, while a table is dumped, will not receive any INSERTs or UPDATEs whatsoever, as it will be locked due the SELECT required to obtain all records from the table. It looks like this

SELECT SQL_NO_CACHE * FROM my_large_table 

and you can see it in the process list with the SHOW PROCESSLIST command. If you are using the MyISAM engine which is non-transactional, locking the tables will not guarantee referential integrity and data consistency in any case, I personally use the --skip-lock-tables parameter almost always. In InnoDB use the --single-transaction parameter for the expected effect. Hope this helps.