Database Sync – Corefinity

Pre-requisites

URL of the live and UAT environment

Working SSH access to both the live and the UAT environment:

You can find this information in Control Panel > Websites > Environments

ssh web@access.gcp.lon1.{{domain}}.prod.cfstack.com -p 5xxx

You may also need the following:

Basic Auth details for the UAT environment

⚠️

If you do not have any of the above details, please raise a ticket

Obtaining UAT environment SSH Key

You will need to obtain the SSH public key for the UAT environment .

SSH into the UAT environment then run:

cat ~/.ssh/id_rsa.pub

Copy the key

⚠️

Please raise a ticket if this is not present.

Transferring SSH Key

We will now add the key to the live production environment.

SSH into the live production environment, replace {{ssh_key}} with the key in your clipboard and run this:

The “” are needed.

echo "{{ssh_key}}" >> ~/.ssh/authorized_keys

Check your key has been added by running:

tail -n 1 ~/.ssh/authorized_keys

Dumping the database

The next stage is to dump the database from the live production environment. Please ensure you are in the live environment.

Disable Crons (Magento2 only)

This is needed to avoid any database locks.

Run this command on the live server:

crontab -l | sed '/magento cron:run/ s/^/#/' | crontab -

In the shared folder

This is needed so the dump file will persist.

If you have a ‘var/www_shared’ folder please go to:

This file path exists for mainly magento2/VSF environments on the V2 pipeline

cd /var/www_shared/{{domain}}/

If this file path does not exist please go to:

This file path will be for mostly wordpress/laravel environments (V1 pipeline)

cd /var/www_m2/{{domain}}/shared/

Perform the dump

Feel free to change the database dump name

mysqldump --quick --single-transaction -h  ${DB_HOST} -u root -p${ROOT_DB_PASSWORD} ${APP_DB_NAME} | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > database.sql

Re-enable the crons (Magento2 only)

crontab -l | sed '/#.*magento cron:run/ s/^#//' | crontab -

⚠️

Do not forget to do this!

Transferring the database

Now go to the UAT environment, we will transfer the database to this environment ready to import.

In the shared folder

If you have a ‘var/www_shared’ folder please go to:

cd /var/www_shared/{{domain}}/

If this file path does not exist please go to:

cd /var/www_m2/{{domain}}/shared/

Transfer the database file

💡

It will help to have the production ssh details here as port and hostname are required

For file paths www_shared:

e.g. scp -P 5000 web@access.gcp.lon1.cf.prod.cfstack.com:/var/www_shared/corefinity.com/database.sql ./

scp -P {{port_production}} web@{{hostname_production}}:/var/www_shared/{{domain}}/database.sql ./

For file paths www_m2:

e.g. scp -P 5000 web@access.gcp.lon1.cf.prod.cfstack.com:/var/www_m2/corefinity.com/shared/database.sql ./

scp -P {{port_production}} web@{{hostname_production}}:/var/www_m2/{{domain}}/shared/database.sql ./

Importing the database

Finally, we import the database into our UAT database. Ensure you are on the UAT environment:

pv database.sql | mysql -h ${DB_HOST} -p${ROOT_DB_PASSWORD} -uroot ${APP_DB_NAME}

⚠️

If this errors or does not complete, please raise a ticket with the error/issue.

Post-requisites

Magento2

You will need to update the URLs in the database for the UAT environment.

Access database

mysql -h ${DB_HOST} -p${ROOT_DB_PASSWORD} -uroot ${APP_DB_NAME}

Base URLs

Check the production URL:

core_config_data is standard , yours may have a prefix; run show tables;

SELECT * FROM core_config_data WHERE path like '%url%';

Search and replace the URL:

e.g. UPDATE core_config_data SET value = replace(value, 'https://live.cfstack.com/', 'https://uat.cfstack.com/');

UPDATE `core_config_data` SET `value` = replace(value, 'https://{{production_url}}/', 'https://{{UAT_url}}/');

Cookie Domain

You may also need to update the cookie domain

Check for cookie domain:

SELECT * FROM core_config_data WHERE path like '%cookie%';

Example of cookie domain that will need updating:

334 | default |        0 | web/cookie/cookie_domain   | live.cfstack.com | 2023-12-05 16:41:25 |

Update example query:

e.g. for the above UPDATE core_config_data set value = 'live.cfstack.com' where config_id = 334;

UPDATE core_config_data set value = 'url' where config_id = x;

Wordpress

You will need to update the URLs in the database for the UAT environment

Access database

mysql -h ${DB_HOST} -p${ROOT_DB_PASSWORD} -uroot ${APP_DB_NAME}

Production URLs

Check the production URLs:

wp_options is standard, yours may have a prefix; run show tables;

select * from wp_options where option_name like '%siteurl%' or option_name like '%home%';

Update the production URLs:

??? could be blog/wp. There are usually 2 URLs to change.

update wp_options set option_value = 'https://{{production_url}}/???' where option_id = x;