Skip to content

Instantly share code, notes, and snippets.

@scottalan
Last active May 25, 2018 15:09
Show Gist options
  • Save scottalan/91746818ee3f30aa91e9a847502af7f5 to your computer and use it in GitHub Desktop.
Save scottalan/91746818ee3f30aa91e9a847502af7f5 to your computer and use it in GitHub Desktop.
mysql : query
SELECT table_name AS "Tables", ROUND(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = DATABASE() AND (data_length + index_length) > ${SIZE} ORDER BY (data_length + index_length) DESC;
# This will describe all tables at once.
mysql -N -u${DB_USER} -p${DB_PASSWORD} ${DB_URL} --execute="show tables" | while read table; do mysql -u${DB_USER} -p${DB_PASSWORD} ${DB_URL} --execute="describe $table \G"; done
# Dump each table individually with a limit
mysql -N -h ${DB_URL} -u${DB_USER} -p${DB_PASSWORD} ${DATABASE} --execute="show tables" | while read tablename; do mysqldump -h ${DB_URL} -u${DB_USER} -p${DB_PASSWORD} --where="1=1 LIMIT 10" ${DATABASE} $tablename > $tablename.sql; done
# Dump the first $N number of rows from each table in a single dump
docker run --name mysqldump -it --rm --entrypoint=mysqldump outrigger/mariadb:10.2 -h ${DB_URL} -u${DB_USER} -p${DB_PASSWORD} --opt --where="1=1 LIMIT ${N}" ${DATABASE} | gzip > ${DATABASE}_$(date +%Y-%m-%d).sql.gz
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment