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 30 GB database.we want to take backup of this database.

here the question is we want to take the backup batch of every 10k records.

How we can take batch of records backup using mysqldump

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Here is something you may find intriguing

SUGGESTION #1 : Let mysqldump create the batches

mysqldump already uses an option called --extended-insert. This will group a series of records that can be loaded in a single INSERT command. While you cannot use mysqldump to control the number of rows to be batched together, you can parse a mysqldump. Of course, these means you will have to script it in the language of your choice (Python, Perl, C++, etc).

For any given table in a mysqldump of a single database, you will see the following paradigm:

CREATE TABLE
LOCK TABLE
INSERT INTO ...
INSERT INTO ...
INSERT INTO ...
...
...
INSERT INTO ...
INSERT INTO ...
UNLOCK TABLES;

You could parse the mysqldump as follows:

  • Step01 : Get the next table name in the mysqldump
  • Step02 : Locate next INSERT INTO or UNLOCK TABLES;
  • Step03 : If you encounter UNLOCK TABLES;, goto Step01
  • Step04 : If you have no more tables, you are done
  • Step05 : Goto Step02

Each INSERT command you read has a certain number of rows. This number you cannot control unless you script to put them together in the number of rows you choose. I would advise against that be mysqld decided the best number of rows for that particular batch given the current configuration settings (perhaps the max_allowed_packet)

SUGGESTION #2 : Use --skip-extended-insert and batch them your way

When you deactivate --extended-insert, each INSERT command will have one row. You can collect the rows using a script and collect as many rows as you like.

CAVEATS

  1. Please be a little conservative on the number of rows you choose to batches together.
  2. You will take much longer to parse the mysqldump because of the mysqldump will be much longer.
share|improve this answer
    
I made minor corrections and added CAVEATs to SUGGESTION #2. My answer is still the same. –  RolandoMySQLDBA Jun 21 '13 at 15:45
add 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.