MySQL #

Working with JSON Columns on MySQL #

SELECT *
FROM <tbl>
WHERE
    JSON_LENGTH(JSON_EXTRACT(<JSON_COLUMN>, '$.age')) > 20;

SELECT *
FROM <tbl>
WHERE <JSON_COLUMN>->>'$.enabled' = 'true';

Show active queries on MySQL #

show full processlist;

Kill a process on AWS RDS MySQL #

CALL mysql.rds_kill(<PROCESSID>)

Find all usages of a view on MySQL #

SELECT *
FROM information_schema.VIEWS
WHERE table_schema = '<DB_NAME>'
AND view_definition LIKE '%<VIEW_NAME>%';

Require SSL for a user on MySQL #

UPDATE mysql.user SET ssl_type = 'ANY' WHERE user = '<USER_NAME>';
FLUSH PRIVILEGES;

Move database schema from one MySQL instance to another #

The following applies for Aurora MySQL, v 8.0.mysql_aurora.3.08.2 and Amazon Linux v3 (arm64 graviton)

Install mysql-shell

sudo dnf install -y https://repo.mysql.com/mysql80-community-release-el9-1.noarch.rpm
sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022 
sudo dnf install -y mysql-shell --nogpgcheck

Set Environment variables

# SOURCE (where the schema currently lives)
export SRC_HOST=""
export SRC_USER=""
export SRC_DB=""

# Where to put the dump on the local filesystem
export DUMP_DIR="/tmp/dump_${SRC_DB}"

# DESTINATION (cluster you’re copying into)
export DST_HOST=""
export DST_USER=""
export DST_DB=""

Export data

mysqlsh --ssl-mode=REQUIRED \
  --user="$SRC_USER" --host="$SRC_HOST" --password \
  -- util dump-schemas "$SRC_DB" \
  --output-url="file://$DUMP_DIR" \
  --threads=8 \
  --compatibility=strip_definers,strip_restricted_grants

Import Data into existing empty schema on destination database

mysqlsh --ssl-mode=REQUIRED \
  --user="$DST_USER" --host="$DST_HOST" --password \
  -- util load-dump "$DUMP_DIR" --threads=8 --schema="$DST_DB"