Edit this page | Blame

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

(made with skribilo)