Mysql:Need to upload data to database, get duplicate key error – how can dup keys be ignored

If your target database is an older MySQL version such as MySQL 5.0, then partitioning commands will already be ignored since they are wrapped in comments like this:

/*!50500 PARTITION BY RANGE  COLUMNS(column)
(PARTITION partition_name VALUES LESS THAN ('value') ENGINE = InnoDB,
 PARTITION partition_name VALUES LESS THAN ('value') ENGINE = InnoDB) */;

If you are loading into MySQL 5.1 or higher, then one trick that came to my mind is to change the version number in the comment section. You do not have to manually edit the dump file, you can process the file through sed. For example:

sed 's\!50500 PARTITION\!60000 PARTITION\g' dumpfile.sql | mysql

1) GUI option

You can use Toad for MySQL Freeware from here.

To take an export that creates inserts for a specified set of data, after creating a connection to your mysql database, you can choose Tools->Export->Export Wizard

Click Next, then Add Button

Choose query as the Export category and then type your SQL statement for one of the tables

SELECT col1, col2 ... 
  FROM table_name
 WHERE tool_id='xyz'

Then Click next - choose SQL SCRIPT any other relevant options

Choose file as the output

Repeat this for as many tables as required

Then execute it to create a set of insert scripts.

You can take a structure export either using TOAD or phpmyadmin and the combination should give you what you are looking for which i assume is a cut down set of data for a test environment.

2) Command line option

Use mysqldump at the command line and list all tables that have the tool_id column

  mysqldump -t -u [username] -p test mytable anothertable --where="tool_id = 1"

This should generate a set of insert statements and as above, if you load these scripts into an empty schema exported with phpmyadmin using structure only, it should give you what you need.