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

I开发者_如何学C 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.

---

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

---

ids.csv => 91916, 91859, 91861, 91894, 92095, 92166, 91796 ...

#!/bin/bash
ids=$(< offer_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
    echo "list: " $list
    mysqldump -uroot -h host -p pw --opt --where="offer_id IN ("${list%,}")" db offer_images >> offer_images.sql 
done

bash output:

offer_id IN (91916, 91859, 91861, 91894, 92095)
mysqldump: Got error: 1049: Unknown database '91859,' when selecting the database