0

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.

2
  • Im a postgres person, but why can't you set up madter-slave replication prior to window, then stop master and promote slave?
    – davidgo
    Apr 30, 2020 at 10:56
  • @davidgo I tried MariaDB/MySQL replication and was not impressed. After copying the initial DB dump to the slave you have to manually tell the slave DB from which transaction ID it hast to start replicating. But what was more worrying was that during test replication runs, and comparing the DB's for equality, they were not 100% the same. I'm talking user data of course. So we didn't trust this and stopped using replication completely.
    – Matthias
    Apr 30, 2020 at 11:01

3 Answers 3

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.

4
  • Thank you for this valuable input. Do you know whether rsync --append a) is safe to us with xz compressed files b) would sync faster
    – Matthias
    Apr 30, 2020 at 12:14
  • I think rsync --append is safe, but why risk it? Are you that short of local disk I/O / page cache? Apr 30, 2020 at 12:24
  • I am concerned about elapsed time. With your example I would gain the time used by step 2 - the network transfer. But to be sure I would need to wait until on the old server the DB dump has finished, rsync has updated the dump file, and only then I could start restoring. In the full command pipe for smaller DB's of my original post the DB was already restoring on the new server while on the old server the DB dump was still generating data.
    – Matthias
    Apr 30, 2020 at 12:34
  • I seem to remember from a long time ago there was a tool written in PHP called "relentlesspipe", that would fit your requirements, but Google is failing me at the moment and I can't find it anywhere... Apr 30, 2020 at 12:58
0

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.

3
  • That was not an option, only 1 of several DB's had to be transferred. Further, on the old server MariaDB 10.1.44 was running while on the new one it is 10.3.22. Possibly not a problem, but to err on the save side doing a dump + restore was the preferred way.
    – Matthias
    May 6, 2020 at 9:46
  • You can always transfer the entire then drop the schemas that you don't need. The version shouldn't matter, because MariaDB storage engines are forwards-compatible.
    – womble
    May 6, 2020 at 9:48
  • No I couldn't transfer the entire set of data files, because on the new server there were already DB's running on that server.
    – Matthias
    May 6, 2020 at 9:54
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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