Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
How much InnoDB data do you have ??? –  RolandoMySQLDBA Jan 15 '13 at 13:35
    
So far just few million of lines for 2 of the tables the rest are below million. –  newbie14 Jan 16 '13 at 1:29
    
How many GB of data ??? –  RolandoMySQLDBA Jan 16 '13 at 13:16
    
As per now the data size is just 500Mb but it will sure to go few GB. I am preparing for future when the size grow and how to handle it. So some of the table I anticipate to be few million therefore I am going to partition them by month is that fine or you suggest some other mechanism? –  newbie14 Jan 16 '13 at 13:30
add comment

2 Answers

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 !!!

share|improve this answer
    
my first worry I read some places it says that mysqldump can cause locking if run on live systems? I just have moderate number of tables to be dump I guess not more then 15 tables. I prefer to do one by one to avoid locking or any other problem. Just before the delete of partition if just want to view the data in that partition is that possible? –  newbie14 Jan 16 '13 at 12:46
    
You could perform a single mysqldump as a full transaction so all the InnoDB tables are from the same point-in-time. I'll update my answer... –  RolandoMySQLDBA Jan 16 '13 at 13:13
    
so if I am going to partition my table I would prefer to dump only those old partition and leave the existing ones in the system how should I tweak the script given by you. –  newbie14 Jan 16 '13 at 13:32
    
any help on the partition backup and deletion only? –  newbie14 Jan 18 '13 at 1:02
add comment

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?

share|improve this answer
    
yes I am now confused between the percona tool and mysqldump I do not know how to make the decision now? So in order to try xtrabackup I need to install the pt toolkit and what are the other tools needed? –  newbie14 Jan 20 '13 at 23:02
    
Here's the installation page, with options for different operating systems, etc. percona.com/doc/percona-xtrabackup/installation.html If you tell me what OS version you are using, I'll give you the direct link. –  Valerie Parham-Thompson Jan 21 '13 at 2:54
    
I am using Centos 6.3. So what else must I be prepared with ? Can I just download the partition easily and recover them thereafter? Reading further this tool can also do a backup is it? But I read some where that it only run only percona mysql is that true? –  newbie14 Jan 21 '13 at 6:42
    
should I provide anymore extra information this? –  newbie14 Jan 22 '13 at 16:46
    
Hi. Download from here: percona.com/downloads/XtraBackup/LATEST/RPM/rhel6/x86_64 It will run on Oracle MySQL/InnoDB as well, not just Percona/XtraDB. –  Valerie Parham-Thompson Jan 28 '13 at 19:27
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.