tungsten-slave-createcachetables.sh

#!/bin/bash

# tungsten-slave-createcachetables.sh

# Tungsten is not extracting data for the following pattern (Drupal 6 and 7):
# replicator.filter.replicate.ignore=*.access,*.cache,*.cache_*,*.ctools_*_cache,*.views_*_cache,*.sessions,*.watchdog
# This means that the tables would not exist on the slave. It would be ideal to have
# the tables exist, but be empty.
# This script will create empty tables for any table matching the pattern in 'grep' commands below.
# It is manually set to match the same replicator.filter.replicate.ignore in the tungsten config.

# Depedent on:
# - jq (`yum install jq`) to parse JSON
# - /var/lib/tungsten/.my.cnf with user=tungsten and password under [client]

# List all serviceNames where the serviceType = remote
for serviceName in $(/opt/continuent/replicator/tungsten/tungsten-replicator/bin/trepctl services -json | jq -M '.[] | select(.serviceType == "remote") | .serviceName' | sed 's/^\"//g; s/\"$//g'); do
  # Use the masterConnectUri value to find the real hostname the service is using to
  # connect to the master
  hostName=$(/opt/continuent/replicator/tungsten/tungsten-replicator/bin/trepctl -service ${serviceName} status -json | jq -M '.masterConnectUri' | sed 's|^"||; s|"$||; s|thls\?://||; s|:[0-9]*/$||')
  echo "- $hostName"

  # List all databases except for any tungsten and built-in MySQL databases
  for database in $(ssh $hostName "mysql --defaults-file=/var/lib/tungsten/.my.cnf --skip-column-names -e'SHOW DATABASES;'" | grep -Ev '^(mysql|(information|performance)_schema|tungsten_.*)$'); do
    echo "  - $database"

    # Make sure the remote database also exists locally before proceeding
    if [[ -z "$(mysql --defaults-file=/var/lib/tungsten/.my.cnf -qfsBe "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='${database}'" 2>&1)" ]]; then
      echo "    !! DATABASE ${database} DOES NOT EXIST LOCALLY, SKIPPING ${database}"

      # break from "for database in" for this database and move onto the next one
      break
    fi

    # List only tables that match the same pattern used by
    # replicator.filter.replicate.ignore in Tungsten
    for table in $(ssh $hostName "mysql --defaults-file=/var/lib/tungsten/.my.cnf --skip-column-names $database -e'SHOW TABLES;'" | grep -E '^(cache|cache_.*|ctools_.*_cache|views_.*_cache|sessions|watchdog)$'); do
      echo "    - $table"

      # Will return 1 if the table exists locally, 0 if it doesn't
      tableExists=$(mysql --defaults-file=/var/lib/tungsten/.my.cnf --skip-column-names -s -e"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '${database}' AND table_name = '${table}';")

      # Create the empty table if it doesn't exist
      if [[ $tableExists -ne 1 ]]; then
        ssh $hostName "mysqldump --defaults-file=/var/lib/tungsten/.my.cnf --add-drop-table --no-data $database $table" | mysql --defaults-file=/var/lib/tungsten/.my.cnf $database
        if [[ $? -eq 0 ]]; then
          echo "      + copied."
        fi
      fi

    done # $table
  done # $database
done # $serviceName