search

sanger / General-Backlog-Items

Broad bucket to collate backlog items that have no obvious repository
0 stars 0 forks source link

DPL-817 [MySQL] Update deployment project to use MySQL version 8.0 copy of database servers #313

Open yoldas opened 1 year ago

yoldas commented 1 year ago

User Story As PSD, we would like to update deployment project to use the upgraded copy of database servers.

Blocking issues Support for MySQL 5.7 ends in October 2023. After this date, there will be no further bug fixes or security patches for this version of MySQL. By upgrading our databases to MySQL 8.0, we extend the support to at least April 2026. As part of the upgrade process, upgraded copy of MySQL database servers will be provided by DBAs for testing and transition. The deployment project will be updated with the new database configuration for products such as host and port.

Who are the primary contacts for this story PSD

Acceptance criteria

Additional context

Related To

  1. 311

  2. 312

yoldas commented 11 months ago

Upgrade Notes for the PSDD databases (a.k.a. UAT, development)

We use CNAMEs to access the database host. As the DNS records will be updated, we were worried about DNS caching might get in the way. Therefore, we decided to stop OpenStack instances of the applications first and start them again after the upgrade is finished by DBA.

Connect VPN, take a note of the DNS records (you need to compare them after the upgrade if the databases are moved to another machine). Execute host psdd-db and nslookup psdd-db

There were multiple servers for the same applications, which were created during earlier maintenance. As we were trying to reduce the time spent, we took both uat and uat2 versions.

We decided to use terminal commands rather than the UI to avoid mistakes and working faster.

How to use openstack command to stop/start instances (sequencescape-test project). Install OpenStack client brew install openstackclient

Go to https://theta.internal.sanger.ac.uk/ Click “API Access” menu item on the sidebar Click “Download OpenStack RC File” menu button Click “OpenStack RC File” menu item to download You have sequencescape-test-openrc.sh file; keep it.

Execute source sequencescape-test-openrc.sh and enter your OpenStack password when prompted.

List all servers: openstack server list

Stop server, for example asset_audits, asau-uat2 opestack server stop asau-uat2

Start server, for example asset_audits, asau-uat2 opestack server start asau-uat2

Show name and status of summary, for example asau-uat2 openstack server show -c name -c status asau-uat2

Example, list names (include uat and uat2, exclude tol, dsm, smrtlink) openstack server list | grep uat | grep ACTIVE | awk -F'|' '{print $3}' | grep -v 'dsm' | grep -v 'tol' | grep -v smrtlink > names.txt

Example, show name and status of those one by one: for x in $(cat names.txt); do openstack server show -c name -c status ${x} ; done

Example, stop all names one by one for x in $(cat names.txt); do openstack server stop ${x} ; done

Example, start all names one by one for x in $(cat names.txt); do openstack start stop ${x} ; done

We have used the loop commands in the examples to stop all UAT applications. We also excluded ware-uat and ware-uat2 from the names.txt file as unified_warehouse and event_warehouse use MLWHD database; not PSDD.

We also checked connections to the database server using the SQL command:

show processlist;

After the upgrade, as the DNS records were updated, you may have a problem with connecting the new database server from your local machine. Execute host and nslookup commands shown above. They should show different outputs than before as the databases are moved to another server. To clear your machine's DNS cache, either elevate and execute sudo dscacheutil -flushcache or reboot your machine.

yoldas commented 11 months ago

Upgrade Notes for the PSDT databases (a.k.a. training)

List names, include training and ACTIVE, exclude dsm and tol, and sort. There are 13 instances. There are no warehouse instances.

openstack server list | grep training | grep ACTIVE | awk -F'|' '{print $3}' |grep -v dsm | grep -v tol | sort > names.txt

Show name and status of those one by one: for x in $(cat names.txt); do openstack server show -c name -c status ${x} ; done

Stop all names one by one for x in $(cat names.txt); do openstack server stop ${x} ; done

