Transfer a bigger MySQL/MariaDB DB fast, save and restartable

Question

I have to transfer a Drupal site with a 43GB MariaDB DB via ssh/scp to another server.

I have a limited downtime window early in the morning. In the past I transferred smaller DB's (<3GB) directly via pipes like this:

ssh -l webuser 10.0.0.99 "cd /var/www/drupal7/htdocs/site_name && drush sql-dump | xz -1 -" | xz -d - | drush sqlc

But with this bigger DB I am worried that some part of this pipe will fail and I have to restart everything again which almost certainly would mean I will exceed the downtime window.

So the obvious solution would be to split it up in single, independent steps so I could redo parts of the task in case one of them fails:

ssh -l webuser 10.0.0.99 "cd /var/www/drupal7/htdocs/site_name && drush sql-dump | xz -1 - > /home/webuser/db_dump.sql.xz"
scp webuser@10.0.0.99:/home/webuser/db_dump.sql.xz .
xzcat /home/webuser/db_dump.sql.xz | drush sqlc

But now the steps are sequential and take more time which means less time to redo a step in case something went wrong.

So I guess what I am looking for is a way to create the DB dump on the old server, and have a second, independent process transferring the data to the new server and start restoring the DB on the new server.

Simply using

scp webuser@10.0.0.99:/home/webuser/db_dump.sql.xz .

on the not yet finished DB dump will not work as scp will not wait until MariaDB finished writing the DB dump.

Does someone know a command, that continues transfering or outputing data until the DB dump is finished?

Or does someone know a better way to transfer the DB?

---

Update:

I figured I could use the tee command like this:

ssh -l webuser 10.0.0.99 "cd /var/www/drupal7/htdocs/site_name && drush sql-dump | xz -1 - | tee -a /home/webuser/db_dump.sql.xz" | xz -d - | drush sqlc

That would improve the situation, however, imagine the situation where the DB dump is still written to and something goes wrong during network transfer or restore. Then you'd have to restart with the dump file, but you can't just copy it via scp cause the MariaDB is still writing to it, so same problem as before.

Answer 1

It sounds like what you are trying to achieve would be best served with a few additional tools:

screen - to make the process execute even if the ssh session disconnects pxz - to speed up the compression by using all the available cores rsync - to make the transfers resumable

The three steps you described would become:

ssh -l webuser 10.0.0.99 'screen -d -m "cd /var/www/drupal7/htdocs/site_name && drush sql-dump | nice -n19 pxz -1 > /home/webuser/db_dump.sql.xz"'
rsync -a webuser@10.0.0.99:/home/webuser/db_dump.sql.xz db_dump.sql.xz
xzcat /home/webuser/db_dump.sql.xz | drush sqlc

The dump process will run in a detached screen session, so that if the ssh session drops, it will still carry on until completion.

rsync can be run repeately while drush sql-dump is running, and it will incrementally transfer the extra part of the file that appeared since the last rsync.

You will need to somehow flag up completion, so that you don't do the last step until the dump and rsync have completed.

I hope this points you in the appropriate direction for achieving what you are trying to do.

Answer 2

The most straightforward way to sync a database is to use rsync on the data store itself and ignore all the tarballing, dumping, and so on. Take an initial rsync prior to the downtime window (if you like, you can run it multiple times; each time the "delta" of changes should reduce because the time between runs reduces), and then when the downtime begins, stop the database server, do a final rsync run to the new machine, fire up the database server on the new machine, and you're done.

Answer 3

This is a step by step guide how I solved it:

1.) Old server: I created a FIFO (named pipe) like this

mkfifo /home/webuser/db_dump.fifo

2.) New server: I connected to the FIFO on the old server via SSH and used a pipe for restoring

cd /var/www/drupal7/htdocs/site_name
nohup ssh -l webuser 10.0.0.99 "cat < /home/webuser/db_dump.fifo" | xz -d - | drush sqlc &

Note that I used nohup so that in case I loose connection to the new server the transfer and restore continues. Note also that at this point the command just waits because no data has been fed into the FIFO yet.

3.) Old server:

cd /var/www/drupal7/htdocs/site_name
nohup drush sql-dump | xz -1 -" | tee -a /home/webuser/site_name.sql.xz > /home/webuser/db_dump.fifo &

Again nohup is used so the database dumping continues even if I loose my ssh connection to the old server. Once you execute this command, the transfer and restore operation on the new server automatically starts.

4.) New server: For the case that the network connection between old and new server was interrupted or something went wrong during restore on the new server I ran a 2nd script in parallel to transfer the DB dump to the new server so once transferred I could manually do a restore again.

Here's the script:

#!/bin/bash

while [ ! -f ./stop ]; do
  printf "\n\nsyncing\n"

  rsync --append --compress-level=9 -e ssh webuser@10.0.0.99:/home/webuser/site_name.sql.xz .

  sleep 60

done

printf "\n\nsync and check\n"

rsync --append-verify --compress-level=9 -e ssh  webuser@10.0.0.99:/home/webuser/site_name.sql.xz .

Once the DB dumping operation on the old server was finished I did a touch stop to halt the script. Note that I use rsync --append inside the loop and rsync --append-verify at the end. Because --apend-verify does checksums over the file to transfer I figured it would cause additional IO load and I wanted to keep that to a minimum during dump and transfer time.

In summary, with this method when the transfer + restore via FIFO fails you would loose only the time for restoring the DB because the DB dump file is transferred in parallel.