
Creating a wordpress staging environment requires 4 things. Here we are assuming that the staging environment is on the same server.
Requirements for creating a wordpress staging environement
- Clone Database – Each Time
- Copy the code – Only the wp-content folder
- Edit wp-config to point to the Staging Database (single site only) plus the Staging Domain (multisite) – Once Only
- Update the wp-options table with the Staging Domain (single site), Update the wp_blogs, wp_site, wp_options, wp_1_options, etc tables with the Staging Domain (multisite) – Each Time
So how to to achive creating a wordpress staging environement with a single script? Here are the steps.
Step 1: Create staging.sh and define variables
Create a file named staging.sh inside a directory named staging (preferably outside your public_html folder)
Define all the variables related to your production and staging database connection as shown below
1 2 3 4 5 6 7 8 9 10 11 12 13 |
#!/bin/sh # (1) set up all the variables FILE=/home/USER/staging/staging_db_export.sql BATCH_FILE=/home/USER/staging/batch.txt DBSERVER=127.0.0.1 PROD_DATABASE=ADD_PROD_DATABASE_NAME_HERE STAG_DATABASE=ADD_STAGING_DATABASE_NAME_HERE PROD_USER=ADD_PROD_DATABASE_USER_NAME PROD_PASS=ADD_PROD_DATABASE_PASSWORD STAG_USER=ADD_STAGING_DATABASE_USER_NAME STAG_PASS=ADD_STAGING_DATABASE_PASSWORD BIN_DIR=/usr/bin/ |
Step 2: MySQL Dump of the Production database
Add below code to staging.sh file to get MySQL Dump of the current production database
1 2 |
${BIN_DIR}mysqldump --lock-tables=false --user=${PROD_USER} --password=${PROD_PASS} ${PROD_DATABASE} > ${FILE} echo "${FILE} was created" |
Step 3: Export the Database to the Staging database
Again add below code to staging.sh. Here we are connecting to staging database and importing the sql file created in Step 2
1 2 |
${BIN_DIR}mysql --host=localhost --user=${STAG_USER} --password=${STAG_PASS} ${STAG_DATABASE} < ${FILE} echo "Database imported to staging successfully" |
Step 4: Batch File
Now create a new file within the staging directory named as batch.txt. Inside this batch file we will write all the queries we want to run to rename the production instances within the staging database to refer to the staging environment. The batch file will hugely depend on the type of site setup you have i.e. single site or multisite
If you have a single site then its very easy. Add below query to the file. Remember to replace PROD_DOMAIN and STAG_DOMAIN with the respective names. e.g. www.myproddomain.com
1 |
update wp_options set option_value = replace(option_value,'PROD_DOMAIN','STAG_DOMAIN'); |
For mulsite it is bit complicated
1 2 3 4 |
update wp_blogs set domain = replace(domain,'PROD_DOMAIN','STAG_DOMAIN'); update wp_site set domain = replace(domain,'PROD_DOMAIN','STAG_DOMAIN'); update wp_options set option_value = replace(option_value,'PROD_DOMAIN','STAG_DOMAIN'); update wp_1_options set option_value = replace(option_value,'PROD_DOMAIN','STAG_DOMAIN'); |
Now just repeat the last time changing the number in wp_1_options to the number of the subsites
Step 5: Run the Batch file queries
To run the batch file queries add below code to staging.sh file
1 2 |
${BIN_DIR}mysql --host=localhost --user=${STAG_USER} --password=${STAG_PASS} ${STAG_DATABASE} < ${BATCH_FILE} echo "Enviromental variable are fixed" |
Step 6: Copy WordPress files
This can be done by a simple cp (copy command).
1 |
cp -R /home/PROD_USER/public_html /home/STAG_USER/public_html |
Step 7: Edit wp-config on the Staging
Now the last thing is to edit the wp-config file to point to the staging database. Thats all.
Finally just running the staging.sh script through SSH will set up the staging server.
Step 8: Weekly Cron Job
To refresh/update the staging server every week add cp command to staging.sh file and this time just copy the wp-content folder. You could also copy differences i.e. last changes only using rsync command.
Then set up a cron job to run staging.sh to run every week
[…] This is very simple way of copying the live server database to the remote server. Read how to create a script to copy database from the live server to the remote server automatically. For this method it is not required to have your database accessible over the network however the […]
[…] This is very simple way of copying the live server database to the remote server. Read how to create a script to copy database from the live server to the remote server automatically. For this method it is not required to have your database accessible over the network however the […]