Start all names one by one for x in $(cat names.txt); do openstack start stop ${x} ; done

yoldas commented 11 months ago

Upgrade Notes for the MLWHD databases (a.k.a UAT, development)

We will stop consumers to mlwhd-db instead of stopping the VMs. We will execute the following commands on two hosts, ware-uat and ware-uat2

Stop consumers

sudo monit stop unified_warehouse.warren_client
sudo monit stop event_warehouse.warren_client
sudo monit stop event_warehouse.puma

Start consumers

sudo monit start unified_warehouse.warren_client
sudo monit start event_warehouse.warren_client
sudo monit start event_warehouse.puma

Displaying/Flushing DNS Cache

Open a two terminal connected to the machine. In one of them, use the following command to watch the log:

journalctl -u systemd-resolved -f | grep mlwhd-db

In the other window, use the following command to dump the cache to log

sudo killall -USR1 systemd-resolved

or use the following command to flush the cache.

sudo killall -USR2 systemd-resolved

yoldas commented 11 months ago

Upgrade Notes for the PSDP and MLWH databases (a.k.a. Production)

We will upgrade them together. Coordinate with DBAs. Stop VMs and services before and start them after the upgrades.

PSDP

Go to https://theta.internal.sanger.ac.uk/ Click “API Access” menu item on the sidebar Click “Download OpenStack RC File” menu button Click “OpenStack RC File” menu item to download You have sequencescape-prod-openrc.sh file; keep it.

Execute source sequencescape-prod-openrc.sh and enter your OpenStack password when prompted.

List of names and write to file. We exclude dsm, tol, and ware. We will deal with warehouse separately. openstack server list | grep prod | grep ACTIVE | awk -F'|' '{print $3}' | grep -v 'dsm' | grep -v 'tol' | grep -v 'ware-prod' > names.txt

Show name and status of those one by one: for x in $(cat names.txt); do openstack server show -c name -c status ${x} ; done

Stop all names one by one for x in $(cat names.txt); do openstack server stop ${x} ; done

Start all names one by one for x in $(cat names.txt); do openstack start stop ${x} ; done

Stopping and starting VMs as above should avoid DNS cache problems.

MLWH

Login to ware-prod

Stop consumers

sudo monit stop unified_warehouse.warren_client
sudo monit stop event_warehouse.warren_client
sudo monit stop event_warehouse.puma

Start consumers

sudo monit start unified_warehouse.warren_client
sudo monit start event_warehouse.warren_client
sudo monit start event_warehouse.puma

Displaying/Flushing DNS Cache

Open a two terminal connected to the machine. In one of them, use the following command to watch the log:

journalctl -u systemd-resolved -f | grep mlwh-db

In the other window, use the following command to dump the cache to log

sudo killall -USR1 systemd-resolved

or use the following command to flush the cache.

sudo killall -USR2 systemd-resolved

Additional notes

DNS info before the upgrade:

% host psdp-db
psdp-db.internal.sanger.ac.uk is an alias for mcs19.internal.sanger.ac.uk.
mcs19.internal.sanger.ac.uk has address 172.17.154.45

% nslookup psdp-db
Server:     172.18.255.1
Address:    172.18.255.1#53

psdp-db.internal.sanger.ac.uk   canonical name = mcs19.internal.sanger.ac.uk.
Name:   mcs19.internal.sanger.ac.uk
Address: 172.17.154.45

% host mlwh-db
mlwh-db.internal.sanger.ac.uk is an alias for mcs20.internal.sanger.ac.uk.
mcs20.internal.sanger.ac.uk has address 172.17.154.46

% nslookup mlwh-db
Server:     172.18.255.1
Address:    172.18.255.1#53

mlwh-db.internal.sanger.ac.uk   canonical name = mcs20.internal.sanger.ac.uk.
Name:   mcs20.internal.sanger.ac.uk
Address: 172.17.154.46