using mysqldump with where option and a long argument list in bash script

I am trying to dump a subset of a table in MySQL with mysqldump. I have the id values of rows that I want to select from the table, stored in a file. When I use these values as a variable as follows:

ids=`cat ids.csv`
mysqldump -u root -p db Table --where="id in ($ids)" >> dump.sql

I get:

x.bash: line x: /usr/bin/mysqldump: Argument list too long

I might can try to divide the single line variable $ids (1,2,3,4,..) into shorter lists and call mysqldump in a loop but I am currently not very good with loops in bash scripts. Or there might be a better way to solve this issue.

Thanks in advance for any help.

EDIT

Considering @ajreal's suggestion, If I do

mysql -u root -p -e "select * into outfile ./dump.sql from db.Table where id in ($ids)"

I get "Argument list too long" again.

I get the id values from another environment. The database that I run this script against and the database that I get the id values to use within the where clause are in separate environments. Additionally, before this step I create the dump file with --ignore-table option, ignoring the "Table" table that I use in the next step. Therefore i would prefer to use mysqldump for that step too.

Answers


Give this a try:

xargs -a ids.csv -d '\n' -n 20 sh -c 'mysqldump -u root -p db Table --where="id in ($@)" >> dump.sql' x

The x is just a dummy value to fill $0. Alternatively:

xargs -a ids.csv -d '\n' -n 20 sh -c 'mysqldump -u root -p db Table --where="id in ($0 $@)" >> dump.sql'

This divides the input file into groups of twenty lines and runs mysqldump once for each group. You can probably increase that number safely and can apply a character cap using --max-chars. You can use xargs -a /dev/null --show-limits to see what the limits are for your system.

xargs -a ids.csv -d '\n' -n 1000 --max-chars=100000 sh -c 'mysqldump -u root -p db Table --where="id in ($@)" >> dump.sql' x

Edit:

Try this Bash script. Set num to any reasonable value.

#!/bin/bash
ids=$(< ids.csv)

saveIFS=$IFS
IFS=','
array=($ids)               # split into an array using commas as the delimiter
IFS=$saveIFS
array=(${array[@]/%/,})    # add commas back to each element

num=100                    # number of elements to process at a time

for ((i=0; i<${#array[@]}; i+=$num))
do
    list=${array[@]:$i:$num}
    # an excess trailing comma is stripped off in the next line
    mysqldump -u root -p db Table --where="id in ("${list%,}")" >> dump.sql
done

Need Your Help

Java Internationalization (i18n) Libraries/Frameworks

java java-ee internationalization

My organisation is about to embark on the long process of internationalizing (i16g?) its corporate website. The website is a mix of Java EE (JSP/Servlets, no EJB) and static content pushed from the

Get all the twitter account in sharepoint

sharepoint sharepoint-2013 jscript

In SharePoint 2013 we have a about me for all the employees that work at our company.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.