mysqldump file restore while sql is still being dumped

Question

Im using MaridaDB 10, but my question applies to all mysql variants. I have large dataset (70GB uncomressed when dumped to file).

The mysqldump process takes 2-3hours. The restore process takes even longer 10-14 hours.

I have a nfs share I use for syncing data between many servers. My question is specifically.. If I have mysqldump process running and outputting data to /nfs/file.sql and is at say ~35gb (50% complete). Can I start a restore process (mysql < /nfs/file.sql) on a different server and have it work as expected? (finish through the end of file when its complete).

Im not worried about restore process getting ahead of mysqldump. With even a 20% buffer, its unlikely.

Answer 1

You could do things to speed up this whole process

STEP 1 : Disable the double write buffer / increase log buffer size

Go to the target server. Login o mysql as root and run

mysql> SET GLOBAL innodb_fast_shutdown=0;

then restart mysqld like this

service mysql restart --innodb-doublewrite=OFF --innodb-log_buffer-size=268435456

This disables the double write buffer and make the log buffer 256M

Running SET GLOBAL innodb_fast_shutdown=0; beforehand does a complete flush of InnoDB.

STEP 2 : Pipe mysqldump from source server to target server

mysqldump ... | mysql ...

This will not save the dump to a file. This send the commands straight to the target server

STEP 3 : Restart mysqld on the target server (restore original options)

service mysql restart

SUGGESTED READING

I have suggested disabling the double write buffer before

• May 03, 2014 : Optimizing a large number of INSERT ... SELECT statements
• Dec 19, 2014 : When is it safe to disable InnoDB doublewrite buffering?
• Sep 29, 2016 : MySQL settings useful to speed up a mysqldump import

I have also suggested piping mysqldump to mysql client before

• Jan 13, 2012 : MySQL - MySqlDump uses \n instead of null
• Mar 02, 2012 : How to log verbose output from mysqldump?
• Nov 11, 2016 : Large MYSQL DB on single file
• Nov 23, 2016 : Moving large databases

Answer 2

Check for redundant indexes and get rid of them. This will help the reload.

Instead separating the dump and the reload, pipe the first into the second. This will mask the 2-3 hours, plus eliminate some amount of I/O.