How to take mysql dump of selected columns of a table

mysql mysql5
user1085195 picture user1085195 · Mar 7, 2013 · Viewed 32.7k times · Source

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?

Answer

rickydj picture rickydj · Jul 7, 2015

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';

Related questions

How to get a list of user accounts using the command line in MySQL?

I'm using the MySQL command line utility and can navigate through a database. Now I need to see a list of user accounts. How can I do this? I'm using MySQL version 5.4.1.

Read More
Can't connect to MySQL server on 'localhost' (10061)

I recently installed MySQL 5 on Windows 2003 and tried configuring an instance. Everything worked fine until I got to "Applying Security settings", at which point it gave me the above error (Can't connect to MySQL server on 'localhost' (10061)). I do have …

Read More
MySQL 1062 - Duplicate entry '0' for key 'PRIMARY'

I have the following table in MySQL version 5.5.24 DROP TABLE IF EXISTS `momento_distribution`; CREATE TABLE IF NOT EXISTS `momento_distribution` ( `momento_id` INT(11) NOT NULL, `momento_idmember` INT(11) NOT NULL, `created_at` DATETIME DEFAULT NULL, `updated_at` DATETIME DEFAULT …

Read More