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?
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.
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, it will get complicated assuming 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.
If you don't care about performance:
create table t2 like t1;
insert into t2 select * from t1;
alter table t2 drop column xyz;
alter table t2 drop column abc;
mysqldump db t2 | sed 's/`t2`/`t1`/'
You can also use Phpmyadmin to do this. Create a query to select the fields you want, then choose the "export" function at the bottom of the page (this exports your query results, not the whole table), and choose the "Custom" option. You can then choose SQL as your format - if you want you can then create a series of UPDATE queries rather than INSERTS. Can often be a quick way to get something like this done.
Many DB admin tools have the ability to do a custom import. In particular, I accomplished this using Toad for MySQL to transfer data between tables using autoincrement PKs.
I saved a query result to Excel using the Export Wizard.
Then you can exclude columns using the Import Wizard by setting the Source Field to: Skip This Column
