tekhead.org

The following is simply a gathering of easy hints, tips and scripts for MySQL beginners (and a memory aid for me!):

Useful MySQL queries

List all your databases and their total sizes

SELECT table_schema “Database Name”, sum( data_length + index_length ) / 1024 / 1024 “Database Size in MB”

FROM information_schema.TABLES GROUP BY table_schema ;

List all your tables in a specific database and their total sizes

SELECT table_schema “Database Name”,
table_name “Table Name”,
sum( data_length + index_length ) / 1024 / 1024 “Table Size in MB”
FROM information_schema.TABLES
WHERE table_schema like “[insertyourdatabasenamehere]“
GROUP BY table_name
ORDER BY table_schema,table_name;

Dump a database contents out to a gzipped file and skip certain tables

mysqldump -u[username] -p [databasename] –ignore-table=[insertyourdatabasenamehere].[tabletoignore1]

–ignore-table=
[insertyourdatabasenamehere] .
[tabletoignore2] | gzip -c > sqlfile.sql.gz

Run an SQL script file against a database

mysql -u[username] -p [databasename] < sqlfile.sql

Easy!