Handy mysql commands and tips

mysql.sh

#!/bin/sh

set -e
set -u

# I'm just getting acquainted with mysql so cut me some slack ;)

## check the size of the db
# source: http://stackoverflow.com/questions/1733507/how-to-get-size-of-mysql-database 
# per schema
SELECT  table_schema                                        "DB Name", 
        Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM    information_schema.tables 
GROUP   BY table_schema; 

# per table
SELECT  table_schema                                        "DB Name", 
        table_name,
        Round( (data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM    information_schema.tables;

## tuning
# tips: http://www.debianhelp.co.uk/mysqlperformance.htm
# mysql tuning primer script: http://www.day32.com/MySQL/

# check the status of the running server (used key buffers, ...), for tuning
mysqlreport --user=$USER --password=$PASSWORD | less

# check current values
mysqladmin --user=$USER --password=$PWD variables | less

## mysql big deletes
# source: http://mysql.rjweb.org/doc.php/deletebig
# use paritions or delete in chunks...

## dump data and import again
# sourcE: http://stackoverflow.com/questions/5475200/mysql-restoring-a-database-via-mysqldump-does-it-overwrite-the-different-desti?rq=1
# handy options: add-drop-table and add-drop-database

# export
mysqldump -uuser -ppassword SourceDatabase > file.sql

# export per table (and optimize+analyze)
#!/bin/sh

set -e
set -u

USER="REDACTED"
PWD="REDACTED"
DB="REDACTED"

TABLES=$(mysql --user=$USER --password=$PWD -Bse 'show tables' $DB | grep -v 'log')
for TABLE in $TABLES
do
    echo "dumping $TABLE"
    mysqldump -u $USER -p$PWD $DB $TABLE > dump-$TABLE.sql
done

# optimize
echo "optimizing all tables"
mysqlcheck --user=$USER --password=$PWD -o $DB

# analyze
echo "analyzing all tables"
mysqlcheck --user=$USER --password=$PWD -a $DB

# import
mysql -u USER -p DATABASE < file.sql

## check if tables are myisam or innodb (all the ones I inherited seem to be myisam, drat)
SHOW TABLE STATUS FROM `database`;

## optimize tables
# source: http://stackoverflow.com/questions/5474662/mysql-optimize-all-tables
# source2: http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/
mysqlcheck -o <db_schema_name>

# -or- (for all databases
mysqlcheck -o --all-databases
mysqlcheck -o <db_name> -u<username> -p

## analyze tables

## dumping an entire database!
# source: http://dba.stackexchange.com/questions/20/how-can-i-optimize-a-mysqldump-of-a-large-database
mysqldump -h... -u... -p... --hex-blob --routines --triggers --all-databases | gzip > MySQLData.sql.gz