Agnus Dei (jackal) wrote,
Agnus Dei
jackal

How to move a large mysql database using rsync (optimized for speed)

So I did something pretty cool Monday night (last night).

I have this database that's let say over a TB big. And let's say it normally takes 4-5 hours to copy it from one server to another using rsync.

So I came up with a way to run rsync(s) in parallel and maximize the throughput. It copied the whole thing in 35 minutes. So what used to take 4-5 hours to copy finished in 35 minutes using my new technique.


Step 1) . First command is for everything outside of the "big database" that you want to sync.  In this case I have only 5 running in parallel.  This should finish pretty quickly.

ls -1 /opt/mysql/ | egrep -v "^mybigdatabase$" | xargs -I {} -P 5 -n 1 rsync -rav --progress --inplace --no-whole-file /opt/mysql/{} myhostname.com:/opt/mysql/

Step 2) Now time to move the big database. The second command is for all the tables inside the big database you want to sync.  In this case I have 30 running in parallel (my host has 32 cores):

ls -1 /opt/mysql/mybigdatabase/ | xargs -I {} -P 30 -n 1 rsync -rav --progress --inplace --no-whole-file /opt/mysql/mybigdatabase/{} myhostname.com:/opt/mysql/mybigdatabase/


Subscribe
  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

  • 0 comments