Mysqldump tables excluding some fields

Question

Is there a way to mysqldump a table without some fields?

Let me explain:
I have a MySQL database called tests. In tests I have 3 tables: USER, TOTO and TATA. I just want to mysqldump some fields of table USER, so excluding some fields like mail, ip_login, etc.

How can I do this?

Answer 1

Try this:

SELECT column1, column2, column10 FROM USER INTO OUTFILE "c:/user.csv" FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY "\n";

For more option read the documentation page.

Answer 2

This can't be done with mysqldump directly. However, you can create a temporary table or a view that does not contain the data in question, and then dump the resulting data. In the case of a view, I think you'll have to use INTO OUTFILE rather than mysqldump to get the data you want, but using a view instead of a temporary table has the advantage of not taking up any extra space in the DBMS.

The other option is to create a parser for the file to strip the fields you don't want. To put it mildly, this can get complicated very quickly, that is if you want to create a parser that's robust in the face of multibyte characters, escaped string separators, etc., etc. But for the trivial or one-time-use case, it may still be a valid option.