Best option to back up mysql innodb partition

Question

We have a number of tables which we plan to partition based on months. The issue now before we can drop those partition we would like to perform backup. One option we found is this link http://www.percona.com/doc/percona-xtrabackup/howtos/recipes_ibkx_partition.html. Can any one suggest is the best mechanism or there other option such as mysqldump etc?

Answer 1

If you need to get backups of tables individualized, you may want to consider performing a mysqldump on each table into separate files.

Start by creating a list of tables

MYSQL_USER=username
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQLSTMT="SELECT CONCAT(table_schema,'.',table_name)"
SQLSTMT="${SQLSTMT} FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE table_schema NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','mysql')"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > ListOfTables.txt

If you have a moderate number of tables, you could dumps them all at the same time:

MYSQL_USER=username
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--hex-blob --triggers"
do
    DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} ${TB} | gzip > ${DB}_${TB}.sql.gz &
done
wait

If you have too many tables to dump in parallel, then dump all tables in 10 at a time

MYSQL_USER=username
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
COMMIT_COUNT=0
COMMIT_LIMIT=10
MYSQLDUMP_OPTIONS="--hex-blob --triggers"
for DBTB in `cat ListOfTables.txt`
do
    DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} ${TB} | gzip > ${DB}_${TB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

If you can load a massage the mysqldump as needed.

Here is another alternative: Do a mysqldump of data only and not the schema for each table you will be partitioning. In the above code just dump the data by adding --no-create-info to the mysqldump options

MYSQLDUMP_OPTIONS="--hex-blob --triggers --no-create-info"

With the data only dumped to a separate file, you can focus on creating the InnoDB partitioned to your liking and then loading the mysqldump of the data in it.

UPDATE 2013-01-16 08:24 EDT

There are two things you could try

OPTION #1

If you prefer dumping one table at a time, you will have to dump everything to one file but do so as a transaction

MYSQL_USER=username
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --all-databases | gzip > MySQLData.sql.gz 

OPTION #2

You could try parallel mysqldumps each marked as a transaction

MYSQL_USER=username
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--hex-blob --triggers --single-transaction"
do
    DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} ${TB} | gzip > ${DB}_${TB}.sql.gz &
done
wait

Hope this helps !!!

Answer 2

The Percona xtrabackup solution that you linked in your question is a good solution. It is a hot backup and doesn't lock innodb tables. I would prefer that to mysqldump in most cases.

Did you try xtrabackup? Have questions about using it?