paulandoman/ghost-migration-help.md

## ghost-migration-help.md

      
    Raw
  

              ghost-migration-help.md
            
          
    GHOST Migration Help 👻

Background

Additional information on gh-ost migrations
1. Setting up the command


These steps assume a PR has been created and approved (DON'T MERGE YET)
Generate command (output should look something like below)

  export MASTER_DB_PASSWORD=""
  gh-ost \
  --user="root" \
  --password="${MASTER_DB_PASSWORD}" \
  --port=3306 \
  --host="ghost-db.market.customer.envato.net" \
  --database="marketplace_production" \
  --table="buyer_fee_credit_notes" \
  --alter="DROP COLUMN \`au_gst_tax_credit_note\`,DROP COLUMN \`eu_vat_tax_credit_note\`" \
  --assume-rbr \
  --hooks-path="/opt/ghost/hooks" \
  --panic-flag-file=/opt/ghost/flags/ghost.panic.flag \
  --postpone-cut-over-flag-file=/opt/ghost/flags/ghost.postpone.flag \
  # [--test-on-replica | 
  --allow-on-master 
  # | --migrate-on-replica] \
  --execute

Un-comment to allow on master and execute.

2. Executing the command


Connect to customer market cron box.

  ssh customer_market_cron

Go to edbot in Slack and find out who the Customer Primary on call person is.
Go to the #customer-on-call channel and let them know that we are about to run a gh-ost migration.
Start Tmux.

  tmux new -s paulanderson-ghost


Create a bash script containing the above command (with MASTER_DB_PASSWORD replaced - Enrico sent password via Keybase).
Execute script.

3. Checking the results


Once output says Copy: 100% and ETA: due  then you can double check the data using checksums.
(optional) To ensure you have access to the Replica DB, you can try and connect via Sequel Pro.

  MySQLHOST - reporting-db.customer.envato.internal
  Username - paul.anderson
  Password - (this can be found in keybase conversation with Ross Simpson)
  Database - marketplace_production
  SSH Host - market-bastion.ap-southeast-2.customer.envato.com
  SSH User - paulandoman

Select checkbox that says [] Connect using SSL.
Note. The Client knows where to find the private ssh key.

Create a bash file ghost-checksum.sh file
Modify the file to contain the following..
You need to adjust the original table eg. buyer_fee_credit_notes, and ghost-migrated table eg. _buyer_fee_credit_notes_gho names passed in as arguments (for each time you run the comparison)
Get the list of column names via Sequel Pro using right click 'Copy Create Table Syntax'

  #!/usr/bin/env bash
    
  # https://stackoverflow.com/c/envato/questions/40/41#41

  limit=100000
  db_user="paul.anderson"
  cert="~/Documents/Work/Certificates/rds-combined-ca-bundle.pem"

  table="$1"
  [ -z $table ] && echo "Please provide a table name" && exit 1

  query="SELECT id, buyer_fee_invoice_id, invoice_document_number, order_id, to_user_id, total, document_number,   eu_vat_charged, created_at, updated_at, from_billing_details_id, to_billing_details_id, tax_type FROM $table ORDER BY id LIMIT $limit"

  # Run the query using the tunnel
  mysql -h 127.0.0.1 -P 3307 -u "$db_user" -p --ssl-mode=REQUIRED --ssl-ca="$cert" -e "$query" marketplace_production | md5 > "$table-$limit-checksum.txt"

  echo "MD5: "
  cat "$table-$limit-checksum.txt"

Create an SSH tunnel pointing to the two different tables (note change the table names).

  ssh -NT -L 3307:reporting-db.customer.envato.internal:3306 customer_market_bastion_ap_southeast
  ./ghost-checksum.sh buyer_fee_credit_notes
  ./ghost-checksum.sh _buyer_fee_credit_notes_gho
Note you will be prompted for your SQL password.
The output should look something like this ..
  paulanderson ~/Desktop () $ ./ghost.sh _buyer_fee_credit_notes_gho
  Enter password: 
  MD5: 
  81b7058cb5d06e7a89c1f3ed3c90922e


Close the SSH tunnel, and return to your original Tmux session (MAKE SURE TO NAME THIS _ MAKES IT EASIER TO FIND)
Create a new window with CTRL+B, %
In the new window un-postpone the socket file. Similar to this ..

  echo unpostpone | nc -U /tmp/gh-ost.marketplace_production.buyer_fee_credit_notes.sock
The copy stream process should die in the other window.
4. Appending to the migration file


Connect to the marketplace_production database (the password for this can be found in 1Password -> Market Monolith -> Marketplace ghost cleanup database user)

  mysql \
  --user=ghost_cleanup \
  --host=primary-database.customer.envato.net \
  --database=marketplace_production \
  -p

Get the version from the migration file (it will be the prefix in the file name - and usually the added line in the structure.sql file)

  eg. 20190501010508


Now insert the version into the schema migrations table

  eg. INSERT INTO schema_migrations (version) VALUES ('20190501010508');

You can double check in the marketplace replica to see that this new value has been appended.
Merge the PR and monitor the deployment as usual.
Here is an example PR https://github.com/envato/marketplace/pull/24526

5. Clean Up and Delete Table


Clean up relevant _del tables.
Connect to cron.
Switch to the marketplace user (using the Marketplace Puppet (Shadow) password)

  sudo su marketplace

Navigate to the latest version of marketplace.

  cd /marketplace/current

Create a Tmux session.

  tmux new -s paulanderson-ghost-migration


Get the table name using SequelPro (may need to refresh table list to clear cache) eg. _buyer_fee_credit_notes_del
Calculate how long the rake tast will take

  (rows / batch_size * 10 seconds delay) / 60 seconds = ~ x minutes


Drain the table - this will delete rows in batches and prevent a spike on resource usage.

  eg. RAILS_ENV=production bundle exec rake db:drain_table[_buyer_fee_credit_notes_del,2500]

Drop the table.

  eg. DROP TABLE _buyer_fee_credit_notes_del;