Running the sync script renders our server unresponsive

plugins
1

Hi there,

I have set up the Sync Script for our project and wanted to copy the production DB over to our staging.

It did run successfully before but this time around I noticed that the production server gets completely unresponsive when synching…

That’s probably also due to the fact that we’re handling a rather big DB but ideally that shouldn’t be an issue.

So I was wondering if anyone has encountered something similar before and how you tackled it?

I could re-work the whole script to not use the STDOUT-export mechanism which (if I understand correctly) imports the remote (= prodcution) database locally and then re-uploads to my other remote (= staging) database.

That though stresses the server so much that nothing else gets through, I cannot access the backend or perform any task like adding a product to the shopping cart whilst the sync is in progress.

We tested with different machines to rule out any isolated local aspects and could confirm that the server is not responsive regardless of whom is trying to access it…

What are the main reasons for using this “on the fly via local”-approach vs. exporting to a file, rsync-ing between remote servers and then importing over at the target server?

Thanks & regards
Henning

P.S.: Pinging @ben as you created the script and maybe have some deeper insight here…?

2

Howdy! Is this an issue with how the script uses standard output, or is this an issue with running wp db export on that server?

Can you try passing some of the flags mentioned here? Speed up wp db export using WP-CLI · GitHub

It’s just a cleaner approach from a code perspective, but if it’s causing these issues then I would just switch to using a file

3

Thanks for getting back and offering your help!

I’ve checked the additional flags and that is very handy indeed – in our case though that didn’t do the trick unfortunately…

I tweaked and enhanced your script to export to a file instead and rsync the DB. Just for reference if anyone else might need the same…

New FROMCURRENT and TOCURRENT vars for placing the files as well as a TIMESTAMP for the exports:

TIMESTAMP=$(date +%s)
[[ $FROMDIR =~ ^(.*): ]] && FROMHOST=${BASH_REMATCH[1]}
[[ $FROMDIR =~ ^.*:((/[^/]+/[^/]+/[^/]+/)).*$ ]] && FROMCURRENT="${BASH_REMATCH[1]}current"
[[ $TODIR =~ ^(.*): ]] && TOHOST=${BASH_REMATCH[1]}
[[ $TODIR =~ ^.*:((/[^/]+/[^/]+/[^/]+/)).*$ ]] && TOCURRENT="${BASH_REMATCH[1]}current"

File export & rsync steps:

wp "@$FROM" db export --default-character-set=utf8mb4 --all-tablespaces --single-transaction --quick --lock-tables=false export-$TIMESTAMP.sql
RSYNCDB=$(ssh -o ForwardAgent=yes $FROMHOST "rsync -aze 'ssh -o StrictHostKeyChecking=no' --progress $FROMCURRENT/export-$TIMESTAMP.sql $TOHOST:$TOCURRENT")
if [[ $RSYNCDB == *"error"* ]]; then
  echo "❌  rsync DB from $FROMSITE to $TOSITE failed: $RSYNCDB"
  exit 1
else
  echo "✅  rsync DB from $FROMSITE to $TOSITE"
fi

Remove export files after sync completes:

# Delete remote DB export files
DELETEDBFROM=$(ssh $FROMHOST "rm $FROMCURRENT/export-$TIMESTAMP.sql")
DELETEDBTO=$(ssh $TOHOST "rm $TOCURRENT/export-$TIMESTAMP.sql")
2 Likes
4

This topic was automatically closed after 42 days. New replies are no longer allowed.