How to dump a single table in MySQL without locking?

Question

When I run the following command, the output only consists of the create syntax for 'mytable', but none of the data:

mysqldump --single-transaction -umylogin -p mydatabase mytable > dump.sql

If I drop --single-transaction, I get an error as I can't lock the tables.

If I drop 'mytable' (and do the DB), it looks like it's creating the INSERT statements, but the entire DB is huge.

Is there a way I can dump the table -- schema & data -- without having to lock the table?

(I also tried INTO OUTFILE, but lacked access for that as well.)

Answer 1

The answer might depend on the database engine that you are using for your tables. InnoDB has some support for non-locking backups. Given your comments about permissions, you might lack the permissions required for that.

The best option that comes to mind would be to create a duplicate table without the indicies. Copy all of the data from the table you would like to backup over to the new table. If you create your query in a way that can easily page through the data, you can adjust the duration of the locks. I have found that 10,000 rows per iteration is usually pretty darn quick. Once you have this query, you can just keep running it until all rows are copied.

Now that you have a duplicate, you can either drop it, truncate it, or keep it around and try to update it with the latest data and leverage it as a backup source.

Jacob