Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to take mysql dump of selected columns of a table

Tags:

mysql

mysql5

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?

like image 984
user1085195 Avatar asked Mar 07 '13 06:03

user1085195


3 Answers

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';
like image 189
rickydj Avatar answered Sep 30 '22 17:09

rickydj


Select the column into a file ?

Select col from table into outfile 'fileame'
like image 9
SteveP Avatar answered Sep 30 '22 19:09

SteveP


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`;
like image 9
chiliNUT Avatar answered Sep 30 '22 17:09

chiliNUT