💾 Archived View for thebird.nl › gn-gemtext-threads › issues › systems › mariadb › cleanup.gmi captured on 2023-03-20 at 17:56:01. Gemini links have been rewritten to link to archived content

View Raw

More Information

⬅️ Previous capture (2022-07-16)

-=-=-=-=-=-=-

Clean Up

Tags

• assigned: pjotrp, robw
• status: unclear
• priority: unclear
• type: database administration
• keywords: database, mariadb

Description

Find all larger tables

SELECT TABLE_SCHEMA,TABLE_NAME,DATA_LENGTH FROM information_schema.TABLES WHERE DATA_LENGTH>10000;

The following four tables live in

MariaDB [mysql]> SELECT TABLE_SCHEMA,TABLE_NAME,DATA_LENGTH FROM information_schema.TABLES WHERE ENGINE = 'InnoDB';
+--------------+----------------------+-------------+
| TABLE_SCHEMA | TABLE_NAME           | DATA_LENGTH |
+--------------+----------------------+-------------+
| db_webqtl    | TraitMetadata        |       16384 |
| db_webqtl    | ProbeSetSE           | 24177016832 |
| db_webqtl    | metadata_audit       |       49152 |
| db_webqtl    | mytest1              |  1567621120 |
| db_webqtl    | ProbeSetXRef         |  2836398080 |
| db_webqtl    | GeneInfo             |    23642112 |
| db_webqtl    | mytest2              | 56524537856 |
| mysql        | transaction_registry |       16384 |
| mysql        | innodb_index_stats   |       16384 |
| mysql        | innodb_table_stats   |       16384 |
| mysql        | gtid_slave_pos       |       16384 |
+--------------+----------------------+-------------+
11 rows in set (0.008 sec)

for x in innodb_index_stats innodb_table_stats gtid_slave_pos transaction_registry ; do echo $x ; mysqldump -u webqtlout -pwebqtlout mysql $x > /export3/$x.sql ; done