1

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.

2 Answers 2

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

I have also suggested piping mysqldump to mysql client before

2
  • Can you explain how innodb_fast_shutdown might help speedup the sql restore process?
    – user132787
    Aug 17, 2017 at 14:13
  • This gives InnoDB a clean slate on the target server in terms of having no transactions to recover when mysqld is restarted. that way, the crash recovery phase of mysqld start up is short. Aug 17, 2017 at 14:19
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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