Insert only selected columns from MySQL dump file into database

Question

I am having a simple (I think) problem.

I am having a dump of MySQL database before disaster.

I need to import and replace from this dump only three columns from single table (in over 5000 rows, so that's why I am aware of doing it manually).

What should I do to do it and do not destroy anything else in working database?

I am just thinking that there is an option to skip columns during import and replace (UPDATE command I think) only these I need.

I will be thankful for help :(

------------ UPDATE ---------------

Okay, I used PHPMyAdmin and first I used SELECT query to get only three columns from whole table. Then I dumped it and I have SQL file with a dump containing only three columns.

Now, having this dump, can I (I do not know how to name it) edit or change something inside this typical MySQL dump file to make it possible to import these three columns with replace all the existing values?

I mean - to make existing column empty, then use maybe "INSERT INTO" but to whole table?

It is just over 2600 rows and I can not change it manually, so it would be better do use automation.

Answer 1

As far as I know, this is not possible. You can try to use sed in order to extract only the table you want - but specifically 3 columns would be complicated if not impossible.

Can I restore a single table from a full mysql mysqldump file?

The best way would be as @Ali said and just import it to a temp DB and then export the required data/columns to a new dump. Restore DB to temp db then:

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

// Since you updated the question:

You want to probably use REPLACE INTO with your dump with the --replace option - though this will delete the row and replace it, not just the individual columns. If you want just the individual columns, the only way I can think of is with UDPATE. To use UPDATE, your options are:

Multi-table update

UPDATE mydb.mytable AS dest JOIN tempdb.mytable AS origin USING (prim_key)
SET dest.col1 = origin.col1,
dest.col2 = origin.col2,
...

Then drop the temp database.

Search/Replace Dump

Take your dump and use the INSERT ... ON DUPLICATE KEY UPDATE option to add it to the end of each insert line (assuming you exported/dumped individual insert commands).