Parallel load a mysqldump

pload.sh

#!/usr/bin/env bash

[[ $# -ne 6 ]] && echo "$(basename $0) <dumpfile> <# of splits/chunks. max 20> <# of mysql clients> <db user> <db password> <db schema>" && exit 1
user=$4
password=$5
schema=$6

#add multiple hosts if available
declare -a hosts=('127.0.0.1' '127.0.0.1')
MYSQL_CMD=mysql

dumpfile=$1
splits=$2
clients=$3

declare -a splitfiles=('xaa'  'xab'  'xac'  'xad'  'xae'  'xaf'  'xag'  'xah'  'xai'  'xaj'  'xak'  'xal' 'xam'  'xan'  'xao'  'xap'  'xaq'  'xar'  'xas'  'xat')

# split file
if [[ -e $dumpfile ]]; then
    mkdir -p staging && cd staging
    echo "splitting $dumpfile into $splits chunks"
    split --verbose --number l/$splits ../$dumpfile
else 
    echo "cannot open $dumpfile"
    exit 1
fi

[[ $clients -gt $splits  ]] && clients=$splits
i=1
for ((i=0;i<splits;i++)); do
    j=$((i%${#hosts[@]}))
    echo "cat ${splitfiles[$i]} | $MYSQL_CMD -f -u${user} -h${hosts[$j]} ${schema}" 
    cat ${splitfiles[$i]} | $MYSQL_CMD -f -u${user} -h${hosts[$j]} ${schema} &
    while [ $(jobs -r | wc -l) -ge $clients  ]; do sleep 1; done
done
echo "done..."

Ex

$ time ./pload.sh cmon-data.sql 16 4 root "" cmon
splitting cmon-data.sql into 16 chunks
creating file `xaa'
creating file `xab'
creating file `xac'
creating file `xad'
creating file `xae'
creating file `xaf'
creating file `xag'
creating file `xah'
creating file `xai'
creating file `xaj'
creating file `xak'
creating file `xal'
creating file `xam'
creating file `xan'
creating file `xao'
creating file `xap'
cat xaa | mysql -f -uroot -h127.0.0.1 cmon
cat xab | mysql -f -uroot -h127.0.0.1 cmon
cat xac | mysql -f -uroot -h127.0.0.1 cmon
cat xad | mysql -f -uroot -h127.0.0.1 cmon
cat xae | mysql -f -uroot -h127.0.0.1 cmon
cat xaf | mysql -f -uroot -h127.0.0.1 cmon
cat xag | mysql -f -uroot -h127.0.0.1 cmon
cat xah | mysql -f -uroot -h127.0.0.1 cmon
cat xai | mysql -f -uroot -h127.0.0.1 cmon
cat xaj | mysql -f -uroot -h127.0.0.1 cmon
cat xak | mysql -f -uroot -h127.0.0.1 cmon
cat xal | mysql -f -uroot -h127.0.0.1 cmon
cat xam | mysql -f -uroot -h127.0.0.1 cmon
cat xan | mysql -f -uroot -h127.0.0.1 cmon
cat xao | mysql -f -uroot -h127.0.0.1 cmon
cat xap | mysql -f -uroot -h127.0.0.1 cmon
done...

real    2m44.153s
user    0m4.708s
sys     0m0.812s

Tags: 

Add new comment

Plain text

  • No HTML tags allowed.
  • Quick Tips:
    • Two or more spaces at a line's end = Line break
    • Double returns = Paragraph
    • *Single asterisks* or _single underscores_ = Emphasis
    • **Double** or __double__ = Strong
    • This is [a link](http://the.link.example.com "The optional title text")
    For complete details on the Markdown syntax, see the Markdown documentation and Markdown Extra documentation for tables, footnotes, and more.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.