Created
August 7, 2024 16:41
-
-
Save NeilMasters/55832c168e9f1fa2bd8920fe4aeaf16c to your computer and use it in GitHub Desktop.
Generate size data for all databases in a mysql server
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/bash | |
| # A simple bash script which will generate 2 csv files; database sizes and | |
| # table sizes for all databases. | |
| # | |
| # This does use performance_schema so ya know... take it with a pinch of salt. | |
| CLUSTER_HOST=$1 | |
| CLUSTER_PORT=3306 | |
| CLUSTER_REF=$2 | |
| INCLUSION=$3 | |
| # Obtain the database and table sizes for each database out of | |
| # performance_schema | |
| if [ "${4}" = "" ]; then | |
| CLUSTER_USERNAME=$(aws secretsmanager get-secret-value --region eu-west-2 --secret-id "{REMOVED}" --query SecretString --output text | jq -r '.username') | |
| CLUSTER_PASSWORD=$(aws secretsmanager get-secret-value --region eu-west-2 --secret-id "{REMOVED}" --query SecretString --output text | jq -r '.password') | |
| else | |
| CLUSTER_USERNAME=$4 | |
| CLUSTER_PASSWORD=$5 | |
| fi | |
| LIST_DATABASES_QUERY="SELECT TABLE_SCHEMA as db FROM information_schema.tables WHERE TABLE_SCHEMA LIKE '${INCLUSION}' GROUP BY db;" | |
| function execQuery () { | |
| QUERY=$1 | |
| OUTPUT=$2 | |
| MYSQL_PWD="${CLUSTER_PASSWORD}" mysql -h "${CLUSTER_HOST}" -P "${CLUSTER_PORT}" -u "${CLUSTER_USERNAME}" -N -e "${QUERY}" >> "./output/${CLUSTER_REF}-${OUTPUT}.csv" | |
| } | |
| function dbSize() { | |
| BASE_QUERY="select concat(table_schema, ', ', round(sum(data_length + index_length) / 1024 / 1024, 2)) from information_schema.TABLES where table_schema = 'INJECT_DB' group by table_schema;" | |
| execQuery "${BASE_QUERY/INJECT_DB/"$1"}" "db-sizes" | |
| } | |
| function tableSizes() { | |
| BASE_QUERY="select concat(table_schema, ', ', table_name, ', ', round(((data_length + index_length) / 1024 / 1024), 2)) from information_schema.TABLES where table_schema = 'INJECT_DB' order by (data_length + index_length) desc;" | |
| execQuery "${BASE_QUERY/INJECT_DB/"$1"}" "table-sizes" | |
| } | |
| # For every databases on this cluster | |
| while IFS=$'\t' read db; do | |
| dbSize "${db}" | |
| tableSizes "${db}" | |
| done < <(MYSQL_PWD=$CLUSTER_PASSWORD mysql \ | |
| -h "$CLUSTER_HOST" \ | |
| -P $CLUSTER_PORT \ | |
| -u $CLUSTER_USERNAME -N -e \ | |
| "${LIST_DATABASES_QUERY}") |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment