Categories

Bash Script for Partitial Data Dump to MySQL

I was required to create a “light version” of our database by having most of the tables empty and some tables having only first x entries. Those entries were specified in a csv file as around 5000 id values. Working on this involved using mysqldump iteratively in a loop. If I did not use mysqldump commands in a loop with fewer id values as arguments, mysqldump threw “Argument list too long” error.
Considering an advice from Dennis Williamson, I ended up with a script like this:

dump_filename=x`date +"%Y%m$d"`.sql
mysqldump -u root -pmysql --no-data curocomp | sed 's/AUTO_INCREMENT=[0-9]*\b//' > `echo $dump_filename`
ids=$(< ids.csv) saveIFS=$IFS IFS=',' array=($ids) # split into an array using commas as the delimiter IFS=$saveIFS count=0 for i in ${array[@]}; do array[$count]=$i","; ((count++));done # add commas back to each element num=1000 # number of elements to process at a time max=2000 for ((i=0; i<$max; i+=$num)) do list=${array[@]:$i:$num} # an excess trailing comma is stripped off in the next line mysqldump -u root -pmysql myDB myTable --skip-add-drop-table --where="Id in ('${list%,}')" >> `echo $dump_filename`
done

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>