Thesqldump file restore while sql is still being dumped

datafileMySQLmysqldumprestore

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.

Best Answer

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

Related Solutions

MySQL – Why DROP DATABASE Takes So Long

Rather than killing the process, it would be safer if you did it within MySQL:

$ mysqladmin processlist -u root -p
Enter password: 
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| Id  | User | Host      | db                | Command | Time | State | Info             |
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| 174 | root | localhost | example           | Sleep   | 297  |       |                  |
| 407 | root | localhost |                   | Query   | 0    |       | show processlist |
+-----+------+-----------+-------------------+---------+------+-------+------------------+

The query with id 174 is the one blocking deletion of the 'example' database, so before you kill any processes first let MySQL try to terminate the query:

$ mysqladmin kill 174

Run the processlist command above again to confirm that it was killed.

If this doesn't work, then you could perhaps look at killing the errant process, but before that you might try restarting the MySQL server.

You can also run commands like 'SHOW FULL PROCESSLIST' and 'KILL 174' in the MySQL shell, for example if you only have the MySQL client installed. The main point is to avoid killing the process using 'kill' in the shell unless absolutely necessary.

Generally speaking you can use either mysql or mysqladmin. You shouldn't need to be running commands like this that often though; once you start killing queries regularly something is definitely wrong and you'd be better off fixing that problem (killing the query process is just treating the symptom).

Sql-server – SQL Server 2008 R2 Restore constantly hanging

I think I have found the issue.

The backups are stored our data domain, so when performing the restore we select the backups from this location. I think there is some type of connection issue between this server and the data domain. Because when I move the backup files over to the server and restore from the server directly, I cannot reproduce the issue and the restores are probably 10x faster.I set a continuous restore of a database (11 GB) that has been running for about 25 minutes and has completed the restore successfully 23 times with no hanging

My thinking is that the connection issue probably lies somewhere with our test server still. Because we use the Data Domain for all of our production backups (although it is changing) and have no problems restoring elsewhere, including the databases I have been working with. Not to mention, we have automated nightly restores on other servers that have never failed.

I think this at least pinpoints the issue. I will look into where the connection issue is and work with the server\networking guys to see if we can resolve it.

Thanks you guys for your quick assistance