#!/bin/bash #################################################################################################### #### author: SlickStack ############################################################################ #### link: https://slickstack.io ################################################################### #### mirror: https://mirrors.slickstack.io/bash/ss-sync-staging.txt ################################ #### path: /var/www/ss-sync-staging ################################################################ #### destination: n/a (not a boilerplate) ########################################################## #### purpose: Syncs relevant files and database from production site to the staging site ########### #### module version: MySQL 8.0.x + WordPress 6.2.x ################################################# #### sourced by: n/a ############################################################################### #### bash aliases: ss sync staging ################################################################# #################################################################################################### ## SS-SYNC-STAGING DUPLICATES RELEVANT FILES FROM PRODUCTION TO STAGING (NOT UPLOADS) ## ## STAGING SITES WILL AUTOMAGICALLY LOAD ALL MEDIA UPLOADS FROM PRODUCTION ## #################################################################################################### #### TABLE OF CONTENTS (SS-Sync-Staging) ########################################################### #################################################################################################### ## this is a brief summary of the different code snippets you will find in this script ## ## each section should be commented so you understand what is being accomplished ## ## A. Source SS-Config + SS-Functions ## B. Touch Timestamp File ## C. Message (Begin Script) ## D. Dump Production Database ## E. Install Staging Database (Conditional) ## F. Import Production Database To Staging Database ## G. Copy Relevant Files From Production To Staging ## H. Run SS-Install-WordPress-Config (Conditional) ## I. Run SS-Install-WordPress-MU-Plugins (Conditional) ## J. Fix Any Hardcoded Links In Staging Theme Files ## K. Fix Incorrect URLs In Staging Database ## L. Reset Permissions (WordPress Core) #################################################################################################### #### A. SS-Sync-Staging: Source SS-Config + SS-Functions ########################################### #################################################################################################### ## before anything else we must source the critical variables that power this script ## ## ss-config is setup during ss-install wizard but ss-functions is hardcoded ## ## source ss-config ## source /var/www/ss-config ## source ss-functions ## source /var/www/ss-functions ## BELOW THIS RELIES ON SS-CONFIG AND SS-FUNCTIONS #################################################################################################### #### B. SS-Sync-Staging: Touch Timestamp File ###################################################### #################################################################################################### ## this is a dummy timestamp file that will remember the last time this script was run ## ## it can be useful for developer reference and is sometimes used by SlickStack ## ss_touch "${TIMESTAMP_SS_SYNC_STAGING}" #################################################################################################### #### C. SS-Sync-Staging: Message (Begin Script) #################################################### #################################################################################################### ## this is a simple message that announces to the shell the purpose of this bash script ## ## it will only be seen by sudo users who manually run this script in the shell ## ss_echo "${COLOR_INFO}Running ss-sync-staging... ${COLOR_RESET}" #################################################################################################### #### Query Settings #################################################################################################### ## EXPAND SS DASHBOARD USING MYSQL??? ## check if SlickStack dashboard staging.wp_options has paused this script before proceeding # select EmployeeName from DemoTable where isMarried=true; # ss_options_staging_sync_status=$(ss_mysql_root -BNe "USE staging; SELECT EmployeeName FROM DemoTable WHERE id=1") # $sql = "SELECT 'id' FROM Users WHERE username='$name'"; #################################################################################################### if [[ "${STAGING_SITE}" != "false" ]] && [[ "${SS_SYNC_STAGING}" != "false" ]] && [[ "${WP_MULTISITE}" != "true" ]]; then #################################################################################################### #### D. SS-Sync-Staging: Dump Production Database ################################################## #################################################################################################### ## we run ss-dump-database now to export the latest version of production database ## ## this ensures the dump is recent before attempting to sync it to staging ## source "${PATH_SS_DUMP_DATABASE}" #################################################################################################### #### E. SS-Sync-Staging: Install Staging Database (Conditional) #################################### #################################################################################################### ## FIX THIS ## IF staging database not found ## run ss-install-mysql-database ## # if [[ ]]; then # source "${PATH_SS_INSTALL_MYSQL_DATABASE}" # fi #################################################################################################### #### F. SS-Sync-Staging: Import Production Database To Staging Database ############################ #################################################################################################### ## this snippet duplicates the dumped production database and imports it into MySQL ## ## then it forcefully replaces the home/siteurl options to staging subdomain ## ## import database ## ss_cp /var/www/backups/mysql/production.sql /tmp/staging.sql chown mysql:mysql /tmp/staging.sql ## does not matter if -L flag used below chmod 0644 /tmp/staging.sql ## does not matter if -L flag used below ss_mysql_user staging < /tmp/staging.sql ## fix staging home and siteurl fields (no mass search/replace to avoid potential conflicts) ## if [[ "${SS_APP}" == "wordpress" ]]; then if [[ -n $(ss_mysql_user --execute "SHOW TABLES FROM staging WHERE Tables_in_staging LIKE '${DB_PREFIX}options';" 2> /dev/null) ]]; then ss_mysql_user --execute="UPDATE staging.${DB_PREFIX}options SET option_value='https://staging.${SITE_DOMAIN_EXCLUDING_WWW}' WHERE option_name='home'"; ss_mysql_user --execute="UPDATE staging.${DB_PREFIX}options SET option_value='https://staging.${SITE_DOMAIN_EXCLUDING_WWW}' WHERE option_name='siteurl'"; fi fi ## cleanup files ## ss_rm /tmp/staging.sql #################################################################################################### #### G. SS-Sync-Staging: Copy Relevant Files From Production To Staging ############################ #################################################################################################### ## here we copy over all the files from production to staging besides media uploads ## ## very large files e.g. backup archives are always skipped in this snippet ## ## copy wp-content files over (skips media uploads) ## ss_rsync --max-size=25mb --exclude 'blacklist.txt' --exclude 'object-cache.php' --exclude='*/mu-plugins/' --exclude='*/temp/' --exclude='*/upgrade/' --exclude='*/uploads/' /var/www/html/wp-content/ /var/www/html/staging/wp-content ## remove potential run-on subdirectories ## ss_rm /var/www/html/staging/staging* ss_rm /var/www/html/staging/wp-content/wp-content* ## remove object-cache.php if exists ## ss_rm /var/www/html/staging/wp-content/object-cache.php* #################################################################################################### #### H. SS-Sync-Staging: Run SS-Install-WordPress-Config (Conditional) ############################# #################################################################################################### ## below snippet will install wp-config.php in case does not exist already in staging ## ## this is a failsafe in case staging enabled in ss-config post-installation ## if [[ ! -f "/var/www/html/staging/wp-config.php" ]]; then source "${PATH_SS_INSTALL_WORDPRESS_CONFIG}" fi #################################################################################################### #### I. SS-Sync-Staging: Run SS-Install-WordPress-MU-Plugins (Conditional) ######################### #################################################################################################### ## below snippet will install MU plugins in case they do not exist already in staging ## ## this is a failsafe in case staging enabled in ss-config post-installation ## if [[ ! -f "/var/www/html/staging/wp-content/mu-plugins/xxx-common.php" ]]; then source "${PATH_SS_INSTALL_WORDPRESS_MU_PLUGINS}" fi #################################################################################################### #### J. SS-Sync-Staging: Fix Any Hardcoded Links In Staging Theme Files ############################ #################################################################################################### ## many themes (or custom-coded themes) often contain hardcoded HTML assets and links ## ## here we forcefully fix these links to ensure staging sites render properly ## find /var/www/html/staging/wp-content/themes/ -type f -exec ss_sed "s|https://${SITE_DOMAIN_INCLUDING_WWW}|https://staging.${SITE_DOMAIN_EXCLUDING_WWW}|g" {} + find /var/www/html/staging/wp-content/themes/ -type f -exec ss_sed "s|http://${SITE_DOMAIN_INCLUDING_WWW}|https://staging.${SITE_DOMAIN_EXCLUDING_WWW}|g" {} + find /var/www/html/staging/wp-content/themes/ -type f -exec ss_sed "s|https://${SITE_DOMAIN_EXCLUDING_WWW}|https://staging.${SITE_DOMAIN_EXCLUDING_WWW}|g" {} + find /var/www/html/staging/wp-content/themes/ -type f -exec ss_sed "s|http://${SITE_DOMAIN_EXCLUDING_WWW}|https://staging.${SITE_DOMAIN_EXCLUDING_WWW}|g" {} + #################################################################################################### ## end if staging site true ## fi #################################################################################################### #### K. SS-Sync-Staging: Fix Incorrect URLs In Staging Database #################################### #################################################################################################### ## needs more testing ## use WP-CLI to search/replace staging links in production database # wp search-replace https://"${SITE_DOMAIN}" https://staging."${SITE_DOMAIN_EXCLUDING_WWW}" --all-tables #################################################################################################### #### L. SS-Sync-Staging: Reset Permissions (WordPress Packages) #################################### #################################################################################################### source "${PATH_SS_PERMS_WORDPRESS_PACKAGES}" #################################################################################################### #### SlickStack: Reset Permissions (SlickStack Scripts) ############################################ #################################################################################################### ## we include this permissions reset in all cron jobs and bash scripts for redundancy ## ## chmod 0700 means only the root/sudo users can execute any SlickStack scripts ## ## THIS SNIPPET DOES NOT RELY ON SS-CONFIG OR SS-FUNCTIONS ## SNIPPET: ss bash scripts, ss cron jobs ## UPDATED: 02JUL2022 chown root:root /var/www/ss* ## must be root:root chown root:root /var/www/crons/*cron* ## must be root:root chown root:root /var/www/crons/custom/*cron* ## must be root:root chmod 0700 /var/www/ss* ## 0700 means only root/sudo can execute chmod 0700 /var/www/crons/*cron* ## 0700 means only root/sudo can execute chmod 0700 /var/www/crons/custom/*cron* ## 0700 means only root/sudo can execute #################################################################################################### #### SlickStack: External References Used To Improve This Script (Thanks, Interwebz) ############### #################################################################################################### ## Ref: https://community.centminmod.com/threads/wp-engine-type-production-staging-file-and-db-sync.19720/ ## Ref: https://softwareengineering.stackexchange.com/questions/117945/staging-environment-vs-production-environment ## Ref: https://www.thegeekstuff.com/2011/01/rsync-exclude-files-and-folders/ ## Ref: https://stackoverflow.com/questions/2466101/how-i-can-change-prefixes-in-all-tables-in-my-mysql-db ## Ref: https://stackoverflow.com/questions/55055130/mysql-copy-tables-with-specific-prefix-between-databases ## Ref: https://stackoverflow.com/questions/3280006/duplicating-a-mysql-table-indices-and-data ## Ref: https://tableplus.com/blog/2018/11/how-to-duplicate-a-table-in-mysql.html ## Ref: https://electrictoolbox.com/copy-table-mysql-create-table-like/ ## Ref: https://guides.wp-bullet.com/advanced-wordpress-search-replace-database-with-linux-using-sed/ ## Ref: https://stackoverflow.com/questions/1917021/mysqldump-table-names-prefix ## Ref: https://dba.stackexchange.com/questions/8869/restore-mysql-database-with-different-name ## Ref: https://linuxize.com/post/how-to-back-up-and-restore-mysql-databases-with-mysqldump/ ## Ref: https://stackoverflow.com/questions/12677037/how-to-import-a-mysql-dump-from-command-line-with-overwrite ## Ref: https://dba.stackexchange.com/questions/35847/mysqldump-flush-privileges-option ## Ref: https://stackoverflow.com/questions/11263018/mysql-ignore-errors-when-importing ## Ref: https://askubuntu.com/questions/76808/how-do-i-use-variables-in-a-sed-command ## Ref: https://stackoverflow.com/questions/54460445/sed-permission-denied-on-temporary-file ## Ref: https://www.unix.com/shell-programming-and-scripting/31583-wildcards-sed.html ## Ref: https://stackoverflow.com/questions/9189120/using-sed-with-wildcard ## Ref: http://helpdoco.com/Linux-Unix/sed-cannot-rename-permission-denied.htm ## Ref: https://precisionsec.com/changing-the-wordpress-site-url-using-the-mysql-command-line/ ## Ref: https://stackoverflow.com/questions/20033648/how-to-run-mysql-command-on-bash ## Ref: https://polevaultweb.com/2014/03/5-ways-synchronise-wordpress-uploads-across-environments/ ## Ref: https://blog.room34.com/archives/5091/wheres-the-option-to-change-the-uploads-path-in-wordpress-3-5/ ## Ref: https://premium.wpmudev.org/blog/sync-staging-live-sites-wordpress/ ## Ref: https://poststatus.com/functions-constants-referencing-wordpress-content-uploads-plugin-directories/ ## Ref: https://phoenixnap.com/kb/rsync-exclude-files-and-directories ## Ref: http://qdosmsq.dunbar-it.co.uk/blog/2013/02/rsync-to-slash-or-not-to-slash/ ## Ref: http://lucasb.eyer.be/snips/rsync-skipping-directory.html ## Ref: https://stackoverflow.com/questions/1583219/how-to-do-a-recursive-find-replace-of-a-string-with-awk-or-sed ## Ref: https://www.jamesnimmo.co.nz/2020/04/16/create-a-dev-copy-of-your-wordpress-site-with-wordops/ ## Ref: https://serverfault.com/questions/105206/rsync-exclude-files-that-are-over-a-certain-size ## Ref: https://man7.org/linux/man-pages/man1/rsync.1.html ## Ref: https://stackoverflow.com/questions/9952000/using-rsync-include-and-exclude-options-to-include-directory-and-file-by-pattern ## Ref: https://askubuntu.com/questions/476041/how-do-i-make-rsync-delete-files-that-have-been-deleted-from-the-source-folder ## Ref: https://www.cyberciti.biz/faq/linux-bash-delete-all-files-in-directory-except-few/ ## Ref: https://superuser.com/questions/783622/how-to-make-grep-command-return-entire-matching-line ## Ref: https://stackoverflow.com/questions/22319345/copy-lines-containing-word-from-one-file-to-another-file-in-linux ## Ref: https://stackoverflow.com/questions/6749128/store-output-of-sed-into-a-variable ## Ref: https://wordpress.org/support/topic/fatal-error-call-to-undefined-function-wp-in-home-content-06-9767406-html-wp/ ## Ref: https://stackoverflow.com/questions/11245144/replace-whole-line-containing-a-string-using-sed/36195381#36195381 ## Ref: https://www.gnu.org/software/sed/manual/html_node/sed-commands-list.html ## Ref: https://www.geeksforgeeks.org/sed-command-in-linux-unix-with-examples/ ## Ref: https://docs.woocommerce.com/document/subscriptions-handles-staging-sites/ ## Ref: https://linuxize.com/post/bash-if-else-statement/ ## Ref: https://wpengine.com/support/wordpress-serialized-data/ ## Ref: https://wp-staging.com/serialized-data-wordpress-important/ ## Ref: https://stackoverflow.com/questions/15138893/fix-serialized-data-broken-due-to-editing-mysql-database-in-a-text-editor ## Ref: https://interconnectit.com/products/search-and-replace-for-wordpress-databases/ ## Ref: https://wordpress.stackexchange.com/questions/9076/why-is-my-database-import-losing-text-widget-data ## Ref: https://gist.github.com/gmartellino/4692252 ## Ref: https://stackoverflow.com/questions/6837061/mysqlimport-error-1045-access-denied ## Ref: https://community.localwp.com/t/database-empty-after-import-help/2820 ## Ref: https://swiftheadline.com/check-a-value-in-a-mysql-database-from-a-linux-bash-script-cloudsavvy-it/ ## Ref: https://www.cloudsavvyit.com/1081/check-a-value-in-a-mysql-database-from-a-linux-bash-script/ ## Ref: https://explainshell.com/explain?cmd=mysql+-BNe ## Ref: https://www.tutorialspoint.com/mysql-query-to-fetch-only-a-single-field-on-the-basis-of-boolean-value-in-another-field ## Ref: https://stackoverflow.com/questions/20878089/php-and-mysql-select-a-single-value ## Ref: https://linuxconfig.org/rsync-exclude-directory ## Ref: https://phoenixnap.com/kb/rsync-exclude-files-and-directories ## Ref: https://wordpress.stackexchange.com/questions/275305/use-staging-media-files-on-development-site-in-wordpress ## Ref: https://stackoverflow.com/questions/425158/skip-certain-tables-with-mysqldump ## Ref: https://www.madboa.com/blog/2018/07/17/rsync-bash-vars/ ## Ref: https://stackoverflow.com/questions/5767062/how-to-check-if-a-symlink-exists ## Ref: https://wordpress.stackexchange.com/questions/408756/best-way-to-search-and-replace-within-serialized-database-strings ## Ref: https://serverfault.com/questions/801875/in-an-rsync-how-do-i-exclude-all-directories-that-match-a-pattern ## Ref: https://unix.stackexchange.com/questions/83394/rsync-exclude-directory-not-working ## Ref: https://github.com/littlebizzy/slickstack/issues/150 ## SS_EOF