From: bars0.bars0.bars0
Date: October 26 2012 7:26am
Subject: Re: get a partial dump
List-Archive: http://lists.mysql.com/mysql/228497
Message-Id: <508A3B0D.8060209@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

W dniu 2012-10-18 16:27, Stefan Kuhn pisze:
> Hi everybody,
> I want to get insert statements from a mysql database for a number of rows of
> a table, specified by a where clause. I also need the inserts for the
> datasets linked via foreign keys in other tables. So I need a sort of partial
> dump, a bit like mysqldump, but restricted to a (small) set of data. This
> needs to be done in a Java program, using mysql via jdbc.
> Does anybody know an easy way to do this? Of course I could build the
> statements in the java code, but I thought mysql might offer that or at least
> parts of it. Does anybody have an idea?
> Thanks,
> Stefan
>

Hi there is a great java dbms client: SQL Workbench/J.
This app has some really great batch capabilities.
Look at chapter: 10,12,13,14 
http://www.sql-workbench.net/manual/workbench-manual.html

I use it to generate insert files by specifying query files.
On the linux box i use simple shell script:

----------------------------------------------------------------------------
#!/bin/bash


#
# This is a script that fetch dataset based on '-script=file'.
# Dataset is saved as SQL INSERT file - this option, as well as
# output direcory can be also set in '-script=file'
#

# variables:

app_loc='-jar /yourPathToAppJar/sqlworkbench.jar '
jdbc_conn='-url=jdbc:mysql://127.0.0.1:3306/yourDatabase '
driver_type='-driver=com.mysql.jdbc.Driver '
db_user='-username=yourUsername '
db_pass='-password=yourPassword '
jdbc_loc='-driverjar=/jdbcPath/mysql-connector-java-3.1.14-bin.jar '
script_loc='-script=/pathToSqlScript/sampleSelectScript.sql '
logfile_loc='-logfile=/pathToLogfile/import.log'


java 
${app_loc}${jdbc_conn}${driver_type}${db_user}${db_pass}${jdbc_loc}${script_loc}${logfile_loc}

# END of script
----------------------------------------------------------------------------

In the sampleSelectScript.sql you specify:

1) type of action (WbExport)
2) type of output (e.g. sqlinsert, can be also Excel file with 
additional jars)
3) encoding (e.g utf-8)
4) output file name and location
5) target table in insert file

EXAMPLE:

----------------------------------------------------------------------------
-- BEGIN of SQL file

WbExport -type=sqlinsert -encoding=utf8 -file='/path/sampleInsert.sql' 
-table=someTargetTable;


SELECT * FROM someTable; -- normal select statement with joins etc.


-- If you want specify another select statement in the same file, but 
save it in another output file, you specify another WbExport:


WbExport -type=sqlinsert -encoding=utf8 
-file='/differentPath/sampleInsert.sql' -table=someTargetTable;


use another_database; -- you can also switch between databases.

SELECT * FROM someTable; -- select statement

-- END of SQL file
----------------------------------------------------------------------------

Whole solution works great when combine with cron :)


I hope this solution will help you.
Chris.