Re: get a partial dump
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: get a partial dump
Ok, but I can't trace foreign key references, can I? Plus I can't run this from Java (well, I can, but it's cumbersome)? But thanks for the hint Stefan On Wednesday 24 October 2012 05:33:22 Eric Bergen wrote: > mysqldump has a --where argument that allows you to pass in a where clause. > > On Thursday, October 18, 2012, Stefan Kuhn wrote: > > 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 > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: get a partial dump
mysqldump has a --where argument that allows you to pass in a where clause. On Thursday, October 18, 2012, Stefan Kuhn wrote: > 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 > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > > -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net
get a partial dump
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
