2

I need to dump a table for daily transfer.
mysqldump requires LOCK TABLES privilege. Unfortunately, LOCK TABLES does not apply at the table level, only at the database level, and I don't want to give the MySQL user that much access. Is there a way to do something like ... 

/usr/bin/mysql -uusername -ppassword -D daterbase -e "select * from table" > outfile.sql

... but have it output in SQL format instead of query result format?

Improve this question

1 Answer 1

Reset to default
1

If the table is an InnoDB table, you can use mysqldump --single-transaction dbname tablename.

By using a transaction, it doesn't need to lock anything.

Or you can use SELECT ... INTO OUTFILE but that dumps in a tab-delimited text format, not in SQL format. See on mysql for details.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

oops ... tried it and ran into a duplicate key issue because keys were being copied ... so I'm back to the drawing board. I need to copy records without the keys so I can insert on the receiving end. Since I didn't clarify that in my question, should I ask a new question?
Mysqldump doesn't have any option for skipping the key column. So you're back to using SELECT <subset-of-columns> INTO OUTFILE and then importing that file with mysqlimport.
I have exactly the same problem, I use --single-transaction but got the error :Couldn't execute 'FLUSH TABLES WITH READ LOCK' so it seems that a lock is actually required PS I've just realized that the answer is old! certainly things are changed now ...
@Nik Is your table an InnoDB table?
Yes @BillKarwin
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.