Is there a Successor or an Alternative to mk-parallel-dump?

Question

Just read about mk-parallel-dump in a possibly-outdated version of High Performance MySQL. Sounded great, but when I went to download it I found out that it's deprecated with no equivalent command in the Percona toolkit.

Are there other tools that do something similar-- ie fast database backup?

Answer 1

Back on April 17, 2011, I answer the post How can I optimize a mysqldump of a large database? and suggested scripting techniques for parallel mysqldumps.

DBLIST=/tmp/ListOfDatabases.txt
MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect Database Names
#
SQL="SELECT schema_name FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLIST}
#
# Dump All The Databases
#
BACKUP_FOLDER=/path/to/mysql/backups
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
for DB in `cat ${DBLIST}`
do
    GZIPFILE=${BACKUP_FOLDER}/${DB}.sql.gz
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} | gzip > ${GZIPFILE} &
done
wait

If you have too many databases, dump them 5 at a time

DBLIST=/tmp/ListOfDatabases.txt
MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect Database Names
#
SQL="SELECT schema_name FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLIST}
#
# Dump All The Databases
# Set COMMIT_LIMIT to determine how many to parallel dump at a time
#
BACKUP_FOLDER=/path/to/mysql/backups
COMMIT_COUNT=0
COMMIT_LIMIT=5
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
for DB in `cat ${DBLIST}`
do
    GZIPFILE=${BACKUP_FOLDER}/${DB}.sql.gz
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} | gzip > ${GZIPFILE} &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_LIMIT} -eq ${COMMIT_COUNT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ] ; then wait ; fi

Give it a Try !!!

Answer 2

A bit late to the party but as a former mk-parallel-dump user I came across the same question recently. I tried using the parallelism feature of MySQL mysqlimport however that only works for different tables even if you break up the dump files into smaller chunks. Shame.

However I then discovered MySQL Data Dumper which is apparently being developed/maintained by Percona people.

MySQL Data Dumper project page

There are source tar balls for the various versions downloadable here:

Source downloads

The latest release supports dumping of AWS RDS instances too. A typical invocation might be like:

./mydumper --user=root -o dump --rows=100000 -B database_name
./myloader --user=root -d dump -B database_name

You can also chunk by statement size, or number of output bytes. By default dumping and loading use four threads, but you can set this. There are a number of options available to control locking strategies and limiting which tables are dumped (the examples above do all the tables). For dumping across a slower network connection it supports data compression.

So far my experiments have been good although as ever I think you have to be careful to optimize MySQL for heavy parallel write access to get the best import time paying special attention to things like InnoDb log file and buffer sizes and concurrency settings.