How to take mysql dump of selected columns of a table

Question

I have a requirement in which I have to take mysql dump of just one column of a table. Since that table has too many columns, I don't want to take dump of the full table. I have to get this dump of the table from one server to another. Any idea how I can do this?

Mysqldump does not support to export selected columns. Only you can dump full table or selected rows form it. But you can create a view & dump the view. But it is a view not a table. — Chamara Maduranga, Nov 21 '18 at 09:53

score 20 · Answer 1 · answered Jul 07 '15 at 07:12

20

If you would like to take mysql dump including the schema, it can be done by following the below steps:

create a temp table:

create table temp_table like name_of_the_original_table;

duplicating data into temp_table:

insert into temp_table select * from name_of_the_original_table;

dropping unnecessary fields:

alter table temp_table drop column somecolumn;

post this, you could take a mysqldump by running:

mysqldump -u <username> -p <password> databasename temp_table

If the intention is to take a data dump(without the schema), you can run the below command:

select * from sometable into outfile '/tmp/datadump' fields terminated by '\t' lines terminated by '\n';

answered Jul 07 '15 at 07:12

rickydj

629
5
17

2

If you've got a lot of rows in your source table, make sure you drop columns from the target table *before* populating the target table. – Erwin Wessels Jun 12 '18 at 11:38
can you dump all the data on the temp table with 1 or more column missing? like if table1 has 3 columns and temp_table1 has 2 columns, can you still dump the data? – Skeeith22 Dec 06 '19 at 04:00
how about if you duplicate certain columns only from database1 to database2? – Skeeith22 Dec 06 '19 at 12:53

score 9 · Answer 2 · answered Mar 07 '13 at 06:34

9

Select the column into a file ?

Select col from table into outfile 'fileame'

answered Mar 07 '13 at 06:34

SteveP

18,840
9
47
60

I have to get this dump from one server to another, so this file wouldn't be accessible over there. – user1085195 Mar 07 '13 at 06:45
1

You copy the file, just like you would with `mysqldump`. – Barmar Mar 07 '13 at 06:50
1

What are you going to with the file on the remove server ? – SteveP Mar 07 '13 at 09:06

score 9 · Answer 3 · answered Sep 27 '17 at 17:59

mysql> CREATE TABLE `tempTable` AS SELECT `columnYouWant` from `table`;
$> mysqldump yourDB tempTable > temp.sql

copy temp.sql to target server, then on target server

$> mysql yourDB < temp.sql
mysql> RENAME TABLE `table` TO `tableBackup`, `tempTable` TO `table`;

How to take mysql dump of selected columns of a table

3 Answers3

Linked