Category: MySQL

The following lets you find all foreign keys setup for a table (or entire database) in MySQL

SELECT    TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE   REFERENCED_TABLE_SCHEMA = '<database>' AND   REFERENCED_TABLE_NAME = '<table>';

Thanks to; https://stackoverflow.com/questions/201621/how-do-i-see-all-foreign-keys-to-a-table-or-column

Below are a bunch of helpful SQL queries to find tables and DB’s using up disk space;

This finds the largest 10 tables across your MySQL server;

SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

This gets the size of each of your databases, ordered smallest to largest;

SELECT table_schema, CONCAT(ROUND( sum((data_length+index_length)/1024/1024)/1024, 2), 'G') AS MB from information_schema.tables group by 1 order by sum(data_length+index_length) asc;

Ref; https://serverfault.com/questions/676669/amazon-rds-instance-slowly-losing-free-diskspace

To save the mysql username & password you use via command line, use the following;

Add ~/.my.cnf file with the following content

[Client]
user=myusernamehere
password=mypasswordhere
host=localhost
database=mydbnamehere

For DB Dumps via command line (in *nix);

Compressing;
mysqldump dbname | gzip -c > myfilename.sql.gz

Resoring DB;
gunzip -c myfilename.sql.gz | mysql dbname