Using mydumper to parallel dump/import from/to MySQL

18 Replies

I originally attempted to use mk-parallel-[dump/restore] but I kept running into issues when restoring the data. mydumper (written by Domas Mituzas) has been tested to be faster so I thought I would give it a try.

The dump command is straight forward:

mydumper --database=mydb --rows=100000 --threads=16

I only wanted to dump a certain DB. Also, I found that chunking insert statements made imports faster (I choose 100,000 rows). Last but not least, the number of threads to dump with (generally number of cores your machine has). This will create an export directory with all the SQL files in your current working directory. I didn’t use compression because my database is relatively small so space was not an issue and I did not want the overhead (albeit small amount) of (de)compression.

Since mydumper only dumps table data, you will also need to dump the table structure.

mysqldump --no-data mydb > mydb_table_structure.sql

Parallel restore is a little bit more “complex”. As the mydumper export is not compatible with mk-parallel-restore, you have to do a little pipe magic. This is a slightly modified one-liner from Domas Mituzas found here. His assumes you used compression so no zcat in mine. Be sure you’ve restored the table structure first using the sql file from mysqldump.

cd /var/data/backups/export-20100717-024935
echo *.sql | xargs -n1 -P 16 -I % sh -c 'mysql -uimporter -pasdf -h10.0.1.10 mydb < %'

I ran this from the same machine I was exporting from (which was a different machine than I was importing to). This little gem, is made possible by xargs’ ability to thread; ‘-P 16′ means 16 threads. If you dump/import on the same machine, you probably won’t need the user/password/host parameters in the mysql command.

Be Sociable, Share!

  • Pingback: How to keep a passive-master MySQL database warm « Dctr Watson()

  • Thomasribu

    Hello,

    I have used the mydumper to create the backup of all the tables which is now in the .sql format. However I am not aware of having it restored using the same. Please let me know this details.

    TY

    BR,
    TIm

  • https://www.dctrwatson.com Doctor Watson

    You are correct, mydumper only works for creating a backup.

    The sql files it creates have to be restored using something else, in my example I fed 'mysql' each of the files using that one-liner.

    Also, I realize now I forgot a step in my post; mydumper dumps the data but not the structure of the tables. I shall update the post with how to also dump the table structure.

  • Thomasribu

    Hello,

    When trying to restore using the one-liner mysql command it comes up with the error that the table is missing and requires to be created

    mysql testdb < testdb.demo123.sql
    ERROR 1146 (42S02) at line 3: Table 'testdb.demo123' doesn't exist

    BR,
    Tim

  • https://www.dctrwatson.com Doctor Watson

    Hi,

    mydumper only dumps the data of the tables. The table structure needs to be dumped separately using: “mysqldump –no-data testdb > testdb_table_structure.sql” and imported before the mydumper dumps.

    I apologize for not noting this issue in my original post.

    John

    • thomasribu

      Hello Dr.Watson,

      Thank you for the response on the above. However now I have already created or made backup of db tables using just the mydumper command. Will it be possible to create them and restore the individual mysql databases for this. Please explain so that I can learn more on this tool. Thank you

  • http://ole.tange.dk/ Ole Tange

    Your use of xargs is dangerous. To see why read: http://en.wikipedia.org/wiki/Xargs#The_separato… (You could have an .sql file with a special char in its name)

    Consider using GNU Parallel http://www.gnu.org/software/parallel/ instead. Watch the intro video to GNU Parallel at http://www.youtube.com/watch?v=OpaiGYxkSuQ

    On some systems the CPU is so much faster than writing to the disk, that it is faster if you gzip on the fly instead of writing uncompressed to the disk. On other systems the CPU is slower, on these systems it may be faster to use lzop for compressing.

  • Pingback: Google NexusOne phone Guangzhou are stir | Droid Reviews()

  • Thomasribu

    Hello ,

    But what if the case is when you have not taken the table dumps in advance and gone into directly backing up using mydumper. In this case , how is it possible that we can restore the entire database for this.

  • https://www.dctrwatson.com Doctor Watson

    Oh very interesting. I'll have to try compiling on FreeBSD and trying it out.
    Thank you for sharing it and the heads up on the dangers of xargs.

  • https://www.dctrwatson.com Doctor Watson

    Unfortunately, if that has happened, I'm afraid all you can do is rebuild tables by hand.

    Unless you have any other backup of schema? Like a full backup of the database files (.FRM,.IBD, etc) or an old export?

  • Pingback: Private Krankenversicherung()

  • Pingback: Swingerportal()

  • Phil

    for me, the restore didn’t work the expected way with echo but with ls:

    ls *.sql | xargs -n1 -P 16 -I % sh -c ‘mysql -uimporter -pasdf -h10.0.1.10 mydb < %'

  • Andrew Hutchings

    I have now added a parallel loader called ‘myloader’ to the mydumper source since 0.2.1 (0.2.2 was released today).  I hope you find it useful.

  • arun
  • Jagatveer Singh

    Hello Everyone!!

    To me the magical thing about MyDumper is only ‘–rows’ tag as this is what actually speeds up the process visibly. However, the MyDumper does create the schema files for every table in the database too.

    For example: if you generate a backup using MyDumper, it will create a directory like “export-20130705-XXXXX” in your PWD which will contain the individual “DB.TABLE_NAME.sql” file for every table’s data and the other file which looks like “DB.TABLE_NAME-schema.sql” contains the schema of that table.

    Restoring the database backup on the same database is again a pain and does not work. So the best practice would be to create a new database and restore the schema and then backup.

    Now as the schema is already present, I created a shell script which you can use to restore the data. Simply, copy the script in the same directory which contains the backup files (*.sql), make sure the script is executable, enter the DB variables and execute. It will first create the entire database schema and then import the data from the individual ‘.sql’ files:

    —————————————————————–
    ######################################
    ## MyDUMPER Restore Script ####
    ## Author: Jagatveer Singh ####
    ## Blog: http://techblog.eieio.in ####
    ######################################
    #!/bin/bash
    USER=”…”
    PASSWD=”…”
    DB=”…”

    # Enter the exact path to backup folder here
    DBBACKUP=”…”
    TMP=”/tmp/bakschema/”
    mkdir $TMP
    SCHEMA=$(ls *-schema.sql)
    for i in $SCHEMA;
    do
    mysql -u $USER -p$PASSWD $DB < $DBBACKUP/"$i"
    echo "$i Complete!!"
    mv $i $TMP
    done
    DBLIST=$(ls *.sql)
    echo "Schema Restore Complete!!"
    echo ""
    echo "Database Restore begins!!"
    for rstr in $DBLIST;
    do
    mysql -u $USER -p$PASSWD $DB < $DBBACKUP/"$rstr"
    echo "$rstr Table Restore Complete!!"
    done
    echo "Database Restored!!"
    exit 0
    —————————————————————–

    Note: MyDumper may help in speeding up the backup process, but it is not going to make restoration any faster.

    Hope it helps ;)

  • Pingback: Unsorted / General | Annotary()